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!!!!
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!!!!
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!!!!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.