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

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

smackerz1988 — Sun, 21 Apr 2024 13:05:22 GMT

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,

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

FreelanceReinh — Sun, 21 Apr 2024 13:21:32 GMT

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.

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

smackerz1988 — Sun, 21 Apr 2024 13:28:49 GMT

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