DATA Step, Macro, Functions and more

How to test for zero observations and still be in control

Reply
Contributor
Posts: 31

How to test for zero observations and still be in control

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 ma

Super User
Posts: 10,518

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

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.

Occasional Contributor
Posts: 16

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

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;

Respected Advisor
Posts: 3,777

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

[ Edited ]

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; 
Valued Guide
Posts: 3,208

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

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

---->-- ja karman --<-----
Frequent Contributor
Posts: 90

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

[ Edited ]

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.   8)   

 

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

Respected Advisor
Posts: 3,777

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

[ Edited ]

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;

Capture.PNG

Frequent Contributor
Posts: 90

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

[ Edited ]

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.

Respected Advisor
Posts: 3,777

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

[ Edited ]

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;

 

Capture.PNG

 

 

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;
Ask a Question
Discussion stats
  • 8 replies
  • 12168 views
  • 5 likes
  • 6 in conversation