proc transpose

[tmm]

My proc transpose is not turning out the way I need it to. I did a groupby in a proc sql to get the data in a table that I needed but I am not getting my proc transpose to look right.

Here is an example of my table:

strata	descript	caths	pcis	mpis	imppacs	secho	pet	tte	tee	revascs	perfs	cts	cabgs	stemi	nstemi	mbr
582563008																715
582563008	IP	26	6		4			218	11	7	55				2	201
582563008	OP	102	7		10	4		156	7	17	140				1	234
582563008	HOSPICE							1								1
582563008	OFFICE					5		1129			509					1128
582563008	OTHER IP										1
582563008	OTHER OP							2			3					2

When I do the proc transpose I get this:

strata	descript	_NAME_	metric1
582563008		caths
582563008		pcis
582563008		mpis
582563008		imppacs
582563008		secho
582563008		pet
582563008		tte
582563008		tee
582563008		revascs
582563008		perfs
582563008		cts
582563008		cabgs
582563008		stemi
582563008		nstemi
582563008		mbr	715
582563008		outpt_pci
582563008		maj_restudy
582563008		min_restudy
582563008		restudy_denom
582563008		PCI_FU_30
582563008		FU30_denom
582563008		pci_fu_182
582563008		FU182_denom
582563008	ACUTE-CARE INPATIENT FACILITY	caths	26
582563008	ACUTE-CARE INPATIENT FACILITY	pcis	6
582563008	ACUTE-CARE INPATIENT FACILITY	mpis
582563008	ACUTE-CARE INPATIENT FACILITY	imppacs	4
582563008	ACUTE-CARE INPATIENT FACILITY	secho
582563008	ACUTE-CARE INPATIENT FACILITY	pet
582563008	ACUTE-CARE INPATIENT FACILITY	tte	218
582563008	ACUTE-CARE INPATIENT FACILITY	tee	11
582563008	ACUTE-CARE INPATIENT FACILITY	revascs	7
582563008	ACUTE-CARE INPATIENT FACILITY	perfs	55
582563008	ACUTE-CARE INPATIENT FACILITY	cts
582563008	ACUTE-CARE INPATIENT FACILITY	cabgs
582563008	ACUTE-CARE INPATIENT FACILITY	stemi
582563008	ACUTE-CARE INPATIENT FACILITY	nstemi	2
582563008	ACUTE-CARE INPATIENT FACILITY	mbr	201
582563008	ACUTE-CARE INPATIENT FACILITY	outpt_pci
582563008	ACUTE-CARE INPATIENT FACILITY	maj_restudy
582563008	ACUTE-CARE INPATIENT FACILITY	min_restudy
582563008	ACUTE-CARE INPATIENT FACILITY	restudy_denom	5
582563008	ACUTE-CARE INPATIENT FACILITY	PCI_FU_30	1
582563008	ACUTE-CARE INPATIENT FACILITY	FU30_denom	6
582563008	ACUTE-CARE INPATIENT FACILITY	pci_fu_182	1
582563008	ACUTE-CARE INPATIENT FACILITY	FU182_denom	5
582563008	ACUTE-CARE OUTPATIENT FACILITY	caths	102
582563008	ACUTE-CARE OUTPATIENT FACILITY	pcis	7
582563008	ACUTE-CARE OUTPATIENT FACILITY	mpis
582563008	ACUTE-CARE OUTPATIENT FACILITY	imppacs	10
582563008	ACUTE-CARE OUTPATIENT FACILITY	secho	4
582563008	ACUTE-CARE OUTPATIENT FACILITY	pet
582563008	ACUTE-CARE OUTPATIENT FACILITY	tte	156
582563008	ACUTE-CARE OUTPATIENT FACILITY	tee	7
582563008	ACUTE-CARE OUTPATIENT FACILITY	revascs	17
582563008	ACUTE-CARE OUTPATIENT FACILITY	perfs	140
582563008	ACUTE-CARE OUTPATIENT FACILITY	cts
582563008	ACUTE-CARE OUTPATIENT FACILITY	cabgs
582563008	ACUTE-CARE OUTPATIENT FACILITY	stemi
582563008	ACUTE-CARE OUTPATIENT FACILITY	nstemi	1
582563008	ACUTE-CARE OUTPATIENT FACILITY	mbr	234
582563008	ACUTE-CARE OUTPATIENT FACILITY	outpt_pci	4
582563008	ACUTE-CARE OUTPATIENT FACILITY	maj_restudy
582563008	ACUTE-CARE OUTPATIENT FACILITY	min_restudy	2
582563008	ACUTE-CARE OUTPATIENT FACILITY	restudy_denom	6
582563008	ACUTE-CARE OUTPATIENT FACILITY	PCI_FU_30
582563008	ACUTE-CARE OUTPATIENT FACILITY	FU30_denom	7
582563008	ACUTE-CARE OUTPATIENT FACILITY	pci_fu_182
582563008	ACUTE-CARE OUTPATIENT FACILITY	FU182_denom	4
582563008	INPATIENT HOSPICE	caths
582563008	INPATIENT HOSPICE	pcis
582563008	INPATIENT HOSPICE	mpis
582563008	INPATIENT HOSPICE	imppacs
582563008	INPATIENT HOSPICE	secho
582563008	INPATIENT HOSPICE	pet
582563008	INPATIENT HOSPICE	tte	1
582563008	INPATIENT HOSPICE	tee
582563008	INPATIENT HOSPICE	revascs
582563008	INPATIENT HOSPICE	perfs
582563008	INPATIENT HOSPICE	cts
582563008	INPATIENT HOSPICE	cabgs
582563008	INPATIENT HOSPICE	stemi
582563008	INPATIENT HOSPICE	nstemi
582563008	INPATIENT HOSPICE	mbr	1
582563008	INPATIENT HOSPICE	outpt_pci
582563008	INPATIENT HOSPICE	maj_restudy
582563008	INPATIENT HOSPICE	min_restudy
582563008	INPATIENT HOSPICE	restudy_denom
582563008	INPATIENT HOSPICE	PCI_FU_30
582563008	INPATIENT HOSPICE	FU30_denom
582563008	INPATIENT HOSPICE	pci_fu_182
582563008	INPATIENT HOSPICE	FU182_denom
582563008	OFFICE/CLINIC	caths
582563008	OFFICE/CLINIC	pcis
582563008	OFFICE/CLINIC	mpis
582563008	OFFICE/CLINIC	imppacs
582563008	OFFICE/CLINIC	secho	5
582563008	OFFICE/CLINIC	pet
582563008	OFFICE/CLINIC	tte	1129
582563008	OFFICE/CLINIC	tee
582563008	OFFICE/CLINIC	revascs
582563008	OFFICE/CLINIC	perfs	509
582563008	OFFICE/CLINIC	cts
582563008	OFFICE/CLINIC	cabgs
582563008	OFFICE/CLINIC	stemi
582563008	OFFICE/CLINIC	nstemi
582563008	OFFICE/CLINIC	mbr	1128
582563008	OFFICE/CLINIC	outpt_pci
582563008	OFFICE/CLINIC	maj_restudy
582563008	OFFICE/CLINIC	min_restudy
582563008	OFFICE/CLINIC	restudy_denom
582563008	OFFICE/CLINIC	PCI_FU_30
582563008	OFFICE/CLINIC	FU30_denom
582563008	OFFICE/CLINIC	pci_fu_182
582563008	OFFICE/CLINIC	FU182_denom
582563008	OTHER INPATIENT FACILITY	caths
582563008	OTHER INPATIENT FACILITY	pcis
582563008	OTHER INPATIENT FACILITY	mpis
582563008	OTHER INPATIENT FACILITY	imppacs
582563008	OTHER INPATIENT FACILITY	secho
582563008	OTHER INPATIENT FACILITY	pet
582563008	OTHER INPATIENT FACILITY	tte
582563008	OTHER INPATIENT FACILITY	tee
582563008	OTHER INPATIENT FACILITY	revascs
582563008	OTHER INPATIENT FACILITY	perfs	1
582563008	OTHER INPATIENT FACILITY	cts
582563008	OTHER INPATIENT FACILITY	cabgs
582563008	OTHER INPATIENT FACILITY	stemi
582563008	OTHER INPATIENT FACILITY	nstemi
582563008	OTHER INPATIENT FACILITY	mbr
582563008	OTHER INPATIENT FACILITY	outpt_pci
582563008	OTHER INPATIENT FACILITY	maj_restudy
582563008	OTHER INPATIENT FACILITY	min_restudy
582563008	OTHER INPATIENT FACILITY	restudy_denom
582563008	OTHER INPATIENT FACILITY	PCI_FU_30
582563008	OTHER INPATIENT FACILITY	FU30_denom
582563008	OTHER INPATIENT FACILITY	pci_fu_182
582563008	OTHER INPATIENT FACILITY	FU182_denom
582563008	OTHER OUTPATIENT PLACE OF SERVICE	caths
582563008	OTHER OUTPATIENT PLACE OF SERVICE	pcis
582563008	OTHER OUTPATIENT PLACE OF SERVICE	mpis
582563008	OTHER OUTPATIENT PLACE OF SERVICE	imppacs
582563008	OTHER OUTPATIENT PLACE OF SERVICE	secho
582563008	OTHER OUTPATIENT PLACE OF SERVICE	pet
582563008	OTHER OUTPATIENT PLACE OF SERVICE	tte	2
582563008	OTHER OUTPATIENT PLACE OF SERVICE	tee
582563008	OTHER OUTPATIENT PLACE OF SERVICE	revascs
582563008	OTHER OUTPATIENT PLACE OF SERVICE	perfs	3
582563008	OTHER OUTPATIENT PLACE OF SERVICE	cts
582563008	OTHER OUTPATIENT PLACE OF SERVICE	cabgs
582563008	OTHER OUTPATIENT PLACE OF SERVICE	stemi
582563008	OTHER OUTPATIENT PLACE OF SERVICE	nstemi
582563008	OTHER OUTPATIENT PLACE OF SERVICE	mbr	2
582563008	OTHER OUTPATIENT PLACE OF SERVICE	outpt_pci
582563008	OTHER OUTPATIENT PLACE OF SERVICE	maj_restudy
582563008	OTHER OUTPATIENT PLACE OF SERVICE	min_restudy
582563008	OTHER OUTPATIENT PLACE OF SERVICE	restudy_denom
582563008	OTHER OUTPATIENT PLACE OF SERVICE	PCI_FU_30
582563008	OTHER OUTPATIENT PLACE OF SERVICE	FU30_denom
582563008	OTHER OUTPATIENT PLACE OF SERVICE	pci_fu_182
582563008	OTHER OUTPATIENT PLACE OF SERVICE	FU182_denom

I am not sure how to do the output I need because I need the mbr to be summed overall regardless of descript and then for example

outpatient_perfs 2/2281 (2281 is the sum of the members)

My ultimate goal is to have a final rtf that looks like this:

Metric Component                                Calculation

IP_Caths                                                   26/2281

IP_PCIs                                                      6/2281

IP_Imppacs                                                4/2281

etc on down all the procedures and then I have to breakout the out patients and by procedures, etc. I just did not want to have to do a ton of proc sqls or other types of tables to end up with the final result. If I have to I will but thought there might be something better out there. A better process.

[Reeza]

Provide a small sample and output data and the LOGIC as clearly as possible. I don't see where 2281 comes from.

Make your example data match your example output, otherwise people have to guess what you want.

[tmm]

Snippet of table I have:

strata	descript	caths	pcis	mpis	imppacs	secho	pet	tte	tee	revascs	perfs	cts	cabgs	stemi	nstemi	mbr
582563008																715
582563008	IP	26	6		4			218	11	7	55				2	201
582563008	OP	102	7		10	4		156	7	17	140				1	234
582563008	HOSPICE							1								1
582563008	OFFICE					5		1129			509					1128
582563008	OTHER IP										1
582563008	OTHER OP							2			3					2
sumofmbr	2281

proc transpose data=have out=need prefix=metric; by strata descript;run;

The output of above code is not what I need. I want to see the following:

strata

IP_caths

IP_pcis

ip_mpis

ip_imppacs

ip_secho

ip_pet

ip_tte

ip_tee

ip_revascs

ip_perfs

ip_cts

ip_cabgs

ip_stemi

ip_nstemi

mbrs

582563008

1/26/1900

6

4

218

11

7

55

2

2281

after the IP the same single row would have

OP_caths, OP_pcis all the way to OP_stemi, then following that on that same single row is hospice_caths all the way to hospice_stemi, then following that same single row would be office_caths. Ultimately I end up with one row that has a total of 86 columns if my math is right. 1 of the columns is the summation of the mbrs and 1 column is the single strata. I might just have to go back to what I had planned in the first place with doing a case statement but did not want to code all that. Would be like 100+ case statements. When I do my final rtf proc format output at the very end it has to look like:

metric component                           calculation

IP Cabg Rate                                 ./2281

IP_nstemi                                       3/2281

all the way until all are accounted for. Of course I could have an output that is:

metric component                          descript                                calculation

nstemi                                                OP                                        1/2281

nsteim                                                IP                                           2/2281                                                                         

Maybe I will play with trying it that way. I might just be able to proc format the table I have. Will play with it again tomm

[Reeza]

That doesn't make sense. Why would you consider IP events the same as OP for the denominator? Or even Hospice?

Your final output is much easier to get, why do you need the intermediate step?

[tmm]

The denominator is the mbr count that is summed. So say this one fac has 100 mbrs that had a list of procedures from caths to ct to echos. Then 2 mbrs had a cath in IP and 4 mbrs had a cath in OP. That would mean for cath 2/100 is what IP would look like and 4/100 is what OP would look like. The denom is the mbr summation. That is how it was requested for breakouts. Right now the breakouts are simply the procedure as the num and the mbrs at the denom. But they want it broken down now by place of service for procedure as the num and mbrs as the denom.

[Vince28_Statcan]

Using the have data you've provided and the transposed data:

proc sql;

     select trim(descript)||"_"||trim(_NAME_) as metric_component, a.value/b.mbrsum as calculation

     from     (

          select *, sum(metric1) as value

          from transposedds

          group by strata, descript, _name_

               ) as a

          full join

               (

          select strata, sum(mbr) as mbrsum

          from have

          group by strata;

               ) as b

          on a.strata=b.strata

;

quit;

This is not tested as you didn't provide a dataset or a datalines segment to use but the idea is somewhat simple:

calculate the mbr sum "by strata", merge that with a sum by strata/descript/_name_ of your transposed dataset so that calculations can be done at that intermediate step. Only select the concatenated variables and the calculations in the final query.

It is possible that you will need to 0 pad all missing values for the aggregate functions to work*. If you can provide a miniature dataset either a text file or in the form of a datalines; statement, I can correct syntax and add missing values handling to the above

Vincent

[art297]

Then, why not use Tom's suggested code, but first do the desired calculations?  e.g.:

proc summary data=have;

  var mbr;

  output out=sums (drop=_:) sum=mbrsum;

  by strata;

run;

data have (drop=i);;

  merge have sums;

  array stuff(*) caths--mbr;

  by strata;

  do i=1 to dim(stuff);

    if not missing(stuff(i)) then stuff(i)=stuff(i)/mbrsum;

  end;

run;

Then do the flipflop.

[Tom]

Probably you want to use a double transpose (nicknamed a flip/flop).  This will allow you to use the value of DESCRIPT to become part of the variable name.  But the values of DESCRIPT are really long.  If the actual values are short then remove the format otherwise create a format to map the long values to something reasonable for making variable names.  You also need to figure out how to handle the rows where DRESCRIPT is blank.  PROC TRANSPOSE does not like that.  In the example below I have converted the blanks to 'none' so that it will run.  You could also add a step before he last transpose to generate the column name yourself and then just use that one variable is the ID value on the last proc transpose.

data have ;

  infile cards dlm='|' dsd truncover ;

  input strata descript $ caths pcis mpis imppacs secho pet

        tte tee revascs perfs cts cabgs stemi nstemi mbr

  ;

  if descript=' ' then descript='none';

cards;

582563008||||||||||||||||715

582563008|IP|26|6||4|||218|11|7|55||||2|201

582563008|OP|102|7||10|4||156|7|17|140

run;

proc print width=min; run;

proc transpose data=have out=flop;

  by strata descript notsorted;

run;

proc print width=min; run;

proc transpose data=flop out=want(drop=_name_) delimiter=_ ;

  by strata ;

  id descript _name_;

  var col1;

run;

data _null_;

  set want (obs=1) ;

  put (_all_) (=/);

run;

strata=582563008

none_caths=.

none_pcis=.

none_mpis=.

none_imppacs=.

none_secho=.

none_pet=.

none_tte=.

none_tee=.

none_revascs=.

none_perfs=.

none_cts=.

none_cabgs=.

none_stemi=.

none_nstemi=.

none_mbr=715

IP_caths=26

IP_pcis=6

IP_mpis=.

IP_imppacs=4

IP_secho=.

IP_pet=.

IP_tte=218

IP_tee=11

IP_revascs=7

IP_perfs=55

IP_cts=.

IP_cabgs=.

IP_stemi=.

IP_nstemi=2

IP_mbr=201

OP_caths=102

OP_pcis=7

OP_mpis=.

OP_imppacs=10

OP_secho=4

OP_pet=.

OP_tte=156

OP_tee=7

OP_revascs=17

OP_perfs=140

OP_cts=.

OP_cabgs=.

OP_stemi=.

OP_nstemi=.

OP_mbr=.