This is from a poster I gave at SUGI 25:

----- PROVIDE AN OPTION TO PRODUCE A HEADER LINE WHEN THE INPUT DATASET CONTAINS NO OBSERVATIONS (1999, 1998)

This is one of the most commonly asked questions on the SAS-L email list. This solution is simple and will handle almost all data sets correctly. It will also handle WHERE clauses:

%macro prnt0obs (data=_last_, msg="No items to display.", line=5, column=5);

data _null_; call execute('data _null_; file print;'); call execute("put #&line. @&column. " || quote(&msg.) || ';'); set &data.; call execute('run cancel;'); stop; run; * Inserted code will be executed here. ; run;

%mend prnt0obs;

You might use it like this:

title 'Iris Data'; proc print data=sasuser.iris (where=(sepallen=0)); %prnt0obs( data=sasuser.iris (where=(sepallen=0)) );

There will be no observations in SASUSER.IRIS fulfilling the WHERE clause, so this code will be generated:

data _null_; file print; put #5 @5 "No items to display.";

A page will be printed in the log telling you that no observations were available to print. Note that the set up for the PROC PRINT will be used for this page (I consider that to be a good feature).

If the WHERE clause selects some observations, this code would be generated:

data _null_; file print; put #5 @5 "No items to display."; run cancel;

The run cancel statement will stop the data step from executing, and the "no items" page will not print.

A more common solution to this type of problem is to use the NOBS= option of the SET statement to obtain the number of observations; if NOBS = 0, then you print a message. There are two problems with that method: it doesn't work correctly with data sets that have deleted observations, or with any kind of view; and it doesn't handle WHERE clauses. Using the observation count from DICTIONARY.TABLES or SASHELP.VTABLE has similar problems.

Another alternative is to use the data set information function ATTRN to get the number of logical observations (NLOBS). This works, but doesn't handle WHERE clauses. You also have to check whether the data set is a real data set or a view; if it's a view, you still have to iterate though the observations. That approach is a lot more work with probably no gain in execution speed.

-----

-- JackHamilton@FirstHealth.com Development Manager, Technical Group METRICS Department, First Health West Sacramento, California USA

>>> "Paula M. Adkins" <PAdkins@CHECKFREE.COM> 06/14/2001 1:47 PM >>> MVS Mainframe-SAS 8.0 Beginner:

I have a WHERE clause in my PROC FREQ and PROC TABULATE:

where [variable] in ('A') and [variable] in ('B')

when the program runs and there are 0 observations that meet this criteria, I would like my output to still print the page for the PROC TABULATE and the PROC FRQ, but I want it to say something to the effect like, "No data met the criteria" or whatever.

Any suggestions?

Paula M. Adkins, Risk Analyst I Risk & Source Management Dept. CheckFree Corporation 6000 Perimeter Drive Dublin, OH 43017 614-564-3117, 614-564-4308-fax www.checkfree.com