Solved
Contributor
Posts: 41

# Proc Expand and performing a rolling sum based on multiple dimensions

Hi,

I'm trying to use proc expand to do a rolling sum based on multiple columns. I'm able to do a rolling sum if I'm just using my year_month date column, but when I add another dimension, I can't have it perform the sum taking into account the 2nd dimension. I have the first three columns below.  I'm not sure how to get the 4th column.  I can do it if I I'm not including the 'State' field, but I need it broken out by month and state.

Tom

 Year_Month State Value Rolling3 201401 CA 11 201402 CA 12 201403 CA 11 34 201404 CA 12 35 201405 CA 14 37 201401 TX 12 201402 TX 14 201403 TX 10 36 201404 TX 11 35 201405 TX 16 37

Accepted Solutions
Solution
‎12-30-2014 12:11 PM
Posts: 5,529

## Re: Proc Expand and performing a rolling sum based on multiple dimensions

Not sure I understand what the problem is. Anyway, this gets the expected result:

data have;

input Year_Month State \$ Value;

datalines;

201401 CA 11

201402 CA 12

201403 CA 11 34

201404 CA 12 35

201405 CA 14 37

201401 TX 12

201402 TX 14

201403 TX 10 36

201404 TX 11 35

201405 TX 16 37

;

proc expand data=have out=want;

by state;

id  year_month;

convert Value=Rolling3 / transformout=(movsum 3 trimleft 2);

run;

PG

PG

All Replies
Solution
‎12-30-2014 12:11 PM
Posts: 5,529

## Re: Proc Expand and performing a rolling sum based on multiple dimensions

Not sure I understand what the problem is. Anyway, this gets the expected result:

data have;

input Year_Month State \$ Value;

datalines;

201401 CA 11

201402 CA 12

201403 CA 11 34

201404 CA 12 35

201405 CA 14 37

201401 TX 12

201402 TX 14

201403 TX 10 36

201404 TX 11 35

201405 TX 16 37

;

proc expand data=have out=want;

by state;

id  year_month;

convert Value=Rolling3 / transformout=(movsum 3 trimleft 2);

run;

PG

PG
Contributor
Posts: 41

## Re: Proc Expand and performing a rolling sum based on multiple dimensions

Thank you PG!!!!!!!!!!!!!  That was the logic I needed to apply.  You just saved me a lot of time

🔒 This topic is solved and locked.