Outputting an Empty Proc Freq table

[dmuzyka]

Hello all,

I have a shell script that runs a SAS program in batch and then emails me the results so I can visually check for anomalies. Some of the checks I run involve a PROC FREQ where an empty table means everything worked properly. My issue is that an empty table will not be generated and therefore throw off the notes and titles in the document I receive. My approach in SAS was to create multiple ODS documents as the quality checks are generated throughout the program, then replay them all at once using PROC DOCUMENT to write all the outputs to a single PDF that can be emailed to me. My generic code is as follows: 

%let filehold = /filepath;
libname holding "/filepath";

ods _all_ close;

ods document name=holding.table1 ;
title "First";
ods text='For cylinders we need to visually check that most models have 4 or 6 cylinders, etc';
proc freq data=sashelp.cars;
table cylinders;
run;
ods document close;

ods document name=holding.table2;
title "Second";
ods text='The second table should be empty. No cars should have 14 cylinders';
proc freq data=sashelp.cars;
table cylinders / list;
where Cylinders = 14;
run;
ods document close;

ods document name=holding.table3;
title "Third";
ods text='For MPG we should not see any unreasonable values like 0 or 10000';
proc means data=sashelp.cars;
var MPG_City;
run;
ods document close;

ods pdf file="&filehold/results.pdf";

proc document name=holding.table1;
replay;
run;
quit;

proc document name=holding.table2;
replay;
run;
quit;

proc document name=holding.table3;
replay;
run;
quit;

ods pdf close;

Of the tables created, table 2 should be empty. My current results can be seen in the attached file. Is there something I can do to force a table from PROC FREQ even if it is empty? Or is there a way I could have PROC DOCUMENT  insert a page break if the table being replayed is empty? Or is there a totally different approach I should consider?

I am using SAS 9.2

Thanks!

[ballardw]

How about just making the description more in line. Such as instead of

ods text='The second table should be empty. No cars should have 14 cylinders'

Something like:

ods text=' No cars should have 14 cylinders. If there are any, a table with the count follows';

Though I may be warped. My data quality reports when dealing with unexpected values tended to only report those conditions that were not expected.

I probably would have worked a different example. Your table one already would have had the cylinders=14 (or more).

[dmuzyka]

Thank you,

I know I probably could've made a more convincing example since cylinders are already shown in table 1, but this is not my concern.

Your change to text does make the email more descriptive about the presence or absence of a table, but it does not fix the problem. My concern is that when no table is produced, the third table gets put on the same page where the second table would be. This creates a misleading page with the title "Second", the text for table 2, the text for table 3, and the third table (see attached pdf in my original post).

I have tried using the obpage statement with the after option in Proc Document, but I have not been able to introduce a page break.

[ballardw]

---

@dmuzyka wrote:

Thank you,

I know I probably could've made a more convincing example since cylinders are already shown in table 1, but this is not my concern.

Your change to text does make the email more descriptive about the presence or absence of a table, but it does not fix the problem. My concern is that when no table is produced, the third table gets put on the same page where the second table would be. This creates a misleading page with the title "Second", the text for table 2, the text for table 3, and the third table (see attached pdf in my original post).

I have tried using the obpage statement with the after option in Proc Document, but I have not been able to introduce a page break.

---

Place the Title statement after the ODS text. If there is no proc output then the title won't appear.

I might also suggest routinely clearing Title statements at the end of procedures. I practically can't type Run; without having it followed by Title; to clear the titles.

Or delve into  the macro language and conditional creation of output. I have macros for some common bits that test if the condition has any results and if so create at table, otherwise use Ods text or Proc Odstext to indicate no records were found. Depending on the version of SAS you are using you may be able to use %if %then %do in open code blocks. If there are records:

Proc sql noprint;
   select count(*) into : varcount
   from somedataset
   where condition=somevalue 
   ;
quit;

%if &varcount > 0 %then %do;
  <your proc freq goes here>
%end;
%else %do;
<code to create empty table>
%end;

But you would have to indicate what the "empty table" should actually look like to get suggestions on creating one. Likely a Data _null_ step with some ODS _PUT_ statement or similar.