BookmarkSubscribeRSS Feed
bollibompa
Quartz | Level 8

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

2 REPLIES 2
Reeza
Super User

Untested:

data want;

set have;

if  stopdate2>=startdate1>=startdate2

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

else match=0;

run;

bollibompa
Quartz | Level 8

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

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 478 views
  • 0 likes
  • 2 in conversation