BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ErikT
Obsidian | Level 7

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.

1 ACCEPTED SOLUTION

Accepted Solutions
data_null__
Jade | Level 19

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.

 

View solution in original post

8 REPLIES 8
ballardw
Super User

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.

Jakkas
Calcite | Level 5

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;

data_null__
Jade | Level 19

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.

 

jakarman
Barite | Level 11

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 --<-----
kjohnsonm
Lapis Lazuli | Level 10

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.

data_null__
Jade | Level 19

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

kjohnsonm
Lapis Lazuli | Level 10

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.

data_null__
Jade | Level 19

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;

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 8 replies
  • 44325 views
  • 6 likes
  • 6 in conversation