So, I had to chime in with a non-PROC REPORT contribution to this discussion. Does your data REALLY have ALL missing values??? Is there NO data for TYPE I, II, and III??? Or are you adding a page dimension to your table? Consider what happens if we modify the data to include values for the other types:[pre]
DATA work.ae ;
INFILE CARDS MISSOVER ;
INPUT ID $ site $ type ;
CARDS ;
01 41
02 42
03 43
04 44
05 41 1
06 42 1
07 43 1
08 44 1
09 41 2
10 42 2
11 43 2
12 44 2
13 41 3
14 42 3
15 43 3
16 44 3
RUN ;[/pre] and run the same PROC FORMAT and PROC TABULATE, then the output looks like this (label shortened to -(unk)- for space purposes:
[pre]
+--------+-----------------------------------+
| | type |
| # |--------+--------+--------+--------|
|enrolled|-(unk)- | Type I |Type II |Type III|
|--------+--------+--------+--------+--------|
| 16| 4| 4| 4| 4|
+--------+--------+--------+--------+--------+
[/pre]
In the above table, I don't see a duplicate column the # enrolled column is truly the sum of all the obs, missing included. Then, in each of the 4 buckets (unk, I, II, III) you have the count of the obs that fall into those categories. If we deconstruct your TABLE statement, you can see that PROC TABULATE is doing what you asked it to do:
TABLE (ALL="# enrolled" type)*N="" / PRINTMISS ;
which is the same thing as requesting:
TABLE ALL="# enrolled"*N type*N="" ;
but it doesn't matter because the table for all (# enrolled) is placed NEXT TO the tables for the individual values for TYPE.
Consider this different take on the table statement, which produces different results because of the placement of ALL in the ROW dimension, with TYPE in the column dimension:[pre]
table all='# enrolled',
type /printmiss box='Adverse Events' rts=16;[/pre]
Output is:
[pre]
+--------------+-----------------------------------+
|Adverse Events| type |
| |--------+--------+--------+--------|
| |-(unk)- | Type I |Type II |Type III|
| |--------+--------+--------+--------|
| | N | N | N | N |
|--------------+--------+--------+--------+--------|
|# enrolled | 4| 4| 4| 4|
+--------------+--------+--------+--------+--------+
[/pre]
Even better, if you DID have all missing data, with the above TABLE statement your ALL missing scenario would end up looking like this: [pre]
+--------------+-----------------------------------+
|Adverse Events| type |
| |--------+--------+--------+--------|
| |-(unk)- | Type I |Type II |Type III|
| |--------+--------+--------+--------|
| | N | N | N | N |
|--------------+--------+--------+--------+--------|
|# enrolled | 4| 0| 0| 0|
+--------------+--------+--------+--------+--------+
[/pre]
Now, let's put SITE into the TABLE statement (but just for fun, use the data with values for all types). When we do this, we now provide PROC TABULATE with MORE buckets to use to categorize the data:
[pre]
OPTION MISSING = "0" nocenter formchar='|-+++|+|+++';
PROC TABULATE DATA = work.ae FORMAT = 8. ;
class site;
CLASS type / PRELOADFMT MISSING ;
FORMAT type types. ;
table site all='# enrolled',type /printmiss box='Adverse Events' rts=16;
RUN ;
[/pre]
and the output looks like this, where the ALL is now a table at the bottom that contains the count of OBS for each TYPE, including (-unk-):[pre]
+--------------+-----------------------------------+
|Adverse Events| type |
| |--------+--------+--------+--------|
| |-(unk)- | Type I |Type II |Type III|
| |--------+--------+--------+--------|
| | N | N | N | N |
|--------------+--------+--------+--------+--------|
|site | | | | |
|--------------| | | | |
|41 | 1| 1| 1| 1|
|--------------+--------+--------+--------+--------|
|42 | 1| 1| 1| 1|
|--------------+--------+--------+--------+--------|
|43 | 1| 1| 1| 1|
|--------------+--------+--------+--------+--------|
|44 | 1| 1| 1| 1|
|--------------+--------+--------+--------+--------|
|# enrolled | 4| 4| 4| 4|
+--------------+--------+--------+--------+--------+
[/pre]
And THEN if you want to have even more fun than you thought was possible with PROC TABULATE,
change the TABLE statement to have ALL in both dimensions:
table site all='# enrolled',type all/printmiss box='Adverse Events' rts=16;
And see what happens. 😉
cynthia