Dear all,
I produce a series of data sets that look like an upper left triangular matrix i.e.
K1 | K2 | K3 | K4 | K5 | K6 | K7 | K8 | |
R1 | 100 | 123 | 145 | 156 | 245 | 567 | 689 | 890 |
R2 | 34 | 98 | 210 | 234 | 345 | 450 | 509 | |
R3 | 22 | 45 | 67 | 78 | 68 | 89 | ||
R4 | 56 | 67 | 89 | 45 | 34 | |||
R5 | 145 | 256 | 324 | 456 | ||||
R6 | 76 | 89 | 121 | |||||
R7 | 59 | 88 | ||||||
R8 | 122 |
I would like to produce dynamically various averages based on the columns for each column from K1 through K8 i.e.
="Average of last 4=" | =AVERAGE(B7:B10) | =AVERAGE(C6:C9) | =AVERAGE(D5:D8) | =AVERAGE(E4:E7) | =AVERAGE(F3:F6) | =AVERAGE(G3:G5) | =AVERAGE(H3:H4) |
="Average of last 3=" | =AVERAGE(B8:B10) | =AVERAGE(C7:C9) | =AVERAGE(D6:D8) | =AVERAGE(E5:E7) | =AVERAGE(F4:F6) | =AVERAGE(G3:G5) | =AVERAGE(H3:H4) |
="Weighted Sum last 4=" | =SUM(C6:C9)/SUM(B6:B9) | =SUM(D5:D8)/SUM(C5:C8) | =SUM(E4:E7)/SUM(D4:D7) | =SUM(F3:F6)/SUM(E3:E6) | =SUM(G3:G5)/SUM(F3:F5) | =SUM(H3:H4)/SUM(G3:G4) | =SUM(I3:I3)/SUM(H3:H3) |
="Weighted Sum last 3=" | =SUM(C7:C9)/SUM(B7:B9) | =SUM(D6:D8)/SUM(C6:C8) | =SUM(E5:E7)/SUM(D5:D7) | =SUM(F4:F6)/SUM(E4:E6) | =SUM(G4:G5)/SUM(F4:F5) | =SUM(H4:H4)/SUM(G4:G4) | =SUM(I4:I4)/SUM(H4:H4) |
Furthermore both R(i) and K(j) vary, meaning that ( i ) and ( j )-- matrix dimensions -- may not be equal.
Thank you in advance
Nikos
How about:
data have; input K1 K2 K3 K4 K5 K6 K7 K8 ; cards; 100 123 145 156 245 567 689 890 34 98 210 234 345 450 509 . 22 45 67 78 68 89 . . 56 67 89 45 34 . . . 145 256 324 456 . . . . 76 89 121 . . . . . 59 88 . . . . . . 122 . . . . . . . ; run; data temp(drop= k:); set have; array x{*} K: ; n=_n_; do i=1 to dim(x); id=vname(x{i}); value=x{i}; if not missing(value) then output; end; run; proc sort data=temp;by i n;run; data temp1(drop=n value); set temp; by i; array four{0:3} _temporary_; array three{0:2} _temporary_; if last.i then do; lag_four_sum=sum(of four{*}); lag_three_sum=sum(of three{*}); end; four{mod(n,4)}=value; three{mod(n,3)}=value; if last.i then do; avg_four=mean(of four{*}); sum_four=sum(of four{*}); avg_three=mean(of three{*});sum_three=sum(of three{*}); output;call missing(of four{*} three{*}); end; run; data temp2(drop=_: lag_: sum_:); merge temp1 temp1(firstobs=2 keep=sum_four sum_three rename=(sum_four=_sum_four sum_three=_sum_three)); weighted_sum_four=divide(_sum_four,lag_four_sum); weighted_sum_three=divide(_sum_three,lag_three_sum); run; proc transpose data=temp2 out=want ; var avg_: weighted_: ; id id; run;
Xia Keshan
You need to give an example of what you want your results to look like. Is this supposed to result in a data set or a report?
I would like to be in both layouts (data and report
This is the example
….. | K1 | K2 | K3 | K4 | K5 | K6 | K7 | K8 |
R1 | 100 | 123 | 145 | 156 | 245 | 567 | 689 | 890 |
R2 | 34 | 98 | 210 | 234 | 345 | 450 | 509 | |
R3 | 22 | 45 | 67 | 78 | 68 | 89 | ||
R4 | 56 | 67 | 89 | 45 | 34 | |||
R5 | 145 | 256 | 324 | 456 | ||||
R6 | 76 | 89 | 121 | |||||
R7 | 59 | 88 | ||||||
R8 | 122 | |||||||
Average of last 4= | 100.50 | 125.00 | 150.25 | 203.25 | 173.00 | 368.67 | 599.00 | |
Average of last 3= | 85.67 | 144.33 | 178.00 | 193.00 | 149.00 | 368.67 | 599.00 | |
Weighted Sum last 4= | 1.49 | 1.32 | 1.18 | 1.35 | 1.68 | 1.18 | 1.29 | |
Weighted Sum last 3= | 1.55 | 1.30 | 1.21 | 1.25 | 1.31 | 1.13 | 1.29 |
How about:
data have; input K1 K2 K3 K4 K5 K6 K7 K8 ; cards; 100 123 145 156 245 567 689 890 34 98 210 234 345 450 509 . 22 45 67 78 68 89 . . 56 67 89 45 34 . . . 145 256 324 456 . . . . 76 89 121 . . . . . 59 88 . . . . . . 122 . . . . . . . ; run; data temp(drop= k:); set have; array x{*} K: ; n=_n_; do i=1 to dim(x); id=vname(x{i}); value=x{i}; if not missing(value) then output; end; run; proc sort data=temp;by i n;run; data temp1(drop=n value); set temp; by i; array four{0:3} _temporary_; array three{0:2} _temporary_; if last.i then do; lag_four_sum=sum(of four{*}); lag_three_sum=sum(of three{*}); end; four{mod(n,4)}=value; three{mod(n,3)}=value; if last.i then do; avg_four=mean(of four{*}); sum_four=sum(of four{*}); avg_three=mean(of three{*});sum_three=sum(of three{*}); output;call missing(of four{*} three{*}); end; run; data temp2(drop=_: lag_: sum_:); merge temp1 temp1(firstobs=2 keep=sum_four sum_three rename=(sum_four=_sum_four sum_three=_sum_three)); weighted_sum_four=divide(_sum_four,lag_four_sum); weighted_sum_three=divide(_sum_three,lag_three_sum); run; proc transpose data=temp2 out=want ; var avg_: weighted_: ; id id; run;
Xia Keshan
Dear Xia,
Thank you!
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.