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!
