Join Tables Based On Lagged Date

I need to perform a LEFT JOIN to bring in a variable from table B to A by joining on the date with the new variable in Table B having a lagged date of the date in Table A: Table A:

Solution 1:

You are using SAS proc SQL, presumably. In this, you can do regular arithmetic on dates, so - 1 means subtract one day. You can try this:

Select A.Date, A.Var1, B.Var2
From A left outer join
     B
     On A.Date = B.Date + 1

If there is no match, then the left outer join will keep the row and insert NULL for the third column.

Solution 2:

Select A.Date,A.Var1,B.Var2 From A
left join B On A.Date = B.DateWhere A.Date = ?

Given Var2 of null if there's record in table a is okay, if not you want an inner join instead of a left one.

Just noticed lag so instead of A.Date = B.date, you'd use whatever that means, need to know DBMS for exact syntax.