Use SAS Proc SQL to Find Rows Where All Columns Are Not Missing?

0
votes

I would like to use SAS Proc SQL to find me row in a table in which every column has a non-missing value. Is there a way to do this without having to list all of the column names? This did not work, but it might give you an idea of what my intended output is.

Proc SQL;
    Select *
    from work.table
    Where * is not null;
Run;

I would also like to limit the results to one observation if possible. Thanks.

3
sqlsas

3 Answers

1
votes

In SQL, you have to be explicit. * is not a general purpose macro that expands out the columns. It is a syntactic elements that happens to be used in select * and count(*).

So, something like this:

Proc SQL;
    Select *
    from work.table
    Where col1 is not null and col2 is not null and col3 is not null . . .
Run;
1
votes

Nontrivial in SQL since you cannot get all variables in one item without using the macro language. In the datastep, this is trivial.

data class;
  set sashelp.class;
  if mod(_n_,3)=1 then call missing(age);
run;

data want;
  set class;
  if cmiss(of _all_)=0;
run;

cmiss indicates a 1 if a character or numeric variable is missing (and specifically in this case counts the total number of missings). You can use the obs=1 option on the data step to limit to one.

1
votes

Using SQL and dictionary tables:

proc sql noprint;
    select cats('not missing(', name, ')')
    into :expression separated by " and "
    from dictionary.columns
    where libname = "SASHELP" and memname = "CLASS";
quit;

proc sql outobs=1;  
    select *
    from sashelp.class
    where &expression.;
quit;