topic Re: Join tables, Date in between check in SAS Programming

Join tables, Date in between check

anweinbe — Thu, 16 Jul 2020 17:53:19 GMT

Good afternoon SAS friends. I could use a little help.

I have one table: Temp.a, that has an Employee ID number and a Hire Date. I have another file Temp.B that has an ID number and a Beginning Date and an End Date as well as some other information. Our company recycles ID's.

I would like to join the files together on Employee ID, but add a clause where the Hire Date must be between the beginning date and end date. That will ensure it matches to the right record in the second table. In the example below, I would want my table 1 to match to the 2nd record of table 2, not the first.

Table 1: 123456 10/23/2019

Table 2: 123456 01/01/2010 12/31/2018

Table 2: 123456 01/01/2019 12/31/2019

I can write the typical join statement... but I figure I need to add something to this...

where a.empno=b.empno;

THANK YOU!!!!

Re: Join tables, Date in between check

Reeza — Thu, 16 Jul 2020 18:01:31 GMT

where a.empno=b.empno and a.date between b.BeginningDate and b.EndDate

Something like that? it includes the endpoints of your intervals though, if you don't want to include the dates in the interval use greater than and less than calculations.

@anweinbe wrote:

Good afternoon SAS friends. I could use a little help.

I have one table: Temp.a, that has an Employee ID number and a Hire Date. I have another file Temp.B that has an ID number and a Beginning Date and an End Date as well as some other information. Our company recycles ID's.

I would like to join the files together on Employee ID, but add a clause where the Hire Date must be between the beginning date and end date. That will ensure it matches to the right record in the second table. In the example below, I would want my table 1 to match to the 2nd record of table 2, not the first.

Table 1: 123456 10/23/2019

Table 2: 123456 01/01/2010 12/31/2018

Table 2: 123456 01/01/2019 12/31/2019

I can write the typical join statement... but I figure I need to add something to this...

where a.empno=b.empno;

THANK YOU!!!!

Re: Join tables, Date in between check

anweinbe — Thu, 16 Jul 2020 18:06:19 GMT

Thank you! I knew it was easy.