topic Re: Need help to create 12-month running total by group in SAS Programming

Need help to create 12-month running total by group

Andrea_S — Mon, 09 Jul 2012 19:31:17 GMT

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

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

ballardw — Mon, 09 Jul 2012 20:12:34 GMT

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.

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

Astounding — Mon, 09 Jul 2012 21:07:03 GMT

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;

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.

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

Andrea_S — Mon, 09 Jul 2012 21:34:00 GMT

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

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

Haikuo — Mon, 09 Jul 2012 22:00:29 GMT

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