topic Re: How to test for zero observations and still be in control in SAS Programming

How to test for zero observations and still be in control

ErikT — Fri, 12 Jan 2018 13:59:39 GMT

I recently came across a situation where an input data set had zero observations. Those things happen, but it should be reported loud and clear in the log or by sending an email to a responsible analyst.

My first reaction was to do it straight forward, like in:

DATA OutputSet;
SET InputSet NOBS=nobs;
IF nobs = 0 then 
  PUTLOG "!!!!!!!!!!! Empty input data set !!!!!!!!!';
RUN;

Well, that does not work. SAS reaches end of input before it ever reaches the IF statement and nothing is sent to the log.

Solution?

Keep in mind that the nobs variable receives its value during the compilation phase. So you can test it's contents before executing the SET statement. So the solution is simple: move the IF statement before the SET statement. This is the result in the log:

422  DATA OutputSet;
423  IF nobs = 0 then PUTLOG '!!!!!!!!!!! Empty input data set !!!!!!!!!';
424  SET InputSet NOBS=nobs;
425  RUN;

!!!!!!!!!!! Empty input data set !!!!!!!!!

NOTE: There were 0 observations read from the data set WORK.INPUTSET.
NOTE: The data set WORK.OUTPUTSET has 0 observations and 2 variables.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds

It is obvious that you could replace the PUTLOG statement by anything, including sending mail.

Re: How to test for zero observations and still be in control

ballardw — Thu, 12 Sep 2013 16:53:05 GMT

To make this more useful, make your statement start with "ERROR:" or "WARNING:' Then the text in the log will have the assigned colors for error or warning messages, at least in interactive mode.

Re: How to test for zero observations and still be in control

Jakkas — Thu, 12 Sep 2013 17:40:28 GMT

Thank you for sharing. Here is a complete code that combines ballardw's comment with ErikT's tip.

Proc Sql; /* Create an empty table. */
Create Table InputSet (x Char(8));
Quit;

DATA _null_;
SET InputSet NOBS=nobs;

IF nobs = 0 then PUT "ERROR: InputSet Table is empty"; /* Doesn't work. */
RUN;

DATA _null_;
IF nobs = 0 then PUT "ERROR: InputSet Table is empty"; /* Works. */
SET InputSet NOBS=nobs;
RUN;

But, a better way to check if a table has zero rows would be to use Proc Datasets as follows:

Proc Sql; /* Create an empty table. */
Create Table InputSet (x Char(8));
Quit;

Proc datasets nolist;
contents DATA=work.InputSet out=temp(Keep=NOBS) noprint;
run;

Data _Null_;
Set temp;
If NOBS = 0 Then Put "ERROR: Table is empty.";
Run;

Re: How to test for zero observations and still be in control

data_null__ — Fri, 12 Jan 2018 14:01:15 GMT

NOBS may not be the most reliable source of 0 records. Also if you want to use WHERE subset then EOF is a better indicator of "will there be any data?"

Using FILEVAR file statement option allows data step to send e-mail conditionally.

data _null_; 
   if eof then do; 
      id = "&sysuserid@yourdomain.com"; 
      length indsname $64; 
      file dummy email filevar=id;
      put "!EM_SUBJECT! INFO from &_CLIENTPROJECTNAME &_CLIENTTASKLABEL"; 
      PUT 'ERROR: Input Table ' indsname 'is empty'; 
      end; 
   stop; 
   set sashelp.class INDSNAME=INDSNAME end=eof;
   where sex eq 'S'; 
   run;

@ErikT wrote:

I recently came across a situation where an input data set had zero observations. Those things happen, but it should be reported loud and clear in the log or by sending an email to a responsible analyst.

My first reaction was to do it straight forward, like in:
DATA OutputSet;
SET InputSet NOBS=nobs;
IF nobs = 0 then 
  PUTLOG "!!!!!!!!!!! Empty input data set !!!!!!!!!';
RUN;
Well, that does not work. SAS reaches end of input before it ever reaches the IF statement and nothing is sent to the log.

Solution?

Keep in mind that the nobs variable receives its value during the compilation phase. So you can test it's contents before executing the SET statement. So the solution is simple: move the IF statement before the SET statement. This is the result in the log:
422  DATA OutputSet;
423  IF nobs = 0 then PUTLOG '!!!!!!!!!!! Empty input data set !!!!!!!!!';
424  SET InputSet NOBS=nobs;
425  RUN;

!!!!!!!!!!! Empty input data set !!!!!!!!!

NOTE: There were 0 observations read from the data set WORK.INPUTSET.
NOTE: The data set WORK.OUTPUTSET has 0 observations and 2 variables.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
It is obvious that you could replace the PUTLOG statement by anything, including sending mail.

Re: How to test for zero observations and still be in control

jakarman — Thu, 12 Sep 2013 18:51:53 GMT

SAS(R) 9.3 Macro Language: Reference (%sysfunc -- Example 5: Determining the Number of Variables and Observations in a Data)

%let dsid = %sysfunc( open(myDataSet) );

%let nobs = %sysfunc( attrn(&dsid,nobs) );

%let rc = %sysfunc( close(&dsid) );

%if &nobs gt 0 %then %do. . .

Re: How to test for zero observations and still be in control

kjohnsonm — Wed, 23 Mar 2016 23:53:51 GMT

This is a great topic, if instead of logging or emailing like shown I wanted to add one record to the dataset with the string

'No data found' or something similar what would be a good a process to do for this case? I am sorry if this should be a new topic. I like the thread but in my case I have a macro that processes 6-8 file types and is processing my full file system for metadata and in some cases there is no result and I wanted my master report to have a new tab per file type with an one liner saying "no data found" for any given type meeting the condition.

I have the macro for the ODS, what I am not sure how to do is if empty dataset found add the one liner inside the ods or as a seperate data step/proc SQL right before. TIA from a green horn. 😎

PS if the ODS call can do this all the better- but that for sure should be a new topic.

Re: How to test for zero observations and still be in control

data_null__ — Thu, 24 Mar 2016 12:51:03 GMT

I use something like this. If the data is empty add one obs and check for a missing variable in COMPUTE AFTER; MODIFY keeps from breaking the data when it HAS obs.

EDIT: 24MAR2015 My example data has ONE obs but should have ZERO so the MODIFY is not adding an observations with all missing values. The output statement in the first data step should be removed to model the exact scenario. The PROC REPORT works the same either way no matter how the "one all missing obs data set is created".

This method has can be modified to function at the end of a breaking variable not just at end of report. I have used this technique in BIMO listings where I want a message "No data for this study site: <siteid>" the site records are created by merging the listing data with a list (data set) of all sites.

data class;
   *output; to model the zero obs scenario;
   stop;
   set sashelp.class;
   run;
*Add one obs to class if it is empty;
data class;
   if eof then output;
   stop;
   modify class end=eof;
   run;
options missing=' ';
proc report data=class list missing;
   columns _all_;
   define name / order;
   compute after name;
      msg = 'No data for this report.';
      l = length(msg);
      if not missing(name) then l=0;
      line msg $varying. l;
      endcomp;
   run;

Re: How to test for zero observations and still be in control

kjohnsonm — Wed, 23 Mar 2016 23:55:37 GMT

That dose produce the type result I want/need.

I will have to learn proc report, it is not in my arsenal at this time, or see if any options relate to the ODS. I was talking about having each data set defined so I might use them with a ODS print like this:

* --- Generate Excel report ---;
ods Tagsets.ExcelXP 
    path = "&pname."
    file = "&outputfile."
	style = Irstyle_xl_sm_titles_grid
	options (
        Embedded_Titles='Yes'
		Embedded_Footnotes='Yes'
        print_footer='&amp;L&amp;A &amp;R(&amp;P of &amp;N)'
		zoom='100'
	 );


%macro excel_report(databasetype);
ods Tagsets.ExcelXP options 
(
    sheet_interval='proc'
	sheet_name="&databasetype."
	orientation = 'landscape'
	frozen_headers = '5'
	frozen_rowheaders = '2'
	row_repeat='4-5'
	column_repeat='1-2'
	Pages_FitWidth='1'
	Autofit_height='yes'
	Absolute_Column_Width='27.5,7.4,16,16,16,16,,8,8,8,8,8'
	Scale='90'
);

* --- print ---;
proc print data=d_&databasetype._w_match noobs label split='\';
title1 "Files found with data type: &databasetype.";
footnote2 "&tdate.";
footnote3 "file: &outputfile.";
footnote4 "program: &fname";
run;
%mend excel_report;

%excel_report(MDB);
%excel_report(DBF);
%excel_report(SAV);
%excel_report(XLS);
...

ods tagsets.Excelxp close ;
...
where all these data sets exist...
/*proc print data= d_DBF_w_match;run;*/
/*proc print data= d_SAV_w_match;run;*/
/*proc print data= d_XLS_w_match;run;*/
...

It seems like if I was reading your code and results correctly that no record was actually created with your method. However I now have options. output is the key not the road i travel... Thank you.

Re: How to test for zero observations and still be in control

data_null__ — Thu, 24 Mar 2016 12:41:20 GMT

This one replaces the data set with one record and one var MSG. If the data has records it is untouched just be sure to STOP before the SET.

This works as long as your PROC PRINT does not have a VAR statement. If it did you could create a macro variable flag in the CALL EXECUTE to change the VAR statement. %if &noobs %then %do;,,,%end; %else %do; ,,, %end;

data class;
   stop;
   set sashelp.class;
   run;
*Add one obs to class if it is empty;
%let databasetype=class;

data _null_;
   if eof then do;
      call execute("data &databasetype; msg='No data to report'; label msg='\'; run;");
      end;
   stop;
   set class end=eof;
   run;
options missing=' ';
proc print data=&databasetype noobs label split='\';
   title1 "Files found with data type: &databasetype.";
   run;
title;
footnote2;