BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Lani
Calcite | Level 5
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
1 ACCEPTED SOLUTION

Accepted Solutions
data_null__
Jade | Level 19

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

7 REPLIES 7
LinusH
Tourmaline | Level 20
You could use the ATTRN function, which will give tje number of observations in a data set.

/Linus
Data never sleeps
Ksharp
Super User
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
Quartz | Level 8
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
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
The IF test must occur BEFORE the SET to work with a zero-observations condition.

Scott Barry
SBBWorks, Inc.
Sandhya
Fluorite | Level 6
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__
Jade | Level 19

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
Fluorite | Level 6
This is what I've been looking for, simple and very useful.

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

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