Help using Base SAS procedures

Summation or maybe breakout problem

Accepted Solution Solved
Reply
Contributor
Posts: 69
Accepted Solution

Summation or maybe breakout problem

I am just having a real time with this. I have tried case statements and that did not work and then just a summation with proc sql. I need my data to match what the data layout is in another table that calculates other procedures by tin, place of srvc. I have 27845 different stratas. I have tried a proc sql with groupbys but no matter what I do I just cannot get it quite right.

DATA HAVE
stratastudylocationcount
11111-182 DAY PCI REDO RATEINPATIENT1
11111-182 DAY PCI REDO RATEOUTPATIENT2
11111-30 DAY PCI REDO RATEINPATIENT1
11111-30 DAY PCI REDO RATEOUTPATIENT2
11113-MONTH MAJOR RESTUDY RATEINPATIENT1
11113-MONTH MAJOR RESTUDY RATEOFFICE/CLINIC2
11113-MONTH MAJOR RESTUDY RATEOUTPATIENT1
11113-MONTH MINOR RESTUDY RATEINPATIENT1
11113-MONTH MINOR RESTUDY RATEOFFICE/CLINIC3
DATA NEED
strataofc30ip30op30ofc182ip182op182ofcmajipmajopmajofcminipminopmin
1111 12 1221131

Accepted Solutions
Solution
‎09-13-2013 11:35 AM
Super Contributor
Posts: 339

Re: Summation or maybe breakout problem

2 proc format, one for variable study, one for location. Then use statement id location study; in a proc transpose.

proc format;

     value $study

          "1-182 DAY PCI REDO RATE"="182"

          "1-30 DAY PCI REDO RATE"="30"

          ... etc take all distinct values of study and map them to the appropriate suffix that you want

          ;

     value $location

     "INPATIENT"="ip"

     "OUTPATIENT"="op"

     "OFFICE/CLINIC"="ofc"

     ;

run;

/*you'll have to get the formats applied to the appropriate variables in HAVE. I don't remember of the top of my head if format statement can be used in proc transpose and whether it is applied to the data= dataset or to the resulting output so I would probably just use a proc datasets modify */

proc transpose data=have out=want;

     by strata;

     id location study;

run;

View solution in original post


All Replies
Super User
Posts: 19,770

Re: Summation or maybe breakout problem

How do your variables map, eg what is ip30 in relation to what your have table is?

Contributor
Posts: 69

Re: Summation or maybe breakout problem

ip30 would relate to ip as the location where you see inpatient and 30 would relate to study where you see 1-30 day pci redo rate. I could keep the actual names as they are under location and study, but I just felt they were too long and wanted to shorten them. So really I could do:

strata        inpatient_1_182_day_pci_redo_rate                                        etc

111                                      1

But doing that way just seems to be far too large. So, in my data need, I was trying to find a way to put all those counts under each column across but I am summarizing the actual column header names.

Contributor
Posts: 69

Re: Summation or maybe breakout problem

I wonder if a proc transpose would work and renaming the column headers when doing the transpose

Super User
Posts: 19,770

Re: Summation or maybe breakout problem

transpose via a data step instead of proc transpose to control the levels better and to initialize missing to 0, for example off missing should be 0 or missing.

Plus you'll understand it more.

Solution
‎09-13-2013 11:35 AM
Super Contributor
Posts: 339

Re: Summation or maybe breakout problem

2 proc format, one for variable study, one for location. Then use statement id location study; in a proc transpose.

proc format;

     value $study

          "1-182 DAY PCI REDO RATE"="182"

          "1-30 DAY PCI REDO RATE"="30"

          ... etc take all distinct values of study and map them to the appropriate suffix that you want

          ;

     value $location

     "INPATIENT"="ip"

     "OUTPATIENT"="op"

     "OFFICE/CLINIC"="ofc"

     ;

run;

/*you'll have to get the formats applied to the appropriate variables in HAVE. I don't remember of the top of my head if format statement can be used in proc transpose and whether it is applied to the data= dataset or to the resulting output so I would probably just use a proc datasets modify */

proc transpose data=have out=want;

     by strata;

     id location study;

run;

🔒 This topic is solved and locked.

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

Discussion stats
  • 5 replies
  • 189 views
  • 0 likes
  • 3 in conversation