I have a sas program that runs based on a macro variable - a customer number that is manually input. I would like to have it so that the program will read from a file with a list of customer numbers, and run the whole program for each customer number one at a time until it reaches the end of the list. Can someone point me in the direction of how I would set this up?
Thanks!
Hi Kelly
Have a look at this blog entry Implement BY processing for your entire SAS program - The SAS Dummy by https://communities.sas.com/people/Chris%40SAS it provides excellent explanation on how to approach this.
Bruno
So you want your existing macro executed on every single customer found in a dataset?
%macro yourmacro(client=)
/*already coded macro*/
%yourmacro;
data _null_;
set have;
call execute('%yourmacro(client='||clientvarname||')');
run;
The same can be done with an infile statement. This will effectively execute the macro that you've defined before as %yourmacro(client=ABC); If you needed the client named within quotes for your macro, you can wrap clientvarname in quotes.
There are probably a handful of other ways and some might be preferable to your desired logic. If your macro creates macro variables through a data step or proc, those macro variables won't be available until after the data _null_; is executed as call execute only executes macro functions immediately and rather create a stack of macros to execute when %yourmacro is not a macro function.
Vince28@Statcan wrote:
So you want your existing macro executed on every single customer found in a dataset?
%macro yourmacro(client=)
/*already coded macro*/
%yourmacro;
data _null_;
set have;
call execute('%yourmacro(client='||clientvarname||')');
run;
The same can be done with an infile statement. This will effectively execute the macro that you've defined before as %yourmacro(client=ABC); If you needed the client named within quotes for your macro, you can wrap clientvarname in quotes.
There are probably a handful of other ways and some might be preferable to your desired logic. If your macro creates macro variables through a data step or proc, those macro variables won't be available until after the data _null_; is executed as call execute only executes macro functions immediately and rather create a stack of macros to execute when %yourmacro is not a macro function.
If decides to use CALL EXECUTE I believe that %NRSTR will be needed to achieve the desired result.
call execute('%nrstr(%yourmacro(client='||clientvarname||'))');
Anytime a macro is call executed that includes data and proc steps the desired result cannot usually be obtain without delaying execution of the macro until after the data step doing the call executing completes.
Hi DN,
As per call execute documentation
Details |
If argument resolves to a
macro invocation, the macro executes immediately and DATA step execution pauses
while the macro executes. If argument resolves to a SAS
statement or if execution of the macro generates SAS statements, the
statement(s) execute after the end of the DATA step that contains the CALL
EXECUTE routine. CALL EXECUTE is fully documented in SAS
Macro Language: Reference.
My understanding (I had to do some research when you corrected me a few days back) was that the macro with sas code will be compiled (even without %NRSTR) but will actually have a delayed execution as if you had done successive macro calls immediately after the run statement because sas code was found in the macro. As with the following example:
394 data h1;
395 x=23;
396 run;
NOTE: The data set WORK.H1 has 1 observations and 1 variables.
NOTE: DATA statement used (Total process time):
real time 0.31 seconds
cpu time 0.00 seconds
397
398 data h2;
399 x=11;
400 run;
NOTE: The data set WORK.H2 has 1 observations and 1 variables.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.03 seconds
401
402 data have;
403 id=1; output;
404 id=2; output;
405 run;
NOTE: The data set WORK.HAVE has 2 observations and 1 variables.
NOTE: DATA statement used (Total process time):
real time 0.02 seconds
cpu time 0.01 seconds
406
407 %macro ex(id=);
408
409 data _null_;
410 set h&id.;
411 put x=;
412 run;
413 %mend;
414
415 data _null_;
416 set have;
417 call execute('%ex(id='||id||')');
418 run;
NOTE: Numeric values have been converted to character values at the places given by: (Line):(Column).
417:29
SYMBOLGEN: Macro variable ID resolves to 1
SYMBOLGEN: Macro variable ID resolves to 2
NOTE: There were 2 observations read from the data set WORK.HAVE.
NOTE: DATA statement used (Total process time):
real time 0.32 seconds
cpu time 0.00 seconds
NOTE: CALL EXECUTE generated line.
1 + data _null_; set h1; put x=; run;
x=23
NOTE: There were 1 observations read from the data set WORK.H1.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds
2 + data _null_; set h2; put x=; run;
x=11
NOTE: There were 1 observations read from the data set WORK.H2.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
So I believe it is only necessary to wrap with %nrstr if the variables coming from the data step contain either & or % etc which would resolve inappropriately for whatever reason.
Hopefully I didn't miss your point
My point is that 99.9% of the time for a macro that "emits" regular SAS code data steps and procs you don't want it to execute immediately as is usual when called with CALL EXECUTE. It might work OK as in your example or not. If we make a slightly more complex, yet completely contrived, example based loosely on your example you will see why we almost always want %NRSTR.
If you run the following with %NRSTR it works otherwise no.
-------------------------------------------------------------------------------------------------------------------------------------
There was an extensive discussion regarding this issue on SAS-L 9 years ago (is that long already?)
http://www.listserv.uga.edu/cgi-bin/wa?A2=ind0512c&L=sas-l&D=0&P=24994SAS-L archives -- March 2004, week 1 (#139)</title><style type="text/css"><!--BODY { font-family: "C...http://www.listserv.uga.edu/cgi-bin/wa?A2=ind0512c&L=sas-l&D=0&P=24994
Also, SAS Doc vaguely recommends using %nrstr() as well :
This post from succinctly states the issue and the need for %NRSTR.
Date: Mon, 19 Dec 2005 14:51:05 -0500
Reply-To: "Fehd, Ronald J" <rjf2@CDC.GOV>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: "Fehd, Ronald J" <rjf2@CDC.GOV>
Subject: Re: Call Execute
Content-Type: text/plain; charset="us-ascii"
add complexity: %If or %do or call symput within HelloWorld
it will not work correctly without being called w/%nrstr
because the -macro- code is expanded
before push onto the SAS tokenizer stack
and you want it expanded -after- pop from the stack
Ron
Well again, thanks DN for the valuable lesson.
Thank you Hai Kuo for the links
*edit SAS9.2 documentation did not have any TIP with regards to %nrstr :smileyshocked:
thanks for the name-cheque, Data _null_
I have politely suggested to SASwareBallot the need for an update to call execute,
which might appropriately be called
call executeX
which would *-always-* delay execution.
The politics I hear from different buildings on SAS campus are not encouraging:
SAS developers of the macro language: "We don't need ..."
SAS Tech Support: "We keep having to explain this issue. Why is it so hard to grasp?"
Uhh, when is the last time we talked about 'stacks' in the SAS (macro) language?
This is neither a SAS nor a macro issue.
It is an interface issue.
And one which is esoteric to programmers.
What you told the computer to do, it will never do, unless you know the language of the interface.
"Wait! Wait! Don't do what I said (wrote) right away! Hesitate!"
Who gets this difference?
"Ok, here is the code I want to run
>>>---> in the next step. <---<<<"
There is *-nothing-* in the log that will help you debug this error!
"What error? I did exactly what you wrote!"
Yeah, but knot what I meant!
This feature of call execute belong in the Proc ShootFoot category.
😉
This program demonstrates the concept of
Futility of Debugging of call execute
DATA _null_;
do i = 1 to 3;
call execute(catt('%let i =',i,';%put note: i:&i;'));
end;
stop;
run;
%macro put_i(data=);
data _null_;
putlog "mvar i: &i from data";
stop;
run;
%put note: &sysmacroname: i:&i;
%mend;
DATA _null_;
do i = 1 to 3;
call execute(catt('%let i =',i,';%put_i'));
end;
stop;
run;
DATA _null_;
file 'subroutine.sas';
put '%put note: i:&i. from subroutine;';
stop;
run;
DATA _null_;
do i = 1 to 3;
call execute(catt('%let i =',i
,';%include "subroutine.sas";'
,'%put note: i:&i in loop;'));
end;
stop;
run;
*NOTE: test of global mvar J
This is what I mean by complexity: a macro with either of percent+do
or percent+if;
%let _global_j = 0;
%macro put_mvar(mvar=,note=none);
%if not &_global_j %then
%put note: &sysmacroname: mvar:&mvar, note=¬e;
%else
%put note: &sysmacroname: nothing to report, note=¬e;
%mend;
%let _global_j = 0;
DATA _null_;
do i = 1 to 3;
call execute(catt('%put_mvar(mvar=',i,')'));
call execute(catt('%nrstr(%put_mvar(mvar=',i,',note=nrstr))'));
end;
stop;
%let _global_j = 1;
%put note: _global_j = &_global_j;
run;
%put note: _global_j = &_global_j;
Ron Fehd macro maven
Whatever answers you get, you should reconsider whether what you are asking is the right thing to do. For example, a typical macro call would take steps like:
1. Extract data for that customer
2. Process data for that customer
3. Print a report for that customer
The most time is probably spent on step 1, extracting data for a single customer from a data set that contains all customers. If you succeed at what you are asking, you will be repeating step 1 for every customer on your list. A much faster approach might be:
A. Extract data for all customers on the list
B. From that extract, process steps 1-3 above for each customer
Only you know the actual process that goes on, and whether speed is important. From this type of scenario, we get the description of macro language: "easy to use, easy to abuse". Just something to think about now, during the planning phase.
Hi Kelly
Have a look at this blog entry Implement BY processing for your entire SAS program - The SAS Dummy by https://communities.sas.com/people/Chris%40SAS it provides excellent explanation on how to approach this.
Bruno
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 25. 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.