DATA Step, Macro, Functions and more

Creating a variable list to loop through macro

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

Creating a variable list to loop through macro

 

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 ) ) ;


Accepted Solutions
Solution
‎02-15-2018 04:25 PM
PROC Star
Posts: 1,449

Re: Creating a variable list to loop through macro

Posted in reply to jeattberg

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 ;

View solution in original post


All Replies
Super User
Posts: 23,237

Re: Creating a variable list to loop through macro

Posted in reply to jeattberg

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

 

 

PROC Star
Posts: 1,449

Re: Creating a variable list to loop through macro

Posted in reply to jeattberg

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()

 

New Contributor
Posts: 3

Re: Creating a variable list to loop through macro

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!

Solution
‎02-15-2018 04:25 PM
PROC Star
Posts: 1,449

Re: Creating a variable list to loop through macro

Posted in reply to jeattberg

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 ;
New Contributor
Posts: 3

Re: Creating a variable list to loop through macro

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.

Super User
Super User
Posts: 7,932

Re: Creating a variable list to loop through macro

Posted in reply to jeattberg

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;
Frequent Contributor
Posts: 109

Re: Creating a variable list to loop through macro

Posted in reply to jeattberg
%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.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 358 views
  • 0 likes
  • 5 in conversation