This is a continuation of sorts on the topic of determining the missing / nonmissing variables in a data set. I am following a code algorithm from the 2020 Global Forum paper by Shan and Bremser (great paper by the way).
PROC SQL
This will be faster for big tables; it will pick up numeric and character variables:
/* firstl, get variable names */
proc transpose data=have(obs=0) out=vname;
var _all_;
run;
/* then check if variable is all missing */
proc sql noprint;
select catx(' ','n(',_name_,') as',_name_) into :vnames separated by ','
from vname;
create table temp as
select &vnames from have;
quit;
proc transpose data=temp out=want;
var _all_;
run;
proc sql noprint;
select _name_ into : missing_variables separated by ' '
from want
where col1=0;
quit;
%put Missing variables are : &missing_variables ;
Anyway, this is my code and the accompanying log which specifies the error that I am getting:
/* First1, get variable names */
proc transpose data = OPERA.OPERA_dis_age(obs = 0) out = OPERA.OPERA_vnames;
var _all_;
run;
/* then check if variable is all missing */
proc sql noprint;
Select catx(' ','n('_name_,') as',_name_) into :vnames separated by ','
From OPERA.OPERA_vnames;
Create table temp As
Select &vnames
From OPERA.OPERA_dis_age;
quit;
And the log
The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
cpu time 0.01 seconds
66 proc sql noprint;
67 Select catx(' ','n('_name_,') as',_name_) into :vnames separated by ','
----------
49 22
NOTE 49-169: The meaning of an identifier after a quoted string might change in a future SAS
release. Inserting white space between a quoted string and the succeeding
identifier is recommended.
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, ',', -, /, <,
<=, <>, =, >, >=, ?, AND, CONTAINS, EQ, EQT, GE, GET, GT, GTT, LE, LET, LIKE,
LT, LTT, NE, NET, OR, ^=, |, ||, ~=.
68 From OPERA.OPERA_vnames;
69 Create table temp As
70 Select &vnames
WARNING: Apparent symbolic reference VNAMES not resolved.
70 Select &vnames
-
22
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.
71 From OPERA.OPERA_dis_age;
72 quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
cpu time 0.01 seconds
What is the problem here? I checked over the code but could have missed something.
Thanks for your help.
wlierman
