I have a working macro %
(yrmt = 202004);
I have 5 values for &yrmt = 202007,202006,202004,202009.202010 ;
I want to run this macro for all these yrmt .can anyone please help.
Here is the code I have tried so far.
data dtmt; Do i= 1 to countw("&yrmt."); dttemp = scan("&yrmt.",i); call execute(%history_data(yrmt = dttemp));
output;
end; run;
Please advise
Thanks
Kajal
Let's first look at the code you wrote to see why it doesn't do what you want.
First let's add an END for your DO loop.
data dtmt;
do i= 1 to countw("&yrmt.");
dttemp = scan("&yrmt.",i);
call execute(%history_data(yrmt = dttemp));
end;
run;
So you are creating a variable named DTTEMP by extracting the next word in the macro variable YRMT. That seems reasonable. Note that DTTEMP will be a character variable but that should not matter. Another thing to note is that you did not specify exactly what delimiter(s) to use in the COUNTW() or SCAN() functions. So if the macro variables also have periods and spaces in addition to commas then you might not get the list of words you expected.
The main issue that the CALL EXECUTE() function wants a STRING. Instead you have just called the macro. That means the macro processor will first run that macro call and all of the SAS code it generates will be dumped into the middle of your CALL EXECUTE() function. You don't say what code the macro actually generates but let's assume that it generates a PROC SORT step. So you tried to run something like this SAS code:
data dtmt;
do i= 1 to countw("202007,202006,202004,202009,202010");
dttemp = scan("202007,202006,202004,202009,202010",i);
call execute(proc sort ; by dttemp; run;);
end;
run;
Instead you want to generate a string to pass to CALL EXECUTE. So perhaps something like this:
call execute('%history_data(yrmt =' || dttemp || ')'));
Note the single quotes to prevent the macro processor from trying to interpret %HISTORY_DATA as a macro call.
But you should also get in the habit of preventing the macro processor from running the macro while CALL EXECUTE() is running. You can delay its execution be wrapping its name in %NRSTR() macro function.
call execute('%nrstr(%history_data)(yrmt =' || dttemp || ')'));
Try it both ways and notice the big difference in how the lines in the SAS log with + in the front that CALL EXECUTE() uses to show the code that ran.
Here are some other things you can do.
1) Use the power of the DO statement. You can use it to iterate of a series of values like: DO X=1,3,4,6,9;
2) Use the CATxx() series of functions to help build the code to push to CALL EXECUTE().
%let yrmt = 202007,202006,202004,202009,202010 ;
data dtmt;
do dttemp= &yrmt ;
call execute(cats('%nrstr(%history_data)(yrmt =',dttemp,')'));
end;
run;
You have to build the string with the variable value and pass that to the macro. You're passing the text value of dttemp.
Creating the string in a separate value allows you to debug your code as well. You can verify that str is valid SAS code.
data dtmt;
Do i= 1 to countw("&yrmt.");
dttemp = scan("&yrmt.",i);
str =catt("%history_data(yrmt = ",
dttemp,
");"
);
call execute(str);
output;
end;
run;
Let's first look at the code you wrote to see why it doesn't do what you want.
First let's add an END for your DO loop.
data dtmt;
do i= 1 to countw("&yrmt.");
dttemp = scan("&yrmt.",i);
call execute(%history_data(yrmt = dttemp));
end;
run;
So you are creating a variable named DTTEMP by extracting the next word in the macro variable YRMT. That seems reasonable. Note that DTTEMP will be a character variable but that should not matter. Another thing to note is that you did not specify exactly what delimiter(s) to use in the COUNTW() or SCAN() functions. So if the macro variables also have periods and spaces in addition to commas then you might not get the list of words you expected.
The main issue that the CALL EXECUTE() function wants a STRING. Instead you have just called the macro. That means the macro processor will first run that macro call and all of the SAS code it generates will be dumped into the middle of your CALL EXECUTE() function. You don't say what code the macro actually generates but let's assume that it generates a PROC SORT step. So you tried to run something like this SAS code:
data dtmt;
do i= 1 to countw("202007,202006,202004,202009,202010");
dttemp = scan("202007,202006,202004,202009,202010",i);
call execute(proc sort ; by dttemp; run;);
end;
run;
Instead you want to generate a string to pass to CALL EXECUTE. So perhaps something like this:
call execute('%history_data(yrmt =' || dttemp || ')'));
Note the single quotes to prevent the macro processor from trying to interpret %HISTORY_DATA as a macro call.
But you should also get in the habit of preventing the macro processor from running the macro while CALL EXECUTE() is running. You can delay its execution be wrapping its name in %NRSTR() macro function.
call execute('%nrstr(%history_data)(yrmt =' || dttemp || ')'));
Try it both ways and notice the big difference in how the lines in the SAS log with + in the front that CALL EXECUTE() uses to show the code that ran.
Here are some other things you can do.
1) Use the power of the DO statement. You can use it to iterate of a series of values like: DO X=1,3,4,6,9;
2) Use the CATxx() series of functions to help build the code to push to CALL EXECUTE().
%let yrmt = 202007,202006,202004,202009,202010 ;
data dtmt;
do dttemp= &yrmt ;
call execute(cats('%nrstr(%history_data)(yrmt =',dttemp,')'));
end;
run;
Thank you so much team for your kind help.
As you didn't include a complete example i have made a test macro below. Here is one method by creating a macro program that calls your history_data macro call with all the values you listed.
options mprint mlogic symbolgen;
%macro history_data(yrt=);
%put its working...here is the value &yrmt;
%mend;
%macro historycall(value);
%let i=1;
%do %until(&yrmt=);
%let yrmt=%scan(&value,&i);
%if &value= %then %put NOTE: Processing complete.;
%else %do;
%history_data(yrt = &yrmt);
%let i=%eval(&i+1);
%end;
%end;
%mend historycall;
%historycall(202007 202006 202004 202009 202010)
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: