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-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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