SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

calculate inverse weighted cumulative average across preceding weeks

Accepted Solution Solved
Reply
Highlighted
Contributor
Posts: 29
Accepted Solution

calculate inverse weighted cumulative average across preceding weeks

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
PROC Star
Posts: 1,283

Re: calculate inverse weighted cumulative average across preceding weeks

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 Smiley Happy

 

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;

View solution in original post


All Replies
PROC Star
Posts: 1,283

Re: calculate inverse weighted cumulative average across preceding weeks

Do you have a SAS/ETS license?

Contributor
Posts: 29

Re: calculate inverse weighted cumulative average across preceding weeks

Yes

PROC Star
Posts: 1,283

Re: calculate inverse weighted cumulative average across preceding weeks

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

Solution
‎02-25-2018 02:16 PM
PROC Star
Posts: 1,283

Re: calculate inverse weighted cumulative average across preceding weeks

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 Smiley Happy

 

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;
Contributor
Posts: 29

Re: calculate inverse weighted cumulative average across preceding weeks

This worked, thank you! 

PROC Star
Posts: 1,283

Re: calculate inverse weighted cumulative average across preceding weeks

@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.

Super User
Posts: 6,785

Re: calculate inverse weighted cumulative average across preceding weeks

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;

   

Esteemed Advisor
Posts: 5,539

Re: calculate inverse weighted cumulative average across preceding weeks

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
Contributor
Posts: 29

Re: calculate inverse weighted cumulative average across preceding weeks

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:

 

wk115.3763avg1.
wk212.1931avg215.3763
wk310.2969avg319.8813
wk49.77625avg411.7755
wk58.708avg54.57326
wk615.4319avg61.27748
wk738.5981avg70.34151
wk810.0525avg80.1024
wk98.78688avg90.01479
wk1010.6669avg100.00186
wk1110.2844avg110.00022
wk1215.4988avg122.2E-05
wk1310.8888avg132.3E-06
wk147.9475avg142E-07
wk159.81188avg151.53E-08
wk1611.3281avg161.13E-09
wk1716.7944avg177.95E-11
wk1821.3356avg185.48E-12
wk1911.0613avg193.64E-13
wk2013.3138avg202.09E-14
wk2112.1488avg211.15E-15
wk2211.0613avg226.00E-17
wk2324.0344avg232.94E-18
wk2429.5988avg241.49E-19
wk2521.4113avg257.37E-21
wk2614.6531avg263.29E-22
wk2714.7avg271.36E-23
wk2818.7425avg285.40E-25
wk2915.8988avg292.10E-26
wk3013.8875avg307.77E-28
wk3119.5225avg312.75E-29
wk3217.2169avg329.60E-31
wk3312.4269avg333.21E-32
wk3416.275avg341.02E-33
wk3522.9125avg353.19E-35
wk3620.1725avg369.88E-37
wk3712.145avg372.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;

Esteemed Advisor
Posts: 5,539

Re: calculate inverse weighted cumulative average across preceding weeks

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
Contributor
Posts: 29

Re: calculate inverse weighted cumulative average across preceding weeks

Yes thank you!

☑ This topic is solved.

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

Discussion stats
  • 11 replies
  • 325 views
  • 0 likes
  • 4 in conversation