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
Untested:
data want;
set have;
if stopdate2>=startdate1>=startdate2
and stopdate2>=stopdate1>=startdate2 then match=1;
else match=0;
run;
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
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.