topic Re: How to create rolling 1 year average value for a particular combination? in SAS Procedures

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

munitech4u — Fri, 26 Sep 2014 16:48:35 GMT

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.

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

mohamed_zaki — Fri, 26 Sep 2014 18:34:09 GMT

could you please clarify what you want to do more?

with example of data even with three observation

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

Ksharp — Sat, 27 Sep 2014 10:44:36 GMT

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

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

munitech4u — Sat, 27 Sep 2014 11:23:57 GMT

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

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

munitech4u — Sat, 27 Sep 2014 11:45:03 GMT

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?

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

Ksharp — Sat, 27 Sep 2014 13:05:28 GMT

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

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

munitech4u — Sat, 27 Sep 2014 15:44:13 GMT

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?

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

Ksharp — Sun, 28 Sep 2014 11:56:17 GMT

" 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

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

munitech4u — Sun, 28 Sep 2014 18:49:49 GMT

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