reporting zero cases or rows when doing a Proc freq (or?)

[IggyDad]

I often need to produce a count of the number of cases of certain diseases by geographic area, such as counties. The data set only consists of cases. When I run a PROC FREQ for the counts by county, I later export it to Excel to format it, etc. The trouble is that each time I run the report, different counties have zero counts for the disease, so there are no rows on the FREQ output for them. It makes comparing the tables from previous time frames difficult, and to make the Excel tables comparable I need to stick in rows or cells with zeroes if I want to combine the tables, which is tedious and error prone. How can I consistently get SAS to output frequencies for all 100 jurisdictions whether or not they have any observations (zero rows of cases) in the data for that time period?

[Cynthia_sas]

hi:
Proc REPORT, MEANS and TABULATE all support the use of the PRELOADFMT option which can be used to provide a list of the universe of values that you would like to see and then if you use that option in the appropriate way, with your user defined format, you will get the zero rows you want. Sadly, PROC FREQ does not support the PRELOADFMT usage.

Consider this data:
[pre]

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
. WEB 800
;
run;
[/pre]

Notice how we have TEL, TRP and WEB type of Sales, but we don't have any SLF (self-booked) sales types. Also, we have an data line with a missing city or DEST. And, each city has different combos of type values.

If you use PROC REPORT, you don't need to do PRELOADFMT right off the bat. You can try COMPLETEROWS first. But then you might need to use PRELOADFMT if you needed to have a "zero" row for the SLF type (Self-Booked) and there were no SLF rows in the data.

The programs below show the different ways that you can use either COMPLETEROWS or COMPLETEROWS with PRELOADFMT and PROC REPORT. An investigation of the documentation will show you about PRELOADFMT and TABULATE or MEANS. This examples shows getting the SUM of AMT, but you could just as easily have changed the statistic to N for amt or just coded the N statistic on the column statement for PROC REPORT.

cynthia

[pre]
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 missing;
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;

*** Completerows ONLY ***;
proc report data=testdata nowd completerows missing;
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 missing;
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 and PRELOADFMT option';
run;

[/pre]

[ChrisNZ]

classdata is also useful in this case.
proc means is just as good as proc freq for counting, and it can use the classdata option:

data AGES;
do AGE=10 to 20;
output;
end;
run;
proc means data=SASHELP.CLASS classdata=AGES noprint nway;
class AGE;
output out=COUNTS(keep=AGE _FREQ_) n=;
run;

[Cynthia_sas]

PROC TABULATE also supports CLASSDATA with or without the EXCLUSIVE option to go with CLASSDATA. There are a variety of techniques/procs that will do the job. But,sadly, PROC FREQ is not one of them. MEANS, TABULATE and REPORT can all generate the N statistic.

Benchmarking to see which approach is suitable would be the next step (after consulting the documentation) to see whether PRELOADFMT or CLASSDATA or a simple COMPLETEROWS is the best approach. It really comes down to whether the data contains all the needed combinations or not.

cynthia

[data_null__]

You "can" get proc freq to display rows and columns of zeros but it has to get some help from another source. Using PROC SUMMARY with PRELOADFMT is one way to help proc freq. Note ZEROS options on weight is version V9 and up.

[pre]
proc format;
value all 1=1 2=2 3=3;
run;
data zeros;
output;
call missing(row,col);
run;
proc summary nway completetypes;
class row col / preloadfmt;
format row col all.;
output out=counts(drop=_type_);
run;
proc freq;
tables row*col;
weight _freq_ / zeros;
format row col;
run;
[/pre]