New Contributor
Posts: 2

# Need help to create 12-month running total by group

Hello,

Would anyone have any suggestions as to how I could create a 12-month running total by group?

Below is an example of what I am trying to accomplish. Within the example there are two groups, A and B. Each row of data represents one month along with total cost per month. The variable I would like to calculate is the "12_Mos_Cost", which is the 12 month running total within the group. As you can see, the first 11 "12_Mos_Cost" is blank because there hasn't been 12 months of data yet. I have figured out how to create a 12 month running total, but have yet to figure out how to do this by group.

Any suggestions would be most appreciated!!!!

Thanks!

 Group Year Month Cost 12_Mos_Cost A 2009 1 \$             5.00 A 2009 2 \$             7.00 A 2009 3 \$             4.00 A 2009 4 \$           12.00 A 2009 5 \$             9.00 A 2009 6 \$           15.00 A 2009 7 \$             8.00 A 2009 8 \$             4.00 A 2009 9 \$           13.00 A 2009 10 \$             6.00 A 2009 11 \$             7.00 A 2009 12 \$           10.00 \$            100.00 A 2010 1 \$           14.00 \$            109.00 A 2010 2 \$             8.00 \$            110.00 A 2010 3 \$           17.00 \$            123.00 A 2010 4 \$           10.00 \$            121.00 B 2009 1 \$           15.00 B 2009 2 \$           19.00 B 2009 3 \$           16.00 B 2009 4 \$           20.00 B 2009 5 \$           14.00 B 2009 6 \$           17.00 B 2009 7 \$           11.00 B 2009 8 \$            9.00 B 2009 9 \$           18.00 B 2009 10 \$           15.00 B 2009 11 \$           13.00 B 2009 12 \$             5.00 \$            172.00 B 2010 1 \$           16.00 \$            173.00 B 2010 2 \$           14.00 \$            168.00
Super User
Posts: 13,498

## Re: Need help to create 12-month running total by group

If your data is sorted by the Group variable then use:

By Group;

if first.group then (reset your 12 month aggregate to missing/ restart what ever methog you're using to calculate the aggregate);

and your code for aggregating goes after.

Super User
Posts: 6,751

## Re: Need help to create 12-month running total by group

Here's one way.

proc sort data=have;

by group year month;

run;

data want;

set have;

by group;

array last12 {0:11} prior1-prior12;

retain prior1-prior12;

if first.group then do;

nnn=1;

do _n_=0 to 11;

last12{_n_}=.;

end;

end;

else nnn + 1;

last12{mod(nnn,12)} = cost;

if n(of prior1-prior12) = 12 then _12_Mos_Cost = sum(of prior1-prior12);

drop prior1-prior12 nnn;

run;

If I remember right, an earlier post attributed some elements of this approach to Howard Schrier.

Some tricky steps are involved, so feel free to ask if you need to understand more.

Good luck.

New Contributor
Posts: 2

## Re: Need help to create 12-month running total by group

Posted in reply to Astounding

Thank you everyone for your suggestions!

I tried out the SAS code (above with the array - thanks Astounding) and it works perfectly!!  I very much appreciate the help.

Thank You!!!!

Respected Advisor
Posts: 3,167

## Re: Need help to create 12-month running total by group

Astounding's array() direction is the way to go if you don't have SAS/ETS (proc expand). The array() solution can be further simplified by using temporary array:

data want;

array tw(0:11) _temporary_;

set have;

by group;

if first.group then call missing(i);

i+1;

tw(mod(i,12))=cost;

if i>=12 then cost_12m=sum(of tw(*));

drop i;

run;

Regards,

Haikuo

Discussion stats
• 4 replies
• 1480 views
• 1 like
• 4 in conversation