How to identify an empty dataset?

[Lani]

Hi Everyone,

I am working on creating an Excel report using proc export. However, I don't want to create a report if the dataset is empty. Could anyone please suggest me a way how to check to see if a dataset is empty or not?

Thanks in advance for your help.
Lani

[LinusH]

You could use the ATTRN function, which will give tje number of observations in a data set.

/Linus

[Ksharp]

I don't understand exactly about empty dataset .
Is that to mean no observations or all of variables in the dataset have missing value.
The following code can print the number of observations in the log .
[pre]
data _null_;
set yourdataset nobs=number;
put number= ;
stop;
run;
[/pre]

Also you can use proc sql to get the number of observations.

Ksharp

[Bill]

Untested ...

data _null_; /*data step to check for number of records in file to be exported/*
set exportfile end=last;
if last then call symput("Records",_n_); /*_n_ is observation number in sas dataset*/
run;

%macro Export;
%if &Nobs > 0 %then %do; /*if records > 0 then execute the export proc*/
proc export ....
run; %end;
%mend Export;
%Export;

[sbb]

The IF test must occur BEFORE the SET to work with a zero-observations condition.

Scott Barry
SBBWorks, Inc.

[Sandhya]

Hi,

The ATTRN function will help. If that looks difficult proc sql can also be used.

proc sql noprint;
select count(*) into :obs_count from dataset1;
quit;

%if (&obs_count ne) %then %do;
proc export ........
..........

%end;

Sandhya.

[data_null__ · Accepted solution]

Editor's note: This topic is very popular.  data_null__ , @Bill, @sbb, @Ksharp and others have contributed great ideas.  We've consolidated those here with a little more detail to help future readers.

For this kind of problem the question is not
 - how many obs?
but
 - are there zero obs?

You don't have to count anything -- just check for imediate EOF.

/* Init check flag */
%let dsempty=0;

/* destination for XLSX file, if generated */
filename outfile "%sysfunc(getoption(work))/class.xlsx";

/* create empty test data set by removing all records */
data class;
 set sashelp.class;
run;
data class;
 modify class;
 remove;
run;

/* Check for empty data */
data _null_;
  if eof then
    do;
     call symput('dsempty',1);
     put 'NOTE: EOF - no records in data!';
    end;
  stop;
  set class end=eof;
run;

%macro Export;
  %if &dsempty. %then
    %do;
      %put WARNING: Export step skipped - no output records.;
    %end;
  %else
    %do;
      /*if not empty then execute the export proc*/
      proc export data=class dbms=xlsx 
        file=outfile replace;
      run;
    %end;
%mend Export;

%Export;

[Schoolmaster]

This is what I've been looking for, simple and very useful.