Juletip #18 - Proc SQL and Macro variables.

[FransHolm]

I like using Proc SQL in my data handling, and using the flexibility by expanding the code by using SAS macro functionality.

In this example I would like to have some values from a dataset into different macro variables. The issue is, that I don’t know the number of values=macro variables. Therefor I have 3 steps:

1.      First I find the number of variables

2.      Left adjust the macro variable

3.      I put the values into the according number of macro variables

** Step 1 - find the number of variables **;

proc sql noprint;

  select count(*) into :nobs

  from sashelp.class

  where age=14;

quit;

*** Step 2 - To use the macro variable it have to be left adjusted ***;

%let nobs=%left(&nobs.);

%put nobs=&nobs;

*** Step 3 - Values are put into the correct number of macro variables ***;

proc sql noprint;

  select name

    into :name1 - :name&nobs.

          from sashelp.class

    where age=14;

quit;

*** Just to see the content - different versions ***;

%put Name = &name1.;

%put Name = &name2.;

%put Name = &name3.;

%put Name = &name4.;

%put Name = &name1-&&name&nobs;

%put _user_;

I can recommend the book: PROC SQL – Beyond the Basics Using SAS” if you want to learn more about Proc SQL.

Merry Christmas

[PeterClemmensen · Accepted solution]

Thank you for a nice juletip. I recently learned that the SQL Procedure lets us omit step 1 by leaving the part after the dash blank. Proc SQL creates as many macro variables as values being processed and we can capture that number in the &sqlobs. macro variable.

proc sql noprint;
   select name into :name1 - 
   from sashelp.class
   where age=14;
quit;

%put Name = &name1.;
%put Name = &name2.;
%put Name = &name3.;
%put Name = &name4.;
%put Name = &name1-&&name&sqlobs.;

Goes to show just how flexible the SQL Procedure is. I learned this trick from the book Carpenter's Complete Guide to the SAS® Macro Language by @ArtC. Also highly recommendable. 

Merry Christmas out there 🙂