Help using Base SAS procedures

proc transpose not outputting data needed

Reply
Regular Contributor
Regular Contributor
Posts: 238

proc transpose not outputting data needed

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
Respected Advisor
Posts: 3,799

Re: proc transpose not outputting data needed

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;
Regular Contributor
Regular Contributor
Posts: 238

Re: proc transpose not outputting data needed

Posted in reply to data_null__

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.

Regular Contributor
Regular Contributor
Posts: 238

Re: proc transpose not outputting data needed

Posted in reply to data_null__

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.

Super User
Posts: 19,772

Re: proc transpose not outputting data needed

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;

Regular Contributor
Regular Contributor
Posts: 238

Re: proc transpose not outputting data needed

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;

PROC Star
Posts: 7,468

Re: proc transpose not outputting data needed

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

Ask a Question
Discussion stats
  • 6 replies
  • 229 views
  • 0 likes
  • 4 in conversation