if _N_ = 1 condition returns true even if the set dataset is empty (zero observations) in SAS

Question

A doubt on SAS:

    data new;
        set _NULL_;
    run;

   data _NULL_;
        set new;
        if _N_ = 0 then call execute ("%put empty dataset;");
        if _N_ = 1 then call execute ("%put non-empty dataset;");
   run;

The above bit of code in my understanding should only print the first comment, i.e. empty dataset. For some reason though it is returning a true for the second if condition as well and printing non-empty dataset as well.

Please let me know where am I going wrong with this?

Answer 1

Okay, here's what I think is going on. The first problem is that your macro invocations are in double quotes, and therefore being processed by the pre-processor before SAS has even started to process the data step (executing regardless of whether the if condition is true or not). You need to put the argument to execute in single quotes rather than double quotes to keep it from being prematurely executed by the macro pre-processor.

This code still won't work on an empty dataset, however, as if the dataset supplied on the set line is empty then the entire data step terminates, before any further code is executed.

Thirdly, _N_ is initialised to 1, not 0, and incremented from there on the data step boundary, so the _N_ = 0 condition will always be false.

An alternative way of going about this would be to use the nobs= option to set as follows:

data _NULL_;
  if 0 then set new nobs=num_obs;
  if num_obs = 0 then call execute ('%put empty dataset;');
  if num_obs > 0 then call execute ('%put non-empty dataset;');
stop;
run;

The if 0 then is a dummy condition used to force execution of the data step code; if a bare set statement were used then execution would not continue past the set line if the dataset "new" were empty.

A better option might be to use macros to open the dataset and read the ANY attribute:

%let dsid = %sysfunc (open(dataset_name));
%let anyobs = %sysfunc (attrn(&dsid,ANY));
%let dsid = %sysfunc (close(&dsid));

The macro variable &anyobs will be 1 if dataset_name contains at least one observation (row) and at least one variable (column), 0 if it contains no observations but at least one variable, and -1 if it contains no observations and no variables.