topic Re: Run Macro for each month between start month and end month in SAS Programming

Run Macro for each month between start month and end month

Ronein — Wed, 15 Jul 2020 04:29:18 GMT

Hello

Let's say that I have monthly data sets that are published at my work every month.

The name of the data sets included form YYMM

(for example:

t2005 is for May 2020

t2004 is for April 2020

t2003 is for Mar 2020

and so on )

I have a SAS program that perform statistics calculations on each of the data sets from month 2004 (Apr 2020) till last month.

Currently I run it in the way that I need to mention each month that I want to run.

The name of the macro is RRR.

So if I want to run for months 2004,2005,2006 then I need to run the statements:

%RRR(2004);

%RRR(2005);

%RRR(2006);

My question is how to run the macros by define 2 macro parameters: FromYYMM ,ToYYMM

and then run the macro automatically for each month between FromYYMM and ToYYMM

Data t2004;
input x;
cards;
1 10
2 20
3 30
;
run;

Data t2005;
input x;
cards;
1 12
2 18
3 40
4 50
;
run;

Data t2006;
input x;
cards;
1 20
4 48
;
run;

/*Current way to run the macro*/
/*Current way to run the macro*/
/*Current way to run the macro*/
%macro RRR(YYMM)
PROC SQL;
	create table summary&YYMM.  as
	select  sum(x) as Sum_x_&YYMM.	   
	from  t&YYMM.
;
QUIT;
%mend RRR;
%RRR(2004);
%RRR(2005);
%RRR(2006);

/*New way to run the macro*/
/*New way to run the macro*/
/*New way to run the macro*/
%let fromYYMM=2004;
%let ToYYMM=2006;
and now I want to run the macro RRR for each month from fromYYMM till ToYYMM

Re: Run Macro for each month between start month and end month

ChrisNZ — Wed, 15 Jul 2020 04:51:04 GMT

Like this?

%macro RRR(from, to);
  %local i;
  %do i=&from. %to &to.;
    %if %sysfunc(exist(T&i)) %then %do;
      proc sql;
        create table SUMMARY&i. as
        select sum(X) as SUM_X_&i.	   
        from T&i. ;
      quit;
    %end;
  %end;
%mend RRR;

Re: Run Macro for each month between start month and end month

andreas_lds — Wed, 15 Jul 2020 05:05:32 GMT

The problem would be easy to solve, if you had just one dataset and date-variable with the data you now have in the dataset names.

I think that using fromYYMM and ToYYMM as start/end of a loop should do it:

%macro fromTo(fromYYMM=, ToYYMM=);
	%local yymm;
	%do yymm = &fromYYMM %to &ToYYMM;
		%rrr(&yymm);
	%end;
%mend;

Re: Run Macro for each month between start month and end month

Kurt_Bremser — Wed, 15 Jul 2020 07:09:55 GMT

Use a data step to build the loop:

%let fromYYMM=2004;
%let ToYYMM=2006;

data _null_;
period = input("&fromyymm.",yymmn4.);
do while (period le input("&toyymm.",yymmn4.));
  call execute('%nrstr(%rrr(' !! put(period,yymmn4.) !! '))');
  period = intnx('month',period,1,'b');
end;
run;

Insert usual comment about the intelligence (or lack thereof) of using 2-digit years here.

Re: Run Macro for each month between start month and end month

ChrisNZ — Wed, 15 Jul 2020 07:16:50 GMT

> Insert usual comment ...

👍

There is an image above, but it only seems to show when opened in a new tab?

I can't see it. Who can?

Re: Run Macro for each month between start month and end month

Kurt_Bremser — Wed, 15 Jul 2020 07:40:34 GMT

@ChrisNZ wrote:

> Insert usual comment ...

👍

There is an image above, but it only seems to show when opened in a new tab?

I can't see it. Who can?

Do you mean the "thumbs up"? I could see that immediately when viewing the message (Safari, MacOSX).

Re: Run Macro for each month between start month and end month

s_lassen — Wed, 15 Jul 2020 12:27:13 GMT

The code suggested by @ChrisNZ is quite elegant and simple, and it may work when you report over more than one year (except that it may also create a summary table for e.g. t1932, if such a table exists, and you go from 2019 into 2020) . But if you expect the tables to exist, and will rather have an error message when one of them does not, this is an alternative possibility:

%macro RRR(from,to);
  %local yymm;
  %do yymm=&from %to &to;
    %if %substr(&yymm,3,2)=13 %then /* jump to first month of next year */
      %let yymm=%eval(&yymm+88);
    PROC SQL;
	  create table summary&YYMM.  as
	  select  sum(x) as Sum_x_&YYMM.	   
	  from  t&YYMM.
      ;
    QUIT;
    %end;
%mend;

An alternative solution is to collect all the data in one table, and use YYMM as a categorical variable, here is a simple way of doing that:

data all;
  set work.t: indsname=indata;
  length yymm $4;
  yymm=substr(indata,7,4); /* length of name of library + 3 = 7 */
run;

PROC SQL;
	create table summary  as
	select yymm, sum(x) as Sum_x	   
	from  all
	group by yymm
;
QUIT;

You can change the SET statement to e.g. "set work.t19: work.t20: indsname=indata;" if you only want the sums for those two years.

Re: Run Macro for each month between start month and end month

Tom — Wed, 15 Jul 2020 12:47:36 GMT

Convert your YYMM strings into dates and then you can use INTNX() to sequence. Use INTCK() to count the number of months.

%macro loop(start,end);
%local offset sdate edate yymm;
%let sdate=%sysfunc(inputn(&start.01,yymmdd6.));
%let edate=%sysfunc(inputn(&end.01,yymmdd6.));
%do offset=0 %to %sysfunc(intck(month,&sdate,&edate));
  %let yymm=%sysfunc(intnx(month,&sdate,&offset),yymmn4);
  %rrr(&yymm)
%end;
%mend loop;
%loop(2004,2006);

Re: Run Macro for each month between start month and end month

Ronein — Wed, 15 Jul 2020 16:01:31 GMT

Thank you but no.
As you can see there should be use of %let.
%let fromYYMM=2004;
%let ToYYMM=2006;
and then Running of the macro should be done automatically for each month between fromYYMM and ToYYMM

Re: Run Macro for each month between start month and end month

Ronein — Wed, 15 Jul 2020 16:05:13 GMT

Thank you!

Re: Run Macro for each month between start month and end month

ChrisNZ — Wed, 15 Jul 2020 20:59:12 GMT

> I could see that immediately

My browser is playing tricks on me. Thanks Kurt.