topic Re: Proc Expand and performing a rolling sum in Statistical Procedures

Proc Expand and performing a rolling sum

dapenDaniel — Wed, 04 Apr 2018 20:49:16 GMT

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-multiple/m-p/165303). 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).

Re: Proc Expand and performing a rolling sum

PGStats — Wed, 04 Apr 2018 21:23:07 GMT

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.

Re: Proc Expand and performing a rolling sum

dapenDaniel — Wed, 04 Apr 2018 21:34:30 GMT

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

Re: Proc Expand and performing a rolling sum

dapenDaniel — Tue, 10 Apr 2018 17:34:40 GMT

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!

Re: Proc Expand and performing a rolling sum

PGStats — Tue, 10 Apr 2018 18:16:43 GMT

For Question 2:

data a2;

set a1;

rolling5 = fuzz(rolling5);

run;

Re: Proc Expand and performing a rolling sum

dapenDaniel — Tue, 10 Apr 2018 18:48:14 GMT

Thanks!