0

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.

sassane
  • 21
  • 5
  • If DATASET1 does not have a variable named \_NAME_ then you would get that error message since that s the variable whose value you are pulling from DATASET1. If you want to query the names of the variables in a dataset then first get the names of the variables in the dataset. Use PROC CONTENTS or other methods to get the names. – Tom May 02 '23 at 17:06

2 Answers2

2

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;
Stu Sztukowski
  • 10,597
  • 1
  • 12
  • 21
  • Your response makes sense, but when running it I am getting errors saying - ERROR: The following columns were not found in the contributing tables: library. ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, a numeric constant, a datetime constant, a missing value, BTRIM, INPUT, PUT, SUBSTRING, USER. – sassane May 02 '23 at 15:44
  • The error message is making think the principle is correct but the "library" reference isn't right. Is there another way of referring to the location other than "library"? – sassane May 02 '23 at 15:55
  • We cannot see your computer so we do not know the name of your dataset or the libref you have defined that points to it. Remember that the values of LIBNAME and MEMNAME in DICTIONARY.COLUMNS always has uppercase letters only. – Tom May 02 '23 at 17:08
  • 1
    I have fixed the error in the code. It should have been `libname` – Stu Sztukowski May 02 '23 at 17:10
  • 1
    All sorted, thanks Stu – sassane May 03 '23 at 14:40
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
yabwon
  • 331
  • 5