Sum values in lagged years

[dapenDaniel]

Hi,

I have a dataset like below.

FirmID      Year       Count

1001        1992          1

1001        1993          2

1001        1994          3

1001        1995          4

1001        1996          5

1001        1997          5

I would like to get the sum of the count in the previous 3 years. For example, Pre3 in 1995 is 6 (1+2+3). The result I want is below.

FirmID      Year       Count       Pre3

1001        1992          1             .

1001        1993          2             .

1001        1994          3             .

1001        1995          4            6

1001        1996          5            9

1001        1997          5           12

I tried to use proc expand, but the sum includes the count in the same year. If I only want to include the previous 3 years, what program do I need? Thanks.

proc expand data=have out=want;
by firmID;
id Year;
Convert Count=Pre3 / transformout=(movsum 3 trimleft 2);
run;

[Kurt_Bremser · Accepted solution]

Please give us your example data in a data step with datalines, so we don't need to waste time writing it.

A data step solution:

data have;
input FirmID $ Year Count;
datalines;
1001        1992          1
1001        1993          2
1001        1994          3
1001        1995          4
1001        1996          5
1001        1997          5
;

data want;
set have;
l_count1 = lag(count);
l_count2 = lag2(count);
l_count3 = lag3(count);
pre3 = l_count1 + l_count2 + l_count3;
drop l_:;
run;

[dapenDaniel]

Thank you, @Kurt_Bremser I will do that next time!