Upper left triangular matrix - Dynamically create various averages for each column or combination of columns

[Nikos]

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

[ballardw]

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?

[Nikos]

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

[Ksharp · Accepted solution]

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

[Nikos]

Dear Xia,

Thank you!