topic Re: How to consolidate these records? in SAS Programming

How to consolidate these records?

jcg2000 — Wed, 17 Aug 2016 15:42:05 GMT

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?

Re: How to consolidate these records?

Kurt_Bremser — Wed, 17 Aug 2016 17:36:55 GMT

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)

Re: How to consolidate these records?

Ksharp — Thu, 18 Aug 2016 02:46:31 GMT

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;

Re: How to consolidate these records?

PGStats — Thu, 18 Aug 2016 03:45:48 GMT

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;