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

Solved
Regular Contributor
Posts: 196

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

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.

Accepted Solutions
Solution
‎09-27-2014 09:05 AM
Super User
Posts: 10,787

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

Posted in reply to munitech4u

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

All Replies
Super Contributor
Posts: 490

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

Posted in reply to munitech4u

could you please clarify what you want to do more?

with example of data even with three observation

Super User
Posts: 10,787

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

Posted in reply to munitech4u

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

Regular Contributor
Posts: 196

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

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

Regular Contributor
Posts: 196

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

Posted in reply to 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?

Solution
‎09-27-2014 09:05 AM
Super User
Posts: 10,787

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

Posted in reply to munitech4u

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

Regular Contributor
Posts: 196

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

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?

Super User
Posts: 10,787

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

Posted in reply to munitech4u

" 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

Regular Contributor
Posts: 196

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

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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
• 8 replies
• 432 views
• 0 likes
• 3 in conversation