Average claims per year before and after an index date

cdubs — Tue, 24 Jul 2018 05:04:05 GMT

Have this dataset

patient_id / claim_date / index_date / enroll_start / enroll_end

1 01JAN2010 01JAN2013 01JAN2010 15DEC2016

1 05FEB2012 01JAN2013 01JAN2010 15DEC2016

1 01JAN2013 01JAN2013 01JAN2010 15DEC2016

1 05AUG2015 01JAN2013 01JAN2010 15DEC2016

2 01JAN2010 03MAR2016 01JAN2010 31DEC2016

2 05FEB2012 03MAR2016 01JAN2010 31DEC2016

2 01JAN2013 03MAR2016 01JAN2010 31DEC2016

2 05AUG2015 03MAR2016 01JAN2010 31DEC2016

2 03MAR2016 03MAR2016 01JAN2010 31DEC2016

Want the following output (outputs mean hospitalizations per year for time period before vs. ON or after index date)

patient_id avg_hosp_preindex avg_hosp_postindex

1 0.66 0.51

2 0.65 1.197

^ there may be manual calculation errors but basically

avg_hosp_preindex = # of hospitalizations per year (time period from enroll_start to index_date)

avg_hosp_postindex = # of hospitalizations per year (time period from index_date to enroll_end).

I was thinking of the following steps

1. Create variable "pre index time" as enroll_start to index_date, in years

2. Create variable "post index time" as index_date to enroll_end, in years

3. Create variable "pre index hosp count" as sum of claims if claim date < index date

4. Create variable "post index hosp count" as sum of claims if claim date >= index date

5. Create variable "pre index avg hosp" as "pre index hosp count" / "pre index time"

6. Create variable "post index avg hosp" as "post index hosp count" / "post index time"

Is there a simpler way to do this? What is the best way to sum the number of claims (e.g. "pre index hosp count") by the same patient id?

Thank you!

Re: Average claims per year before and after an index date

PeterClemmensen — Tue, 24 Jul 2018 05:37:52 GMT

Is index_date always the same across IDs?

cdubs — Tue, 24 Jul 2018 13:02:42 GMT

Yes, as well as enroll start and end