SAS STUDIO - Selecting columns based on their name

Question

I'm looking to select columns based depending on if they contain the string "time" in the column name.

My first attempt looks like this, but I understand there are some issues, and I'm getting the following - ERROR: The following columns were not found in the contributing tables: name.

proc sql;
select _name_
into :names
from work.dataset1
where (_name_) like '%time%';
quit;

What am I missing, as I'm almost sure I'm using the "Where" function incorrectly.

Answer 1

You'll have to search the columns dictionary table to generate a list of columns that only contain the word time. These column names will be saved into a comma-separated macro variable named cols and passed into a subsequent SQL select statement.

proc sql noprint;
    select name
    into :names separated by ','
    from dictionary.columns
    where     libname = 'WORK'
          AND memname = 'DATASET1'
          AND upcase(name) LIKE '%TIME%'
    ;
quit;

proc sql;
    select &names
    from dataset1;
quit;

Answer 2

for your approach some modifications are needed:

data work.dataset1;
  first_time=1;
  abc=2;
  last_time=3;
  BIG_TIME=4;
run;

proc transpose data=work.dataset1(obs=0) out=temp(keep=_name_);
  var _all_;
run;
proc sql;
  select _name_
  into :names separated by " "
  from temp
  where lowcase(_name_) like '%time%'; /* case insensitive */
quit;

%put &=names.;

The log:

1    data work.dataset1;
2      first_time=1;
3      abc=2;
4      last_time=3;
5      BIG_TIME=4;
6    run;

NOTE: The data set WORK.DATASET1 has 1 observations and 4 variables.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.01 seconds


7
8    proc transpose data=work.dataset1(obs=0) out=temp(keep=_name_);
9      var _all_;
10   run;

NOTE: There were 0 observations read from the data set WORK.DATASET1.
NOTE: The data set WORK.TEMP has 4 observations and 1 variables.
NOTE: PROCEDURE TRANSPOSE used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds


11   proc sql;
12     select _name_
13     into :names separated by " "
14     from temp
15     where lowcase(_name_) like '%time%';
15 !                                        /* case insensitive */
16   quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds


17
18   %put &=names.;
NAMES=first_time last_time BIG_TIME