BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
BTAinRVA
Quartz | Level 8

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
1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

 

 

View solution in original post

17 REPLIES 17
Reeza
Super User

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
Quartz | Level 8

THANK YOU!!!!!!!

Ksharp
Super User

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
Quartz | Level 8

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
Super User

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
Quartz | Level 8

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
Super User

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
Quartz | Level 8

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
Quartz | Level 8

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
Super User

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
Quartz | Level 8
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
Super User

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

BTAinRVA
Quartz | Level 8

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
Super User

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 

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!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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