02-19-2014 05:25 PM
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!
input pt $ wt;
select mean(wt) into :result
02-19-2014 06:49 PM
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).
02-19-2014 10:10 PM
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!
02-19-2014 08:46 PM
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
02-19-2014 10:13 PM
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!
02-19-2014 10:15 PM
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!
02-19-2014 10:30 PM
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?
02-20-2014 12:58 AM
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!
02-20-2014 01:13 PM
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!
11-25-2014 01:19 PM
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:
proc sql noprint;
select mean(wt) into :result
data y2 (drop=rc);
11-26-2014 08:22 AM
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;