BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Ronein
Onyx | Level 15

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;
1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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.

View solution in original post

3 REPLIES 3
PeterClemmensen
Tourmaline | Level 20

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;

 

 

andreas_lds
Jade | Level 19

Activate option "symbolgen", re-run the code and examine or post the log.

Kurt_Bremser
Super User

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.

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

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!

Register Now

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 3 replies
  • 1225 views
  • 1 like
  • 4 in conversation