02-20-2013 01:46 PM
Here's a little macro I wrote today in SAS 9.3 Windows that will detect a zero-observation condition in a SAS dataset and, if encountered, will replace the contents of the Results Viewer with a message and terminate the SAS program gracefully. Perhaps not elegant, but I hope some of you will find this to be useful.
Sul Ross State University
%* end program if zero observations encountered;
%let lib = %upcase(&l);
%let dsn = %upcase(&d);
proc sql noprint;
select nobs into :nobs separated by " "
where libname = &lib and memname = &dsn;
%if &nobs = 0 %then %do;
ods html close;
message = 'NO RECORDS RETURNED - ENDING PROGRAM';
proc print data=zeroobs noobs;
02-20-2013 06:38 PM
I'd recommend adding the following %local statement in at the beginning of the macro to avoid clobbering any macro variables with the same names that might be used in your main SAS program:
%local l d lib dsn nobs;
02-20-2013 07:27 PM
Do you really need to know how many? Consider this data step.
02-21-2013 03:46 PM
Clever, data_null_, interesting data _null_ step usage I have not seen before.
I still prefer my technique because it clears out the Results Viewer and places a message there instead of the SAS log, and the %abort cancel keeps all the rest of the SAS program from running.
02-21-2013 04:36 PM
I'm sure you can still do the clearing and aborting as you see fit. My point is that you don't need to count anything. This technique adds the ability to test for a zero obs subset created with a where clause, which for me is a more common application. Once you "get the answer" what you do is up to you.
02-21-2013 04:52 PM
The beauty of using PROC SQL to read the SAS dictionary tables is that the number of observations has already been counted (NOBS) and I am just reading the value that is already on the table. So either technique will work fine. One of the things I love about SAS is there are multiple good ways to do just about anything. Thanks for sharing your insight.
02-21-2013 06:09 PM
You might consider a different variable from dictionary.tables
nlobs num label='Number of Logical Observations',
As demonstrated by this program using NOBS which produces and incorrect result. Of course the example is somewhat contrived.