topic Re: Upper left triangular matrix - Dynamically create various averages for each column or combination of columns in SAS Procedures

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

Nikos — Thu, 30 Oct 2014 17:37:32 GMT

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

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

ballardw — Thu, 30 Oct 2014 17:50:31 GMT

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?

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

Nikos — Thu, 30 Oct 2014 18:08:06 GMT

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

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

Ksharp — Fri, 31 Oct 2014 09:53:22 GMT

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

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

Nikos — Sun, 02 Nov 2014 13:39:16 GMT

Dear Xia,

Thank you!

	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

	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

	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