turn on suggestions

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
- /
- calculate inverse weighted cumulative average acro...

Topic Options

- Subscribe to 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
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

4 weeks ago

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

4 weeks ago

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

Posted in reply to draycut

4 weeks ago

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
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to psh23

4 weeks ago

Do you have a SAS/ETS license?

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

Posted in reply to draycut

4 weeks ago

Yes

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

Posted in reply to psh23

4 weeks ago

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

Solution

4 weeks ago

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

Posted in reply to draycut

4 weeks ago

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
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to draycut

4 weeks ago

This worked, thank you!

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

Posted in reply to draycut

4 weeks ago

@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
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to draycut

4 weeks ago

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
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to draycut

3 weeks ago

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
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to PGStats

2 weeks ago

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
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to psh23

2 weeks ago

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

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

Posted in reply to PGStats

2 weeks ago

Yes thank you!