CODEWITHSUNDEEP
sqlsas
D. Bryant
D. Bryant

Reputation: 105

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

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.

Upvotes: 0

Views: 4064

Answers (3)

Robert Soszyński
Robert Soszyński

Reputation: 1188

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;

Upvotes: 1

Joe
Joe

Reputation: 63424

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.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269803

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;

Upvotes: 1

Related Questions