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 ) ) ;
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 ;
Check the SAS 9.4 Macro appendix for an example of looping through dates in a macro loop.
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()
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!
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 ;
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.
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;
%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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.