Juletip #18 - Proc SQL and Macro variables.

FransHolm — Fri, 18 Dec 2020 07:00:00 GMT

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

Re: Juletip #18 - Proc SQL and Macro variables.

PeterClemmensen — Fri, 18 Dec 2020 08:47:59 GMT

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 🙂

topic Juletip #18 - Proc SQL and Macro variables. in SAS Community Nordic

Juletip #18 - Proc SQL and Macro variables.

Re: Juletip #18 - Proc SQL and Macro variables.