Hello, I have a large panel data set, I would like to find a new value in VAR compared with all prior observations within-group BY FIRM, YEAR DATA HAVE;
INPUT (FIRM 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
A 2005 pig
A 2005 mice
A 2006 raccoon
A 2006 pig
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
B 2005 rice
B 2005 meatloaf
B 2006 pickle
B 2006 chili
;
run; This is what I want: DATA WANT;
INPUT (FIRM YEAR VAR) (:$8.);
CARDS;
A 2000 dog
A 2001 cat
A 2002 bird
A 2003 chicken
A 2004 mice
A 2005 pig
A 2006 raccoon
B 2000 fries
B 2001 burger
B 2001 steak
B 2003 pasta
B 2004 cheese
B 2005 rice
B 2005 meatloaf
B 2006 pickle
B 2006 chili
;
run; So, for each FIRM in a given YEAR, I want to find the new value in VAR. How to define it as 'new'? Compare VAR in a given YEAR with all observations of VAR in all prior YEAR For example, to find the new VAR for A in 2004, I will compare VAR in 2004 with VAR in 2003, 2002, 2001, and 2000. to find the new values of VAR for A in 2006, I will compare VAR in 2006 with VAR in 2005, 2004, 2003, 2002, 2001, and 2000. Same logic for B. I wonder can I do this with SQL and without a loop macro? Thank you very much. Appreciate your help.
... View more