Improving on a SAS programming pattern

Earlier this week I described a common programming pattern in the SAS macro language. The pattern sets up a loop for processing each distinct value of a classification variable. The program uses the PROC SQL SELECT INTO feature to populate SAS macro variables. The effect: you can roll your own BY processing in SAS.

I posted the example and received many useful comments, including news about new features in SAS 9.3 that allow us to improve this pattern. Instead of two SELECT statements (one to gather a count, and a second to populate a range of variables), we can now achieve the same with just one SELECT statement (and thus, just one pass through the data):

/* SAS 9.3 approach */
/* Create macro vars with values and a total count of distinct values */
proc sql noprint;
  select distinct TYPE into :varVal1- from SASHELP.CARS;
  %let varCount = &SQLOBS.;
quit;

With the new SAS 9.3 syntax, we can now specify an open-ended range for macro variable names. SAS will allocate as many macro variables as are needed to store each value. The values will be TRIMMED of whitespace by default, but you can control this by specifying NOTRIM before the FROM keyword.

Since we don’t need to know the count ahead of time, we can then safely use &SQLOBS after the SELECT to determine the upper bound of our index.

You can also specify leading zeros in the macro variable range, which can help with sorting the ordinals later. Here’s a revised example:

proc sql noprint;
  /* using leading zero */
  select distinct MAKE into :varVal01- from SASHELP.CARS;
  %let varCount = &SQLOBS.;
quit;

The accompanying image shows the resulting macro variables in the SAS Macro Variable Viewer, neatly collated in numerical order. (Remember that if you do add the leading zeros, you may need to use the Zw.d format when building up the macro variable names within a loop.)