BookmarkSubscribeRSS Feed
Andrea_S
Calcite | Level 5

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
4 REPLIES 4
ballardw
Super User

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.

Astounding
PROC Star

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.

Andrea_S
Calcite | Level 5

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

Haikuo
Onyx | Level 15

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

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 4 replies
  • 3368 views
  • 1 like
  • 4 in conversation