topic Re: How to identify an empty dataset? in SAS Procedures

How to identify an empty dataset?

Lani — Thu, 27 May 2010 03:23:26 GMT

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

Re: How to identify an empty dataset?

LinusH — Thu, 27 May 2010 09:46:26 GMT

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

/Linus

Re: How to identify an empty dataset?

Ksharp — Thu, 27 May 2010 13:41:23 GMT

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

Re: How to identify an empty dataset?

Bill — Thu, 27 May 2010 19:20:31 GMT

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;

Re: How to identify an empty dataset?

sbb — Thu, 27 May 2010 22:35:37 GMT

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

Scott Barry
SBBWorks, Inc.

Re: How to identify an empty dataset?

Sandhya — Tue, 01 Jun 2010 15:16:57 GMT

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.

Re: How to identify an empty dataset?

data_null__ — Thu, 07 Jul 2016 15:03:21 GMT

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;

Re: How to identify an empty dataset?

Schoolmaster — Sat, 18 Mar 2017 15:20:46 GMT

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