topic Using Proc Expand to calculate a YTD Moving Sum in SAS Procedures

Using Proc Expand to calculate a YTD Moving Sum

teg_76 — Mon, 05 Jan 2015 22:58:14 GMT

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;

Re: Using Proc Expand to calculate a YTD Moving Sum

Reeza — Mon, 05 Jan 2015 23:18:14 GMT

Do you have to use proc expand?

Re: Using Proc Expand to calculate a YTD Moving Sum

stat_sas — Tue, 06 Jan 2015 00:38:36 GMT

proc expand data=have out=want;

by state;

id year_month;

convert Value=Rolling_sum/transformout=(sum);

run;

Re: Using Proc Expand to calculate a YTD Moving Sum

Reeza — Tue, 06 Jan 2015 01:46:16 GMT

What if you have multiple years?

Re: Using Proc Expand to calculate a YTD Moving Sum

teg_76 — Tue, 06 Jan 2015 02:16:35 GMT

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!

Re: Using Proc Expand to calculate a YTD Moving Sum

PGStats — Tue, 06 Jan 2015 02:27:53 GMT

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;

Re: Using Proc Expand to calculate a YTD Moving Sum

teg_76 — Thu, 08 Jan 2015 07:47:23 GMT

Thank you PGStats! You did it again!