Proc Expand and performing a rolling sum based on multiple dimensions

[teg_76]

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.

Thank you in advance!

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

[PGStats · Accepted solution]

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

[teg_76]

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