How to create rolling 1 year average value for a particular combination?

[munitech4u]

I have 4 columns as:

Week Key Product Value

I want to roll up 52 weeks and replace the value for the last week(for combination of key and its product and its corresponding value) with average of 52 weeks for same combination, and keep doing so till the last week by 52 weeks average.,

Example:

Week Key Product Value

1        541  A           1.6

1        542  B            6.4

2        541  A            8.4

...............................

98       541  A         3.4

98       542  B         4.5

So i want to roll up week 1-52 into week 1 and make 541-A combination values as average of that period and so for 542-B combination and move on for week 2-53, 3-54 and keep doing that for both the combinations.

[mohamed_zaki]

could you please clarify what you want to do more?

with example of data even with three observation

[Ksharp]

Not tested code :

proc sql;
create table want as
 select a.*,( select avg(value) from have where key=a.key and product=a.product and week lt week+52 )  as new_value
  from have as a  ;
quit;

Xia Keshan

[munitech4u]

Its giving only one average value for all the weeks combined.

[munitech4u]

I got some replies at stackover flow: sql - How to create rolling average data? - Stack Overflow

But they are using some sql functions which are not compatible with proc sql. Maybe somebody can tweak their idea?

[Ksharp · Accepted solution]

Is it what you are looking for ?

data x;
key=541;product='A';output;
key=542;product='B';output;
run;
data have;
 set x;
 do week=1 to 98;
  value=ranuni(0)*10;
  output;
 end;
run;
proc sort data=have;by week key product;run;


proc sql;
create table want as
 select a.*,( select avg(value) from have where key=a.key and product=a.product and a.week le week le a.week+51 )  as new_value
  from have as a  ;
quit;

Xia Keshan

Message was edited by: xia keshan

[munitech4u]

Thanks a lot!! But only problem remains is that new value wont be for first week, but week 52(1-52),53(2-53) and so on. I know I can simply add 52 to week number, but can i do that during sql step itself?

[Ksharp]

" that new value wont be for first week, but week 52(1-52),53(2-53) and so on."

What does that mean ? Don't you want rolling a window which has a 52 week ?

Xia Keshan

[munitech4u]

never mind, first part was good enough to solve the problem