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;
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
Do you have to use proc expand?
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!
proc expand data=have out=want;
by state;
id year_month;
convert Value=Rolling_sum/transformout=(sum);
run;
What if you have multiple years?
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
Thank you PGStats! You did it again!
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.