Proc Report/Tabulate to show results with zero occurences?

[deleted_user]

I have a problem trying to run a proc tab on a file where records are dropped by a hierarchy of criteria. My pesky business partners would like to see all possible drops, even if zero records were dropped for a criterion. Is there a way (by using a proc format by chance?) that I can get these results to show?

I run the criteria thru a data step with a large if-then-else statement setting a drop indicator such as "a01", "a02", "a03"... and then running a proc tabulate on the drop indicator to get totals.

I'd apreciate any help on my problem. Thanks!

[Cynthia_sas]

Hi:
Both PROC REPORT and PROC TABULATE support the PRELOADFMT option, which can be used to surface combinations with missing values. In addition, both PROC REPORT and PROC TABULATE have MISSING options.PROC TABULATE has a PRINTMISS option as well. You can use COMPLETEROWS and COMPLETECOLS options with PROC REPORT and PRELOADFMT to get all combinations of GROUP variables. Some examples (mostly with tabulate):

http://support.sas.com/kb/25/403.html
http://support.sas.com/kb/25/400.html
http://support.sas.com/kb/25/101.html
http://support.sas.com/documentation/onlinedoc/v82/whatsnew/z1838923.htm

And, there's a PROC REPORT example below.

cynthia
[pre]

options nodate nonumber ps=60 ls=100 missing=0;
title;
footnote;
ods listing;

data testdata;
input dest $ type $ amt;
cards;
SYDNEY TEL 900
SYDNEY TEL 800
RIO TRP 700
RIO TRP 600
MOSCOW TEL 500
MOSCOW WEB 600
MOSCOW TRP 700
DUBLIN TEL 800
DUBLIN TRP 900
;
run;

proc format;
value $tktfmt 'TEL'='Telephone Agent (Airline Employee)'
'WEB'='On-line Agent (Airline Employee)'
'TRP'='Travel Agent (Private Company)'
'SLF'='Free Agent (SELF Booked) ';
run;

*** Default PROC REPORT Behavior ***;
proc report data=testdata nowd ;
column dest type amt;
define dest / group 'DEST';
define type / group 'TYPE' format=$tktfmt.;
define amt / analysis sum 'AMT';
break after dest /skip;
rbreak after / summarize ul ol;
title1 'PROC REPORT Default Behavior';
run;

*** PRELOADFMT option: Review behavior WITHOUT COMPLETEROWS ***;
proc report data=testdata nowd;
column dest type amt;
define dest / group 'DEST';
define type / group 'TYPE' format=$tktfmt. preloadfmt;
define amt / analysis sum 'AMT';
break after dest /skip;
rbreak after / summarize ul ol;
title1 'PROC REPORT without COMPLETEROWS option';
title2 'ONLY PRELOADFMT on the DEFINE statement';
run;

*** Completerows ONLY ***;
proc report data=testdata nowd completerows;
column dest type amt;
define dest / group 'DEST';
define type / group 'TYPE' format=$tktfmt. ;
define amt / analysis sum 'AMT';
break after dest /skip;
rbreak after / summarize ul ol;
title1 'PROC REPORT with COMPLETEROWS option';
run;

*** Completerows with PRELOADFMT ***;
proc report data=testdata nowd completerows;
column dest type amt;
define dest / group 'DEST';
define type / group 'TYPE' format=$tktfmt. preloadfmt;
define amt / analysis sum 'AMT';
break after dest /skip;
rbreak after / summarize ul ol;
title1 'PROC REPORT with COMPLETEROWS option';
run;
[/pre]