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

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!

1 ACCEPTED SOLUTION

Accepted Solutions
PeterClemmensen
Tourmaline | Level 20

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;

View solution in original post

11 REPLIES 11
PeterClemmensen
Tourmaline | Level 20

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

PeterClemmensen
Tourmaline | Level 20

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;
PeterClemmensen
Tourmaline | Level 20

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

Astounding
PROC Star

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;

   

PGStats
Opal | Level 21

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
psh23
Fluorite | Level 6

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;

PGStats
Opal | Level 21

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

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!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

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