Hi,
I'm migrating a piece of work from excel into SAS EG 8.3 and have hit a road block when trying to carry out a rolling sumproduct. I've posted 2 sample data sets below which are representative of what I'm working with. The aim of the calc is to divide variable in L1 by a sumproduct of the previous 13 K1 observations times _1 variable in Weights i.e. NewVar= L1/sumproduct(K1 {01/01/2020, 01/01/2019,....,01/01/2019}] * Weights _1 {T1, T2,....,T13}, rolling through each iteration to the end of the data set. This process is repeated for K2 _2, K3 _3 K4 _4. Anyone have any ideas how to approach this? As always, any help is greatly appreciated. Thank you.
data K_L; input Date $10. K1 K2 K3 K4 L1 L2 L3 L4; datalines; 01/01/2019 121.926016 30.1118022 117.9159667 114.8032827 0.0106866 0.0148462 0.0162278 0.0083365 01/02/2019 64.1152185 37.7879647 115.4533426 25.4625441 0.0082903 0.0049103 0.0131859 0.0128432 01/03/2019 69.8954642 73.9363367 149.8814052 25.9723443 0.023876 0.0180732 0.0193988 0.0128268 01/04/2019 93.9618886 114.2841703 23.3093198 15.7037899 0.0056912 0.0109052 0.0235227 0.00363 01/05/2019 49.7325073 120.8835863 119.7751177 66.9855032 0.0061046 0.019724 0.0168608 0.0243884 01/06/2019 14.6921632 66.9838787 20.4316234 33.6180846 0.0085689 0.01493 0.0174641 0.0034949 01/07/2019 31.7261233 47.6206993 15.063515 112.1523805 0.0226357 0.0173301 0.0051066 0.0063488 01/08/2019 43.0899512 71.3193465 144.7094998 38.531899 0.0108307 0.008498 0.0061353 0.0121331 01/09/2019 37.3073324 31.1596471 70.934477 119.3631722 0.0065147 0.0091191 0.0108937 0.0055851 01/10/2019 101.8151148 103.7241755 79.1916022 31.8400543 0.0082805 0.0082198 0.0093628 0.0247967 01/11/2019 141.6785982 82.1741977 76.6770373 62.2417837 0.0140984 0.0048539 0.0037639 0.0073256 01/12/2019 94.2208776 58.0121484 129.0459861 71.7350841 0.0137397 0.0049652 0.0042945 0.0242653 01/01/2020 139.4946582 27.6016136 12.7545772 32.6708783 0.011223 0.0037558 0.0032948 0.0166059 01/02/2020 80.486222 103.6853139 81.3539301 55.1492974 0.0048909 0.0183308 0.015025 0.0243772 01/03/2020 122.0825271 110.4888403 62.4210334 147.5287977 0.007687 0.0091621 0.0092826 0.0052491 01/04/2020 80.1413426 67.5958511 52.915515 39.796265 0.0096555 0.0221558 0.0145667 0.0113954 01/05/2020 54.3343459 106.7091799 29.5151955 74.8352642 0.0048417 0.0077815 0.0041301 0.0216362 01/06/2020 39.2123992 52.0335799 105.0662082 31.8119421 0.0173234 0.0210945 0.0216121 0.0147118 01/07/2020 64.5916759 17.4899281 104.6063942 107.3731431 0.0085178 0.0093562 0.017146 0.0184087 01/08/2020 127.5682489 118.4554408 119.6971101 107.1793866 0.011997 0.0125137 0.0128569 0.0057761 01/09/2020 31.5029836 148.0847799 54.7589425 12.9187809 0.0109211 0.0093215 0.017948 0.0167315 01/10/2020 49.3388651 141.0252796 43.6687193 88.7784704 0.0229832 0.0212622 0.0232255 0.0099701 01/11/2020 107.6002073 126.5032261 78.5925082 17.5196923 0.0121384 0.0086928 0.0126987 0.0194811 01/12/2020 14.6516049 136.2847937 141.273386 142.3719276 0.0094999 0.0127409 0.0232006 0.0130772 01/01/2021 39.8236419 62.5668547 65.2942222 116.9457868 0.0236656 0.0147904 0.0062289 0.0098804 01/02/2021 40.2123146 39.7449587 91.6050256 122.6587219 0.0225419 0.0154077 0.0200757 0.010701 01/03/2021 41.9045371 65.3020177 41.829014 78.0845254 0.009547 0.0039977 0.0195096 0.0207348 01/04/2021 99.3733839 127.0447013 122.3381337 11.6428299 0.0159084 0.0189978 0.0213137 0.0134971 01/05/2021 68.3073449 68.8008188 42.9544126 78.0167043 0.0120663 0.0205594 0.0215848 0.0243624 01/06/2021 89.3609816 89.2937286 44.2130984 45.244841 0.0106073 0.0120053 0.0098607 0.023001 01/07/2021 37.4475439 109.7924378 144.9185598 137.6460937 0.0197659 0.0237077 0.0119351 0.0107182 01/08/2021 67.148617 43.2618187 133.569472 106.6704203 0.0094042 0.0143152 0.0217298 0.014833 01/09/2021 46.84389 143.2617852 137.9613868 116.5332468 0.0228075 0.0111544 0.0152732 0.0167283 01/10/2021 100.6643394 73.5632955 46.6392064 72.0664848 0.0184261 0.0092798 0.0185922 0.0041382 01/11/2021 97.2158686 108.5241008 52.4988934 34.3571445 0.0095111 0.0151183 0.0041784 0.0032289 01/12/2021 38.1587444 115.1221924 68.3364786 25.2518211 0.0132877 0.0113634 0.0058994 0.0106635 01/01/2022 16.3336723 18.1755389 84.8752453 68.6899765 0.0118928 0.0059135 0.0127204 0.0057086 01/02/2022 101.7305927 132.3780312 91.745964 121.3893317 0.0211951 0.0105514 0.0233112 0.0236517 01/03/2022 138.621144 116.5008081 90.2615256 143.9117409 0.020235 0.0127502 0.0133932 0.0221366 01/04/2022 92.2332614 110.1752375 141.8428504 131.8100939 0.0141841 0.0112025 0.0053434 0.0116661 01/05/2022 61.6558276 43.1461302 116.1462283 86.8363286 0.0213428 0.0180899 0.0077884 0.0114901 01/06/2022 122.8001125 103.6117242 65.818532 135.1846391 0.0097324 0.0042181 0.0046499 0.007177 ; run;
data Weights; input T $3. _1 _2 _3 _4; datalines; T13 0.03 0.01 0.1 0.04 T12 0.15 0.26 0.09 0.01 T11 0.06 0.03 0.06 0.05 T10 0.07 0.04 0.04 0.03 T9 0.1 0.07 0.04 0.1 T8 0.06 0.04 0.1 0.3 T7 0.16 0.1 0.06 0.23 T6 0.07 0.07 0.03 0.14 T5 0.17 0.08 0.09 0.05 T4 0.04 0.06 0.1 0.02 T3 0.05 0.03 0.15 0.01 T2 0.02 0.16 0.08 0.01 T1 0.02 0.05 0.06 0.01 ; run;
... View more