Using MACRO inside Proc SQL

[prhamilton]

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!

[Reeza]

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]

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]

@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]

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.