Good Morning!
I'm using proc tabulate to summarize the number of quarterly hysterectomies at several hospitals, many of which have had none. When I run the code below, the hospitals that had no hysterectomies do not have a row for hysterectomies (NH #1) while those that do have hysterectomies have a Hysterectomy row (NH #2). Is there a way to get Tabulate to show zero rows?
2014 Fourth Quarter | |||
N | PctN | ||
NH #1 | Hysterectomy | 6 | 100 |
No Hysterectomy | |||
All | 6 | 100 | |
NH #2 | Hysterectomy | 10 | 90.91 |
No Hysterectomy | |||
Hysterectomy | 1 | 9.09 | |
All | 11 | 100 |
If it was proc freq you could use the SPARSE option.
Several other options are detailed here:
http://www.lexjansen.com/nesug/nesug11/cc/cc29.pdf
If it was proc freq you could use the SPARSE option.
Several other options are detailed here:
http://www.lexjansen.com/nesug/nesug11/cc/cc29.pdf
THANK YOU!!!!!!!
And Don't forget the CLASSDATA= option of proc tabulate .
data levels;
sex='F';output;
sex='M';output;
sex='O';output;
run;
proc tabulate data=sashelp.class classdata=levels;
class sex;
table sex,n pctn;
run;
Xia,
Thanks for bringing that to my attention. I'm trying to use it for another problem. I'm using proc Tabulate to get the monthly PPH numbers for several hospitals for the last 12 months. I can't use PRINTMISS because it will give me all months for 2014 and 2015 and I only want it to diplay the last 12 months. I'm trying to follow the example here
http://support.sas.com/resources/papers/proceedings11/087-2011.pdf
DATA CLASS;
DO ID_Name="SITE #1", "SITE #2", "SITE #3", "SITE #4", "SITE #5";
DO PPH= '0','1';
OUTPUT;
END;
END;
RUN;
DATA PPIP_new2;
set PPIP_new;
IF 0 THEN SET CLASS;
run;
PROC TABULATE data=PPIP_new2 MISSING CLASSDATA=CLASS;
CLASS ID_Name PPH /PRELOADFMT ORDER=DATA;
TABLE ID_Name * (PPH all) all, CY * CM * (N PCTN<pph all>) all*(N PCTN<pph all>) /;
FORMAT PPH $PPH. CM $CM.;
RUN;
I'm having trouble getting the formats of the CLASS dataset the same as PPIP_new. The data step creating PPIP_new2 is telling me
Variable PPH has been defined as both character and numeric
Any suggestions?
I guest table PPIP_new must have the same variable PPH which is numeric type.
So you might need change code as :
DATA CLASS;
DO ID_Name="SITE #1", "SITE #2", "SITE #3", "SITE #4", "SITE #5";
DO PPH= 0,1;
OUTPUT;
END;
END;
RUN;
Xia,
Thanks for the reply. Unfortunately it didn't seem to work. What I did do is chnage the formats for PPH so they are the same in each data set but now when i run the code
PROC TABULATE data=temp missing classdata=CLASS;
CLASS Site_Name PPH / PRELOADFMT ORDER=DATA ;
TABLE Site_Name * (PPH all) all, CY * CM * (N PCTN<pph all>) all*(N PCTN<pph all>);
RUN;
I get an error message:
The type of name (CY) is unknown
I thought it might be because CY was a character variable so I changed it to a numeric varaible but I get the same error message.
Any suggestions would be greatly appreciated!
All variables used in a table statement in proc tabulate must be defined as either a Class or Var variable.
Your code
PROC TABULATE data=temp missing classdata=CLASS;
CLASS Site_Name PPH / PRELOADFMT ORDER=DATA ;
TABLE Site_Name * (PPH all) all, CY * CM * (N PCTN<pph all>) all*(N PCTN<pph all>);
RUN;
References variable CY and CM that are note described. I suspect from the way you are useing them they are additional Class variables but Tabulate will not make an assumption.
Add:
Class Cy Cm; with any other options needed (missing may be needed if ther are values of Cm or Cy that do not occur with every Site_name or PPH)
Ballardw,
Thanks for the reply! I tried adding CM and CY to the CLASS statement and then created the CLASSDATA set as follows:
DATA CLASS;
length CM $2.;
DO TMT_Parent_DMIS_ID_Name= "SITE #1", "SITE #2", "SITE #3", "SITE #4", "SITE #5;
DO CY= "2014", "2015";
DO CM= "1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12";
DO PPH= "0", "1";
OUTPUT;
END;
END;
END;
END;
RUN;
But I get the error below:
Class variable CM was not found in the preload data set or was incompatible with the
primary data set variable of the same name
The thing is I don't really want to force the display of each Calendar Month (CM) so I don't really want to put CY and CM in the CLASS statement. I just want to force the display of the PPH categories (Hysterectomy, No Hysterectomy) for each hospital even if that hospital had no hysterectomies. Does anyone know how I can do that?
Then make a simple variable PPH contains the levels you want , don't mixed up with other class variable , just as I did before .
Show some sample data and the output you want to see .
Xia,
Many thanks again for taking a look at this. The data looks sort of like this
Site_ID | PPH | CY2 | CM2 |
Site#1 | No Postpartum Hemorrhage | 2015 | 5 |
Site#1 | No Postpartum Hemorrhage | 2015 | 5 |
Site#1 | Postpartum Hemorrhage | 2015 | 4 |
Site#1 | No Postpartum Hemorrhage | 2015 | 6 |
Site#1 | No Postpartum Hemorrhage | 2015 | 6 |
Site#1 | No Postpartum Hemorrhage | 2015 | 6 |
Site#2 | Postpartum Hemorrhage | 2015 | 5 |
Site#2 | No Postpartum Hemorrhage | 2015 | 6 |
Site#2 | No Postpartum Hemorrhage | 2015 | 6 |
Site#2 | Postpartum Hemorrhage | 2015 | 5 |
Site#2 | No Postpartum Hemorrhage | 2015 | 5 |
Site#2 | No Postpartum Hemorrhage | 2015 | 5 |
and the output I'm looking for is this
2014 | |||||||
CM | |||||||
October | November | December | |||||
N | PctN | N | PctN | N | PctN | ||
Site_ID | PPH | 54 | 93.1 | 60 | 98.36 | 47 | 95.92 |
Site #1 | No Postpartum Hemorrhage | ||||||
Postpartum Hemorrhage | 4 | 6.9 | 1 | 1.64 | 2 | 4.08 | |
All | 58 | 100 | 61 | 100 | 49 | 100 | |
Site #2 | PPH | 170 | 97.14 | 170 | 96.59 | 160 | 95.81 |
No Postpartum Hemorrhage | |||||||
Postpartum Hemorrhage | 5 | 2.86 | 6 | 3.41 | 7 | 4.19 | |
All | 175 | 100 | 176 | 100 | 167 | 100 |
OK. Here is what you are looking for ?
data have;
infile cards expandtabs ;
input Site_ID $ PPH & $40. CY2 CM2;
date=mdy(cm2,1,cy2);
format date monname.;
cards;
Site#1 No Postpartum Hemorrhage 2015 5
Site#1 No Postpartum Hemorrhage 2015 5
Site#1 Postpartum Hemorrhage 2015 4
Site#1 No Postpartum Hemorrhage 2015 6
Site#1 No Postpartum Hemorrhage 2015 6
Site#1 No Postpartum Hemorrhage 2015 6
Site#2 Postpartum Hemorrhage 2015 5
Site#2 No Postpartum Hemorrhage 2015 6
Site#2 No Postpartum Hemorrhage 2015 6
Site#2 Postpartum Hemorrhage 2015 5
Site#2 No Postpartum Hemorrhage 2015 5
Site#2 No Postpartum Hemorrhage 2015 5
;
run;
proc sql;
create table level as
select * from (select distinct Site_ID from have),
(select distinct pph from have),
(select distinct CY2 from have),
(select distinct date from have);
create table temp as
select a.*,1/(select count(*) from have where
Site_ID=a.Site_ID and
CY2=a.CY2 and
CM2=a.CM2) as pct
from have as a;
quit;
proc tabulate data=temp classdata=level ;
class Site_ID PPH CY2 date;
var pct;
table Site_ID*(PPH all),CY2=' '*date='CM'*(n pct*sum=''*f=percent8.);
run;
Edited
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: