BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
jcg2000
Calcite | Level 5

I need to consolidate one member’s multiple records with consecutive dates into one. That is, one member has multiple records with the start date of one records being the next day of the previous record’s end date.  Here is one example:

 

Member_ID   Eff_Date   Term_Date   Sequence_ID
--------- ---------- ---------- --------------------
123456789   2005-01-01   2006-01-31   1
123456789   2006-02-01   2007-02-14   2
123456789   2007-02-15   2007-05-11   3
123456789   2007-05-12   2008-02-28   4
123456789   2008-02-29   2008-12-31   5
123456789   2009-11-01   2009-12-31   6
123456789   2010-01-01   2010-08-11   7
123456789   2010-08-12   2017-12-31   8


The end result I'm looking for would be two records for this member like this.

Member_ID   Eff_Date   Term_Date   Sequence_ID
--------- ---------- ---------- --------------------
123456789   2005-01-01   2008-12-31   1
123456789   2009-11-01   2017-12-31   2

 

Any idea about how to deal with this in SAS?

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

Do Until() can be useful for this:

 

data have;
input Member_ID   (Eff_Date   Term_Date) (:yymmdd.)   Sequence_ID;
format eff_date term_date yymmdd10.;
datalines;
123456789   2005-01-01   2006-01-31   1
123456789   2006-02-01   2007-02-14   2
123456789   2007-02-15   2007-05-11   3
123456789   2007-05-12   2008-02-28   4
123456789   2008-02-29   2008-12-31   5
123456789   2009-11-01   2009-12-31   6
123456789   2010-01-01   2010-08-11   7
123456789   2010-08-12   2017-12-31   8
;

data want;
do until(last.member_id);
    set have; by member_id;
    if first.member_id then do;
        e_date = eff_date;
        end;
    else if intnx("DAY", eff_date, -1) ne next_date then do;
        t_date = next_date;
        s_id = sum(s_id, 1);
        output;
        e_date = eff_date;
        end;
    next_date = term_date;
    end;
t_date = term_date;
s_id = sum(s_id, 1);
output;

format e_date t_date yymmdd10.;
keep member_id e_date t_date s_id;
rename e_date=eff_date t_date=term_date s_id=sequence_id;
run;

proc print; run;
PG

View solution in original post

3 REPLIES 3
Kurt_Bremser
Super User
data want;
set have (
  drop=sequence_id
  rename=(
    member_id=id
    eff_date=eff
    term_date=term
  )
) end=done;
by id;
retain eff_date term_date sequence_id;
format eff_date term_date yymmddd10.;
if first.id
then do;
  if member_id ne . /* we had a previous member_id that is through now */
  then output;
  eff_date = eff;
  term_date = term;
  sequence_id = 1;
  member_id = id;
end;
if term_date + 1 < eff /* start a new sequence */
then do;
  output; /* write the old sequence */
  member_id = id;
  eff_date = eff;
  sequence_id + 1;
end;
term_date = term;
if done then output;
run;

(Untested)

Ksharp
Super User
data have;
input Member_ID   Eff_Date  : yymmdd10. Term_Date : yymmdd10. Sequence_ID;
format Eff_Date Term_Date  yymmdd10.;
cards;
123456789   2005-01-01   2006-01-31   1
123456789   2006-02-01   2007-02-14   2
123456789   2007-02-15   2007-05-11   3
123456789   2007-05-12   2008-02-28   4
123456789   2008-02-29   2008-12-31   5
123456789   2009-11-01   2009-12-31   6
123456789   2010-01-01   2010-08-11   7
123456789   2010-08-12   2017-12-31   8
;
run;

data temp;
 set have;
 by Member_ID;
 if first.Member_ID or Eff_Date ne lag(Term_Date)+1 then group+1;
run;
data want;
 set temp;
 by group;
 retain start ;
 if first.group then start=Eff_Date;
 if last.group then do;
  new_id+1;end=Term_Date;output;
 end;
 format start end yymmdd10.;
 keep Member_ID start end new_id;
run;


PGStats
Opal | Level 21

Do Until() can be useful for this:

 

data have;
input Member_ID   (Eff_Date   Term_Date) (:yymmdd.)   Sequence_ID;
format eff_date term_date yymmdd10.;
datalines;
123456789   2005-01-01   2006-01-31   1
123456789   2006-02-01   2007-02-14   2
123456789   2007-02-15   2007-05-11   3
123456789   2007-05-12   2008-02-28   4
123456789   2008-02-29   2008-12-31   5
123456789   2009-11-01   2009-12-31   6
123456789   2010-01-01   2010-08-11   7
123456789   2010-08-12   2017-12-31   8
;

data want;
do until(last.member_id);
    set have; by member_id;
    if first.member_id then do;
        e_date = eff_date;
        end;
    else if intnx("DAY", eff_date, -1) ne next_date then do;
        t_date = next_date;
        s_id = sum(s_id, 1);
        output;
        e_date = eff_date;
        end;
    next_date = term_date;
    end;
t_date = term_date;
s_id = sum(s_id, 1);
output;

format e_date t_date yymmdd10.;
keep member_id e_date t_date s_id;
rename e_date=eff_date t_date=term_date s_id=sequence_id;
run;

proc print; run;
PG

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 3 replies
  • 568 views
  • 2 likes
  • 4 in conversation