## Proc Tabulate and Zero Rows

Solved
Regular Contributor
Posts: 151

# Proc Tabulate and Zero Rows

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

Accepted Solutions
Solution
‎01-07-2016 10:43 AM
Super User
Posts: 23,720

## Re: Proc Tabulate and Zero Rows

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

All Replies
Solution
‎01-07-2016 10:43 AM
Super User
Posts: 23,720

## Re: Proc Tabulate and Zero Rows

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

Regular Contributor
Posts: 151

THANK YOU!!!!!!!

Super User
Posts: 10,778

## Re: Proc Tabulate and Zero Rows

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;``````
Regular Contributor
Posts: 151

## Re: Proc Tabulate and Zero Rows

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?

Super User
Posts: 10,778

## Re: Proc Tabulate and Zero Rows

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;

Regular Contributor
Posts: 151

## Re: Proc Tabulate and Zero Rows

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!

Super User
Posts: 13,542

## Re: Proc Tabulate and Zero Rows

All variables used in a table statement in proc tabulate must be defined as either a Class or Var variable.

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.

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)

Regular Contributor
Posts: 151

## Re: Proc Tabulate and Zero Rows

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

Regular Contributor
Posts: 151

## Re: Proc Tabulate and Zero Rows

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?

Super User
Posts: 10,778

## Re: Proc Tabulate and Zero Rows

Then make a simple variable PPH contains the levels you want , don't mixed up with other class variable , just as I did before .

Regular Contributor
Posts: 151

## Re: Proc Tabulate and Zero Rows

Ballardw,

Many thanks for the reply. But if I add CY and CM as class variables don't
I also need to include them in the CLASSDATA dataset? I don't want all
levels of CM to be displayed in the table.
Super User
Posts: 10,778

## Re: Proc Tabulate and Zero Rows

Show some sample data and the output you want to see .

Regular Contributor
Posts: 151

## Re: Proc Tabulate and Zero Rows

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

Super User
Posts: 10,778

## Re: Proc Tabulate and Zero Rows

[ Edited ]

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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
• 17 replies
• 1353 views
• 1 like
• 4 in conversation