DATA Step, Macro, Functions and more

Need help to create 12-month running total by group

Reply
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!

GroupYearMonth Cost 12_Mos_Cost
A20091 $             5.00
A20092 $             7.00
A20093 $             4.00
A20094 $           12.00
A20095 $             9.00
A20096 $           15.00
A20097 $             8.00
A20098 $             4.00
A20099 $           13.00
A200910 $             6.00
A200911 $             7.00
A200912 $           10.00 $            100.00
A20101 $           14.00 $            109.00
A20102 $             8.00 $            110.00
A20103 $           17.00 $            123.00
A20104 $           10.00 $            121.00
B20091 $           15.00
B20092 $           19.00
B20093 $           16.00
B20094 $           20.00
B20095 $           14.00
B20096 $           17.00
B20097 $           11.00
B20098 $            9.00
B20099 $           18.00
B200910 $           15.00
B200911 $           13.00
B200912 $             5.00 $            172.00
B20101 $           16.00 $            173.00
B20102 $           14.00 $            168.00
Super User
Posts: 10,550

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: 5,099

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

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!!!!  Smiley Happy

Respected Advisor
Posts: 3,124

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

Ask a Question
Discussion stats
  • 4 replies
  • 1195 views
  • 1 like
  • 4 in conversation