Show all categories (even with no cases) in PROC TABULATE

[dstuder]

Hello everybody!

I have the following sample data:

DATA offenders;
INPUT group :$1. age :3. residenceStatus :$1. sex :$1.;
INFILE DATALINES DSD;
DATALINES;
A,10,A,m
A,14,A,m
A,11,B,m
B,21,A,w
B,20,B,m
B,,B,m
B,34,C,m
A,60,C,w
;
RUN;

PROC FORMAT;
VALUE agegrp 0-10 = "0-10"
			 11-20 = "11-20"
			 21-30 =  "21-30"
             31-40 = "31-40"
             40-high = "41+"
			 . = "unknown";
RUN;

and the following proc tabulate:

PROC TABULATE;
	class group; 
	class age / preloadfmt missing;
	class residenceStatus;
	TABLE group, residenceStatus * age;
	FORMAT age agegrp.;
RUN;

How can I repeat all agegroups (0-10, 11-20, 21-30, unknown) for every residence status (A, B, C)?

I have experimented somewhat with "preloadfmt" but didn't get it to work properly. Thanks for help!

[ballardw]

You have a typo in your format.

To get PRELOADFMT to display values that do not appear in the data you need the PRINTMISS option in the table options:

PROC FORMAT;
VALUE agegrp 0-10 = "0-10"
			    11-20 = "11-20"
			    21-30 =  "21-30"
             31-40 = "31-40"
             40-high = "41+"
			    . = "unknown";
RUN;

PROC TABULATE;
	class group; 
	class age / preloadfmt missing ;
	class residenceStatus;
	TABLE group, 
         residenceStatus * age
         /printmiss ;
	FORMAT age agegrp.;
RUN;

sometimes you may want the ORDER=DATA on the class statement with the Preloadfmt as well. It can depend on the actual contents of your data.

[Reeza]

When I ran your code (SAS On Demand) I get this error message in the log:

 WARNING: PreloadFmt will have no effect on the output without one of the following options: "printmiss", "order=data", or the class 
          statement option "exclusive".

I then added the printmiss statement to the TABLE statement and it works as expected. You can add misstext to have them show as 0 rather than a period.

	TABLE group, residenceStatus * age / printmiss misstext='0';

---

@dstuder wrote:

Hello everybody!

I have the following sample data:

DATA offenders;
INPUT group :$1. age :3. residenceStatus :$1. sex :$1.;
INFILE DATALINES DSD;
DATALINES;
A,10,A,m
A,14,A,m
A,11,B,m
B,21,A,w
B,20,B,m
B,,B,m
B,34,C,m
A,60,C,w
;
RUN;

PROC FORMAT;
VALUE agegrp 0-10 = "0-10"
			 11-20 = "11-20"
			 21-30 =  "21-30"
             31-40 = "31-40"
             40-hight = "41+"
			 . = "unknown";
RUN;

and the following proc tabulate:

PROC TABULATE;
	class group; 
	class age / preloadfmt missing;
	class residenceStatus;
	TABLE group, residenceStatus * age;
	FORMAT age agegrp.;
RUN;

How can I repeat all agegroups (0-10, 11-20, 21-30, unknown) for every residence status (A, B, C)?

I have experimented somewhat with "preloadfmt" but didn't get it to work properly. Thanks for help!

---