BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
jeattberg
Calcite | Level 5

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
Quentin
Super User

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 ;
BASUG is hosting free webinars Next up: Jane Eslinger presenting PROC REPORT and the ODS EXCEL destination on Mar 27 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.

View solution in original post

7 REPLIES 7
Reeza
Super User

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

 

 

Quentin
Super User

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

 

BASUG is hosting free webinars Next up: Jane Eslinger presenting PROC REPORT and the ODS EXCEL destination on Mar 27 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.
jeattberg
Calcite | Level 5

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!

Quentin
Super User

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 ;
BASUG is hosting free webinars Next up: Jane Eslinger presenting PROC REPORT and the ODS EXCEL destination on Mar 27 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.
jeattberg
Calcite | Level 5

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.

Tom
Super User Tom
Super User

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;
Satish_Parida
Lapis Lazuli | Level 10
%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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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