topic Re: Rolling sum without the last observations in New SAS User

Rolling sum without the last observations

dapenDaniel — Mon, 30 Mar 2020 03:39:24 GMT

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.

Re: Rolling sum without the last observations

PeterClemmensen — Mon, 30 Mar 2020 07:02:42 GMT

Here is a fix for your currrent code:

proc expand data=have out=want;
   by firmid;
   id Year;
   Convert value=cul_value / transformout=(-1 movsum 3 trimleft 3);
run;

Result:

firmID Year  cul_value  Value 
A      1995  .          1 
A      1996  .          2 
A      1997  .          3 
A      1998  6          4 
A      1999  9          5 
A      2000  12         6 
A      2001  15         7 
A      2002  18         8 
A      2003  21         9 
B      1995  .          10 
B      1996  .          11 
B      1997  .          12 
B      1998  33         13 
B      1999  36         14 
B      2000  39         15 
B      2001  42         16 
B      2002  45         17 
B      2003  48         18

Re: Rolling sum without the last observations

dapenDaniel — Mon, 30 Mar 2020 15:13:21 GMT

Thank you, @PeterClemmensen

Re: Rolling sum without the last observations

dapenDaniel — Mon, 30 Mar 2020 17:23:26 GMT

Hi @PeterClemmensen , I revised the code based on your advice but the output is not what I expected. Sorry, the data above is just an example and the difference between values in the real data is not always 1. There are also some zeros in the real dataset, which lead the output to be negative.