NOTE: SQL INTO, Revisited

In Macro Arrays Straight From a Data Set last week I wrote about using SQL's INTO in order to create macro variables (and arrays of macro variables) from PROC SQL. I said that we had to use an arbitrarily large number to define the maximum size of the macro array. Well, Chris Hemedinger was quick to post a comment and point out that with SAS 9.3 there are a few improvements in the SELECT INTO range options.

Not only that but Chris highlighted his Improving on a SAS Programming Pattern post from March last year in which Chris showed the new SAS 9.3 syntax where we can now specify an open-ended range for macro variable names. Apparently, SAS will allocate as many macro variables as are needed to store each value, and the values will be TRIMMED of whitespace by default (but you can control this by specifying NOTRIM before the FROM keyword).

I've reproduced Chris's example below.

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

Thanks for the tip Chris.