Hi everyone,
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!
Thank you!
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.
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)
1 43
2 14
3 183
Thanks.
Please let me know if you have other questions.
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;
data output;
set input;
by MemberID;
retain xxx _sum .;
if first MemberID then do;
xxx=program_end_date;
end;
if program_start_date=xxx+1 then do;
_sum=sum(_sum,duration);
xxx=program_end_date;
end;
if last.MemberID then output;
run;
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
Here's an attempt.
data want;
set have;
by MemberID;
prior_end = lag(program_end_date);
if first.MemberID then total_days = duration;
else do;
if prior_end + 1 = program_start_date then total_days + duration;
else total_days = duration;
end;
if last.memberID;
drop prior_end;
run;
Good luck.
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 the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.