0

Good day everyone. I am trying to join two datasets by comparing the dates. Suppose I have two datasets as such:

---Table A---      ---Table B---
ID  Date1          ID  Date2
01  29/1/2010      01  28/1/2011
01  29/1/2011      01  28/1/2012
01  29/1/2012      01  28/1/2013
01  29/1/2013      01  28/1/2014
01  29/1/2014      01  28/1/2015
01  29/1/2015      01  28/1/2016

I am trying to join the table A and table B by comparing date 1 and date 2. If date2 < date1 then join. The intended result should like this (table 3):

---Table C---
ID  Date1      Date2
01  29/1/2010  .
01  29/1/2011  28/1/2011
01  29/1/2012  28/1/2012
01  29/1/2013  28/1/2013
01  29/1/2014  28/1/2014
01  29/1/2015  28/1/2015

But when I tried using this code

PROC SQL;
    CREATE TABLE TABLE_C AS
    SELECT TABLE_A.*, TABLE_B.DATE2
    FROM TABLE_A LEFT JOIN TABLE_B
    ON 
        TABLE_A.ID = TABLE_B.ID AND 
        TABLE_A.DATE1 < TABLE_B.DATE2;

I ended up with this result:

---Table C---
ID  Date1      Date2
01  29/1/2010  .
01  29/1/2011  28/1/2011
01  29/1/2012  28/1/2011 *
01  29/1/2013  28/1/2011 *
01  29/1/2014  28/1/2011 *
01  29/1/2015  28/1/2011 *
01  29/1/2012  28/1/2012
01  29/1/2013  28/1/2012 *
01  29/1/2014  28/1/2012 *
01  29/1/2015  28/1/2012 *
01  29/1/2013  28/1/2013
01  29/1/2014  28/1/2013 *
01  29/1/2015  28/1/2013 *
01  29/1/2014  28/1/2014 
01  29/1/2015  28/1/2014 *
01  29/1/2015  28/1/2015

How can I remove the ones that are labelled (*)? Can anybody help? Thanks!

saspower
  • 53
  • 1
  • 1
  • 4

1 Answers1

0

Based on your expected result it looks like you would only need to replace 

TABLE_A.DATE1 < TABLE_B.DATE2;

with

TABLE_A.DATE1 > TABLE_B.DATE2 and /* date2 < date1 */
YEAR(TABLE_A.DATE1) = YEAR(TABLE_B.DATE2);
sushil
  • 1,576
  • 10
  • 14