topic Show frequency 0 in proc tabulate in SAS Programming

Show frequency 0 in proc tabulate

Ronein — Mon, 03 Feb 2020 05:11:35 GMT

Hello

In the following example I want to show also categories with frequency=0

In this example there is nobody with category "Left" so I want to show 0 under this category.

What is the way to do it please using proc tabulate?

Data rawtbl;
input ID status ;
cards;
1 2
2 2
3 1
4 3
5 3 
6 2
7 2
8 1
9 3
10 1
;
run;

proc format ;
value oorderCode
1='worse'
2='Better'
3='Same'
4='Left'
;
Run;

title;
PROC TABULATE DATA=rawtbl f=comma21.;
	VAR ID   ;
	CLASS status /	ORDER=UNFORMATTED MISSING;
	TABLE status="" all='TOTAL',
	     ID=''*N='No_Customers'
		 ID=''*pctn='PCT_No_Customers'*f=my1pct. /box="Status";
    format status oorderCode.;
RUN;

Re: Show frequency 0 in proc tabulate

SASKiwi — Mon, 03 Feb 2020 05:19:12 GMT

You may find your answer here: https://communities.sas.com/t5/SAS-Procedures/How-To-Shows-PROC-TABULATE-s-Empty-Rows/td-p/285178

Plus probably the MISSING = 0 SAS option.

Re: Show frequency 0 in proc tabulate

Ronein — Mon, 03 Feb 2020 05:23:53 GMT

I know how to do it in proc report

proc report data=rawtbl nowd completerows;
column ID status PCTN;
define status /group format=oorderCode. preloadfmt exclusive;
define ID /N format=4. 'Freq';
define PCTN / 'PCTN' format=percent9.1;
run;

Re: Show frequency 0 in proc tabulate

Ronein — Mon, 03 Feb 2020 05:44:51 GMT

Great.

As I see the answer is to use PRELOADFMT option with Printmiss

title;
PROC TABULATE DATA=rawtbl f=comma21.;
	VAR ID   ;
	CLASS status / MISSING PRELOADFMT;
	TABLE status="" all='TOTAL',
	     ID=''*N='No_Customers'
		 ID=''*pctn='PCT_No_Customers'*f=my1pct. /box="Status" Printmiss MissText='';
    format status oorderCode. ;
RUN;