topic Re: Merging two intervals based on a condition in SAS Programming

Merging two intervals based on a condition

lillymaginta1 — Thu, 24 Dec 2020 12:44:45 GMT

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
;

Re: Merging two intervals based on a condition

PaigeMiller — Thu, 24 Dec 2020 13:45:23 GMT

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

Re: Merging two intervals based on a condition

lillymaginta1 — Thu, 24 Dec 2020 15:18:51 GMT

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.

Re: Merging two intervals based on a condition

novinosrin — Thu, 24 Dec 2020 16:12:45 GMT

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.

Re: Merging two intervals based on a condition

Ksharp — Fri, 25 Dec 2020 12:33:34 GMT

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;