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!
Is index_date always the same across IDs?
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.