## How to calculate weighted average in rolling windows

Solved
Frequent Contributor
Posts: 130

# How to calculate weighted average in rolling windows

I will upload sample data latter when I am allowed to do so, currently the system doesn't allow to upload any type of files.)

The sample data is sorted by id, year, month and day.

yr_qrt represents year and quarter.

yr_mth represents year and month.

x_daily is daily data.

y_qrtly is quarterly data, so it does not change within a quarter (its value in the real data is random number).

What I try to do is as showed in the table named "result":

1. For each id, calculate the mean and standard deviation of x_daily in 3-month window (outputs denoted as x_mean, x_std).
2. Rolling one month forward, then calculate the mean and standard deviation of x_daily in another 3-month window.
3. Each time rolling one month forward until the end.

For example,

the first line in the 'result' calculates the mean and standard deviation for x_daily of 1993 Jan, Feb, and Mar;

the second line in the 'result' rolling one month forward, so it calculates the mean and standard deviation for x_daily of 1993 Feb, Mar and Apr.

so on and so forth.

The tricky part is for y_qrtly_weighted.

Y_qrtly is quarterly data, so it is different for different quarters.

Each time rolling one month forward, y_qrtly will include values from two different quarters, as showed in image "3-month window", so I need to calculate weighted average (the result is denoted as "y_qrtly_weighted"), eg., in line 2 of the 'result', y_qrtly_weighted = =(2/3)*21+(1/3)*22 (because two months are from quarter 1, and one month from quarter 2).

**if I use 6-month window, and each time rolling one month forward, then y_qrtly will include values from three different quarters, as showed in image "6-month window".

Thank you as always!

 Results id yr_mth x_mean x_std y_qrtly_weighted NOTE for variable 'y_qrtly_weighted': including y_qrtly from 3 months C1 1993.01 0.586272 0.222748 21 1993.01, 1993.02, 1993.03 all from 1993 quarter 1, so y_qrtly_weighted =(3/3)*21+(0/3)*22 C1 1993.02 0.467427 0.272757 21.333333 1993.02, 1993.03, 1993.04 1993.02 and 1993.03 from 1993 q1, while 1993.04 from 1993 q2.  =(2/3)*21+(1/3)*22 C1 1993.03 0.464920 0.253269 21.666667 1993.03, 1993.04, 1993.05 1993.03 from 1993 q1, while 1993.04 and 1993.05 from 1993 q2.  =(1/3)*21+(2/3)*22 C1 1993.04 0.426983 0.176654 22 1993.04, 1993.05, 1993.06 all from 1993 quarter 2. =(3/3)*22+(0/3)*23 C1 1993.05 0.426860 0.207852 22.333333 1993.05, 1993.06, 1993.07 1993.05 and 1993.06 from 1993 q2, while 1993.07 from 1993 q3.  =(2/3)*22+(1/3)*23 C1 1993.06 0.427672 0.266344 22.666667 1993.06, 1993.07, 1993.08 1993.06 from 1993 q2, while 1993.07 and 1993.08 from 1993 q3.  =(1/3)*22+(2/3)*23 C1 1993.07 0.451026 0.279004 23 1993.07, 1993.08, 1993.09 all from 1993 quarter 3. =(3/3)*23+(0/3)*24 C1 1993.08 0.521282 0.219959 23.33333333 1993.08, 1993.09, 1993.10 C1 1993.09 0.604753 0.201956 23.66666667 1993.09, 1993.10, 1993.11 C1 1993.10 0.505592 0.279543 24 1993.10, 1993.11, 1993.12 C1 1993.11 0.413657 0.325117 24.33333333 1993.11, 1993.12, 1994.01 C1 1993.12 0.237006 0.178642 24.66666667 1993.12, 1994.01, 1994.02 C1 1994.01 0.318783 0.202079 25 1994.01, 1994.02, 1994.03 C1 1994.02 0.446695 0.31459 25.33333333 1994.02, 1994.03, 1994.04 C1 1994.03 0.593523 0.323855 25.66666667 1994.03, 1994.04, 1994.05 C1 1994.04 0.527737 0.38738 26 1994.04, 1994.05, 1994.06 C1 1994.05 0.528612 0.309449 26.33333333 1994.05, 1994.06, 1994.07 C1 1994.06 0.462934 0.306889 26.66666667 1994.06, 1994.07, 1994.08 C1 1994.07 0.529519 0.2793 27 1994.07, 1994.08, 1994.09 C1 1994.08 0.562938 0.289716 27.33333333 1994.08, 1994.09, 1994.10 C1 1994.09 0.520011 0.242936 27.66666667 1994.09, 1994.10, 1994.11 C1 1994.10 0.528183 0.206463 28 1994.10, 1994.11, 1994.12 C2 1993.01 C2 1993.02 C2 1993.03 C2 1993.04 C2 1993.05

3-month window, each time rolling 1 month forward

 Year Quarter Month 3-month window, rolling 1 month 1993 1 1 @ 2 @ @ 3 @ @ @ 2 4 @ @ @ 5 @ @ @ 6 @ @ @ 3 7 @ @ @ 8 @ @ @ 9 @ @ @ 4 10 @ @ @ 11 @ @ 12 @

6-month window, each time rolling 1 month forward

 Year Quarter Month 6-month window 1993 1 1 @ 2 @ @ 3 @ @ @ 2 4 @ @ @ @ 5 @ @ @ @ @ 6 @ @ @ @ @ @ 3 7 @ @ @ @ @ @ 8 @ @ @ @ @ 9 @ @ @ @ 4 10 @ @ @ 11 @ @ 12 @

 Sample Data id year quarter month day yr_qrt yr_mth x_daily y_qrtly C1 1993 1 1 1 1993.1 1993.01 0.6991863822 21 C1 1993 1 1 2 1993.1 1993.01 0.4188095445 21 C1 1993 1 1 3 1993.1 1993.01 0.7669214303 21 C1 1993 1 2 1 1993.1 1993.02 0.7626337004 21 C1 1993 1 2 2 1993.1 1993.02 0.3310922338 21 C1 1993 1 2 3 1993.1 1993.02 0.4720571960 21 C1 1993 1 3 1 1993.1 1993.03 0.3182310962 21 C1 1993 1 3 2 1993.1 1993.03 0.5460470188 21 C1 1993 1 3 3 1993.1 1993.03 0.9614720765 21 C1 1993 2 4 1 1993.2 1993.04 0.2448514237 22 C1 1993 2 4 2 1993.2 1993.04 0.0605496911 22 C1 1993 2 4 3 1993.2 1993.04 0.5099089561 22 C1 1993 2 5 1 1993.2 1993.05 0.5974678636 22 C1 1993 2 5 2 1993.2 1993.05 0.4144236295 22 C1 1993 2 5 3 1993.2 1993.05 0.5313275277 22 C1 1993 2 6 1 1993.2 1993.06 0.4332121843 22 C1 1993 2 6 2 1993.2 1993.06 0.6142944847 22 C1 1993 2 6 3 1993.2 1993.06 0.4368145807 22 C1 1993 3 7 1 1993.3 1993.07 0.1635815183 23 C1 1993 3 7 2 1993.3 1993.07 0.6254998357 23 C1 1993 3 7 3 1993.3 1993.07 0.0251148200 23 C1 1993 3 8 1 1993.3 1993.08 0.2245898911 23 C1 1993 3 8 2 1993.3 1993.08 0.8971340141 23 C1 1993 3 8 3 1993.3 1993.08 0.4288076309 23 C1 1993 3 9 1 1993.3 1993.09 0.5289141138 23 C1 1993 3 9 2 1993.3 1993.09 0.7145691198 23 C1 1993 3 9 3 1993.3 1993.09 0.4038452509 23 C1 1993 4 10 1 1993.4 1993.10 0.7510774181 24 C1 1993 4 10 2 1993.4 1993.10 0.3815164396 24 C1 1993 4 10 3 1993.4 1993.10 0.3610818837 24 C1 1993 4 11 1 1993.4 1993.11 0.5930880003 24 C1 1993 4 11 2 1993.4 1993.11 0.9342462700 24 C1 1993 4 11 3 1993.4 1993.11 0.7744344805 24 C1 1993 4 12 1 1993.4 1993.12 0.0346028630 24 C1 1993 4 12 2 1993.4 1993.12 0.3402029259 24 C1 1993 4 12 3 1993.4 1993.12 0.3800805619 24 C1 1994 1 1 1 1994.1 1994.01 0.5395688261 25 C1 1994 1 1 2 1994.1 1994.01 0.0440990921 25 C1 1994 1 1 3 1994.1 1994.01 0.0825897569 25 C1 1994 1 2 1 1994.1 1994.02 0.0946161560 25 C1 1994 1 2 2 1994.1 1994.02 0.3025730601 25 C1 1994 1 2 3 1994.1 1994.02 0.3147217703 25 C1 1994 1 3 1 1994.1 1994.03 0.4734695338 25 C1 1994 1 3 2 1994.1 1994.03 0.5015168888 25 C1 1994 1 3 3 1994.1 1994.03 0.5158942878 25 C1 1994 2 4 1 1994.2 1994.04 0.9419447213 26 C1 1994 2 4 2 1994.2 1994.04 0.0043763457 26 C1 1994 2 4 3 1994.2 1994.04 0.8711450088 26 C1 1994 2 5 1 1994.2 1994.05 0.2757407595 26 C1 1994 2 5 2 1994.2 1994.05 0.8991403677 26 C1 1994 2 5 3 1994.2 1994.05 0.8584785915 26 C1 1994 2 6 1 1994.2 1994.06 0.6110990929 26 C1 1994 2 6 2 1994.2 1994.06 0.2547390994 26 C1 1994 2 6 3 1994.2 1994.06 0.0329722262 26 C1 1994 3 7 1 1994.3 1994.07 0.4751589307 27 C1 1994 3 7 2 1994.3 1994.07 0.4716351814 27 C1 1994 3 7 3 1994.3 1994.07 0.8785433147 27 C1 1994 3 8 1 1994.3 1994.08 0.2030396292 27 C1 1994 3 8 2 1994.3 1994.08 0.9464783957 27 C1 1994 3 8 3 1994.3 1994.08 0.2927367473 27 C1 1994 3 9 1 1994.3 1994.09 0.8129264096 27 C1 1994 3 9 2 1994.3 1994.09 0.2746820796 27 C1 1994 3 9 3 1994.3 1994.09 0.4104693898 27 C1 1994 4 10 1 1994.4 1994.10 0.9185667789 28 C1 1994 4 10 2 1994.4 1994.10 0.4960573668 28 C1 1994 4 10 3 1994.4 1994.10 0.7114845093 28 C1 1994 4 11 1 1994.4 1994.11 0.2398818887 28 C1 1994 4 11 2 1994.4 1994.11 0.3228150218 28 C1 1994 4 11 3 1994.4 1994.11 0.4932111704 28 C1 1994 4 12 1 1994.4 1994.12 0.6316404842 28 C1 1994 4 12 2 1994.4 1994.12 0.3993126101 28 C1 1994 4 12 3 1994.4 1994.12 0.5406755160 28 C2 1993 1 1 1 1993.1 1993.01 0.1404106664 31 C2 1993 1 1 2 1993.1 1993.01 0.0042181071 31 C2 1993 1 1 3 1993.1 1993.01 0.6612789196 31 C2 1993 1 2 1 1993.1 1993.02 0.9866000757 31 C2 1993 1 2 2 1993.1 1993.02 0.7487539526 31 C2 1993 1 2 3 1993.1 1993.02 0.5549712092 31 C2 1993 1 3 1 1993.1 1993.03 0.2377428614 31 C2 1993 1 3 2 1993.1 1993.03 0.1975908200 31 C2 1993 1 3 3 1993.1 1993.03 0.8790837648 31 C2 1993 2 4 1 1993.2 1993.04 0.5927255573 32 C2 1993 2 4 2 1993.2 1993.04 0.7753605196 32 C2 1993 2 4 3 1993.2 1993.04 0.6130982465 32 C2 1993 2 5 1 1993.2 1993.05 0.2371637204 32 C2 1993 2 5 2 1993.2 1993.05 0.9407495518 32 C2 1993 2 5 3 1993.2 1993.05 0.3734901620 32 C2 1993 2 6 1 1993.2 1993.06 0.4951367474 32 C2 1993 2 6 2 1993.2 1993.06 0.7537148950 32 C2 1993 2 6 3 1993.2 1993.06 0.6548179168 32 C2 1993 3 7 1 1993.3 1993.07 0.1505288735 33 C2 1993 3 7 2 1993.3 1993.07 0.3178738824 33 C2 1993 3 7 3 1993.3 1993.07 0.5768104827 33 C2 1993 3 8 1 1993.3 1993.08 0.8163487766 33 C2 1993 3 8 2 1993.3 1993.08 0.8669987440 33 C2 1993 3 8 3 1993.3 1993.08 0.2502204879 33 C2 1993 3 9 1 1993.3 1993.09 0.6007882390 33 C2 1993 3 9 2 1993.3 1993.09 0.7341289940 33 C2 1993 3 9 3 1993.3 1993.09 0.0610418621 33 C2 1993 4 10 1 1993.4 1993.10 0.2780458980 34 C2 1993 4 10 2 1993.4 1993.10 0.0886410862 34 C2 1993 4 10 3 1993.4 1993.10 0.2692696308 34 C2 1993 4 11 1 1993.4 1993.11 0.8339314943 34 C2 1993 4 11 2 1993.4 1993.11 0.8037322285 34 C2 1993 4 11 3 1993.4 1993.11 0.4076172518 34 C2 1993 4 12 1 1993.4 1993.12 0.1801678671 34 C2 1993 4 12 2 1993.4 1993.12 0.4015914219 34 C2 1993 4 12 3 1993.4 1993.12 0.6368116192 34 C2 1994 1 1 1 1994.1 1994.01 0.9993039157 35 C2 1994 1 1 2 1994.1 1994.01 0.0623687831 35 C2 1994 1 1 3 1994.1 1994.01 0.5564758814 35 C2 1994 1 2 1 1994.1 1994.02 0.5159281582 35 C2 1994 1 2 2 1994.1 1994.02 0.0259838417 35 C2 1994 1 2 3 1994.1 1994.02 0.2442614012 35 C2 1994 1 3 1 1994.1 1994.03 0.3506325841 35 C2 1994 1 3 2 1994.1 1994.03 0.3859868378 35 C2 1994 1 3 3 1994.1 1994.03 0.1181077247 35 C2 1994 2 4 1 1994.2 1994.04 0.2747128897 36 C2 1994 2 4 2 1994.2 1994.04 0.0369628876 36 C2 1994 2 4 3 1994.2 1994.04 0.5459171566 36 C2 1994 2 5 1 1994.2 1994.05 0.5962766915 36 C2 1994 2 5 2 1994.2 1994.05 0.0179308383 36 C2 1994 2 5 3 1994.2 1994.05 0.4597822841 36 C2 1994 2 6 1 1994.2 1994.06 0.5814561892 36 C2 1994 2 6 2 1994.2 1994.06 0.5278386357 36 C2 1994 2 6 3 1994.2 1994.06 0.7308049624 36 C2 1994 3 7 1 1994.3 1994.07 0.2588968025 37 C2 1994 3 7 2 1994.3 1994.07 0.3599506725 37 C2 1994 3 7 3 1994.3 1994.07 0.6717860393 37 C2 1994 3 8 1 1994.3 1994.08 0.7018230279 37 C2 1994 3 8 2 1994.3 1994.08 0.1232914674 37 C2 1994 3 8 3 1994.3 1994.08 0.8767294226 37 C2 1994 3 9 1 1994.3 1994.09 0.3238259663 37 C2 1994 3 9 2 1994.3 1994.09 0.7796738755 37 C2 1994 3 9 3 1994.3 1994.09 0.0465424382 37 C2 1994 4 10 1 1994.4 1994.10 0.0981541393 38 C2 1994 4 10 2 1994.4 1994.10 0.9631875620 38 C2 1994 4 10 3 1994.4 1994.10 0.1803793356 38 C2 1994 4 11 1 1994.4 1994.11 0.2711392788 38 C2 1994 4 11 2 1994.4 1994.11 0.9360266261 38 C2 1994 4 11 3 1994.4 1994.11 0.4892338391 38 C2 1994 4 12 1 1994.4 1994.12 0.8518156137 38 C2 1994 4 12 2 1994.4 1994.12 0.9309163121 38 C2 1994 4 12 3 1994.4 1994.12 0.7187143340 38

Accepted Solutions
Solution
‎07-03-2015 10:51 PM
Super User
Posts: 10,787

## Re: How to calculate weighted average in rolling windows

### Code: Program

`data have;infile cards truncover expandtabs;input id \$ year quarter month day yr_qrt  \$ yr_mth \$ x_daily y_qrtly;cards;C1 1993 1 1 1 1993.1 1993.01 0.6991863822 21C1 1993 1 1 2 1993.1 1993.01 0.4188095445 21C1 1993 1 1 3 1993.1 1993.01 0.7669214303 21C1 1993 1 2 1 1993.1 1993.02 0.7626337004 21C1 1993 1 2 2 1993.1 1993.02 0.3310922338 21C1 1993 1 2 3 1993.1 1993.02 0.4720571960 21C1 1993 1 3 1 1993.1 1993.03 0.3182310962 21C1 1993 1 3 2 1993.1 1993.03 0.5460470188 21C1 1993 1 3 3 1993.1 1993.03 0.9614720765 21C1 1993 2 4 1 1993.2 1993.04 0.2448514237 22C1 1993 2 4 2 1993.2 1993.04 0.0605496911 22C1 1993 2 4 3 1993.2 1993.04 0.5099089561 22C1 1993 2 5 1 1993.2 1993.05 0.5974678636 22C1 1993 2 5 2 1993.2 1993.05 0.4144236295 22C1 1993 2 5 3 1993.2 1993.05 0.5313275277 22C1 1993 2 6 1 1993.2 1993.06 0.4332121843 22C1 1993 2 6 2 1993.2 1993.06 0.6142944847 22C1 1993 2 6 3 1993.2 1993.06 0.4368145807 22C1 1993 3 7 1 1993.3 1993.07 0.1635815183 23C1 1993 3 7 2 1993.3 1993.07 0.6254998357 23C1 1993 3 7 3 1993.3 1993.07 0.0251148200 23C1 1993 3 8 1 1993.3 1993.08 0.2245898911 23C1 1993 3 8 2 1993.3 1993.08 0.8971340141 23C1 1993 3 8 3 1993.3 1993.08 0.4288076309 23C1 1993 3 9 1 1993.3 1993.09 0.5289141138 23C1 1993 3 9 2 1993.3 1993.09 0.7145691198 23C1 1993 3 9 3 1993.3 1993.09 0.4038452509 23C1 1993 4 10 1 1993.4 1993.10 0.7510774181 24C1 1993 4 10 2 1993.4 1993.10 0.3815164396 24C1 1993 4 10 3 1993.4 1993.10 0.3610818837 24C1 1993 4 11 1 1993.4 1993.11 0.5930880003 24C1 1993 4 11 2 1993.4 1993.11 0.9342462700 24C1 1993 4 11 3 1993.4 1993.11 0.7744344805 24C1 1993 4 12 1 1993.4 1993.12 0.0346028630 24C1 1993 4 12 2 1993.4 1993.12 0.3402029259 24C1 1993 4 12 3 1993.4 1993.12 0.3800805619 24;run;data temp; set have; by id year month; date=mdy(month,day,year); if first.month; format date yymmp.; keep id year month date y_qrtly;run;%let window=2;proc sql;create table qrtly_weighted as select *,(select sum(y_qrtly)/count(*) from temp where id=a.id and   date between a.date and   intnx('month',a.date,&window)) as y_qrtly_weighted     from temp as a;quit;`

All Replies
Super User
Posts: 10,787

## Re: How to calculate weighted average in rolling windows

Here is an example to calculate rolling mean and std (window=3 months) , you can get start from it .

### Code: Program

`data have;infile cards truncover expandtabs;input id \$ year quarter month day yr_qrt  \$ yr_mth \$ x_daily y_qrtly;cards;C1 1993 1 1 1 1993.1 1993.01 0.6991863822 21C1 1993 1 1 2 1993.1 1993.01 0.4188095445 21C1 1993 1 1 3 1993.1 1993.01 0.7669214303 21C1 1993 1 2 1 1993.1 1993.02 0.7626337004 21C1 1993 1 2 2 1993.1 1993.02 0.3310922338 21C1 1993 1 2 3 1993.1 1993.02 0.4720571960 21C1 1993 1 3 1 1993.1 1993.03 0.3182310962 21C1 1993 1 3 2 1993.1 1993.03 0.5460470188 21C1 1993 1 3 3 1993.1 1993.03 0.9614720765 21;run;%let window=3;proc sql;create table rolling_month as select *,(select mean(x_daily) from have where id=a.id and   mdy(month,day,year) between mdy(a.month,a.day,a.year) and   intnx('month',mdy(a.month,a.day,a.year),&window)) as x_mean,   (select std(x_daily) from have where id=a.id and   mdy(month,day,year) between mdy(a.month,a.day,a.year) and   intnx('month',mdy(a.month,a.day,a.year),&window)) as x_std  from have as a;quit;`
Frequent Contributor
Posts: 130

## Re: How to calculate weighted average in rolling windows

Thank you Keshan!

This will calculate the mean and average for 3-month window, but each time rolling one day forward. Slightly different from my result.

Solution
‎07-03-2015 10:51 PM
Super User
Posts: 10,787

## Re: How to calculate weighted average in rolling windows

### Code: Program

`data have;infile cards truncover expandtabs;input id \$ year quarter month day yr_qrt  \$ yr_mth \$ x_daily y_qrtly;cards;C1 1993 1 1 1 1993.1 1993.01 0.6991863822 21C1 1993 1 1 2 1993.1 1993.01 0.4188095445 21C1 1993 1 1 3 1993.1 1993.01 0.7669214303 21C1 1993 1 2 1 1993.1 1993.02 0.7626337004 21C1 1993 1 2 2 1993.1 1993.02 0.3310922338 21C1 1993 1 2 3 1993.1 1993.02 0.4720571960 21C1 1993 1 3 1 1993.1 1993.03 0.3182310962 21C1 1993 1 3 2 1993.1 1993.03 0.5460470188 21C1 1993 1 3 3 1993.1 1993.03 0.9614720765 21C1 1993 2 4 1 1993.2 1993.04 0.2448514237 22C1 1993 2 4 2 1993.2 1993.04 0.0605496911 22C1 1993 2 4 3 1993.2 1993.04 0.5099089561 22C1 1993 2 5 1 1993.2 1993.05 0.5974678636 22C1 1993 2 5 2 1993.2 1993.05 0.4144236295 22C1 1993 2 5 3 1993.2 1993.05 0.5313275277 22C1 1993 2 6 1 1993.2 1993.06 0.4332121843 22C1 1993 2 6 2 1993.2 1993.06 0.6142944847 22C1 1993 2 6 3 1993.2 1993.06 0.4368145807 22C1 1993 3 7 1 1993.3 1993.07 0.1635815183 23C1 1993 3 7 2 1993.3 1993.07 0.6254998357 23C1 1993 3 7 3 1993.3 1993.07 0.0251148200 23C1 1993 3 8 1 1993.3 1993.08 0.2245898911 23C1 1993 3 8 2 1993.3 1993.08 0.8971340141 23C1 1993 3 8 3 1993.3 1993.08 0.4288076309 23C1 1993 3 9 1 1993.3 1993.09 0.5289141138 23C1 1993 3 9 2 1993.3 1993.09 0.7145691198 23C1 1993 3 9 3 1993.3 1993.09 0.4038452509 23C1 1993 4 10 1 1993.4 1993.10 0.7510774181 24C1 1993 4 10 2 1993.4 1993.10 0.3815164396 24C1 1993 4 10 3 1993.4 1993.10 0.3610818837 24C1 1993 4 11 1 1993.4 1993.11 0.5930880003 24C1 1993 4 11 2 1993.4 1993.11 0.9342462700 24C1 1993 4 11 3 1993.4 1993.11 0.7744344805 24C1 1993 4 12 1 1993.4 1993.12 0.0346028630 24C1 1993 4 12 2 1993.4 1993.12 0.3402029259 24C1 1993 4 12 3 1993.4 1993.12 0.3800805619 24;run;data temp; set have; by id year month; date=mdy(month,day,year); if first.month; format date yymmp.; keep id year month date y_qrtly;run;%let window=2;proc sql;create table qrtly_weighted as select *,(select sum(y_qrtly)/count(*) from temp where id=a.id and   date between a.date and   intnx('month',a.date,&window)) as y_qrtly_weighted     from temp as a;quit;`
Frequent Contributor
Posts: 130

## Re: How to calculate weighted average in rolling windows

Thank you again! this looks very creative to me.

Frequent Contributor
Posts: 130

## Re: How to calculate weighted average in rolling windows

I run the code with my data (more than 3 million observations), the proc sql procedure is very slow, it has been running for many hours, still no sign of finishing soon. any way to speed up a little bit? thanks!

Super User
Posts: 10,787

## Re: How to calculate weighted average in rolling windows

If it is really big , suggest you to split your table into lots of small sub-table by ID variable .

And for each sub-table :

%let window=2;

proc sql;

create table qrtly_weighted_1 as

select *,(select sum(y_qrtly)/count(*) from temp_1 where

date between a.date and

intnx('month',a.date,&window)) as y_qrtly_weighted

from temp as a;

quit;

Other wise, I am going to use Hash Table.

Super User
Posts: 10,787

## Re: How to calculate weighted average in rolling windows

Here is Hash Table version of code .

### Code: Program

`data have;infile cards truncover expandtabs;input id \$ year quarter month day yr_qrt  \$ yr_mth \$ x_daily y_qrtly;cards;C1 1993 1 1 1 1993.1 1993.01 0.6991863822 21C1 1993 1 1 2 1993.1 1993.01 0.4188095445 21C1 1993 1 1 3 1993.1 1993.01 0.7669214303 21C1 1993 1 2 1 1993.1 1993.02 0.7626337004 21C1 1993 1 2 2 1993.1 1993.02 0.3310922338 21C1 1993 1 2 3 1993.1 1993.02 0.4720571960 21C1 1993 1 3 1 1993.1 1993.03 0.3182310962 21C1 1993 1 3 2 1993.1 1993.03 0.5460470188 21C1 1993 1 3 3 1993.1 1993.03 0.9614720765 21C1 1993 2 4 1 1993.2 1993.04 0.2448514237 22C1 1993 2 4 2 1993.2 1993.04 0.0605496911 22C1 1993 2 4 3 1993.2 1993.04 0.5099089561 22C1 1993 2 5 1 1993.2 1993.05 0.5974678636 22C1 1993 2 5 2 1993.2 1993.05 0.4144236295 22C1 1993 2 5 3 1993.2 1993.05 0.5313275277 22C1 1993 2 6 1 1993.2 1993.06 0.4332121843 22C1 1993 2 6 2 1993.2 1993.06 0.6142944847 22C1 1993 2 6 3 1993.2 1993.06 0.4368145807 22C1 1993 3 7 1 1993.3 1993.07 0.1635815183 23C1 1993 3 7 2 1993.3 1993.07 0.6254998357 23C1 1993 3 7 3 1993.3 1993.07 0.0251148200 23C1 1993 3 8 1 1993.3 1993.08 0.2245898911 23C1 1993 3 8 2 1993.3 1993.08 0.8971340141 23C1 1993 3 8 3 1993.3 1993.08 0.4288076309 23C1 1993 3 9 1 1993.3 1993.09 0.5289141138 23C1 1993 3 9 2 1993.3 1993.09 0.7145691198 23C1 1993 3 9 3 1993.3 1993.09 0.4038452509 23C1 1993 4 10 1 1993.4 1993.10 0.7510774181 24C1 1993 4 10 2 1993.4 1993.10 0.3815164396 24C1 1993 4 10 3 1993.4 1993.10 0.3610818837 24C1 1993 4 11 1 1993.4 1993.11 0.5930880003 24C1 1993 4 11 2 1993.4 1993.11 0.9342462700 24C1 1993 4 11 3 1993.4 1993.11 0.7744344805 24C1 1993 4 12 1 1993.4 1993.12 0.0346028630 24C1 1993 4 12 2 1993.4 1993.12 0.3402029259 24C1 1993 4 12 3 1993.4 1993.12 0.3800805619 24;run;data temp; set have; by id year month; date=mdy(month,day,year); if first.month; format date yymmp.; keep id year month date y_qrtly;run;%let window=2;data want; if _n_ eq 1 then do;  if 0 then set temp(rename=(y_qrtly=_y_qrtly));  declare hash ha(dataset:'temp(rename=(y_qrtly=_y_qrtly))',hashexp:20);  ha.definekey('id','date');  ha.definedata('_y_qrtly');  ha.definedone(); end;set temp;sum=0;n=0;do i=date to intnx('month',date,&window); if ha.find(key:id,key:i)=0 then do;sum+_y_qrtly;n+1;end;end;y_qrtly_weighted=divide(sum,n);drop i sum n _y_qrtly;run;`
Frequent Contributor
Posts: 130