topic Re: run a program multiple times for a list of inputs in SAS Programming

run a program multiple times for a list of inputs

Kelli — Tue, 03 Dec 2013 15:58:14 GMT

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!

Re: run a program multiple times for a list of inputs

Vince28_Statcan — Tue, 03 Dec 2013 16:10:07 GMT

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.

Re: run a program multiple times for a list of inputs

Astounding — Tue, 03 Dec 2013 16:33:20 GMT

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.

Re: run a program multiple times for a list of inputs

BrunoMueller — Tue, 03 Dec 2013 16:44:25 GMT

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

Re: run a program multiple times for a list of inputs

data_null__ — Tue, 03 Dec 2013 17:02:37 GMT

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;

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.

Re: run a program multiple times for a list of inputs

Vince28_Statcan — Tue, 03 Dec 2013 18:02:36 GMT

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

Re: run a program multiple times for a list of inputs

data_null__ — Tue, 03 Dec 2013 19:29:13 GMT

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.

data h1; x=23; run; 
data h2; x=11; run; 

data have;
   do id=1,2; output; end; 
   run; 
%macro ex(id=); 
   data _null_;
      set h&id.; 
      call symputx('x',x,'local');
      put x=;
      run;
   %if &x eq 23 %then %do; 
      proc print data=h&id;
         run;
      %end; 
   %mend; 
options mprint=1 mlogic=1; 
data _null_; 
   set have;
   call execute('%ex(id='||vvalue(id)||')');
/*   call execute('%nrstr(%ex(id='||vvalue(id)||'))');*/
   run; 
options mprint=0 mlogic=0; 

-------------------------------------------------------------------------------------------------------------------------------------

18         data h1; x=23; run; 

NOTE: The data set WORK.H1 has 1 observations and 1 variables.
      

19         data h2; x=11; run; 

NOTE: The data set WORK.H2 has 1 observations and 1 variables.


20         
21         data have;
22            do id=1,2; output; end; 
23            run;

NOTE: The data set WORK.HAVE has 2 observations and 1 variables.
      

24         %macro ex(id=);
25            data _null_;
26               set h&id.; 
27               call symputx('x',x,'local');
28               put x=;
29               run;
30            %if &x eq 23 %then %do; 
31               proc print data=h&id;
32                  run;
33               %end; 
34            %mend; 
35         options mprint=1 mlogic=1; 
36         data _null_;
37            set have;
38            call execute('%ex(id='||vvalue(id)||')');
39         /*   call execute('%nrstr(%ex(id='||vvalue(id)||'))');*/
40            run;

MLOGIC(EX):  Beginning execution.
MLOGIC(EX):  Parameter ID has value 1
MPRINT(EX):   data _null_;
MPRINT(EX):   set h1;
MPRINT(EX):   call symputx('x',x,'local');
MPRINT(EX):   put x=;
MPRINT(EX):   run;
WARNING: Apparent symbolic reference X not resolved.
ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric operand is required. The condition was: 
       &x eq 23 
ERROR: The macro EX will stop executing.
MLOGIC(EX):  Ending execution.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.01 seconds
      
MLOGIC(EX):  Beginning execution.
MLOGIC(EX):  Parameter ID has value 2
MPRINT(EX):   data _null_;
MPRINT(EX):   set h2;
MPRINT(EX):   call symputx('x',x,'local');
MPRINT(EX):   put x=;
MPRINT(EX):   run;
WARNING: Apparent symbolic reference X not resolved.
ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric operand is required. The condition was: 
       &x eq 23 
ERROR: The macro EX will stop executing.
MLOGIC(EX):  Ending execution.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: There were 2 observations read from the data set WORK.HAVE.

NOTE: CALL EXECUTE generated line.
1         + data _null_;       set h1;       call symputx('x',x,'local');       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.00 seconds
      cpu time            0.00 seconds
      

2         + data _null_;       set h2;       call symputx('x',x,'local');       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
      

41         options mprint=0 mlogic=0; 
42         

Re: run a program multiple times for a list of inputs

Haikuo — Tue, 03 Dec 2013 22:09:35 GMT

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=24994 SAS-L archives -- March 2004, week 1 (#139)</title><style type="text/css"></style><head><title>LISTSERV at the University of Georgia 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 :

SAS(R) 9.3 Macro Language: Reference

Re: run a program multiple times for a list of inputs

data_null__ — Tue, 03 Dec 2013 23:06:36 GMT

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

Re: run a program multiple times for a list of inputs

Vince28_Statcan — Wed, 04 Dec 2013 12:26:52 GMT

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:

Re: run a program multiple times for a list of inputs

Ron_MacroMaven — Thu, 05 Dec 2013 22:14:38 GMT

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.

😉

Re: run a program multiple times for a list of inputs

Ron_MacroMaven — Fri, 06 Dec 2013 12:28:23 GMT

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=&note;

%else

%put note: &sysmacroname: nothing to report, note=&note;

%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