topic Re: Proc Tabulate and Zero Rows in SAS Procedures

Proc Tabulate and Zero Rows

BTAinRVA — Thu, 07 Jan 2016 14:59:43 GMT

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	6	100
	All	6	100
NH #2	Hysterectomy	10	90.91
	No Hysterectomy	10	90.91
	Hysterectomy	1	9.09
	All	11	100

Re: Proc Tabulate and Zero Rows

Reeza — Thu, 07 Jan 2016 15:34:05 GMT

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

Re: Proc Tabulate and Zero Rows

BTAinRVA — Thu, 07 Jan 2016 15:43:48 GMT

THANK YOU!!!!!!!

Re: Proc Tabulate and Zero Rows

Ksharp — Fri, 08 Jan 2016 01:44:29 GMT

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;

Re: Proc Tabulate and Zero Rows

BTAinRVA — Fri, 08 Jan 2016 19:03:15 GMT

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;

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?

Re: Proc Tabulate and Zero Rows

Ksharp — Mon, 11 Jan 2016 01:03:48 GMT

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;

RUN;

Re: Proc Tabulate and Zero Rows

BTAinRVA — Mon, 11 Jan 2016 19:11:24 GMT

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!

Re: Proc Tabulate and Zero Rows

ballardw — Mon, 11 Jan 2016 20:48:35 GMT

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)

Re: Proc Tabulate and Zero Rows

BTAinRVA — Mon, 11 Jan 2016 22:14:53 GMT

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;

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

Re: Proc Tabulate and Zero Rows

BTAinRVA — Thu, 14 Jan 2016 16:50:36 GMT

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?

Re: Proc Tabulate and Zero Rows

Ksharp — Fri, 15 Jan 2016 00:56:00 GMT

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

Re: Proc Tabulate and Zero Rows

BTAinRVA — Fri, 15 Jan 2016 04:41:41 GMT

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.

Re: Proc Tabulate and Zero Rows

Ksharp — Fri, 15 Jan 2016 04:53:49 GMT

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

Re: Proc Tabulate and Zero Rows

BTAinRVA — Fri, 15 Jan 2016 20:49:28 GMT

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	54	93.1	60	98.36	47	95.92
	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	170	97.14	170	96.59	160	95.81
	Postpartum Hemorrhage	5	2.86	6	3.41	7	4.19
	All	175	100	176	100	167	100

Re: Proc Tabulate and Zero Rows

Ksharp — Mon, 18 Jan 2016 01:24:47 GMT

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

Re: Proc Tabulate and Zero Rows

BTAinRVA — Mon, 18 Jan 2016 02:53:31 GMT

Ksharp,

Thanks for the reply! This looks interesting. I will try it out when I get
back to work on Tuesday.

btadams

##- Please type your reply above this line. Simple formatting, no
attachments. -##

Re: Proc Tabulate and Zero Rows

BTAinRVA — Tue, 19 Jan 2016 17:00:42 GMT

Xia,

Yes, this works but it still give me output for all 12 months (CM) in 2014, even those that don't have any data in the dataset. My dataset has data for only the last three months of 2014 but the table output from the Tabulate procedure displays all 12 months of 2014. Is there some way to exclude columns in the output table where there are all zeros?

Thanks again for all your assistance!

Brian

Re: Proc Tabulate and Zero Rows

BTAinRVA — Tue, 19 Jan 2016 20:03:00 GMT

Xia,

I think I figured out how to get what I want: I run the first Proc Tabulate with the PRINTMISS option that outputs all months for all sites. I store this in a table named TabData. I then delete all rows from TabData where the variable PctN_1011 equals ".". I then do another Proc Tabulate on this table as shown below.

title 'Monthly PPH by MTF';

proc tabulate data=Navy_PPIP_new missing out=tabdata;

class Site_ID PPH CY CM;

TABLE Site_ID * (PPH all) all, CY * CM * (N PCTN<PPH all>) all*(N PCTN<PPH all>) /printmiss MISSTEXT='0';

run;

Data temp;

set tabdata;

if PctN_1011 ne .;

run;

title 'Monthly PPH by MTF';

proc tabulate data=temp ;

class Site_ID PPH CY CM;

var N PctN_1011;

TABLE Site_ID * (PPH all) all, CY * CM * (N PctN_1011="PCTN") all*(N PctN_1011="PCTN") / MISSTEXT='0';

run;

I appreciate all your help. I couldn't have figured it out without it!