BookmarkSubscribeRSS Feed
cindyforest7
Calcite | Level 5

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!

4 REPLIES 4
ballardw
Super User

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
Calcite | Level 5

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
Obsidian | Level 7

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
PROC Star

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.

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 986 views
  • 0 likes
  • 4 in conversation