Solved
Contributor
Posts: 68

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

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(D58) =AVERAGE(E4:E7) =AVERAGE(F3:F6) =AVERAGE(G3:G5) =AVERAGE(H3:H4) ="Average of last 3=" =AVERAGE(B8:B10) =AVERAGE(C7:C9) =AVERAGE(D68) =AVERAGE(E5:E7) =AVERAGE(F4:F6) =AVERAGE(G3:G5) =AVERAGE(H3:H4) ="Weighted Sum last 4=" =SUM(C6:C9)/SUM(B6:B9) =SUM(D58)/SUM(C5:C8) =SUM(E4:E7)/SUM(D47) =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(D68)/SUM(C6:C8) =SUM(E5:E7)/SUM(D57) =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

Accepted Solutions
Solution
‎10-31-2014 05:53 AM
Super User
Posts: 10,770

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

```

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

All Replies
Super User
Posts: 13,521

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

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?

Contributor
Posts: 68

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

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
Solution
‎10-31-2014 05:53 AM
Super User
Posts: 10,770

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

```

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

Contributor
Posts: 68

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

Dear Xia,

Thank you!

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
• 4 replies
• 348 views
• 0 likes
• 3 in conversation