topic Re: selecting variables based on name prefix and suffix in SAS Programming

selecting variables based on name prefix and suffix

axescot78 — Wed, 09 Feb 2022 23:36:11 GMT

I have dataset with a long list of variables. I am interested in selecting those variables that start with:

mon_

wed_

fri_

and end with:

_07

_11

I am also interested in those that start with:

sat_

and end with:

_11

There could be any character and any number of character in between. How can I select these programmatically?

Re: selecting variables based on name prefix and suffix

SASKiwi — Thu, 10 Feb 2022 00:20:19 GMT

Using the SAS DICTIONARY tables is the most flexible way of getting a list of the required columns. The one you want is called COLUMNS:

proc sql noprint;
select name
into :varlist separated by ' '
from dictionary.columns
where libname = 'SASHELP'
and memname = 'CLASS'
;
quit;

%put varlist = &varlist;

Re: selecting variables based on name prefix and suffix

Reeza — Thu, 10 Feb 2022 00:24:11 GMT

proc sql;
select name into :var_list separated by " "
from dictionary.columns
where libname = "WORK" and memname="HAVE"
and (%lowcase(name) substr(name, 1, 4) in ('mon_', 'wed_', 'fri_') AND substr(%lowcase(name), length(name)-3) in ('_07', '_11') )
or %lowcase(name) like 'sat_%_11';
quit;

%put &var_list;

Untested but something along those lines is what you should aim for.
If you're doing this in SQL then you may want to change the separate by to have a comma in between the names.

Data set names and libraries are stored in upper case letters. Variable names can be mixed case so you may need to account for that more than what I did above.

HTH

@axescot78 wrote:

I have dataset with a long list of variables. I am interested in selecting those variables that start with:

mon_

wed_

fri_

and end with:

_07

_11

I am also interested in those that start with:

sat_

and end with:

_11

There could be any character and any number of character in between. How can I select these programmatically?

Re: selecting variables based on name prefix and suffix

ballardw — Thu, 10 Feb 2022 03:45:57 GMT

Without knowing where you got those variable names I can't suggest an actual fix. However, you are placing data in the variable names as it appears you have day of the week and possibly a month or day of month value. You will find in the long run that such "names" are very cumbersome to work with and often indicated poor data structure for many analysis, reporting or graphing tasks.