Proc Tabulate and Zero Rows

[BTAinRVA]

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

[Reeza · Accepted solution]

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

[BTAinRVA]

THANK YOU!!!!!!!

[Ksharp]

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;

[BTAinRVA]

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?

[Ksharp]

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;

[BTAinRVA]

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!

[ballardw]

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)

[BTAinRVA]

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

[BTAinRVA]

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?

[Ksharp]

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

[BTAinRVA]

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.

[Ksharp]

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

[BTAinRVA]

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

[Ksharp]

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