Creating a Rolling 12 Month Total

[cbrotz]

Reeza - thank you for your patience - this is not how I usually code so I had to work through it a bit but I think this part is SOLVED.  I would tag you but I do not see a way to do that. 🙂  Have a good evening.

[Reeza]

---

@cbrotz wrote:

So I made a couple more changes below....zeroed out a field I am calling roll_tot at each break and then adding "sum_of_billings to that field to accumulate.  I changed "mean" to "sum".  I am not sure how that is going to affect the overall results but they look good from what I am seeing.

data ret_avg12;

set work.summary_ret_data;

by group_status fcst_prod_grp yyyymm;

array values(12) _temporary_;

if first.fcst_prod_grp then do;

call missing(of values(*));

count=0;

roll_tot=0;

end;

count+1;

roll_tot+sum_of_billings; *-> This line is wrong;

values(mod(count, 12)+1) = sum_of_billings;

if count>=12 then roll_tot=sum(of values(*));

format roll_tot dollar12.2;

run;

---

[Reeza]

---

@cbrotz wrote:

Perhaps once I get a working example I will understand it better.

Both examples posted are fully worked examples with BY groups/breaks exactly like yours with also exactly a 12 month lag. The only difference is the source data set and variables and replace MEAN with the word SUM. 

[cbrotz]

Thank you!

[cbrotz]

Oh sorry I did not know how to delete and change the solution...