30 day events

[Anna Tavakkoli]

Hi,

I am using a large claims database to look at the costs of a procedure (ERCP) and compare the costs among those patients with and without an adverse event called post-ercp pancreatitis (PEP). Patients can have ERCPs and PEP more than once and I am struggling with how to capture 30-day events when a patient has multiple procedures. I was using by enrolid : replace date_bil_alt_ = date_bil_alt_[_n-1] if date_bil_alt_ == . to fill in any missing ERCP date observations so I can identify claims occuring in 30 days and bys enrolid date_bil_alt: replace pep_date = pep_date[_n-1] if pep_date == . to fill in any missing dates of post-ERCP pancreatitis. However, what was happening is that I am not capturing all 30 days if someone has multiple ERCPs or multiple episodes of PEP (example below). My coding will only identify up to their next procedure and I am missing costs. Any suggestions on how I could organize this would be helpful.

Thank you

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

* Example generated by -dataex-. To install: ssc install dataex
clear
input double enrolid float(svcdate date_bil_alt pep_date)
38501 21640     .     .
38501 21641 21641     .
38501 21641 21641     .
38501 21641 21641     .
38501 21641 21641     .
38501 21641 21641     .
38501 21641 21641     .
38501 21641 21641     .
38501 21642 21641     .
38501 21642 21641 21642
38501 21643 21641     .
38501 21649 21641     .
38501 21650 21641     .
38501 21651 21641     .
38501 21651 21641     .
38501 21651 21641     .
38501 21651 21641     .
38501 21651 21641     .
38501 21656 21641     .
38501 21656 21641     .
38501 21656 21641     .
38501 21661 21641     .
38501 21666 21641     .
38501 21666 21641     .
38501 21666 21641     .
38501 21666 21641     .
38501 21666 21641     .
38501 21666 21641     .
38501 21666 21641     .
38501 21666 21641     .
38501 21666 21641     .
38501 21666 21641     .
38501 21666 21641     .
38501 21666 21641     .
38501 21666 21641     .
38501 21666 21641     .
38501 21666 21641     .
38501 21666 21641     .
38501 21667 21641     .
38501 21667 21641     .
38501 21668 21641     .
38501 21668 21641     .
38501 21668 21641     .
38501 21668 21641     .
38501 21668 21641     .
38501 21669 21641     .
38501 21669 21641     .
38501 21669 21641     .
38501 21669 21641     .
38501 21669 21641     .
38501 21670 21641     .
38501 21670 21641     .
38501 21672 21641     .
38501 21675 21675     .
38501 21675 21675     .
38501 21675 21675     .
38501 21675 21675     .
38501 21675 21675     .
38501 21675 21675     .
38501 21675 21675     .
38501 21675 21675     .
38501 21675 21675     .
38501 21676 21675     .
38501 21676 21675     .
38501 21676 21675     .
38501 21676 21675     .
38501 21676 21675     .
38501 21676 21675     .
38501 21676 21675     .
38501 21676 21675     .
38501 21676 21675     .
38501 21676 21675     .
38501 21676 21675     .
38501 21676 21675     .
38501 21676 21675     .
38501 21676 21675     .
38501 21676 21675     .
38501 21676 21675     .
38501 21676 21675     .
38501 21676 21675     .
38501 21676 21675     .
38501 21676 21675     .
38501 21676 21675     .
38501 21676 21675     .
38501 21676 21675     .
38501 21676 21675     .
38501 21676 21675     .
38501 21676 21675     .
38501 21676 21675     .
38501 21676 21675     .
38501 21676 21675     .
38501 21676 21675     .
38501 21676 21675     .
38501 21676 21675     .
38501 21676 21675     .
38501 21676 21675     .
38501 21676 21675     .
38501 21676 21675     .
38501 21676 21675     .
38501 21676 21675     .
38501 21676 21675     .
38501 21676 21675     .
38501 21676 21675     .
38501 21676 21675     .
38501 21676 21675     .
38501 21676 21675     .
38501 21676 21675     .
38501 21676 21675     .
38501 21676 21675     .
38501 21676 21675     .
38501 21676 21675     .
38501 21676 21675     .
38501 21676 21675     .
38501 21676 21675     .
38501 21676 21675     .
38501 21676 21675     .
38501 21676 21675     .
38501 21676 21675 21676
38501 21676 21675     .
38501 21676 21675     .
38501 21676 21675     .
38501 21676 21675     .
38501 21677 21675     .
38501 21677 21675     .
38501 21677 21675     .
38501 21678 21678     .
38501 21678 21678     .
38501 21678 21678     .
38501 21678 21678     .
38501 21678 21678     .
38501 21678 21678     .
38501 21679 21678     .
38501 21679 21678     .
38501 21680 21678     .
38501 21681 21678     .
38501 21681 21678     .
38501 21682 21678     .
38501 21682 21678     .
38501 21683 21678     .
38501 21683 21678     .
38501 21684 21678     .
end
format %td svcdate
format %td date_bil_alt
format %td pep_date

[Clyde Schechter]

It isn't at all clear to me what you want. And your data confuses me because the vast majority of the observations are pure duplicates of other observations, and therefore convey no information. I don't know what you mean by a "30 day event." So I'm just going to make something up that sounds like it might be helpful, and hope that it is. I'm going to give you code that will create a new data set containing each PEP that occurs within 30 days after a billing date (including on the billing date itself), and which billing date(s) are involved.

//    REORGANIZE THE DATA SET INTO A TIMELINE OF DISTINC BILLING AND PEP EVENTS
drop svcdate // AS BEST I CAN TELL THIS VARIABLE IS IRRELEVANT TO CURRENT PROBLEM
rename pep_date date_pep
gen `c(obs_t)' obs_no = _n
reshape long date_, i(obs_no) j(event) string
rename date_ date
drop if missing(date)
drop obs_no
duplicates drop
isid enrolid date event, sort

//    NOW FOR EACH PEP EVENT, COUNT NUMBER OF BILLING EVENTS IN PRECEDING 30 DAYS
gen billing_date = date if event == "bil_alt"
gen earliest = date - 30 if event == "pep"
format earliest billing_date %td

//    SEPARATE BILLINGS AND PEPS INTO SEPARATE FILES
preserve
keep event enrolid billing_date
keep if event == "bil_alt"
tempfile billings
save `billings'
list

restore
keep if event == "pep"
keep enrolid event date earliest

//    COMBINE THEM & KEEP PAIRINGS WHERE PEP IS WITHIN 30 DAYS OF BILLING DATE
joinby enrolid using `billings'
keep if inrange(billing_date, date-30, date)
drop earliest

At the end of this code, the data in memory will consist of one observation for each pair of PEP dates and billing dates (date_bil_alt) where the PEP date is within 30 days of the billing date (inclusive on both ends). Hopefully you can make use of this for your purposes.

Note: You expressed concern that you didn't know how to handle the problem of getting past a second billing date that occurred within the 30 day window. There are no instances in your example data of a second billing date that intercedes between an earlier billing date and a PEP within 30 days of the earlier date. But if there were, this code would show both billing dates for that PEP.

[Anna Tavakkoli]

Clyde Schechter thank you for the help. My apologies for not being clear. The svcdates were for procedure codes or diagnosis codes related to a claim. The 30 days is in reference to 30 days after their ERCP or 30 days after their diagnosis of PEP. I calculate the 30-day events using svcdate (svcdate-date_bil; svdate-pep-date). I then accumulate the total costs for those outpatient or inpatient admissions associated with the claim on the svcdates.

In the example below, there is an overlap of days for the ERCP: On may 6 the pt had an ERCP, then on May 9th another one and that was associated with PEP. The way I have it now I am only catching 3 days of claims after the May 6th ERCP and need all 30 days after (for both date_bil and pep_date)

* Example generated by -dataex-. To install: ssc install dataex
clear
input double enrolid float svcdate str5 proc float(pep_date date_bil)
38501 21641 "43274" . 21641
38501 21641 "00732" . .
38501 21641 "74181" . .
38501 21641 "88112" . .
38501 21641 "99232" . .
38501 21641 "88112" . .
38501 21641 "74328" . .
38501 21642 "99233" . .
38501 21642 "99232" 21642 .
38501 21643 "99239" . .
38501 21649 "G0463" . .
38501 21650 "99495" . .
38501 21651 "82150" . .
38501 21651 "36415" . .
38501 21651 "83690" . .
38501 21651 "85025" . .
38501 21651 "80053" . .
38501 21656 "85610" . .
38501 21656 "85027" . .
38501 21656 "80053" . .
38501 21661 "99213" . .
38501 21666 "" . .
38501 21666 "" . .
38501 21666 "" . .
38501 21666 "99053" . .
38501 21666 "71045" . .
38501 21666 "" . .
38501 21666 "" . .
38501 21666 "" . .
38501 21666 "" . .
38501 21666 "" . .
38501 21666 "" . .
38501 21666 "99285" . .
38501 21666 "" . .
38501 21666 "" . .
38501 21666 "" . .
38501 21666 "" . .
38501 21667 "74177" . .
38501 21667 "99222" . .
38501 21668 "99223" . .
38501 21668 "99223" . .
38501 21668 "99223" . .
38501 21668 "99223" . .
38501 21668 "99233" . .
38501 21669 "93971" . .
38501 21669 "99233" . .
38501 21669 "99232" . .
38501 21669 "99232" . .
38501 21669 "99232" . .
38501 21670 "99232" . .
38501 21670 "99239" . .
38501 21672 "88321" . .
38501 21675 "43276" . 21675
38501 21675 "43242" . .
38501 21675 "74328" . .
38501 21675 "99100" . .
38501 21675 "88173" . .
38501 21675 "88177" . .
38501 21675 "88172" . .
38501 21675 "00732" . .
38501 21675 "88305" . .
38501 21676 "83690" . .
38501 21676 "74176" . .
38501 21676 "84145" . .
38501 21676 "" . .
38501 21676 "" . .
38501 21676 "" . .
38501 21676 "" . .
38501 21676 "" . .
38501 21676 "" . .
38501 21676 "" . .
38501 21676 "J2550" . .
38501 21676 "" . .
38501 21676 "99222" . .
38501 21676 "" . .
38501 21676 "" . .
38501 21676 "96376" . .
38501 21676 "99285" . .
38501 21676 "96365" . .
38501 21676 "83605" . .
38501 21676 "" . .
38501 21676 "" . .
38501 21676 "96361" . .
38501 21676 "96372" . .
38501 21676 "" . .
38501 21676 "93005" . .
38501 21676 "93010" . .
38501 21676 "J2405" . .
38501 21676 "J2405" . .
38501 21676 "" . .
38501 21676 "" . .
38501 21676 "80053" . .
38501 21676 "" . .
38501 21676 "" . .
38501 21676 "" . .
38501 21676 "" . .
38501 21676 "93010" . .
38501 21676 "" . .
38501 21676 "A0426" . .
38501 21676 "J0360" . .
38501 21676 "99223" . .
38501 21676 "" . .
38501 21676 "" . .
38501 21676 "82150" . .
38501 21676 "J7040" 21676 .
38501 21676 "" . .
38501 21676 "" . .
38501 21676 "74176" . .
38501 21676 "A0425" . .
38501 21676 "" . .
38501 21676 "J2270" . .
38501 21676 "" . .
38501 21676 "99285" . .
38501 21676 "99285" . .
38501 21676 "" . .
38501 21676 "96375" . .
38501 21676 "" . .
38501 21676 "" . .
38501 21676 "" . .
38501 21676 "" . .
38501 21676 "85025" . .
38501 21677 "99232" . .
38501 21677 "74177" . .
38501 21677 "71260" . .
38501 21678 "43274" . 21678
38501 21678 "00732" . .
38501 21678 "99232" . .
38501 21678 "99100" . .
38501 21678 "99222" . .
38501 21678 "74328" . .
38501 21679 "99232" . .
38501 21679 "99232" . .
38501 21680 "99233" . .
38501 21681 "99233" . .
38501 21681 "74183" . .
38501 21682 "99222" . .
38501 21682 "99233" . .
38501 21683 "99232" . .
38501 21683 "99231" . .
38501 21684 "72100" . .
end
format %td svcdate
format %td pep_date
format %td date_bil
[/CODE]

Please let me know if that is more clear. The code you wrote above takes out svcdate because I was unclear (sorry).

Appreciate your help

[Clyde Schechter]

Sorry to be dense, but now I'm perhaps even more confused. Now we have this list of procedure codes. Which one(s) correspond to an ERCP. You said ERCP's were done on May 6 and May 9, and of the codes associated with both of those dates, there are only three in common: 00732, 74328, and 99100. Which of those is (are) an ERCP code? I'm guessing it's 99100, because that's the only one of the three that doesn't also occur on other service dates. Is that right?

And what, if anything, is the relevance of the many observations containing non-ERCP procedure codes and have neither a billing date nor a PEP date? It seems like they could be deleted with no loss of information for the present purpose. Is that right?

The variable date_bil is mostly missing. I know the health care system well enough to be confident that these other procedures are not going unbilled. Carrying forward the non-missing billing dates to subsequent observations is clearly wrong because that leads to observations with later service dates, and you can't bill for services not yet rendered. So do we carry the billing dates backwards to preceding observations until an earlier non-missing billing date is encountered? Or something else? And what about the observations in the data set that follow the last observation with a non-missing billing-date: there is no billing date at the end to carry backwards.

The way I have it now I am only catching 3 days of claims after the May 6th ERCP and need all 30 days after (for both date_bil and pep_date)

But the billing dates only go as far as May 9, and the service dates only as far as May 15, so I don't understand what this means.

I suspect we will get nowhere slowly trying to explain this in words. I suggest that you work out by hand what the results you want for this example would be and post back with that. Then perhaps I can "reverse engineer" the "finished product" and get you some usable code.

[Anna Tavakkoli]

Thank you Clyde Schechter I appreciate your help. I think what I am going to do is just assign an index ERCP for each person (their first one) and count 30 days from there. After reading how confusing it is to you (and trying to post the result I want) I am realizing its more complicated than I need it to be.

I appreciate your willingness to help.

Anna