Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- Data Management
- /
- Forum
- /
- Re: calculate inverse weighted cumulative average ...

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-23-2018 12:01 AM

I would like to calculate weighted averages across 37 weeks, where each average includes all previous weeks, inversely weighted by their distance from the current week.

My data is in wide format where each observation is one person, with variables wk1 - wk37 as weekly values.

This is what I would like to accomplish:

data test;

avg1 = .;

avg2 = wk1;

avg3 = (( wk2*(2/3) + wk1*(1/3)) / 2);

avg4 = (( wk3*(3/4) + wk2*(2/4) + wk1*(1/4)) / 3) ;

avg5 = (( wk4*(4/5) + wk3*(3/5) + wk2*(2/5) + wk1*(1/5)) / 4);

avg6 = (( wk5*(5/6) + wk4*(4/6) + wk3*(3/6) + wk2*(2/6) + wk1*(1/6)) / 5);

avg7 = (( wk6*(6/7) + wk5*(5/7) + wk4*(4/7) + wk3*(3/7) + wk2*(2/7)) + wk1(1/7)) / 6);

avg8 = (( wk7*(7/8) + wk6*(6/8) + wk5*(5/8) + wk4*(4/8) + wk3*(3/8) + wk2*(2/8) + wk1*(1/8) / 7;

etc. ....

Thank you!

Accepted Solutions

Solution

02-25-2018
02:16 PM

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to draycut

02-24-2018 09:41 AM

If you do..

I think the easiest way to solve this problem is to think of it in terms of vectors and matrices and use the IML language.

A way to go about is is like this, which is easily extendable to 37 weeks..

Feel free to ask again if you have questions

```
data have;
input personID wk1-wk5;
datalines;
1 23 61 31 86 32
2 24 69 34 83 31
3 21 65 33 85 35
4 28 63 33 81 34
;
proc iml;
use have;
read all var ("wk1":"wk5") into W;
read all var {personID} into personID;
close have;
Avg=j(nrow(W),ncol(W),.);
Avg[ ,2] = W[ ,1];
do i=1 to nrow(W);
do j=3 to 5;
v=do(1/j, (j-1)/j, 1/j);
Avg[i,j]= sum(v#W[i,1:j-1])/(j-1);
end;
end;
Avg=personID||Avg;
varNames= "personID"||("avg1":"avg5");
create want from Avg[c=varNames];
append from Avg;
close want;
quit;
proc print data=want;
run;
```

All Replies

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to psh23

02-23-2018 08:04 AM

Do you have a SAS/ETS license?

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to draycut

02-23-2018 12:42 PM

Yes

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to psh23

02-24-2018 04:47 AM

Sorry. I meant SAS/IML. Do you have a SAS/IML license?

Solution

02-25-2018
02:16 PM

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to draycut

02-24-2018 09:41 AM

If you do..

I think the easiest way to solve this problem is to think of it in terms of vectors and matrices and use the IML language.

A way to go about is is like this, which is easily extendable to 37 weeks..

Feel free to ask again if you have questions

```
data have;
input personID wk1-wk5;
datalines;
1 23 61 31 86 32
2 24 69 34 83 31
3 21 65 33 85 35
4 28 63 33 81 34
;
proc iml;
use have;
read all var ("wk1":"wk5") into W;
read all var {personID} into personID;
close have;
Avg=j(nrow(W),ncol(W),.);
Avg[ ,2] = W[ ,1];
do i=1 to nrow(W);
do j=3 to 5;
v=do(1/j, (j-1)/j, 1/j);
Avg[i,j]= sum(v#W[i,1:j-1])/(j-1);
end;
end;
Avg=personID||Avg;
varNames= "personID"||("avg1":"avg5");
create want from Avg[c=varNames];
append from Avg;
close want;
quit;
proc print data=want;
run;
```

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to draycut

02-25-2018 02:16 PM

This worked, thank you!

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to draycut

02-25-2018 04:28 PM

@Reeza, @PGStats, @Astounding can I ask for a minute of your time?

Am I overthinking this problem? An IML approach is the only solution I could think of and it worked, but I feel like there is a simpler way?

Thank you in advance.

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to draycut

02-25-2018 05:34 PM

I would expect you can use arrays to do this. I'm just being lazy on a Sunday ... here's the structure but without a key formula that needs to be filled in.

data want;

set have;

array wk {37};

array avg {37};

do j=2 to 37;

avg_numer = 0;

do k=1 to j-1;

* increment avg_numer;

end;

avg{j} = avg_numer / (j-1);

end;

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to draycut

02-26-2018 01:15 AM

Arrays?

```
data want;
array wk{37};
array avg{37};
set have;
do i = 1 to dim(wk);
do j = 1 to i-1;
avg{i} = sum(avg{i}, wk{j} * j / i);
end;
if i > 1 then avg{i} = avg{i} / (i-1);
end;
drop i j;
run;
```

PG

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to PGStats

03-06-2018 11:49 AM

This works for my previous method - however now I want to modify my formula so that:

avg(wk4) = wk3*3 + wk2*2 + wk1*1 / 3!

I attempted to modify the code but it is giving me this:

wk1 | 15.3763 | avg1 | . |

wk2 | 12.1931 | avg2 | 15.3763 |

wk3 | 10.2969 | avg3 | 19.8813 |

wk4 | 9.77625 | avg4 | 11.7755 |

wk5 | 8.708 | avg5 | 4.57326 |

wk6 | 15.4319 | avg6 | 1.27748 |

wk7 | 38.5981 | avg7 | 0.34151 |

wk8 | 10.0525 | avg8 | 0.1024 |

wk9 | 8.78688 | avg9 | 0.01479 |

wk10 | 10.6669 | avg10 | 0.00186 |

wk11 | 10.2844 | avg11 | 0.00022 |

wk12 | 15.4988 | avg12 | 2.2E-05 |

wk13 | 10.8888 | avg13 | 2.3E-06 |

wk14 | 7.9475 | avg14 | 2E-07 |

wk15 | 9.81188 | avg15 | 1.53E-08 |

wk16 | 11.3281 | avg16 | 1.13E-09 |

wk17 | 16.7944 | avg17 | 7.95E-11 |

wk18 | 21.3356 | avg18 | 5.48E-12 |

wk19 | 11.0613 | avg19 | 3.64E-13 |

wk20 | 13.3138 | avg20 | 2.09E-14 |

wk21 | 12.1488 | avg21 | 1.15E-15 |

wk22 | 11.0613 | avg22 | 6.00E-17 |

wk23 | 24.0344 | avg23 | 2.94E-18 |

wk24 | 29.5988 | avg24 | 1.49E-19 |

wk25 | 21.4113 | avg25 | 7.37E-21 |

wk26 | 14.6531 | avg26 | 3.29E-22 |

wk27 | 14.7 | avg27 | 1.36E-23 |

wk28 | 18.7425 | avg28 | 5.40E-25 |

wk29 | 15.8988 | avg29 | 2.10E-26 |

wk30 | 13.8875 | avg30 | 7.77E-28 |

wk31 | 19.5225 | avg31 | 2.75E-29 |

wk32 | 17.2169 | avg32 | 9.60E-31 |

wk33 | 12.4269 | avg33 | 3.21E-32 |

wk34 | 16.275 | avg34 | 1.02E-33 |

wk35 | 22.9125 | avg35 | 3.19E-35 |

wk36 | 20.1725 | avg36 | 9.88E-37 |

wk37 | 12.145 | avg37 | 2.94E-38 |

data want;

array wk{37};

array avg{37};

set wide;

do i = 1 to dim(wk);

do j = 1 to i-1;

avg{i} = sum(avg{i}, wk{j} * j);

end;

if i > 1 then avg{i} = avg{i} / fact(i-1);

end;

drop i j;

run;

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to psh23

03-06-2018 02:50 PM

Looks about right to me. *Fact(n)* increases very quickly with *n*. I would guess that what you really want is to divise the weighted sum by the sum of the weights. Something like :

```
data want;
array wk{37};
array avg{37};
set have;
do i = 1 to dim(wk);
w = 0;
do j = 1 to i-1;
avg{i} = sum(avg{i}, wk{j} * j);
w = w + j;
end;
if i > 1 then avg{i} = avg{i} / w;
end;
drop i j w;
run;
```

PG

Highlighted
## Re: calculate inverse weighted cumulative average across preceding weeks

Options

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to PGStats

03-06-2018 03:10 PM

Yes thank you!