CAN I EXECUTE LOOPS ON ENCAPSULATED MACROS?

[_Sas_Beginner_]

Encapsulated macros can only perform one operation, each time I need a piece of code, sometimes I want to use a macro code to perform multiple loop operations, is this OK? Or is there another solution?

%MACRO EXTRACT(DATANAME,IMPORTNAME,MONTH);
DATA &DATANAME ;
SET &IMPORTNAME;
WHERE MONTH(VAR2)=&MONTH;
SUM+VAR3;
MEAN=SUM/_N_;
KEEP VAR2 VAR3 SUM MEAN;
RUN;
%MEND EXTRACT;
%LET JAN=1;
%LET FEB=2;
%LET MAR=3;
%LET APR=4;
%LET MAY=5;
%LET JUN=6;
%LET JUL=7;
%LET AUG=8;
%LET SEP=9;
%LET OCT=10;
%LET NOV=11;
%LET DEC=12;
%EXTRACT(_201701,FIRST_2017,&JAN);
%EXTRACT(_201702,FIRST_2017,&FEB);
%EXTRACT(_201703,FIRST_2017,&MAR);
%EXTRACT(_201704,FIRST_2017,&APR);
%EXTRACT(_201705,FIRST_2017,&MAY);
%EXTRACT(_201706,FIRST_2017,&JUN);
%EXTRACT(_201707,FIRST_2017,&JUL);
%EXTRACT(_201708,FIRST_2017,&AUG);
%EXTRACT(_201709,FIRST_2017,&SEP);
%EXTRACT(_201710,FIRST_2017,&OCT);
%EXTRACT(_201711,FIRST_2017,&NOV);
%EXTRACT(_201712,FIRST_2017,&DEC);
..........
%EXTRACT(_201901,FIRST_2019,&JAN);
/*This seems too verbose, so what are some ways to make your code more concise*/

🤔🤔🤔

[Patrick]

CALL EXECUTE() allows you to generate macro calls that get stacked and then all executed once the data step terminates. 

data _null_;
  do dt='01jan2017'd to '01dec2020'd;
    cmd=cats('%EXTRACT(_',put(dt,yymmn6.),',first_',put(dt,year4.),',', month(dt),  ');' );
    put cmd=;
/*    call execute( cmd );*/
    dt=intnx('month',dt,0,'e');
  end;
run;

[_Sas_Beginner_]

thank you for ur answer,i have tried to run ur code ,it is work!mainly focused on"turn code into characters and store it in variables to run",i think it is very technical.
thank u for letting me learn new knowledge!

[ballardw]

How about don't bother to create all those data sets. Use a WHERE clause the full data set if you really need to subset the data.

Proc print data=first_2017 (where=(month(var2)=1));
run;

Or if you must perhaps a driver macro

%macro driver(dsn, datastem);
%do i = 1 %to 12;
   %let name=&datastem.%sysfunc(putn(&i.,z2.));
   %extract(&name, &dsn,&i);
%end;
%mend;

DSN would be the name of the source data set, datastem would be the desired output name without the 01, 02, etc. Name builds the desiret output name and the loop counter provides the number of the month.

[_Sas_Beginner_]

thank u for ur answer, i have tried to run ur code , mainly focusing on"calling another macro in one macro", this method is very flexible and useful to use, thank u for letting me learn new knowledge!

[whymath]

I think you are looking for an quick way for macro loops, which may like:

%do i=1 %to 12;
  %EXTRACT(_2017%sysfunc(putn(&i.,z2.)),FIRST_2017,&i.);
%end;

I would like to recommand you the %for macro version:

%for(i,in=1:12,do=%nrstr(
  %EXTRACT(_2017%sysfunc(putn(&i.,z2.)),FIRST_2017,&i.);
));

[_Sas_Beginner_]

I have tried to run the second method you recommend and I am a bit confused about what %for means, prompting that it is a macro that needs to be customized and it doesn't seem to work correctly, but I appreciate your answer anyway

[AhmedAl_Attar · Accepted solution]

Hi @_Sas_Beginner_ 

I'm approaching this from these perspectives

• Using named macro parameters rather than positional
• Reding the source data set only once, while producing multiple output data sets
• Providing the flexibility to explicitly pick your min-max months

%MACRO EXTRACT(p_inDsName=, p_year=, p_minMonth=1, p_maxMonth=12);
	%LOCAL l_i;

	DATA
		/* Dynamically construct the output data set names */
		%do l_i=&p_minMonth %to &p_maxMonth; _&p_year.%sysfunc(putn(&l_i,z2.)) %end;
	;

		/* Using DOW Loop for selective iterative processing */
		DO _n_=1 by 1 UNTIL (last.VAR2);
		
			/* Filter the data by specified year */
			SET &p_inDsName(where=(year=&p_year));
			BY VAR2;
			
			SUM+VAR3;
			MEAN=SUM/_N_;
			
			/* Dynamically construct the output statements */
			%let l_i=&p_minMonth;
			if (put(MONTH,month.)=&l_i) then output _&p_year.%sysfunc(putn(&l_i,z2.));
			%do l_i=%eval(&p_minMonth+1) %to &p_maxMonth;
				else if (put(MONTH,month.)=&l_i) then output _&p_year.%sysfunc(putn(&l_i,z2.));
			%end;
		END;

		KEEP VAR2 VAR3 SUM MEAN; 
	RUN;

%MEND EXTRACT;

options mprint; /* Display in the log the SAS statements being processed */

/* Ensure data set properly sorted */
%let g_srcDsName= first_2017;
proc sort data=&g_srcDsName;
	by var2 year;
run;
%EXTRACT(p_inDsName=&g_srcDsName, p_year=2017, p_minMonth=1, p_maxMonth=12);

Hope this helps,

Ahmed

[Tom]

I don't understand what you are doing and why you need any macro code at all.

You appear to be calculating a cumulative SUM and MEAN of some VALUE by MONTH.

data want;
  set have;
  by MONTH ;
  if first.month then call missing(n,sum);
  n+1;
  sum+VALUE ;
  mean=sum/n;
run;

 In your case you have named the MONTH variable as VAR2 and the VALUE variable as VAR3.

And if you don't need all of the intermediate sums and means then you can just use PROC SUMMARY (aka PROC MEANS) to find the SUM and MEAN per month.

proc summary data=have nway;
  by month;
  var VALUE;
  output out=want sum=sum mean=mean n=n ;
run;

And if the data is not already sorted by MONTH you can use CLASS MONTH instead of BY MONTH.

[_Sas_Beginner_]

Thank you very much for your reply. Yes, I have 12 data sets, each of which has more than 60,000 data, but the date of the data is taken as a data row in the form of every day, similar to "2017Jan01 2017Jan02", but I want to calculate its average value in monthly time, and the by statement is invalid

[Tom]

---

@_Sas_Beginner_ wrote:
Thank you very much for your reply. Yes, I have 12 data sets, each of which has more than 60,000 data, but the date of the data is taken as a data row in the form of every day, similar to "2017Jan01 2017Jan02", but I want to calculate its average value in monthly time, and the by statement is invalid

---

That does not help much.  Does the variable actual contain date values (numeric variable with a display format like DATE9 or YYMMDD10 that will display the dates in a human recognizable way)?

Or is the variable a character string?  If so are the values consistent?  So values for the month of January in the year 2017 always start with the string '2017Jan' and never have '2017JAN' or '2017 Jan' or '2017-Jan' instead?

Either way CLASS will work.  You just need to make to use a FORMAT that maps the values for the same month to the same string.

Do the dataset have the YEAR as the last 4 characters of the dataset name?  Then it should be simple to combine them.  You can use a data step VIEW to combine them on-the-fly so you don't have to save the combined values back to an actual dataset.

So for example to produce monthly SUM and MEAN for the variable VALUE of the data for the years 2017 thru 2020 you could use these two steps.

data all_years / view=all_years;
  set  FIRST_2017-FIRST_2020 ;
* For Numeric DATE variable ;
  month = put(date,yymm7.);
/*
* For Character DATE variable ;
  month = substr(date,1,7);
*/
run;
proc summary data=all_years nway;
  class month;
  var VALUE ;
  output out=WANT sum= mean= n= / autoname ;
run;

[_Sas_Beginner_]

data all_years ;
  set  FIRST_2017-FIRST_2023 SECOND_2017-SECOND_2022 ;
MONTH=MONTH(VAR2);
  YEAR=YEAR(VAR2);
run;
proc summary data=all_years nway;
  class MONTH YEAR ;
  var VAR3 ;
  output out=WANT sum= mean= n= / autoname ;
run;

Hello, I modified the code you provided, it is undoubtedly successful, and completely achieved my purpose, indeed this does not require any macro language, and it seems simpler, in fact, I am more concerned with the question: can the macro implement a loop in the process of calling, so that I can write more efficient code in the face of other similar situations, of course, other people's replies have benefited me a lot, I am very grateful to you🤗

[Tom]

If you want to loop over date and/or time intervals then use an OFFSET variable and the INTNX() and INTCK() functions with the appropriate interval name.

In SAS code that might look like:

do offset=0 to intck('month',start,end);
   date = intnx('month',start,offset);
....
end;

In MACRO code you will need to wrap the function calls in %SYSFUNC().

%do offset=0 %to %sysfunc(intck(month,&start,&end));
   %let date = %sysfunc(intnx(month,&start,&offset));
....
%end;

Note the offset and INTNX() is not needed when the intervals are just multiples of the units used to store the values. 

For example to loop by days just use a normal DO loop:

do date=start to end ;

Or to loop by hours just use a time constant for the step value.

do time = start_time to end_time by '01:00:00't ;