proc transpose not outputting data needed

[tmm]

Here is the code I am using:

proc transpose data=have out=need prefix=metric;

by clm_aud_nbr sbmt_chrg_amt allw_amt net_pd_amt;

var proc_cd;

run;

This is the data I have and what I am looking to do. Just a note I have 4 million rows so what I am including is 7 observations from the main dataset. The data need might also have more than proc3 if a claim has more than 3 procedures and monetary values associated. I counted and the maximum number of procedures I have on a claim is 20.

DATA HAVE
CLM_AUD_NBR	PROC_CD	SBMT_CHRG_AMT	ALLW_AMT	NET_PD_AMT
111	92083	150	65.41	65.41
111	99213	105	92.12	67.12
111	92014	275	166.95	141.95
222	99213	155	96.3	71.3
222	99214	175	130.94	105.94
333	99213	98	62.71	37.71
333	99204	250	104.95	79.95

DATA NEED
CLM_AUD_NBR	PROC1	SBMT1	ALLW1	NET1	PROC2	SBMT2	ALLW2	NET2	PROC3	SBMT3	ALLW3	NET3
111	92083	150	65.41	65.41	99213	105	92.12	67.12	92014	275	166.95	141.95
222	99213	155	96.3	71.3	99214	175	130.94	105.94
333	99213	98	62.71	37.71	99204	250	104.95	79.95
333	99204	250	104.95	79.95	99204	250	104.95	79.95	99204	250	104.95	79.95

[data_null__]

I don't about performance with 4 million obs.  proc summary is very fast but you will need to test.  Change the 3 to 20.

DATA clm;
   input CLM_AUD_NBR PROC_CD SBMT_CHRG_AMT ALLW_AMT NET_PD_AMT;
   cards;
111 92083 150 65.41 65.41
111 99213 105 92.12 67.12
111 92014 275 166.95 141.95
222 99213 155 96.3 71.3
222 99214 175 130.94 105.94
333 99213 98 62.71 37.71
333 99204 250 104.95 79.95
;;;;
   run;
proc summary data=clm;
   by clm_aud_nbr;
   output out=wide(drop=_type_) idgroup(out[3](proc_cd--net_pd_amt)=);
   run;
proc print;
   run;

[tmm]

No. That is not quite what I am looking for. I need 1 claim number per line with all the procs and the amounts listed within that 1 line. For instance, I can do this code:

proc transpose;

data=have out=need prefix=metric;

by clm_aud_nbr;

var proc_cd;

run;

I get:

claim        name of former variable     label     metric1    metric2     metric3    metric4

111          proc_cd                              proc_cd   11900    99212     82270       99214

What I need is more than 1 var transposed. The goal is to have 1 claim number per line only with the proc_cd and monetary values all in that 1 line only.

[tmm]

Yeah. What you put in does not output to a file. So, I will have to see if there is a way to output it to another dataset. I will continue to flip thru my books and online. Just trying to get this project wrapped up so I can go on to another.

[Reeza]

SAS Learning Module: Reshaping data long to wide using the data step

Your output doesn't match your request. Why does 3333 have two lines in the output?

Also, re-look at the WIDE dataset from data_null_'s solution. 

DATA clm;
   input CLM_AUD_NBR PROC_CD SBMT_CHRG_AMT ALLW_AMT NET_PD_AMT;
   cards;
111 92083 150 65.41 65.41
111 99213 105 92.12 67.12
111 92014 275 166.95 141.95
222 99213 155 96.3 71.3
222 99214 175 130.94 105.94
333 99213 98 62.71 37.71
333 99204 250 104.95 79.95
;;;;
   run;
proc summary data=clm;
   by clm_aud_nbr;
   output out=wide(drop=_type_) idgroup(out[3](proc_cd--net_pd_amt)=);
   run;

[tmm]

I figured out how to get it to work. I just did multiple transposes and then merged it all. Here is an example of what I did. I have to have 1 CLAIM NUMBER PER ROW,  for all the data. When claims come in they create 1 row per procedure. So you might have a single claim that has 20 rows cuz of 20 different procedure codes. In order to bounce my final dataset against what Medicare produces, I need distinct claim numbers with all data across or wide I guess in this case. Then when I review Medicare data and see say proc code 29888 billed first and proc cd 29845 billed second and see they do not pay on that claim, they deny the second procedure, I can easily query by looking at the dataset I created from my claims data. This might be too involved for someone not in healthcare and understanding claims to follow, but whatever. I got what I needed. Thanks.

proc transpose data=cci.phyclms2 out=cci.phyclms3 prefix=proc;

by clm_aud_nbr;

var proc_cd;

run;

proc transpose data=cci.phyclms2 out=cci.phyclms4 prefix=net;

by clm_aud_nbr;

var net_pd_amt;

run;

data cci.phyclms5;

merge cci.phyclms3 (drop=_name_ _label_) cci.phyclms4 (drop=_name_ _label_);

by clm_aud_nbr;

run;

[art297]

; You might find it a little easier to do using the macro a group of us presented at SAS Global Forum last year.  You can download the macro at: A Better Way to Flip (Transpose) a SAS Dataset - sasCommunity

The following shows your example code and the macro call that will produce the desired result:

libname cci "c:\art";

DATA cci.phyclms2;

   input CLM_AUD_NBR PROC_CD SBMT_CHRG_AMT ALLW_AMT NET_PD_AMT;

   cards;

111 92083 150 65.41 65.41

111 99213 105 92.12 67.12

111 92014 275 166.95 141.95

222 99213 155 96.3 71.3

222 99214 175 130.94 105.94

333 99213 98 62.71 37.71

333 99204 250 104.95 79.95

;

%transpose(data=cci.phyclms2,

                  out=cci.phyclms3,

                  by=CLM_AUD_NBR,

                  var=PROC_CD SBMT_CHRG_AMT ALLW_AMT NET_PD_AMT,

                  sort=yes,

                  newid=row)