Hi kdt, 6 years later but it may be useful to others.
If you want to use a macro to return a value to a datastep you can do that:
%macro retval;
%let retval=Barbara;
&retval.
%mend retval;
data tmp;
set sashelp.class;
where name="%retval";
%put "%retval";
run;
You can also use a macro to return a value to a macro:
%macro retval;
%let retval=Barbara;
&retval.
%mend retval;
%macro useretval;
%let newretval=%retval;
%put &newretval.;
%mend;
%useretval;
You can also use a macro as a datastep function, but be mindful not to use any macro assignments, i.e. anything with a %:
%macro retval;
*The macro is resolved before the code is send to the datastep compiler;
Barbara;
%mend retval;
data tmp;
set sashelp.class;
where name="%retval";
run;
or a combination of stangeties:
%macro retval(passval, ageval, retval);
*The macro is resolved before the code is send to the datastep compiler;
if &passval=1 then retval="Mrs. Owen";
if &ageval=14 then grade9='Y';
%mend retval;
data tmp;
set sashelp.class;
%retval(1, age, teachername);
run;
However always remember as you stated above, that the macro processor first resolved any variables. So if you want to use it in a datastep/proc, make sure that it is plaintext that will be inserted into the datastep/proc.
In your example abovce, it effectively tried to insert the proc sql into the datastep.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Join us for two new fee-based courses: Administrative Healthcare Data and SAS via Live Web Monday-Thursday, April 24-27 from 1:00 to 4:30 PM ET each day. And Administrative Healthcare Data and SAS: Hands-On Programming Workshop via Live Web on Friday, April 28 from 9:00 AM to 5:00 PM ET.