Summation or maybe breakout problem

[tmcrouse]

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
strata	study	location	count
1111	1-182 DAY PCI REDO RATE	INPATIENT	1
1111	1-182 DAY PCI REDO RATE	OUTPATIENT	2
1111	1-30 DAY PCI REDO RATE	INPATIENT	1
1111	1-30 DAY PCI REDO RATE	OUTPATIENT	2
1111	3-MONTH MAJOR RESTUDY RATE	INPATIENT	1
1111	3-MONTH MAJOR RESTUDY RATE	OFFICE/CLINIC	2
1111	3-MONTH MAJOR RESTUDY RATE	OUTPATIENT	1
1111	3-MONTH MINOR RESTUDY RATE	INPATIENT	1
1111	3-MONTH MINOR RESTUDY RATE	OFFICE/CLINIC	3
DATA NEED
strata	ofc30	ip30	op30	ofc182	ip182	op182	ofcmaj	ipmaj	opmaj	ofcmin	ipmin	opmin
1111		1	2		1	2	2	1	1	3	1

[Reeza]

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

[tmcrouse]

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]

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

[Reeza]

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 · Accepted solution]

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;