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
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.
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
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.
Ready to level-up your skills? Choose your own adventure.