Macro Variable Tips

Below are a few relatively unknown features related to SAS macro variables.

Most of us know about PROC SQL and the INTO :macro-var option to create macro variables. In this example, I have created a data set containing a numeric column named SSN that is 9 bytes wide. Be sure to add the NOPRINT option to avoid writing to the output window.

The first example has issues because a numeric value over 8 bytes gets converted to scientific notation. Notice the use of the TRIMMED option, new in SAS 9.3, on the &trimmed_range macro variable to strip leading and trailing spaces. Vertical bars were used to illustrate the leading spaces. In the next example, the format = 9. modifier is used to preserve the desired output - no more scientific notation.

The last example uses the unbounded macro variable range which was added in SAS version 9.3. In the past I have seen queries run to find the row limit and then use that value to define the upper boundary. Personally, I have used boundary limits such as :x1 - :x9999 but that is no longer required as you can simply use INTO :x1 - . Both of these techniques will only create the number of macro variables needed. That is if you use either INTO :X1 - :X9999 or INTO :X1 -, only &X1 and &X2 will be created using the below example.

Starting in SAS 9.3 you can use the &=macro-var syntax. According to SAS documentation, if you place an equal sign between the ampersand and the macro variable name of a direct variable reference, the macro variable's name displays in the log along with the macro variable's name. You can see that in the last part of the output below.

data ssns ;
  input ssn ;
  datalines ;
123456789
987654321
;
run ;
 
proc sql noprint ;
   select  count(*)
         , count(*)
   into    :untrimmed_range    
         , :trimmed_range trimmed   
   from    ssns ;
 
   select  ssn 
   into    :a1 - :a&trimmed_range  /* &trimmed_range defines the upper limit */
   from    ssns ;
   
   select  ssn format = 9. /* FORMAT= prevents scientific notation */
   into    :f1 - :f9999
   from    ssns ;
   
   select  ssn format = 9.
   into    :u1 -       /* unbounded macro-variable range */
   from    ssns ;
quit ;  
 
 
%put untrimmed: |&untrimmed_range| ;
untrimmed: |       2|
%put trimmed:   |&trimmed_range| ;
trimmed:   |2|
%put &=a1 ;
A1=1.2346E8
%put &=f1 ;
F1=123456789
%put &=u1 ;
U1=123456789