How to consolidate these records?

Accepted Solution Solved
Reply
Learner
Posts: 1
Accepted Solution

How to consolidate these records?

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?


Accepted Solutions
Solution
‎08-22-2016 01:51 PM
Respected Advisor
Posts: 4,816

Re: How to consolidate these records?

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


All Replies
Super User
Posts: 7,431

Re: How to consolidate these records?

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)

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 9,867

Re: How to consolidate these records?

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;


Solution
‎08-22-2016 01:51 PM
Respected Advisor
Posts: 4,816

Re: How to consolidate these records?

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
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 264 views
  • 2 likes
  • 4 in conversation