Solved
Contributor
Posts: 41

# Using Proc Expand to calculate a YTD Moving Sum

I have this code below which user PGStats provided me in an answer to a different question a few weeks ago.  It worked great for helping me learn how to calculate a moving sum.  Now I'm wondering, how would I adjust the code below to do a moving sum that, instead of a three month moving average, was a year to date average based on the year_month field below. For example if YR_Month=201402 for the given record, it would give me the moving sum of the Value field through the first two months of the year.  Basically my transformout=(movsum n) would have to have a dynamic "n" based on the month in the year_month field.  Anybody have an idea on how I can use a dynamic for my movesum?  Thank you!!!

data have;

input Year_Month State \$ Value;

datalines;

201401 CA 11

201402 CA 12

201403 CA 11

201404 CA 12

201405 CA 14

201401 TX 12

201402 TX 14

201403 TX 10

201404 TX 11

201405 TX 16

;

proc expand data=have out=want;

by state;

id  year_month;

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

run;

Accepted Solutions
Solution
‎01-05-2015 09:27 PM
Posts: 5,535

## Re: Using Proc Expand to calculate a YTD Moving Sum

If you want cumulative Value average, within each year, for each State, do:

data have;

input Year_Month State \$ Value;

year = floor(Year_Month/100);

datalines;

201301 CA 11

201302 CA 12

201303 CA 11

201304 CA 12

201305 CA 14

201401 CA 11

201402 CA 12

201403 CA 11

201404 CA 12

201405 CA 14

201401 TX 12

201402 TX 14

201403 TX 10

201404 TX 11

201405 TX 16

;

proc expand data=have out=want;

by state year;

id  year_month;

convert Value=YTD_Ave / transformout=(cuave);

run;

PG

PG

All Replies
Super User
Posts: 23,758

## Re: Using Proc Expand to calculate a YTD Moving Sum

Do you have to use proc expand?

Contributor
Posts: 41

## Re: Using Proc Expand to calculate a YTD Moving Sum

Hi Reeza,

Preferably I could use Proc Expand.  If there's no way to do it through Proc Expand it would be great to hear other suggestions you might have.

Thank you!

Posts: 1,270

## Re: Using Proc Expand to calculate a YTD Moving Sum

proc expand data=have out=want;

by state;

id  year_month;

convert Value=Rolling_sum/transformout=(sum);

run;

Super User
Posts: 23,758

## Re: Using Proc Expand to calculate a YTD Moving Sum

What if you have multiple years?

Solution
‎01-05-2015 09:27 PM
Posts: 5,535

## Re: Using Proc Expand to calculate a YTD Moving Sum

If you want cumulative Value average, within each year, for each State, do:

data have;

input Year_Month State \$ Value;

year = floor(Year_Month/100);

datalines;

201301 CA 11

201302 CA 12

201303 CA 11

201304 CA 12

201305 CA 14

201401 CA 11

201402 CA 12

201403 CA 11

201404 CA 12

201405 CA 14

201401 TX 12

201402 TX 14

201403 TX 10

201404 TX 11

201405 TX 16

;

proc expand data=have out=want;

by state year;

id  year_month;

convert Value=YTD_Ave / transformout=(cuave);

run;

PG

PG
Contributor
Posts: 41

## Re: Using Proc Expand to calculate a YTD Moving Sum

Thank you PGStats!  You did it again!

🔒 This topic is solved and locked.