Compare start_date and end_date in different rows

[cindyforest7]

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!

[ballardw]

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.

[cindyforest7]

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.

[Barnipaz]

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

[Astounding]

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.