Help using Base SAS procedures

How to identify an empty dataset?

Accepted Solution Solved
Reply
Contributor
Posts: 22
Accepted Solution

How to identify an empty dataset?

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

Accepted Solutions
Solution
‎07-07-2016 11:03 AM
Respected Advisor
Posts: 3,777

Re: How to identify an empty dataset?

[ Edited ]

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;

View solution in original post


All Replies
Super User
Posts: 5,260

Re: How to identify an empty dataset?

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

/Linus
Data never sleeps
Super User
Posts: 9,691

Re: How to identify an empty dataset?

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
Super Contributor
Posts: 291

Re: How to identify an empty dataset?

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;
Super Contributor
Super Contributor
Posts: 3,174

Re: How to identify an empty dataset?

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

Scott Barry
SBBWorks, Inc.
Contributor
Posts: 57

Re: How to identify an empty dataset?

Hi,

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

proc sql noprint;
select count(*) into Smiley Surprisedbs_count from dataset1;
quit;

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

%end;

Sandhya.
Solution
‎07-07-2016 11:03 AM
Respected Advisor
Posts: 3,777

Re: How to identify an empty dataset?

[ Edited ]

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;
New Contributor
Posts: 3

Re: How to identify an empty dataset?

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

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 26434 views
  • 1 like
  • 8 in conversation