topic Re: Compare/match timeranges in SAS Programming

Compare/match timeranges

bollibompa — Wed, 29 Jan 2014 20:11:02 GMT

Hi,

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!

Thomas

Re: Compare/match timeranges

Reeza — Wed, 29 Jan 2014 20:21:29 GMT

Untested:

data want;

set have;

if stopdate2>=startdate1>=startdate2

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

else match=0;

run;

Re: Compare/match timeranges

bollibompa — Thu, 30 Jan 2014 14:18:38 GMT

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!

Thomas