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*/
🤔🤔🤔
I'm approaching this from these perspectives
%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
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;
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.
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.);
));
I'm approaching this from these perspectives
%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
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_ 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;
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🤗
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 ;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: