BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
teg_76
Calcite | Level 5

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;






1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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

View solution in original post

6 REPLIES 6
Reeza
Super User

Do you have to use proc expand?

teg_76
Calcite | Level 5

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!

stat_sas
Ammonite | Level 13

proc expand data=have out=want;

by state;

id  year_month;

convert Value=Rolling_sum/transformout=(sum);

run;

Reeza
Super User

What if you have multiple years?

PGStats
Opal | Level 21

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
teg_76
Calcite | Level 5

Thank you PGStats!  You did it again!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 3313 views
  • 1 like
  • 4 in conversation