I have added a final row to your sample data set. As a result the id 253333 has three consecutive obs that overlap to make a single date range. This code will accommodate that situation:
data have;
input ID Event_Date QTR Payment_To Monthes_To_Pay ;
informat event_date qtr payment_to ddmmyy10.;
format event_date qtr payment_to ddmmyy10. ;
datalines;
251595 23/03/2011 01/03/2011 21/09/2011 6
277681 20/06/2004 01/06/2004 30/04/2005 10
277681 15/12/2004 01/12/2004 28/02/2005 2
283044 21/11/2004 01/12/2004 31/07/2006 20
285726 17/01/2008 01/03/2008 31/01/2009 12
311449 01/11/2004 01/12/2004 31/12/2005 13
311449 03/02/2005 01/03/2005 31/07/2005 5
253333 07/11/2005 01/12/2005 31/10/2010 59
253333 12/12/2007 01/12/2007 30/09/2011 45
253333 12/12/2008 01/12/2008 30/09/2012 45
run;
data want (drop=i n nxt_: extended_:);
/*Read all overlaps and generate extended payment_to */
do n=1 by 1 until (last.id or nxt_ev>extended_pt);
set have (keep=id);
by id notsorted;
merge /*Current obs */ have
/*Next obs */ have (firstobs=2 keep=event_date payment_to
rename=(event_date=nxt_ev payment_to=nxt_pt)) ;
if extended_pt=. then extended_pt=payment_to;
if last.id=0 and nxt_ev<=extended_pt then extended_pt=max(extended_pt,nxt_pt);
end;
/*Reread the same N obs, output only the first, but with the extended payment_to */
do i=1 to n;
set have;
if i=1 then do;
payment_to=extended_pt;
monthes_to_pay=intck('month',event_date,payment_to);
output;
end;
end;
run;
... View more
