BookmarkSubscribeRSS Feed
tmm
Fluorite | Level 6 tmm
Fluorite | Level 6

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:

stratadescriptcathspcismpisimppacssechopettteteerevascsperfsctscabgssteminstemimbr
582563008 715
582563008IP266 4 21811755 2201
582563008OP1027 104 156717140 1234
582563008HOSPICE 1 1
582563008OFFICE 5 1129 509 1128
582563008OTHER IP 1
582563008OTHER OP 2 3 2

When I do the proc transpose I get this:

stratadescript_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 mbr715
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
582563008ACUTE-CARE INPATIENT FACILITYcaths26
582563008ACUTE-CARE INPATIENT FACILITYpcis6
582563008ACUTE-CARE INPATIENT FACILITYmpis
582563008ACUTE-CARE INPATIENT FACILITYimppacs4
582563008ACUTE-CARE INPATIENT FACILITYsecho
582563008ACUTE-CARE INPATIENT FACILITYpet
582563008ACUTE-CARE INPATIENT FACILITYtte218
582563008ACUTE-CARE INPATIENT FACILITYtee11
582563008ACUTE-CARE INPATIENT FACILITYrevascs7
582563008ACUTE-CARE INPATIENT FACILITYperfs55
582563008ACUTE-CARE INPATIENT FACILITYcts
582563008ACUTE-CARE INPATIENT FACILITYcabgs
582563008ACUTE-CARE INPATIENT FACILITYstemi
582563008ACUTE-CARE INPATIENT FACILITYnstemi2
582563008ACUTE-CARE INPATIENT FACILITYmbr201
582563008ACUTE-CARE INPATIENT FACILITYoutpt_pci
582563008ACUTE-CARE INPATIENT FACILITYmaj_restudy
582563008ACUTE-CARE INPATIENT FACILITYmin_restudy
582563008ACUTE-CARE INPATIENT FACILITYrestudy_denom5
582563008ACUTE-CARE INPATIENT FACILITYPCI_FU_301
582563008ACUTE-CARE INPATIENT FACILITYFU30_denom6
582563008ACUTE-CARE INPATIENT FACILITYpci_fu_1821
582563008ACUTE-CARE INPATIENT FACILITYFU182_denom5
582563008ACUTE-CARE OUTPATIENT FACILITYcaths102
582563008ACUTE-CARE OUTPATIENT FACILITYpcis7
582563008ACUTE-CARE OUTPATIENT FACILITYmpis
582563008ACUTE-CARE OUTPATIENT FACILITYimppacs10
582563008ACUTE-CARE OUTPATIENT FACILITYsecho4
582563008ACUTE-CARE OUTPATIENT FACILITYpet
582563008ACUTE-CARE OUTPATIENT FACILITYtte156
582563008ACUTE-CARE OUTPATIENT FACILITYtee7
582563008ACUTE-CARE OUTPATIENT FACILITYrevascs17
582563008ACUTE-CARE OUTPATIENT FACILITYperfs140
582563008ACUTE-CARE OUTPATIENT FACILITYcts
582563008ACUTE-CARE OUTPATIENT FACILITYcabgs
582563008ACUTE-CARE OUTPATIENT FACILITYstemi
582563008ACUTE-CARE OUTPATIENT FACILITYnstemi1
582563008ACUTE-CARE OUTPATIENT FACILITYmbr234
582563008ACUTE-CARE OUTPATIENT FACILITYoutpt_pci4
582563008ACUTE-CARE OUTPATIENT FACILITYmaj_restudy
582563008ACUTE-CARE OUTPATIENT FACILITYmin_restudy2
582563008ACUTE-CARE OUTPATIENT FACILITYrestudy_denom6
582563008ACUTE-CARE OUTPATIENT FACILITYPCI_FU_30
582563008ACUTE-CARE OUTPATIENT FACILITYFU30_denom7
582563008ACUTE-CARE OUTPATIENT FACILITYpci_fu_182
582563008ACUTE-CARE OUTPATIENT FACILITYFU182_denom4
582563008INPATIENT HOSPICEcaths
582563008INPATIENT HOSPICEpcis
582563008INPATIENT HOSPICEmpis
582563008INPATIENT HOSPICEimppacs
582563008INPATIENT HOSPICEsecho
582563008INPATIENT HOSPICEpet
582563008INPATIENT HOSPICEtte1
582563008INPATIENT HOSPICEtee
582563008INPATIENT HOSPICErevascs
582563008INPATIENT HOSPICEperfs
582563008INPATIENT HOSPICEcts
582563008INPATIENT HOSPICEcabgs
582563008INPATIENT HOSPICEstemi
582563008INPATIENT HOSPICEnstemi
582563008INPATIENT HOSPICEmbr1
582563008INPATIENT HOSPICEoutpt_pci
582563008INPATIENT HOSPICEmaj_restudy
582563008INPATIENT HOSPICEmin_restudy
582563008INPATIENT HOSPICErestudy_denom
582563008INPATIENT HOSPICEPCI_FU_30
582563008INPATIENT HOSPICEFU30_denom
582563008INPATIENT HOSPICEpci_fu_182
582563008INPATIENT HOSPICEFU182_denom
582563008OFFICE/CLINICcaths
582563008OFFICE/CLINICpcis
582563008OFFICE/CLINICmpis
582563008OFFICE/CLINICimppacs
582563008OFFICE/CLINICsecho5
582563008OFFICE/CLINICpet
582563008OFFICE/CLINICtte1129
582563008OFFICE/CLINICtee
582563008OFFICE/CLINICrevascs
582563008OFFICE/CLINICperfs509
582563008OFFICE/CLINICcts
582563008OFFICE/CLINICcabgs
582563008OFFICE/CLINICstemi
582563008OFFICE/CLINICnstemi
582563008OFFICE/CLINICmbr1128
582563008OFFICE/CLINICoutpt_pci
582563008OFFICE/CLINICmaj_restudy
582563008OFFICE/CLINICmin_restudy
582563008OFFICE/CLINICrestudy_denom
582563008OFFICE/CLINICPCI_FU_30
582563008OFFICE/CLINICFU30_denom
582563008OFFICE/CLINICpci_fu_182
582563008OFFICE/CLINICFU182_denom
582563008OTHER INPATIENT FACILITYcaths
582563008OTHER INPATIENT FACILITYpcis
582563008OTHER INPATIENT FACILITYmpis
582563008OTHER INPATIENT FACILITYimppacs
582563008OTHER INPATIENT FACILITYsecho
582563008OTHER INPATIENT FACILITYpet
582563008OTHER INPATIENT FACILITYtte
582563008OTHER INPATIENT FACILITYtee
582563008OTHER INPATIENT FACILITYrevascs
582563008OTHER INPATIENT FACILITYperfs1
582563008OTHER INPATIENT FACILITYcts
582563008OTHER INPATIENT FACILITYcabgs
582563008OTHER INPATIENT FACILITYstemi
582563008OTHER INPATIENT FACILITYnstemi
582563008OTHER INPATIENT FACILITYmbr
582563008OTHER INPATIENT FACILITYoutpt_pci
582563008OTHER INPATIENT FACILITYmaj_restudy
582563008OTHER INPATIENT FACILITYmin_restudy
582563008OTHER INPATIENT FACILITYrestudy_denom
582563008OTHER INPATIENT FACILITYPCI_FU_30
582563008OTHER INPATIENT FACILITYFU30_denom
582563008OTHER INPATIENT FACILITYpci_fu_182
582563008OTHER INPATIENT FACILITYFU182_denom
582563008OTHER OUTPATIENT PLACE OF SERVICEcaths
582563008OTHER OUTPATIENT PLACE OF SERVICEpcis
582563008OTHER OUTPATIENT PLACE OF SERVICEmpis
582563008OTHER OUTPATIENT PLACE OF SERVICEimppacs
582563008OTHER OUTPATIENT PLACE OF SERVICEsecho
582563008OTHER OUTPATIENT PLACE OF SERVICEpet
582563008OTHER OUTPATIENT PLACE OF SERVICEtte2
582563008OTHER OUTPATIENT PLACE OF SERVICEtee
582563008OTHER OUTPATIENT PLACE OF SERVICErevascs
582563008OTHER OUTPATIENT PLACE OF SERVICEperfs3
582563008OTHER OUTPATIENT PLACE OF SERVICEcts
582563008OTHER OUTPATIENT PLACE OF SERVICEcabgs
582563008OTHER OUTPATIENT PLACE OF SERVICEstemi
582563008OTHER OUTPATIENT PLACE OF SERVICEnstemi
582563008OTHER OUTPATIENT PLACE OF SERVICEmbr2
582563008OTHER OUTPATIENT PLACE OF SERVICEoutpt_pci
582563008OTHER OUTPATIENT PLACE OF SERVICEmaj_restudy
582563008OTHER OUTPATIENT PLACE OF SERVICEmin_restudy
582563008OTHER OUTPATIENT PLACE OF SERVICErestudy_denom
582563008OTHER OUTPATIENT PLACE OF SERVICEPCI_FU_30
582563008OTHER OUTPATIENT PLACE OF SERVICEFU30_denom
582563008OTHER OUTPATIENT PLACE OF SERVICEpci_fu_182
582563008OTHER OUTPATIENT PLACE OF SERVICEFU182_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.

7 REPLIES 7
Reeza
Super User

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
Fluorite | Level 6 tmm
Fluorite | Level 6

Snippet of table I have:

stratadescriptcathspcismpisimppacssechopettteteerevascsperfsctscabgssteminstemimbr
582563008 715
582563008IP266 4 21811755 2201
582563008OP1027 104 156717140 1234
582563008HOSPICE 1 1
582563008OFFICE 5 1129 509 1128
582563008OTHER IP 1
582563008OTHER OP 2 3 2
sumofmbr2281

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:

strataIP_cathsIP_pcisip_mpisip_imppacsip_sechoip_petip_tteip_teeip_revascsip_perfsip_ctsip_cabgsip_stemiip_nstemimbrs
5825630081/26/19006 4 21811755 22281

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
Super User

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
Fluorite | Level 6 tmm
Fluorite | Level 6

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
Quartz | Level 8

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
Opal | Level 21

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
Super User Tom
Super User

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=.

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
  • 7 replies
  • 1593 views
  • 1 like
  • 5 in conversation