Help using Base SAS procedures

Proc Expand and performing a rolling sum based on multiple dimensions

Accepted Solution Solved
Reply
Contributor
Posts: 41
Accepted Solution

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.

Thank you in advance!

Tom

Year_MonthStateValueRolling3
201401CA11
201402CA12
201403CA1134
201404CA1235
201405CA1437
201401TX12
201402TX14
201403TX1036
201404TX1135
201405TX1637

Accepted Solutions
Solution
‎12-30-2014 12:11 PM
Respected Advisor
Posts: 4,934

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

View solution in original post


All Replies
Solution
‎12-30-2014 12:11 PM
Respected Advisor
Posts: 4,934

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 Smiley Happy

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 393 views
  • 0 likes
  • 2 in conversation