Macro interacting with DataStep

[Learn_uk]

Hello everyone,

I am a relatively new SAS programmer and have what appears to be a rather basic question:

I am trying to execute a SAS macro that I have written in a datastep. To do that, you use the call execute command. The one issue that I am encountering is that I dont get the results I am looking for. I think the problem lies with the fact that macros exucute during the compile phase whereas the datastep is run during the execution phase. So what I want is for the macro to run in the execution stage not the compile stage. Below is very simple example code I wrote: (Note:I realize I can do this many many ways including SQL, merge etc), but I really am only interested in doing this the macro iterative way...more from a learning perspective.

Appreciate your help!

data x;

input pt $ wt;

datalines;

1001 120

1002 244

1002 246

1003 145

1004 241

;

data y;

input pt;

datalines;

1002

1003

1005

;

%macro search(id);

proc sql;

select mean(wt) into :result

from x

where pt="&id";

quit;

%mend search;

data y2;

set y;

call execute('%search(id='||pt||')');

wt=symget('result')

run;

[ballardw]

Assuming you actually have a ; after the wt=symget('result');

The documentation for Call Execute says "resolves its argument and executes the resolved value at the next step boundary (if the value is a SAS statement)". In your example the Proc SQL calls execute after the datastep, think of it as submitting one call to the macro AFTER the data step executes (that's the Step boundary mentioned).

[Learn_uk]

Thanks Ballardw for a good explanation...I think I understand what you are saying...that is that the data step will fully execute and then calls in the macro as a last step....Do you know of a way to prevent that...i.e for the macro to run iteratively with the datastep. I think I am going to contact the people at SAS and tell them that this would be a nice feature to include...perhaps a double %% sign to indicate iterative macro running!

[Tom]

Macro is just a way to programmatically generate code.

Your simple problem does not require a macro since there is no need to generate code from data.

proc sql ;

  create table y2 as

     select y.id,mean(x.wt) as result

     from y left join x

     on y.id = x.id

     group by y.id

  ;

quit;

[Learn_uk]

Thanks also to you Tom for your great way...I actually am aware that you can do it in SQL amongst many other ways...the point of the exercise was to do it using macros in an iterative way...i.e once an observation enters the PDV...i want the macro to be called on that...and then the next statement assigns the value of the global macro that is created. Then the next observation is read into the PDV after the datastep has completed and so on and so forth!

But thank you anyways for the alternative method!

[Learn_uk]

Reeza: Thank you for the Proc FCMP suggestion....I dont know proc FCMP very well but if you can think of a way to do it using the input data I provided I would really appreciate that. Note that I was reading about Proc FCMP and there was mention of a function called run_macro....I dont know more about that or how to use it. Thanks also to you!

[Tom]

In general you will make more progress operating on the data as a whole rather than trying to turn SAS into a low level language where you have to tell it every minute step of the process.

Can you think of a better example where it would actually make sense to do what you are trying to do?

[Learn_uk]

Thanks again Tom for your response. I agree with you that there are better ways to do this. Basically, what I have been trying to do is a table look-up and there are many ways in SAS (like SQL (your way), proc format, data step merge, hashes, etc etc). I like to program in R primarily where like SAS there are many ways to do a table look-up (R uses vectors and it also allows you to use 'for' loops). However there are times when you can't use vectors...think of a task that requires you to retain variables, is recursive, has fuzzy logic...although rare, these situations require that you think from a bottom up .

SAS however has implicit looping by the very nature of the data step. Why not take advantage of the fact that observations are read one at a time and then you can use macros to act on the PDV and then . All of this is the SAS way after all...I don't think any other language prides itself in being able to do things many ways! Its actually not complicated at all. I think its easier than a Data Step merge where you have conditional statements. Okay I am rambling!

Thanks!

[Reeza]

Have you looked into Proc FCMP, its more similar to a function, though I don't know how it works .

[gergely_batho]

Use dosubl()  instead of call execute().

[Learn_uk]

Thank you Gergely! I shall try to use dosubl() using my example code. Its too bad that docubl cannot access macro variables (unlike macro_run()) but I can work around that I think...Instead of throwing the results in a macro, I will ask to throw result into a dataset instead!

[GUEST101]

This is Learn_uk with a changed username: We finally got SAS 9.4 on our computers and I am now going to present the solution to what I was hoping for...Thanks to Gergely for his advice:

data x;

input
pt wt;

datalines;

1001 120

1002 244

1002 246

1003 145

1004 241

;

data y;

input
pt;

datalines;

1002

1003

1005

;

%let result=.;

%macro search(id);

proc sql noprint;

select mean(wt) into :result

from x

where pt=&id;

quit;

%mend search;

data y2 (drop=rc);

set y;

rc=dosubl('%search(id='||pt||')');

wt=symget('result');

run;

[Ksharp]

You don't need a macro . If I understood what you mean.

 
data x;
input
pt wt;
datalines;
1001 120
1002 244
1002 246
1003 145
1004 241
;
 
data y;
input
pt;
datalines;
1002
1003
1005
;
run;
proc sql ;
create table want as
 select *,(select mean(wt) from x where pt=y.pt) as mean_wt
  from y;
quit;

Xia Keshan