Hi, I have a question about the rolling sum. Suppose I have the data below. data have;
input firmID $ Year Value;
datalines;
A 1995 1
A 1996 2
A 1997 3
A 1998 4
A 1999 5
A 2000 6
A 2001 7
A 2002 8
A 2003 9
B 1995 10
B 1996 11
B 1997 12
B 1998 13
B 1999 14
B 2000 15
B 2001 16
B 2002 17
B 2003 18
;
run; The code below provided by @PGStats works well. proc expand data=have out=want;
by firmid;
id Year;
Convert value=cul_value / transformout=(movsum 3 trimleft 2);
run; The output is below. firmID Year Value Cul_value A 1995 1 . A 1996 2 . A 1997 3 6 A 1998 4 9 A 1999 5 12 A 2000 6 15 A 2001 7 18 A 2002 8 21 A 2003 9 24 B 1995 10 . B 1996 11 . B 1997 12 33 B 1998 13 36 B 1999 14 39 B 2000 15 42 B 2001 16 45 B 2002 17 48 B 2003 18 51 However, I want that the sum exclude the last value. For example, Cul_value is 6 (1+2+3) for firm A in 1998 rather than 9. The expected output is below. firmID Year Value cul_want A 1995 1 . A 1996 2 . A 1997 3 . A 1998 4 6 A 1999 5 9 A 2000 6 12 A 2001 7 15 A 2002 8 18 A 2003 9 21 B 1995 10 . B 1996 11 . B 1997 12 . B 1998 13 33 B 1999 14 36 B 2000 15 39 B 2001 16 42 B 2002 17 45 B 2003 18 48 How can I revise the program? Alternatively, what new program do I need to use? Thanks.
... View more
