topic Re: How to find a new value of a variable compared with all prior variable observations by ID, YEAR in SAS Programming

How to find a new value of a variable compared with all prior variable observations by ID, YEAR

yanshuai — Wed, 09 Jun 2021 05:28:11 GMT

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.

Re: How to find a new value of a variable compared with all prior variable observations by ID, YEAR

ballardw — Wed, 09 Jun 2021 05:56:43 GMT

What role does the Firm variable play in this process? You do not mention it at all.

Re: How to find a new value of a variable compared with all prior variable observations by ID, YEAR

andreas_lds — Wed, 09 Jun 2021 06:27:22 GMT

If you don't insist on the order in "want", try

proc sort data=have out=want nodupkey;
	by firm var;
run;

Re: How to find a new value of a variable compared with all prior variable observations by ID, YEAR

Josie1 — Wed, 09 Jun 2021 06:35:37 GMT

data want2; set want; by firm year var; if first.var; run;

how about that?

Re: How to find a new value of a variable compared with all prior variable observations by ID, YEAR

FreelanceReinh — Wed, 09 Jun 2021 10:30:10 GMT

Hello @yanshuai,

With PROC SQL you can do something like this:

proc sql;
create table want as
select firm, min(year) as year, var
from have
group by 1,3
order by 1,2;
quit;

Note, however, that the ORDER BY clause does not necessarily maintain the sort order within a (FIRM, YEAR) BY group, for example, the order of "burger" and "steak" for FIRM="B" and YEAR="2001". To maintain this sort order you could introduce a temporary sequential number and use it as a sort key:

data _tmp / view=_tmp;
set have;
by firm year;
_seqno=_n_;
run;

proc sql;
create table want(drop=_s) as
select firm, min(year) as year, var, min(_seqno) as _s
from _tmp
group by 1,3
order by 1,4;
drop view _tmp;
quit;

Re: How to find a new value of a variable compared with all prior variable observations by ID, YEAR

yanshuai — Wed, 09 Jun 2021 14:27:17 GMT

Hi,

FIRM is the ID variable, YEAR is the time variable, VAR is the variable I look for new value

Re: How to find a new value of a variable compared with all prior variable observations by ID, YEAR

yanshuai — Wed, 09 Jun 2021 14:31:20 GMT

Then the first step: How do I get the dataset WANT?

Re: How to find a new value of a variable compared with all prior variable observations by ID, YEAR

yanshuai — Wed, 09 Jun 2021 14:34:47 GMT

Interesting.

Your code should be what I expect. Very beautiful. How could I not think of this? lol

Thank you very much