Macro list of variables to loop through proc sql

[kevsma]

Hello folks, I have been searching for answers to this but I can't find the best solution so far. I want to write a macro to loop through a lot of variables in a proc SQL join command. Below is my code with one of the variables in the dataset:

proc sql;
create table z1 as
select A.svscd, 
	   A.ufs_mo,
	   B.fym201703
	from Z A
	left join (select distinct svscd, fym201703 from Z where fym201703 ne .) B
	on 	A.svscd=B.svscd;
quit;

the variable "fym201703" is the one I need to create a loop so that the above code can be applied to all variables start with "fym" and it goes by month from 201703 to 202106. Therefore, I need to create multiple datasets like "z1" above for each of the fym variable I am looping through. I don't want to write down all the variables in the code, is there a way to handle this? Thanks a lot!!!

[PaigeMiller · Accepted solution]

UNTESTED CODE since we don't have your data sets

%macro dothis;
    %let startmonth=%sysevalf('01MAR2017'd);
    %let endmonth=%sysevalf('01JUN2021'd);
    %let month=&startmonth;
    %let month1=%sysfunc(putn(&month,yymm6.));
    %let incr=0;
    %do while(&month<=&endmonth);
         /* your sql goes here using variable &month1 instead of fym201703 */
         %let incr=%eval(&incr+1);
         %let month=%sysfunc(intnx(month,&month,&incr,b));
         %let month1=%sysfunc(putn(&month,yymm6.));
    %end;
%mend dothis;
         
    

[Tom]

The code you posted is not creating the variable FYM201703, it is just referencing it.

I suspect that you meant that you need to macro code to calculate the NAME of the variable you want to reference.

To loop over dates use a %DO loop with an integer index and use INTNX() function to calculate the next date in the series.

So if you want to months from March 2017 to June 2021 then use a %DO loop like this:

%let start='01MAR2017'd;
%let end='01JUN2021'd;

proc sql;
%do offset=0 to %sysfunc(intck(month,&start,&end));
  %let varname=FYM%sysfunc(intnx(month,&start,&offset),YYMMN6.);

create table z&offset as
  select A.svscd
       , A.ufs_mo
       , B.&varname
 from Z A
 left join (select distinct svscd, &varname from Z where not missing(&varname)) B
   on A.svscd=B.svscd
;

%end;

quit;

 If you only have the start and end dates in your YYYYMM 6 digit strings then use INPUTN() function to convert them into real dates to drive the INTCK() and INTNX() function calls.

[andreas_lds]

Having variables named like "fym201703" looks like bad data-design, so i suggest to transpose the data moving the year/month information into a variable, because that's where it belongs. Then drop all obs with missing value and you will have a clear and easy to use dataset.

[PaigeMiller]

Agreeing with @andreas_lds as well. Although I posted macro code, the better approach is to have a smarter arrangement of your data (long, not wide), which then makes coding a lot simpler.

[Kurt_Bremser]

What @andreas_lds said. This looks like a case for transposing first, so the "period" becomes data.

Please provide a portion of your dataset in usable form (as a data step with datalines), so we can see what your code is intended to do for a single variable.

In particular we need to know how many observations per svscd exist, and what values are possible in the fym... variables.