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!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.