Compare/match timeranges

Posts: 73

Compare/match timeranges


I wonder is someone has a solution to this.

I have a dataset with id and dates (start and stop) like this:

id     startdate1          stopdate1        startdate2         stopdate2        

1     7FEB2013          28FEB2013     1JAN2013         31DEC2013

2     15MAR2013      1APR2013        1JAN2013         30MAR2013

3     10JUN2013       7JUL2013          1JAN2013        30NOV2013

I now want to create a "match variable" if startdat1 and stopdate1 is "inside" startdate2 and stopdate2.

The final table should be like this:

id     startdate1          stopdate1        startdate2         stopdate2          match

1     7FEB2013          28FEB2013     1JAN2013         31DEC2013       1

2     15MAR2013      1APR2013        1JAN2013         17MAR2013       0 (startdate1 and stopdate1 is outside startdate2 and stopdate2)

3     10JUN2013       7JUL2013          1JAN2013        30NOV2013        1

Thanks in advance!


Super User
Posts: 23,714

Re: Compare/match timeranges

Posted in reply to bollibompa


data want;

set have;

if  stopdate2>=startdate1>=startdate2

and stopdate2>=stopdate1>=startdate2 then match=1;

else match=0;


Posts: 73

Re: Compare/match timeranges

Thanks for answer. It works fine!

However, I now realize that I have startdate2 and stopdate2 in another table and the same id can also have more than one startdate2 and stopdate2

Like this:

data one

id     startdate1          stopdate1     

1     7FEB2013          28FEB2013 

2     15MAR2013       1APR2013       

data two

id    startdate2       stopdate2

1    1JAN2012       30NOV2012

1    1JAN2013       30MAR2013

2    1MAR2011      20APR2011

2    30MAR2012    31DEC2012

2     1JAN2013      15FEB2013

data want:

id   startdate1       stopdate1       match

1    7FEB2013      28FEB2013    1          /*This id should have a match=1 because the timeperiod of 7FEB2013 and 28FEB 2013 is included data two (row 2) */

2   15MAR2013    1APR2013      0   /*This timeperiod is not included in data two for id2*/

I have tried different sql-queries with having-clauses but with no luck

Thank for help!


Ask a Question
Discussion stats
  • 2 replies
  • 2 in conversation