topic Sum values in lagged years in New SAS User

Sum values in lagged years

dapenDaniel — Tue, 11 Feb 2020 05:42:36 GMT

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;

Re: Sum values in lagged years

Kurt_Bremser — Tue, 11 Feb 2020 06:20:47 GMT

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;

Re: Sum values in lagged years

dapenDaniel — Tue, 11 Feb 2020 06:25:46 GMT

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