Hello
Why this code is not working?(Taks is to get observations without null values)
Error "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. "
proc sql noprint;
select cats('not missing(', name, ')')
into :expression separated by " and "
from dictionary.columns
where libname = "WORK" and memname = "a";
quit;
%put &expression;
proc sql ;
create table b as
select *
from a
where &expression.
;
quit;
Start debugging your code from the top.
Your first clue comes here:
38 proc sql noprint; 39 select cats('not missing(', name, ')') 40 into :expression separated by " and " 41 from dictionary.columns 42 where libname = "WORK" and memname = "a"; NOTE: No rows were selected.
SAS library references and dataset names are always uppercase, so there can't be a memname of "a", it has to be "A".
The next clue is
44 %put &expression; WARNING: Apparent symbolic reference EXPRESSION not resolved. &expression
because SQL won't create the macro variable if no rows were found to match the where condition.
As soon as you fix the first problem:
where libname = "WORK" and memname = "A"
everything will start to work.
See my example:
data a;
infile cards dlm=' ' dsd;
input a $ b;
cards;
A 1
B
.
C 4
;
run;
proc sql noprint;
select cats('not missing(', name, ')')
into :expression separated by " and "
from dictionary.columns
where libname = "WORK" and memname = "A";
quit;
%put &expression;
proc sql ;
create table b as
select *
from a
where &expression.
;
quit;
Log:
27 data a; 28 infile cards dlm=' ' dsd; 29 input a $ b; 30 cards; NOTE: The data set WORK.A has 4 observations and 2 variables. NOTE: DATA statement used (Total process time): real time 0.01 seconds cpu time 0.00 seconds 35 ; 36 run; 37 38 proc sql noprint; 39 select cats('not missing(', name, ')') 40 into :expression separated by " and " 41 from dictionary.columns 42 where libname = "WORK" and memname = "A"; 43 quit; NOTE: PROZEDUR SQL used (Total process time): real time 0.00 seconds cpu time 0.00 seconds 44 %put &expression; not missing(a) and not missing(b) 45 46 2 Das SAS System 08:44 Wednesday, March 13, 2019 47 proc sql ; 48 create table b as 49 select * 50 from a 51 where &expression. 52 ; NOTE: Table WORK.B created, with 2 rows and 2 columns. 53 quit;
and the resulting dataset:
a b A 1 C 4
But @PeterClemmensen's solution is to be preferred, of course.
In what part of your code is this error issued?
Also, if your only task is to extract observations without any missing values, there are far better approaches such as
data NoMissingValues;
set Sashelp.Heart;
if cmiss(of _ALL_)=0;
run;
Activate option "symbolgen", re-run the code and examine or post the log.
Start debugging your code from the top.
Your first clue comes here:
38 proc sql noprint; 39 select cats('not missing(', name, ')') 40 into :expression separated by " and " 41 from dictionary.columns 42 where libname = "WORK" and memname = "a"; NOTE: No rows were selected.
SAS library references and dataset names are always uppercase, so there can't be a memname of "a", it has to be "A".
The next clue is
44 %put &expression; WARNING: Apparent symbolic reference EXPRESSION not resolved. &expression
because SQL won't create the macro variable if no rows were found to match the where condition.
As soon as you fix the first problem:
where libname = "WORK" and memname = "A"
everything will start to work.
See my example:
data a;
infile cards dlm=' ' dsd;
input a $ b;
cards;
A 1
B
.
C 4
;
run;
proc sql noprint;
select cats('not missing(', name, ')')
into :expression separated by " and "
from dictionary.columns
where libname = "WORK" and memname = "A";
quit;
%put &expression;
proc sql ;
create table b as
select *
from a
where &expression.
;
quit;
Log:
27 data a; 28 infile cards dlm=' ' dsd; 29 input a $ b; 30 cards; NOTE: The data set WORK.A has 4 observations and 2 variables. NOTE: DATA statement used (Total process time): real time 0.01 seconds cpu time 0.00 seconds 35 ; 36 run; 37 38 proc sql noprint; 39 select cats('not missing(', name, ')') 40 into :expression separated by " and " 41 from dictionary.columns 42 where libname = "WORK" and memname = "A"; 43 quit; NOTE: PROZEDUR SQL used (Total process time): real time 0.00 seconds cpu time 0.00 seconds 44 %put &expression; not missing(a) and not missing(b) 45 46 2 Das SAS System 08:44 Wednesday, March 13, 2019 47 proc sql ; 48 create table b as 49 select * 50 from a 51 where &expression. 52 ; NOTE: Table WORK.B created, with 2 rows and 2 columns. 53 quit;
and the resulting dataset:
a b A 1 C 4
But @PeterClemmensen's solution is to be preferred, of course.
Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.