data have;
infile cards ;
input ID first_Date last_Date ;
cards;
001 1/1/2004 1/5/2004
001 1/5/2004 1/10/2004
003 1/1/2005 4/5/2009
004 4/4/2010 4/5/2010
004 4/5/2010 4/15/2010
;
Hi everyone, I have the above data, I am trying to merge any intervals when the last date for that id equals the first date of a subsequent interval. For example for id number 1: I want the start date to be 1/1/2004 and the last date to be a/10/2004. Same for id number 4.
output data
001 1/1/2004 1/10/2004
003 1/1/2005 4/5/2009
004 4/4/2010 4/15/2010
;
That is complicated . There too many scenario you need to consider.
data have;
infile cards ;
input ID (first_Date last_Date) (: mmddyy10.) ;
cards;
001 1/1/2004 1/5/2004
001 1/5/2004 1/10/2004
003 1/1/2005 4/5/2009
004 4/4/2010 4/5/2010
004 4/5/2010 4/15/2010
;
data temp;
set have;
do date=first_date to last_date;
output;
end;
drop first_date last_date;
run;
proc sort data=temp out=temp1 nodupkey;
by id date;
run;
data temp2;
set temp1;
by id;
if first.id or dif(date) ne 1 then group+1;
run;
data want;
do until(last.group);
set temp2;
by group;
if first.group then first_date=date;
if last.group then last_date=date;
end;
format first_date last_date mmddyy10.;
drop group date;
run;
Does it always happen that the first_date of one record is always equal to the last_date for the previous record, within an ID? Or can there be cases where this is not true, for example:
001 1/1/2004 1/5/2004 001 1/6/2004 1/10/2004
Hi PaigeMiller, yes plenty. But in that case, I want to keep them as-is and only merge the one where the last date equals the first date of a subsequent record within the same ID.
Hi @lillymaginta1 Please post a more comprehensive/representative sample of your HAVE and WANT that covers all your scenarios. Explain your requirements for those. That would help in providing a solution in one shot.
That is complicated . There too many scenario you need to consider.
data have;
infile cards ;
input ID (first_Date last_Date) (: mmddyy10.) ;
cards;
001 1/1/2004 1/5/2004
001 1/5/2004 1/10/2004
003 1/1/2005 4/5/2009
004 4/4/2010 4/5/2010
004 4/5/2010 4/15/2010
;
data temp;
set have;
do date=first_date to last_date;
output;
end;
drop first_date last_date;
run;
proc sort data=temp out=temp1 nodupkey;
by id date;
run;
data temp2;
set temp1;
by id;
if first.id or dif(date) ne 1 then group+1;
run;
data want;
do until(last.group);
set temp2;
by group;
if first.group then first_date=date;
if last.group then last_date=date;
end;
format first_date last_date mmddyy10.;
drop group date;
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.