topic Re: finding time periods that overlap in SAS Programming

finding time periods that overlap

SarahW13 — Fri, 22 Jun 2018 01:52:33 GMT

Hello all!

I have a dataset in which I would like to be able to find time periods that overlap for each id.

Below is an example of the start dataset:

id start_date end_date

1 3/1/2016 6/2/2016

1 4/1/2016 7/1/2016

1 8/2/2016 12/1/2016

1 1/2/2017 3/1/2017

2 5/10/2014 12/1/2014

2 7/10/2014 10/1/2014

2 1/1/2015 3/2/2015

I want to be able to identify the rows for each id with the overlapping periods, like below:

id start_date end_date

1 3/1/2016 6/2/2016

1 4/1/2016 7/1/2016

2 5/10/2014 12/1/2014

2 7/10/2014 10/1/2014

Can anyone guide me on this?

Thank you!

Re: finding time periods that overlap

pau13rown — Fri, 22 Jun 2018 02:08:44 GMT

there is discussion of overlap here: http://www2.sas.com/proceedings/sugi31/048-31.pdf

they use arrays

Re: finding time periods that overlap

Patrick — Fri, 22 Jun 2018 03:18:37 GMT

A SQL self-join makes this sort of problem often quite simple.

data have;
  infile datalines dlm=' ' truncover;
  input id start_date:mmddyy. end_date:mmddyy.;
  format start_date end_date date9.;
  datalines;
1 3/1/2016 6/2/2016
1 4/1/2016 7/1/2016
1 8/2/2016 12/1/2016
1 1/2/2017 3/1/2017
2 5/10/2014 12/1/2014
2 7/10/2014 10/1/2014
2 1/1/2015 3/2/2015
;
run;

proc sql;
  create table want as
  select 
    l.*
  from have l, have r
    where 
      /* test only within same id */
  l.id=r.id
  /* do not join a record with itself */
  and not (l.start_date=r.start_date and l.end_date=r.end_date)
  /* find overlapping dates */
  and 
  (
  l.start_date between r.start_date and r.end_date
  or
  r.start_date between l.start_date and l.end_date
    )
  ;
quit;

Re: finding time periods that overlap

Ksharp — Fri, 22 Jun 2018 12:56:48 GMT

data have;
  infile datalines dlm=' ' truncover;
  input id start_date:mmddyy. end_date:mmddyy.;
  format start_date end_date date9.;
  datalines;
1 3/1/2016 6/2/2016
1 4/1/2016 7/1/2016
1 8/2/2016 12/1/2016
1 1/2/2017 3/1/2017
2 5/10/2014 12/1/2014
2 7/10/2014 10/1/2014
2 1/1/2015 3/2/2015
;
run;
data temp;
 set have;
 by id;
 if start_date <= lag(end_date) then flag=1;
 if first.id then flag=.;
run;
data temp;
 set temp;
 if missing(flag) then group+1;
run;
proc sql;
create table want as
 select *
  from temp
   group by group
    having sum(flag) > 0;
quit;