BookmarkSubscribeRSS Feed
prhamilton
Calcite | Level 5

Hi, I'm trying to define a variable list for a proc sql command rather than type each instance out. For example, here's the code breaking out each variable into its own table:

 

proc sql;

create table remi.Emp as
select subregion_ID, year, emp
from remi.remisas
where emp is not missing
order by subregion_ID, year;

 

create table remi.r111 as
select subregion_ID, year, r111
from remi.remisas
where r111 is not missing
order by subregion_ID, year;

 

create table remi.r113 as
select subregion_ID, year, r113
from remi.remisas
where r113 is not missing
order by subregion_ID, year;

quit;

 

I would like to have something more efficient lwould give me the same output as above, but I keep getting errors on this code:

 

%let var_list= emp r111 r113

 

proc sql;

create table remi."&var_list" as
select subregion_id, year, "&var_list"
from remi.remisas
where "&var_list" is not missing
order by subregion_id, year;
quit;

 

Thanks in advance for any help!

4 REPLIES 4
Reeza
Super User

Your fairly close, things to check:

 

1. Does your macro variable need to be in quotes? If it doesn't in regular code it likely doesn't in macro code 

2. Write it out for one iteration. In this case you need to loop through your variable list. I'm attaching some sample code that loops through a variable list, hopefully you can expand it to your code.

3. Test your macro first as raw code, second as with a single macro variable, third with the macro variable list, so looping through the macro variables.

 

/*This Loops thoough a set of variables where the variables
are separated by "|". Any other delimiter can be used
and specified in the scan function as well*/
 
%macro loop(varlist);
%let i=1;
%do %while (%scan(&varlist, &i, |) ^=%str());
%let var=%scan(&varlist, &i, |); 
%put &var;

*rest of SAS code goes here;
 
*Increment counter;
%let i=%eval(&i+1);
%end;
%mend;
%let temp=a|b|c|d|e;
%loop(&temp);
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Simple really, the only thing that changes is the one element, so use a datastep do loop and call execute your code:

data _null_;
length i $5; do i="EMP","R111","R113"; call execute('proc sql; create table REMI.'||strip(i)||' as select SUBREGION_ID ,YEAR ,'||strip(i)||' from REMI.REMISAS where '||strip(i)||' is not null order by SUBREGION_ID ,YEAR; quit;'); end; run;

What this will do is create one proc sql for each of the three loop elements.  

FreelanceReinh
Jade | Level 19

@RW9: This is a nice technique to implement the first efficiency tip of the macro documentation: "use a macro only when necessary." Thank you very much for bringing this to my attention by similar posts in the recent past. (I had not been aware that CALL EXECUTE has been around since SAS 6.07.)

 

I suggest two minor corrections:

  1. Inserting length i $4; at the beginning would avoid the truncation of the second and third value of i to "R11".
  2. The variable name EMP in the SELECT statement should be replaced by '||strip(i)||'.
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Thanks, I only wrote it straight out, hadn't tested it, so those things should come up, but I am always forgetting to set lengths.  Call execute is helpful in a lot of instances, I use it a lot, in fact most of my programs are metadata generated.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 3164 views
  • 4 likes
  • 4 in conversation