Help using Base SAS procedures

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

Accepted Solution Solved
Reply
Regular Contributor
Posts: 188
Accepted Solution

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: 9,676

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

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

View solution in original post


All Replies
Super Contributor
Posts: 490

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

could you please clarify what you want to do more?

with example of data even with three observation

Super User
Posts: 9,676

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

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: 188

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: 188

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

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: 9,676

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

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: 188

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: 9,676

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

" 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: 188

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.

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

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