topic Re: Compare start_date and end_date in different rows in SAS Procedures

Compare start_date and end_date in different rows

cindyforest7 — Mon, 24 Feb 2014 22:33:30 GMT

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!

Re: Compare start_date and end_date in different rows

ballardw — Mon, 24 Feb 2014 22:48:48 GMT

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.

Re: Compare start_date and end_date in different rows

cindyforest7 — Tue, 25 Feb 2014 00:36:28 GMT

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.

Re: Compare start_date and end_date in different rows

Barnipaz — Tue, 25 Feb 2014 14:26:44 GMT

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

Re: Compare start_date and end_date in different rows

Astounding — Tue, 25 Feb 2014 14:47:28 GMT

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.