BookmarkSubscribeRSS Feed
deleted_user
Not applicable
Hi,
I have the following data:
ID site type
01 41
02 42
03 43
04 44

There are three types available (I, II, III) however the above don't fall into any of the three. I'd like to make a table that shows the following:

#enrolled TypeI TypeII TypeIII
4 0 0 0

I've tried PROC TABULATE but it says 'Class, frequency, weight missing...' How do I do this? Thanks very much!
8 REPLIES 8
Olivier
Pyrite | Level 9
How about trying to design a format and use the PRELOADFMT option ? This does inspect the "expected" values described in the format and displays columns or rows for each and every of them, even if there is not data to connect to each value. Use it with the PRINTMISS option to have it work properly.

DATA work.ae ;
INPUT ID type ;
CARDS ;
01 41
02 42
03 43
04 44
RUN ;
PROC FORMAT ;
VALUE types
1 = "Type I"
2 = "Type II"
3 = "Type III"
OTHER = " "
;
RUN ;
OPTION MISSING = " " ;
PROC TABULATE DATA = work.ae FORMAT = 8. ;
CLASS type / PRELOADFMT ;
FORMAT type types. ;
TABLE (ALL="# enrolled" type)*N="" / PRINTMISS ;
RUN ;
OPTION MISSING = "." ;

I hope it will be of any help.
Olivier
deleted_user
Not applicable
Thanks very much! This is very close to what I'm looking for but not quite. It is not totaling the number of IDs. For "# enrolled" it outputs "0". It should say "4". Any input? Thanks again!
Olivier
Pyrite | Level 9
With this code :

DATA work.ae ;
INPUT ID type ;
CARDS ;
01 41
02 42
03 43
04 44
RUN ;
PROC FORMAT ;
VALUE types
1 = "Type I"
2 = "Type II"
3 = "Type III"
OTHER = " "
;
RUN ;

/* !!! I've corrected this line */
OPTION MISSING = "0" ;

PROC TABULATE DATA = work.ae FORMAT = 8. ;
CLASS type / PRELOADFMT ;
FORMAT type types. ;
TABLE (ALL="# enrolled" type)*N="" / PRINTMISS ;
RUN ;
OPTION MISSING = "." ;


I have :

+---------------------------------------------
| | type |
| # +--------+--------+--------+--------+
|enrolled| Type I |Type II |Type III| |
+--------+--------+--------+--------+---------
| 4| 0| 0| 0| 4|
+--------+--------+--------+--------+--------+

What is different from what you expect ?
I hope we can fix it.

Olivier
deleted_user
Not applicable
I think you have the data entered wrong. As it appears now in your code, you have two variables and you're giving the value of "41, 42, 43, 44" to the variable "Type." I have 41, 42, 43, 44 to be the variable "Site" and 'Type' should have no values. Does this make sense? Thanks!
Olivier
Pyrite | Level 9
Well, I did not understand what your data really were made of. Is it more realistic now ?

DATA work.ae ;
INFILE CARDS MISSOVER ;
INPUT ID $ site $ type ;
CARDS ;
01 41
02 42
03 43
04 44
RUN ;
PROC FORMAT ;
VALUE types
1 = "Type I"
2 = "Type II"
3 = "Type III"
OTHER = "-(unknown)-"
;
RUN ;
OPTION MISSING = "0" ;
PROC TABULATE DATA = work.ae FORMAT = 8. ;
CLASS type / PRELOADFMT MISSING ;
FORMAT type types. ;
TABLE (ALL="# enrolled" type)*N="" / PRINTMISS ;
RUN ;
OPTION MISSING = "." ;

Unfortunately, I don't see how I can get rid of the duplicate column corresponding to "unknown" type.
deleted_user
Not applicable
Yes, it worked now. Thank you very much! Unfortunately, as you said, I can't figure out how to get rid of that duplicate column, causing this report to appear a bit peculiar.
Cynthia_sas
SAS Super FREQ
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
deleted_user
Not applicable
Thanks everyone for all your help!!!! I really appreciate it.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

New Learning Events in April

 

Join us for two new fee-based courses: Administrative Healthcare Data and SAS via Live Web Monday-Thursday, April 24-27 from 1:00 to 4:30 PM ET each day. And Administrative Healthcare Data and SAS: Hands-On Programming Workshop via Live Web on Friday, April 28 from 9:00 AM to 5:00 PM ET.

LEARN MORE

Discussion stats
  • 8 replies
  • 1490 views
  • 0 likes
  • 3 in conversation