BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
PeterClemmensen
Tourmaline | Level 20

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 🙂

View solution in original post

1 REPLY 1
PeterClemmensen
Tourmaline | Level 20

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 🙂

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

Discussion stats
  • 1 reply
  • 837 views
  • 10 likes
  • 2 in conversation