02-24-2014 05:33 PM
I have a program participation table look like this:
member ID program_start_date program_end_date duration
1 01/13/2013 01/29/2013 13
1 01/30/2013 08/21/2013 30
2 07/14/2012 09/30/2013 63
2 10/01/2013 10/20/2013 90
2 11/02/2013 12/31/2013 14
3 03/25/2013 05/17/2013 230
3 07/23/2013 08/09/2013 130
3 08/10/2013 12/01/2013 12
3 12/02/2013 12/31/2013 41
For each member, I want to compare program_end_date of an enrollment period with program_start_date of the next enrollment period. If the enrollment periods are all continuous, I want to add the durations together as total_days. If there is a gap between two periods, I want to keep the total_days of the most recent continuous enrollment period.
I have thought about LAG() function, but had trouble summing up durations for different situations.
Anyone who can help me out will be greatly appreciated!
02-24-2014 05:48 PM
How did the duration from 01/13/2013 to 08/21/2013 get to be 30??
It will help if you provide an example of exactly how your output should look.
02-24-2014 07:36 PM
Those are random numbers....I didn't really do the calculations.
I want the final table to look like this:
Member ID total continous days (total_days)
Please let me know if you have other questions.
02-25-2014 09:26 AM
I'll use the retain and the first and last.
Something like this (I don't know if works because have written it by memory, but you can start by it)
proc sorta data=; by MemberID program_start_date;run;
retain xxx _sum .;
if first MemberID then do;
if program_start_date=xxx+1 then do;
if last.MemberID then output;
The problem is if yuo need the max totalday or the last total_day. My code do the last, but with some changment you can do the first option
02-25-2014 09:47 AM
Here's an attempt.
prior_end = lag(program_end_date);
if first.MemberID then total_days = duration;
if prior_end + 1 = program_start_date then total_days + duration;
else total_days = duration;