Proc Expand and performing a rolling sum

[dapenDaniel]

I am going to use Proc Expand to calculate a rolling sum. I found that another user asked the same question and user PG Stats gave the solution (https://communities.sas.com/t5/SAS-Procedures/Proc-Expand-and-performing-a-rolling-sum-based-on-mult...). Currently there are some problems.

Variables: FirmID is a firm ID that represent different companies. Count is the value I would like to sum. I want to have a five-year rolling sum for each year and each company. The file "original" is the original data. My code is following below. 

proc expand data=original out=output;

by FirmID;

id Year;

Convert Count=Rolling5 / transformout=(movsum 5 trimleft 4);

run;

Problem 1: when I type the code, the color of the word "convert" is red but there is no error shown in the log file.

Problem 2: The second file is the output. I wonder why there are some numbers like those (highlighted) and how to make them to zero (The last column is the data format I expected).

[PGStats · Accepted solution]

Problem 1: Don't worry. It is a feature (bug) of the program editor. Your program is syntactically correct.

Problem 2: Unfortunately, proc expand transformations do not include FUZZ. You would have to add a datastep to fuzz your values to zero.

[dapenDaniel]

Thank you PG Stats!!! Very quick response!!!

[dapenDaniel]

Hi PG,

another two questions:

1. I found that the rolling sum includes the last year when I use that code. For example, rolling sum for Year 1994= 1990+1991+1992+1993+1994. 

what I want to is rolling sum for Year 1995 = 1990+1991+1992+1993+1994.

Is there any solution for this kind of rolling sum? 

2. When I use the following code to remove FUZZ, I found that other value also changed. For example, some value was 1 originally. It changed to be 0 after I applied the following code. Is it the right code?

data a2;
set a1;
if rolling5<1
then rolling5 = 0;
run;

Thanks in advance!

[PGStats]

For Question 2:

data a2;

set a1;

rolling5 = fuzz(rolling5);

run;

[dapenDaniel]

Thanks!