BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
kajal_30
Quartz | Level 8

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 

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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;

 

View solution in original post

4 REPLIES 4
Reeza
Super User

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;
Tom
Super User Tom
Super User

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;

 

kajal_30
Quartz | Level 8

Thank you so much team for your kind help.

CarmineVerrell
SAS Employee

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)

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

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
  • 4 replies
  • 489 views
  • 1 like
  • 4 in conversation