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?
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;
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)
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;
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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.