DATA Step, Macro, Functions and more

Average claims per year before and after an index date

Reply
Contributor
Posts: 53

Average claims per year before and after an index date

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! 

 

PROC Star
Posts: 1,403

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

Is index_date always the same across IDs?

Contributor
Posts: 53

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

Yes, as well as enroll start and end
Ask a Question
Discussion stats
  • 2 replies
  • 52 views
  • 0 likes
  • 2 in conversation