How to understand the a.YEAR-1 = b.YEAR in the SQL when match a VAR with prior YEAR obs

[yanshuai]

Hi,

I would like to find the new value in a VAR in a given year compared with its prior year value. I luckily figured out the code, but I am very confused by the 'a.YEAR-1 = b.YEAR' in the code.

This is what I have

DATA HAVE;
    INPUT FIRM :$8. YEAR VAR :$8.;
    CARDS;
A 2000 dog
A 2001 dog
A 2001 cat
A 2002 dog
A 2002 bird
A 2003 dog
A 2003 chicken
A 2004 chicken
A 2004 mice
B 2000 fries
B 2001 burger
B 2001 steak
B 2002 burger
B 2002 fries
B 2003 steak
B 2003 pasta
B 2004 pasta
B 2004 cheese
;
RUN;

This is the code I figured out and it gives me the outcome I expect

proc sql;
create table want as select
distinct a.firm, a.year, a.var 
from have as a
LEFT JOIN
have as b on a.firm = b.firm and a.var=b.var and a.YEAR-1=b.YEAR
WHERE b.var is null;
QUIT;

However, I am curious why it is a.YEAR-1=b.YEAR not a.YEAR=b.YEAR-1

a should be the focal data and compare it with its prior 1 year observations (b), I thought it should be a.YEAR = b.YEAR-1

Do I understand it wrong?

Thank you

[ballardw]

The direction of the join makes a difference depending on exactly what you want.

a.year -1  = b.year   means : compare the value of the variable year in set A minus one with the value of year in set b.

The place you might want the "- 1" part could depend on whether you are using a Left or Right join and the desired result.

See the differences in this code and then check the result with yours:

proc sql;
create table want2 as select
distinct b.firm, b.year, b.var 
from have as a
Right JOIN
have as b on a.firm = b.firm and a.var=b.var and a.YEAR=b.YEAR-1
WHERE a.var is null;
QUIT;

Note where a. has been changed to b. or b. to a.  and the join is now a right join.