CODEWITHSUNDEEP
sas
Pawan Tolani
Pawan Tolani

Reputation: 3

I want to select variables in proc sql only if they exist

I want to write a proc sql query that will select only those variables that exist in a data set

I found an answer on stack overflow and tried to implement it but didn't work for me.

proc sql noprint;
select cats('a.', name) into :varlist separated by ','
from dictionary.columns
where libname='WORK' and memname='display_type_transposed' and name in 
('_4','_5','_6','_7','_8','_9');
quit;

proc print data=display_type_transposed (obs=100);
run;

%put &=varlist.;

proc sql;
create table merged_display_flags as
select a.*, &varlist.
from display_loc_transposed as a, display_type_transposed as b
where a.period_id=b.period_id and a.store_id=b.store_id and 
a.PRODUCT_ID=b.PRODUCT_ID;
quit;

I am getting the following error messages.

WARNING: Apparent symbolic reference VARLIST not resolved.
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.

409        from display_loc_transposed as a, display_type_transposed as b
            ______________________
            22
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, 
**, +, ',', -, /, <, <=, <>, =, >, >=, ?, AND, AS,
CONTAINS, EQ, EQT, FROM, GE, GET, GT, GTT, LE, LET, LIKE, LT, LTT, NE, 
NET, OR, ^=, |, ||, ~=.

410        where a.period_id=b.period_id and a.store_id=b.store_id and 
a.PRODUCT_ID=b.PRODUCT_ID;

Upvotes: 0

Views: 699

Answers (1)

Tom
Tom

Reputation: 51581

The values of MEMNAME in the metadata view/table DICTIONARY.COLUMNS is always in uppercase. So your query is not finding any variables with those names because you search for the member name in lowercase. So it never makes the macro variable.

A simple way to prevent errors about macro variables not defined when not finding any matching observations is to set the target macro variable empty BEFORE the query.

%let varlist=;

But your SQL code will not work when VARLIST is empty because you will then have an extra comma in your query.

You can test the automatic macro variable SQLOBS to see if any variables are generated.

%if (&SQLOBS>0) %then %do;
...
%end;

Upvotes: 1

Related Questions