Proc SQL assignment of missing values

2

votes

If I run a query that produces zero rows, I still want a SAS dataset created with one row having all columns with missing values assigned.

I found a way to do this using a separate data step:

%let dsid   = %sysfunc (open(myfile));
%let anyobs = %sysfunc (attrn(&dsid,ANY));
%let dsid   = %sysfunc (close(&dsid));
data _null_;
  if &anyobs=0 then do;
     call execute('data work.myfile; call missing(col1, col2, col3); run;');
end;

This works fine but i was wondering if there a way to assign missing for each column within proc sql?

Thanks Dan

sasproc-sql

3

votes

So let's say you execute this:

proc sql;
  create table class as 
    select * from sashelp.class 
    where age=19;
quit;

Then you could just do this:

%macro ifMissingRow(data=);
  %let dsid   = %sysfunc (open(&data.));
  %let anyobs = %sysfunc (attrn(&dsid,ANY));
  %let dsid   = %sysfunc (close(&dsid));
  %if &anyobs=0 %then %do;
    data &data.;
      output;
      set &data.;
    run;
  %end;
%mend ifmissingRow;

%ifMissingRow(data=class);

Output is before set in order to get the row before SET stops the data step with 0 row (h/t Tom for the pointer).

3

votes

Here is a easy method using the NOBS= option of the SET statement to insure that your dataset has at least one observation.

data want ;
  if 0=_nobs then output;
  set want nobs=_nobs;
run;

Note if the dataset is large and you did not want to have to re-write the data then you might use some method to conditionally generate the data step. For example you could test the automatic macro variable SQLOBS and if it is 0 then generate a data step. In this case no need to test the nobs since you already did. Also you could use the automatic macro variable SYSLAST instead of hard coding the dataset name. You could use CALL EXECUTE for this, but you could also just use IFC() function.

%sysfunc(dequote(
 %sysfunc(ifc(0=&sqlobs,'data &syslast;output;set &syslast;run;',''))
))

Of perhaps best of all use the MODIFY statement and conditionally run an OUTPUT statement. You could test the SQLOBS macro variable.

data &syslast ;
  if &sqlobs=0 then output;
  modify &syslast ;
  stop;
run;

or you could use NOBS= option the MODIFY statement.

data &syslast ;
  if 0=_nobs then output;
  modify &syslast nobs=_nobs;
  stop;
run;

All SQL solution

If you know at least one of the variable names then you can use and SQL insert statement.

insert into &syslast (varname) values (null);

So you might make a simple macro that takes Dataset Name, Number of Observations and a variable name as input.

%macro ifzeronull(dsn,nobs,avar);
%if &nobs=0 %then %do;
  insert into &dsn (&avar) values (null);
%end;
%mend ;

Then you can stay in the same PROC SQL call and conditionally add the observation.

proc sql;
  create table want as
    select * from sashelp.class
    where age=19
  ;
  %ifzeronull(&syslast,&sqlobs,name)
quit;

Proc SQL assignment of missing values

2 Answers