topic Re: Creating a variable list to loop through macro in SAS Programming

Creating a variable list to loop through macro

jeattberg — Thu, 15 Feb 2018 00:08:29 GMT

I want to loop through the following code, for each month up to the current month. So if I ran this today, it would run for Jan (01) and Feb (02), and next month it will do the same, but also for Mar (03), and so on. I can't just do a do loop with I=1 to 12 because the DATE variable below is in the format of mm_2018_01. any suggestions?

%MACRO UPDATE_MM(MON=);

PROC SQL;

create table tmp_&MON as

Select DISTINCT *

FROM WORK.SOURCE where DATE = "mm_&cu_yr._&mon" ;

QUIT;

%MEND UPDATE_MM;

I think I need to make a list of the month values, as shown below, and do a %do loop, but I'm not quite sure how.

%let MONTHS= O1 02 03 04 05 06 07 08 09 10 11 12;

%LET TODAY=%SYSFUNC(TODAY(),DATE9.);

%LET CU_MO= %Sysfunc( PutN("&TODAY"D, MONTH2 ) ) ;

Re: Creating a variable list to loop through macro

Reeza — Thu, 15 Feb 2018 00:44:01 GMT

Check the SAS 9.4 Macro appendix for an example of looping through dates in a macro loop.

Re: Creating a variable list to loop through macro

Quentin — Thu, 15 Feb 2018 01:04:58 GMT

If you just want month looping, from one to the current month, maybe something like:

%macro test(dummy) ;
  %local month ;
  %do month=1 %to %sysfunc(today(),month) ;
    %put month: %sysfunc(putn(&month,z2)) ;
  %end ;
%mend test ;

%test()

Re: Creating a variable list to loop through macro

jeattberg — Thu, 15 Feb 2018 02:40:54 GMT

I understand up to this point, I've found a couple ways to generate a list from 01 to current month, but how do I use that dynamic list to loop through my macro code. That's where I'm getting stuck. Thank you so much for your help!

Re: Creating a variable list to loop through macro

Tom — Thu, 15 Feb 2018 03:19:11 GMT

I think you are saying that you have a CHARACTER variable whose value starts with two digit month number?

So the issue is getting those leading zeros.

The Z. format can do that.

%do month=1 to 12;
  %update_mm(mon=%sysfunc(putn(&month,z2)))
%end;

Re: Creating a variable list to loop through macro

Satish_Parida — Thu, 15 Feb 2018 12:33:22 GMT

%MACRO UPDATE_MM(MON=,CU_YR=);
	PROC SQL;
	create table tmp_&MON as
	Select DISTINCT *
	FROM WORK.SOURCE where DATE = "mm_&cu_yr._&mon" ;
	QUIT;
%MEND UPDATE_MM;

data _null_;
run_date=today();			/*Here you can put your date or programetically pass your date*/
cu_yr=put(year(run_date),z4.);
do i=1 to 12;
	mon=put(i,z2.);
	execution_cmd='%UPDATE_MM('||mon||','||cu_yr||')';
	call execute(execution_cmd);
end;
run;

This will work, and it will be better if you pass the cu_yr macro variable to the macro function.

Re: Creating a variable list to loop through macro

Quentin — Thu, 15 Feb 2018 15:15:24 GMT

Assuming you have the macro variable CU_YR defined somewhere outside the macro (it is not created in the sample code you posted), I would think you could just paste your PROC SQL code into my %DO loop, so something like:

%macro test ;
  %local month ;
  %do month=1 %to %sysfunc(today(),month) ;
    %put month: %sysfunc(putn(&month,z2)) ;
    
    PROC SQL;
      create table tmp_%sysfunc(putn(&month,z2)) as
      Select DISTINCT *
      FROM WORK.SOURCE where DATE = "mm_&cu_yr._%sysfunc(putn(&month,z2))" ;
    QUIT;

  %end ;
%mend test ;

Re: Creating a variable list to loop through macro

jeattberg — Thu, 15 Feb 2018 21:26:59 GMT

Thank you so much, this solution worked great. I did not know you could use the %sysfunc equation as part of a variable name, so I learned something new about DO loops and that as well.