topic RE: Sumproduct referencing an external data set in SAS Programming

RE: Sumproduct referencing an external data set

gsisme — Mon, 19 Jun 2023 10:30:58 GMT

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;

RE: Sumproduct referencing an external data set

Kurt_Bremser — Mon, 19 Jun 2023 11:19:52 GMT

To facilitate easy processing, start by doing the following:

read obvious date values as SAS dates; right now, we don't even know if 01/02/2019 is 2019-02-01 or 2019-01-02
convert the T-values in the weights dataset to numbers for easy indexing

Please define your calculation as a formula using standard math operators; I have no clue what "sumproduct" means.

RE: Sumproduct referencing an external data set

Ksharp — Mon, 19 Jun 2023 12:25:56 GMT

Can you post the output variable L1 L2 ...?

And
01/01/2020, 01/01/2019,....,01/01/2019}
should be
01/01/2020, 01/12/2019,....,01/01/2019}
??

RE: Sumproduct referencing an external data set

Ksharp — Mon, 19 Jun 2023 12:47:24 GMT

OK. Anyway, try the following code and see whether it is what you are looking for.


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 $ _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;

proc iml;
use K_L nobs nobs;
read all var{date K1 K2 K3 K4 L1 L2 L3 L4};
close;
use Weights;
read all var{_1 _2 _3 _4};
close;

n=nobs-12;

want1=J(n,1,0);
want2=J(n,1,0);
want3=J(n,1,0);
want4=J(n,1,0);
do i=1 to n;
  want1[i]=L1[i]/sum(K1[i:i+12]#_1);
  want2[i]=L2[i]/sum(K2[i:i+12]#_2);
  want3[i]=L3[i]/sum(K3[i:i+12]#_3);
  want4[i]=L4[i]/sum(K4[i:i+12]#_4);
end;

create want var{date K1 K2 K3 K4 L1 L2 L3 L4 want1 want2 want3 want4};
append;
close;
quit;

RE: Sumproduct referencing an external data set

gsisme — Mon, 19 Jun 2023 16:13:39 GMT

Hi Kurt,

Script below has been updated as requested in the post.

Where I'm trying to get to with this is to apply the 1-13 weights to each of the observations in K, dividing L by the result. I've dropped an screenshot below which will hopefully add come context. Columns marked M1-M4 contain the results. I've highlighted cell J15 as this contains the formula in the bar at the top of the screenshot

i.e. NewVar= L1/sum[K1 {01/01/2020, 01/12/2019,....,01/01/2019} * Weights _1 {T1, T2,....,T13}]

data K_L;

input Date ddmmyy10. K1 K2 K3 K4 L1 L2 L3 L4;
format Date ddmmyy10.;

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 _1 _2 _3 _4;
datalines;
13 0.03 0.01 0.1 0.04
12 0.15 0.26 0.09 0.01
11 0.06 0.03 0.06 0.05
10 0.07 0.04 0.04 0.03
9 0.1 0.07 0.04 0.1
8 0.06 0.04 0.1 0.3
7 0.16 0.1 0.06 0.23
6 0.07 0.07 0.03 0.14
5 0.17 0.08 0.09 0.05
4 0.04 0.06 0.1 0.02
3 0.05 0.03 0.15 0.01
2 0.02 0.16 0.08 0.01
1 0.02 0.05 0.06 0.01
;
run;

RE: Sumproduct referencing an external data set

gsisme — Mon, 19 Jun 2023 16:15:26 GMT

Thanks for the response. I've posted a response to Kurt which should check off the first question. To the second point, yes, that was a typo on my part, apologies.

RE: Sumproduct referencing an external data set

gsisme — Mon, 19 Jun 2023 16:18:58 GMT

Hi,

Thanks for taking the time on this. There seems to be an issue with the Proc IML procedure - I'm getting an error saying 'ERROR: Procedure IML not found'. Haven't used this Proc command before so not sure what the issue is here.

RE: Sumproduct referencing an external data set

PaigeMiller — Mon, 19 Jun 2023 19:33:00 GMT

@gsisme wrote:
Thanks for the response. I've posted a response to Kurt which should check off the first question. To the second point, yes, that was a typo on my part, apologies.

Put the response here so we can all follow along.

RE: Sumproduct referencing an external data set

SASKiwi — Tue, 20 Jun 2023 01:51:48 GMT

PROC IML is provided by the SAS product SAS/IML. It appears you don't have it installed. Run this to confirm whether it is installed or licensed:

proc product_status;
run;

proc setinit;
run;

RE: Sumproduct referencing an external data set

gsisme — Tue, 20 Jun 2023 08:49:06 GMT

Hi,

I ran the script called out in the post - the following was sent to the log:

1 ;*';*";*/;quit;run;
2 OPTIONS PAGENO=MIN;
3 %LET _CLIENTTASKLABEL='Program';
4 %LET _CLIENTPROCESSFLOWNAME='Standalone Not In Project';
5 %LET _CLIENTPROJECTPATH='';
6 %LET _CLIENTPROJECTPATHHOST='';
7 %LET _CLIENTPROJECTNAME='';
8 %LET _SASPROGRAMFILE='';
9 %LET _SASPROGRAMFILEHOST='';
10
11 ODS _ALL_ CLOSE;
12 OPTIONS DEV=SVG;
13 GOPTIONS XPIXELS=0 YPIXELS=0;
14 %macro HTML5AccessibleGraphSupported;
15 %if %_SAS_VERCOMP_FV(9,4,4, 0,0,0) >= 0 %then ACCESSIBLE_GRAPH;
16 %mend;
17 FILENAME EGHTML TEMP;
18 ODS HTML5(ID=EGHTML) FILE=EGHTML
19 OPTIONS(BITMAP_MODE='INLINE')
20 %HTML5AccessibleGraphSupported
21 ENCODING='utf-8'
22 STYLE=HTMLBlue
23 NOGTITLE
24 NOGFOOTNOTE
25 GPATH=&sasworklocation
26 ;
NOTE: Writing HTML5(EGHTML) Body file: EGHTML
27
28 proc product_status;
29 run;

For Base SAS Software ...
Custom version information: 9.4_M7
Image version information: 9.04.01M7P080520
For SAS/STAT ...
Custom version information: 15.2
For SAS/GRAPH ...
Custom version information: 9.4_M7
For High Performance Suite ...
Custom version information: 2.2_M8
For SAS/ACCESS Interface to PC Files ...
Custom version information: 9.4_M7
For SAS/ACCESS Interface to ODBC ...
Custom version information: 9.4_M7
NOTE: PROCEDURE PRODUCT_STATUS used (Total process time):
real time 0.02 seconds
cpu time 0.03 seconds

30
31 proc setinit;
32 run;

NOTE: PROCEDURE SETINIT used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

Original site validation data
2 The SAS System 09:41 Tuesday, June 20, 2023

Current version: 9.04.01M7P080520
Site name: 'XXXXXXXXXXXXXXXXXXXXXXXXXXX'.
Site number: XXXXXXXXXXXXX.
CPU A: Model name='' model number='' serial=''.
Expiration: 27FEB2024.
Grace Period: 30 days (ending 28MAR2024).
Warning Period: 33 days (ending 30APR2024).
System birthday: 09FEB2023.
Operating System: WX64_WKS.
Product expiration dates:
---Base SAS Software 27FEB2024 (CPU A)
---SAS/STAT 27FEB2024 (CPU A)
---SAS/GRAPH 27FEB2024 (CPU A)
---SAS/Secure 168-bit 27FEB2024 (CPU A)
---SAS/Secure Windows 27FEB2024 (CPU A)
---SAS Enterprise Guide 27FEB2024 (CPU A)
---SAS/ACCESS Interface to PC Files 27FEB2024 (CPU A)
---SAS/ACCESS Interface to ODBC 27FEB2024 (CPU A)
---SAS Workspace Server for Local Access 27FEB2024 (CPU A)
---High Performance Suite 27FEB2024 (CPU A)

33
34 %LET _CLIENTTASKLABEL=;
35 %LET _CLIENTPROCESSFLOWNAME=;
36 %LET _CLIENTPROJECTPATH=;
37 %LET _CLIENTPROJECTPATHHOST=;
38 %LET _CLIENTPROJECTNAME=;
39 %LET _SASPROGRAMFILE=;
40 %LET _SASPROGRAMFILEHOST=;
41
42 ;*';*";*/;quit;run;
43 ODS _ALL_ CLOSE;
44
45
46 QUIT; RUN;
47

RE: Sumproduct referencing an external data set

Ksharp — Tue, 20 Jun 2023 11:34:46 GMT

/*
If you don't have PROC IML. Try this one :
*/
data want;
 array t1{13} _temporary_;
 array t2{13} _temporary_;
 array t3{13} _temporary_;
 array t4{13} _temporary_;

 array kk1{13} _temporary_;
 array kk2{13} _temporary_;
 array kk3{13} _temporary_;
 array kk4{13} _temporary_;

if _n_=1 then do;
 do i=1 by 1 until(last);
   set Weights end=last;
   t1{i}=_1;
   t2{i}=_2;
   t3{i}=_3;
   t4{i}=_4;
 end;
end;

set K_L;
do i=2 to 13;
 kk1{i-1}=kk1{i};
 kk2{i-1}=kk2{i};
 kk3{i-1}=kk3{i};
 kk4{i-1}=kk4{i};
end;
 kk1{13}=k1;
 kk2{13}=k2;
 kk3{13}=k3;
 kk4{13}=k4;

if _n_>12 then do;
  sum1=0;
  sum2=0;
  sum3=0;
  sum4=0;
  do i=1 to 13;
    sum1+kk1{i}*t1{i};
 sum2+kk2{i}*t2{i};
    sum3+kk3{i}*t3{i};
    sum4+kk4{i}*t4{i};
  end;
  m1=l1/sum1;
  m2=l2/sum2;
  m3=l3/sum3;
  m4=l4/sum4;
end;
drop i sum: t _: ;
run;

P.S. If you could run PROC IML, try the code within attachement.

RE: Sumproduct referencing an external data set

gsisme — Wed, 21 Jun 2023 16:18:31 GMT

Thank you for taking the time to build the above - this is doing exactly what is needed. Much appreciated.