Help grouping variables by a common date

[Anna Tavakkoli]

Hi,

I am using a claims based database in regards to a procedure called ERCP. A patient may get billed with multiple CPT codes for the same date of procedure to describe what happened. Example would be CPT code 43262 (ercp with sphincterotomy) and CPT code 43264 (ercp with calculi removal) which is billed separately but I would like to categorize it under one indication: ERCP with sphincterotomy and stone removal.

I want to be able to group the indications for these procedures but am having a difficult time figuring out how to group multiple CPT codes under one service date. I have tried going both long and short with the data but still cannot see how to do it. Any help would be appreciated it.

----------------------- copy starting from the next line -----------------------

* Example generated by -dataex-. To install: ssc install dataex
clear
input float id str2 ercp_ind float svcdate str5 proc1
1 "3"  21628 "43262"
1 "7"  21628 "43274"
1 "5"  22131 "43264"
1 "3"  22131 "43262"
1 "3"  22131 "43262"
1 "5"  22131 "43264"
2 "5"  22125 "43264"
2 "5"  22125 "43264"
2 "7"  22125 "43274"
2 "7"  22125 "43274"
2 "5"  22125 "43264"
2 "7"  22125 "43274"
2 "7"  22125 "43274"
2 "5"  22125 "43264"
2 "7"  22125 "43274"
2 "7"  22125 "43274"
2 "7"  22125 "43274"
2 "7"  22125 "43274"
2 "7"  22125 "43274"
2 "7"  22125 "43274"
2 "7"  22125 "43274"
2 "5"  22125 "43264"
2 "5"  22125 "43264"
2 "7"  22125 "43274"
2 "7"  22125 "43274"
2 "7"  22125 "43274"
2 "5"  22125 "43264"
2 "7"  22125 "43274"
2 "7"  22125 "43274"
2 "5"  22125 "43264"
2 "7"  22125 "43274"
2 "7"  22125 "43274"
2 "7"  22125 "43274"
2 "5"  22125 "43264"
2 "5"  22125 "43264"
2 "7"  22125 "43274"
2 "5"  22171 "43264"
2 "5"  22171 "43264"
2 "9"  22171 "43276"
2 "9"  22171 "43276"
2 "9"  22171 "43276"
2 "9"  22171 "43276"
2 "5"  22171 "43264"
2 "5"  22171 "43264"
2 "5"  22171 "43264"
2 "9"  22171 "43276"
2 "5"  22190 "43264"
2 "5"  22190 "43264"
2 "5"  22190 "43264"
2 "8"  22190 "43275"
2 "8"  22190 "43275"
2 "5"  22190 "43264"
2 "5"  22190 "43264"
2 "8"  22190 "43275"
2 "8"  22190 "43275"
2 "8"  22190 "43275"
2 "5"  22190 "43264"
2 "8"  22190 "43275"
3 "5"  21656 "43264"
3 "10" 21656 "43277"
3 "5"  21656 "43264"
4 "10" 21741 "43277"
4 "7"  22085 "43274"
4 "5"  22085 "43264"
4 "7"  22085 "43274"
4 "5"  22085 "43264"
4 "5"  22285 "43264"
4 "5"  22285 "43264"
4 "7"  22285 "43274"
4 "7"  22285 "43274"
5 "10" 21957 "43277"
5 "10" 21957 "43277"
5 "10" 21957 "43277"
5 "10" 21957 "43277"
5 "10" 21957 "43277"
5 "10" 21957 "43277"
5 "7"  21958 "43274"
5 "7"  21958 "43274"
5 "7"  21977 "43274"
5 "5"  21977 "43264"
5 "5"  21977 "43264"
5 "7"  21977 "43274"
5 "9"  22064 "43276"
5 "5"  22064 "43264"
5 "8"  22064 "43275"
5 "5"  22064 "43264"
5 "9"  22064 "43276"
5 "8"  22064 "43275"
5 "8"  22064 "43275"
5 "9"  22064 "43276"
5 "9"  22064 "43276"
5 "9"  22064 "43276"
6 "10" 22306 "43277"
6 "10" 22306 "43277"
7 "7"  21853 "43274"
7 "7"  21853 "43274"
7 "8"  21896 "43275"
7 "8"  21896 "43275"
8 "7"  22316 "43274"
8 "7"  22316 "43274"
end
format %td svcdate

------------------ copy up to and including the previous line ------------------

[George Ford]

It is unclear what you are looking to do. I'd provide and example using id 1 and 2.

[Anna Tavakkoli]

Hi George Ford ,

Thanks for the reply. Sorry I was unclear.

1 "3" 21628 "43262" 1 "7" 21628 "43274" Examples 1 : this patient has CPT code 43262 and 43274 for the same procedure date. This would be categorized as an ERCP with sphincterotomy (43262) and stent placement (43274). I would like to categorize each ERCP by their group of CPT codes into one common indication so I can report the reasons for the procedure. But I am not sure how to do this when there are multiple CPT codes for the same date of procedure.

1 "5" 22131 "43264" 1 "3" 22131 "43262" Example 2:The same patient above had a repeat ERCP on a different date. This ERCP is an ERCP with stone removal (CPT 43264) and ERCP with sphincterotomy (43262). This would be categorized as an ERCP with stone removal and sphincterotomy.

2 "5" 22125 "43264" 2 "7" 22125 "43274" Example 3 with pt 2. This patient has duplicates (which I have dealt with in my data separately) but has two CPT codes for the same date of procedure. This would be under ERCP with stone removal (43262) and stent placement.

Let me know if this is still unclear. I just want one common indication for each ERCP rather than one indication for each billing code on the same date.

[Clyde Schechter]

I agree with George Ford that the question is not stated clearly. But here's my guess as to what is wanted:

sort id svcdate, stable
by id svcdate: gen int seq = _n
reshape wide ercp_ind proc1, i(id svcdate) j(seq)

Two comments:

1. The data looks rather wierd. You have many observations that are complete duplicates: will a patient really have multiple instances of the exact same procedure (at least at the level of the CPT code) for the same exact indication on the same date? Or do you need to run -duplicates drop- first?
2. Not sure where you're going with this data next. I'll just point out that most of data management and analysis in Stata is easier, or even only possible, when the data is in the long layout that you're starting with. I have a feeling that you will regret changing the data organization in the way shown here: easy things will become difficult or impossible. Unless you specifically know you will be doing things that work better with, or require, a wide data layout, you should not do this.

[Anna Tavakkoli]

Hi Clyde Schechter

Thank you so much. This is what I needed--I could not figure out the correct reshape code. I agree things will be much harder in wide layout but I just need it to create the indications variable and can do the rest of it long. Also yes you are correct I do have a lot of duplicates. I have dealt with it in my dataset but could not delete the duplicates until I took care of the indications with the multiple CPT codes.

I appreciate your help!
Anna