topic Problem calling a macro within a data step using call execute in SAS Programming

Problem calling a macro within a data step using call execute

Lakes74 — Fri, 05 Mar 2010 13:51:30 GMT

Hello,

I’m trying to call a macro within a data step and only seems to be evaluating on the second run of the my program!

For example, if i run the following code below, N, T1, T2 and T3 will be null even though the log shows the macro has run and set each global variable.

Only when the program is run a second time (within the SAS session), dataset TEST is written too.

I’m desperate to find a solution to this if possible? Why does it only compute on the second run of the SAS program.

Thank you in advance. Lakes74.

Data look_up;

input id D1 $ D2 $ D3 $;

datalines;

1 A B C

2 D E F

3 G H I

4 J K L

5 M N O

6 P Q R

7 S T U

8 V W X

9 Y Z A

;

run;

%global var1;

%global var2;

%global var3;

%macro temp(number);

proc sql noprint;

select D1,D2,D3 into :var1, :var2, :var3

from look_up

where id = &number;

quit;

%mend;

%let id = 2;

data test;

call execute ('%temp('||put(&id,3.)||')');

N = "&id";

T1 = "&var1";

T2 = "&var2";

T3 = "&var3";

run;

%put &var1;

%put &var2;

%put &var3;

Re: Problem calling a macro within a data step using call execute

sbb — Fri, 05 Mar 2010 13:57:52 GMT

You are attempting to mix SAS MACRO language, macro variable usage, and SAS DATA step processing, which normally is not going to work properly. Read up on CALL EXECUTE and you'll see that the invoked code will actually execute *AFTER* the current DATA step completes.

Suggest adding the code below to get the most output in your SAS log with your compiled and executed code:

OPTIONS SOURCE SOURCE2 MACROGEN SYMBOLGEN MPRINT MLOGIC;

If you have a particular need to use SAS macro language facilities, it's best to get your code working without macros, understand the flow such that it works properly, and then decide what's needed with macros, if at all, to enhance or make your program more effective/efficient by using SAS macros and macro variables.

Scott Barry
SBBWorks, Inc.

Recommended Google advanced search arguments, this topic/post:

macro language introduction site:sas.com

macro variable scoping site:sas.com

Re: Problem calling a macro within a data step using call execute

DanielSantos — Fri, 05 Mar 2010 18:08:45 GMT

Agree with Scott.

Mixing macro variables with dataset variables could be quite challenging.

From your example you are trying to read a dataset from within a dataset.

This could be done with some other techniques, like index match, hashing or even using the SAS File I/O functions.

Check the online documentation and examples:
http://support.sas.com/documentation/cdl/en/lrdict/62618/HTML/default/a000148395.htm

Cheers from Portugal.

Daniel Santos @ www.cgd.pt

Re: Problem calling a macro within a data step using call execute

ChrisNZ — Mon, 08 Mar 2010 22:36:22 GMT

You can't run proc sql inside a data step, think about it.

2 ways to do what you want:

Either look up the data some other way (no need to call a macro to look up data), or if you need to do more complex things in your macro, it has to be pure macro language for the data step to run it immediately.

This does both:
[pre]

data look_up(index=(ID));
input id D1 $ D2 $ D3 $;
datalines;
1 A B C
2 D E F
3 G H I
4 J K L
5 M N O
6 P Q R
7 S T U
8 V W X
9 Y Z A
run;

%macro temp(number);
%global var1 var2 var3;
%let dsid=%sysfunc(open(look_up(where=(ID = &number))));
%let rc=%sysfunc(fetch(&dsid));
%let var1=%sysfunc(getvarc(&dsid,%sysfunc(varnum(&dsid,D1))));
%let var2=%sysfunc(getvarc(&dsid,%sysfunc(varnum(&dsid,D2))));
%let var3=%sysfunc(getvarc(&dsid,%sysfunc(varnum(&dsid,D3))));
%let rc=%sysfunc(close(&dsid));
%mend;

%let id = 2;

data TEST;
call execute ('%temp('||put(&id,3.)||')'); *get values from macro;
T1 = symget('var1');
T2 = symget('var2');
T3 = symget('var3');
output;
ID = &id+1;
set look_up key=ID; *get values from look-up key
T1 = D1;
T2 = D2;
T3 = D3;
output;
stop;
run;[/pre]

Re: Problem calling a macro within a data step using call execute

Lakes74 — Tue, 18 May 2010 10:43:25 GMT

Thanks all for the helpful replies, really appreciate it!