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

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

Accepted Solutions
Vince28_Statcan
Quartz | Level 8

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

5 REPLIES 5
Reeza
Super User

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

tmcrouse
Calcite | Level 5

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.

tmcrouse
Calcite | Level 5

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

Reeza
Super User

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.

Vince28_Statcan
Quartz | Level 8

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;

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
  • 5 replies
  • 640 views
  • 0 likes
  • 3 in conversation