topic Re: Summation or maybe breakout problem in SAS Procedures

Summation or maybe breakout problem

tmcrouse — Fri, 13 Sep 2013 14:56:30 GMT

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

Re: Summation or maybe breakout problem

Reeza — Fri, 13 Sep 2013 15:06:22 GMT

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

Re: Summation or maybe breakout problem

tmcrouse — Fri, 13 Sep 2013 15:23:13 GMT

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.

Re: Summation or maybe breakout problem

tmcrouse — Fri, 13 Sep 2013 15:24:11 GMT

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

Re: Summation or maybe breakout problem

Vince28_Statcan — Fri, 13 Sep 2013 15:35:26 GMT

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;

Re: Summation or maybe breakout problem

Reeza — Fri, 13 Sep 2013 15:48:06 GMT

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.