[SOLVED] SAS STUDIO - Selecting columns based on their name

SAS STUDIO - Selecting columns based on their name

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.

Solution

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;