Wild card operation to include all variables that include TOXGR in PROC SQL Step

[smackerz1988]

Hello,

I'm developing a macro and converting a data step to a proc sql step. so going from this 

data ae00 (keep=usubjid &pop.  country countryc aedecod aegdecod astdy trtp AESHOSP TRTEMFL AESOC TOXG:);
	set adam.adae;
    where &popflg. and country in (&formatted_country_list.) and &inds_whr.;
run;

to this

proc sql;
    create table ae00 as
    select usubjid, &pop., country, countryc, aedecod, aegdecod, astdy, trtp, AESHOSP, TRTEMFL, AESOC, TOXG:
    from adam.adae
    where &popflg. 
        and country in (&formatted_country_list.) 
        and &inds_whr.;
quit;

The only issue I'm having is the TOXGR: from the original data step. Is there a wildcard operation that would do this or do I just have to explicitly state each TOXGR variable?. Sometimes the TOXGR variables may change over different study datasets which is the rationale for doing this. I know for fuzzy matching variable values you could use SPEDIS, SOUNDEX, COMPLEV functions. However, I'm wondering if there's also a function that can be applied to finding similarities in variable names, similar to these methods,

[FreelanceReinh · Accepted solution]

Hello @smackerz1988,

To keep the code simple, you can use the same KEEP= dataset option in the PROC SQL step that you used in the DATA step and then select * from ...:

proc sql;
create table ae00 (keep=usubjid ... TOXG:) as
select *
from adam.adae
...

Alternatively (and possibly more efficient), create the variable list from DICTIONARY.COLUMNS:

proc sql noprint;
select name into :toxgvars separated by ' '
from dictionary.columns
where libname='ADAM' & memname='ADAE' & upcase(name) eqt 'TOXG';
quit;

and then replace TOXG: with &toxgvars in your PROC SQL code.

[smackerz1988]

Yeah using dicitonary.columns and eqt makes the most sense to me!. Thanks for your help!