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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.