BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
munitech4u
Quartz | Level 8

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

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

8 REPLIES 8
mohamed_zaki
Barite | Level 11

could you please clarify what you want to do more?

with example of data even with three observation

Ksharp
Super User

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
Quartz | Level 8

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

munitech4u
Quartz | Level 8

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
Super User

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
Quartz | Level 8

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
Super User

" 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
Quartz | Level 8

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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