If you are looking to improve performance of your SQL code consider the following options:
Include all your numeric, date or datetime variables first in the SELECT statement and leave the character ones for the end. Character variables are not padded when included last, meaning fewer I/O and lower CPU consumption.
Add the parameter _METHOD to your SQL code. It will write debugging messages to the log about the type of join used by the SQL optimizer.
sqxjm: sort-merge join. Algorithm most commonly used by the optimizer. First it will sort the data files and then perform the join. You can influence the optimizer to try to select this method by adding MAGIC=102 to your proc sql statement.
proc sql _method magic=102;
sqxjndx: index join. Using indexes present in the data sets. You can try to influence the optimizer for this method by adding the data set option IDXWHERE=YES.
dir="ltr" style="margin-right: 0px;" mce_style="margin-right: 0px;" mce_style="margin-right: 0px;">
from library.index_data
(idxwhere=yes)
sqxjhsh: hash join. The smaller table in the join is loaded in memory. Influence the optimizer by increasing the default value of the 64K for the BUFFERSIZE option
proc sql _method
buffersize=1048576;
For more such SAS Tips follow us on Facebook: