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

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_NBRPROC_CDSBMT_CHRG_AMTALLW_AMTNET_PD_AMT
1119208315065.4165.41
1119921310592.1267.12
11192014275166.95141.95
2229921315596.371.3
22299214175130.94105.94
333992139862.7137.71
33399204250104.9579.95

DATA NEED
CLM_AUD_NBRPROC1SBMT1ALLW1NET1PROC2SBMT2ALLW2NET2PROC3SBMT3ALLW3NET3
1119208315065.4165.419921310592.1267.1292014275166.95141.95
2229921315596.371.399214175130.94105.94
333992139862.7137.7199204250104.9579.95
33399204250104.9579.9599204250104.9579.9599204250104.9579.95
6 REPLIES 6
data_null__
Jade | Level 19

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

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

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

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

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

; 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)

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
  • 6 replies
  • 897 views
  • 0 likes
  • 4 in conversation