Please post your code using a complete data step and datalines statement so we don't have to work so hard to help you out. As to your problem, and only considering the pattern in the data you posted: Use a surrogate key (record pointer) and SQL's Cartesian product to "look ahead" to the next record. data have;
length cat1 $1 cat2 8 start end 8;
format start end date9.;
informat start end mmddyy10.;
input cat1 cat2 start end;
datalines;
A 1 8/8/2016 10/16/2016
A 1 11/1/2016 1/10/2017
A 1 1/11/2017 4/23/2017
A 1 4/24/2017 7/19/2017
A 1 7/20/2017 9/30/2017
A 1 10/1/2017 3/31/2018
A 2 10/1/2017 4/29/2018
A 2 10/1/2017 7/1/2018
;
run;
* create surrogate key ;
data sk / view=sk;
sk+1;
set have;
run;
proc sql;
* for illustration/debugging ;
select
a.*
,b.*
from
sk a
join
sk b
on
a.cat1=b.cat1
and
a.cat2=b.cat2
and
a.sk=b.sk-1
;
create table want as
select
a.cat1
,a.cat2
,a.start as old_start
,a.end as old_end
,b.start as new_start
,b.end as new_end
,intck('day',old_end,new_start) as gap
from
sk a
join
sk b
on
a.cat1=b.cat1
and
a.cat2=b.cat2
and
a.sk=b.sk-1
where
intck('day',old_end,new_start) > 1
;
quit; You can tidy this up a bit, dropping old_start and new_end if you wish. This doesn't cover say overlapping dates, where the new start date < old end date. Again, see bold type above. Is there a typo in EffDt in your last dataline?
... View more
