topic Get obs without null values in SAS Programming

Get obs without null values

Ronein — Wed, 13 Mar 2019 06:50:12 GMT

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;

Re: Get obs without null values

PeterClemmensen — Wed, 13 Mar 2019 07:18:15 GMT

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;

Re: Get obs without null values

andreas_lds — Wed, 13 Mar 2019 07:24:09 GMT

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

Re: Get obs without null values

Kurt_Bremser — Wed, 13 Mar 2019 07:52:38 GMT

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:

But @PeterClemmensen's solution is to be preferred, of course.