BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
dapenDaniel
Obsidian | Level 7

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).

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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.

PG

View solution in original post

5 REPLIES 5
PGStats
Opal | Level 21

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.

PG
dapenDaniel
Obsidian | Level 7

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

dapenDaniel
Obsidian | Level 7

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
Opal | Level 21

For Question 2:

 

data a2;

set a1;

rolling5 = fuzz(rolling5);

run;

PG
dapenDaniel
Obsidian | Level 7

Thanks!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is ANOVA?

ANOVA, or Analysis Of Variance, is used to compare the averages or means of two or more populations to better understand how they differ. Watch this tutorial for more.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 5 replies
  • 2704 views
  • 0 likes
  • 2 in conversation