Hi @PaigeMiller,
I like it. I think DOSUBL is probably severely under-used. It brings new possibilities to function-style macros, like this. I do have some thoughts to share for the sake of discussion.
1. As a general rule, I try to declare all macro variables that are intended to be "internal" to a macro as %local so as to avoid naming collisions. So I would add:
%local word rc temp_varnames varexist ;
Note while this is usually just protection against the possibility that a macro variable exists in an outer scope, this is particularly important when using DOSUBL. If you run the macro as is, you will see that it creates temp_varnames as a global macro variable. This is a result of an unfortunate (IMHO) DOSUBL design decision. The macro variable temp_varnames is created as a global macro variable in the side session. When it is returned to the main session if the macro variable does not already exist as a local variable, it is created as a global variable. I think it would have been better for DOSUBL to create it as a local macro variable (assuming it did not already exist as a global macro variable). Creating the variable as %local before calling DOSUBL allows DOSUBL to return the the macro variable to the local symbol table.
2. I think as a general practice it's better to use
%sysfunc(dosubl(%nrstr(
as a wrapper for the DOSUBL block rather than
%sysfunc(dosubl(%str(
Using %NRSTR() delays resolution of macro references until the code executes in the DOSUBL side-session. It's important to use %NRSTR() when the side-session code will use SAS code to create new macro variables that are referenced inside the DOSUBL block. The same way that it's recommended to use %NRSTR() inside CALL EXECUTE to delay resolution of macro variables. It's also important to use %NRSTR() if the code in the DOSUBL block has any macro statements, because you want them to execute in the side session. In this case, using %STR() works fine. But since %NRSTR() always works, I think it's a better choice.
3. I feel like %VarExist is an odd name for this macro. I'm not sure what I would name this macro. It's almost %ListVariablesAndAddANewVariableIfItDoesNotExist. If I see a macro named %VarExist, I think it will return 1/0 as to whether or not a variable exists.
4. Thinking about %VarExist made me wander down a different design path, still using DOSUBL. Suppose you take the core of your macro and turn it into a macro %VarList, which returns a list of variables in a dataset:
%macro varlist(data,dlm=%str( ));
%local rc varlist ;
%let rc =
%sysfunc(dosubl(%nrstr(
proc contents data=&DATA out=ExpandVarList_temp(keep=name) noprint;
run;
proc sql noprint;
select name
into :varlist separated by "&dlm" from ExpandVarList_temp ;
drop table ExpandVarList_temp;
quit;
)));
&varlist
%mend varlist;
I think that is a really useful utility macro. You can do stuff like:
%put %varlist(data=sashelp.class) ;
%put %varlist(data=sashelp.class(keep=_numeric_)) ;
%put %varlist(data=sashelp.class(keep=name--age)) ;
That macro makes it easy to write a %VarExist macro which will tell you if a variable exists or not, e.g.:
%macro varexist(data=,var=);
%*function-style macro, return 1 if variable exists, else 0 ;
%eval(%sysfunc(findw(%qupcase(%varlist(&data,dlm=|)),%upcase(&var),|))>0)
%mend varexist;
With a %VarExist macro utility like that, I might stop there, and use an open %IF statement in my PROC SQL, e.g.:
proc sql;
create table want as select *
%if NOT %varexist(data=sashelp.class,var=bloodpressure) %then %do ;
, . as bloodpressure
%end ;
from sashelp.class;
quit;
For the join:
data iq;
length name $ 8;
name='Jane';
iq=150;
output;
name='Janet';
iq=144;
output;
run;
proc sql;
create table want as select
a.*
%if NOT %varexist(data=sashelp.class,var=bloodpressure) %then %do ;
, . as bloodpressure
%end ;
,b.iq
from sashelp.class as a left join iq as b
on a.name=b.name;
quit;
Because %varlist returns a list of variables, you can use other list-processing macros on the list. For example, suppose you want to do the join, but want to keep only the character variables from sashelp.class. You can use Richard DeVenezia's %seplist (https://www.devenezia.com/downloads/sas/macros/index.php?m=seplist) to add the a. alias to the list of character variables, e.g.:
proc sql;
create table want as select
%seplist(%varlist(sashelp.class(keep=_character_)),prefix=a.,dlm=%str(,))
%if NOT %varexist(data=sashelp.class,var=bloodpressure) %then %do ;
, . as bloodpressure
%end ;
,b.iq
from sashelp.class as a left join iq as b
on a.name=b.name;
quit;
The Boston Area SAS Users Group is hosting
free webinars!
Next webinar will be in January 2025. Until then, check out our archives:
https://www.basug.org/videos. And be sure to subscribe to our our email list.
