Help using Base SAS procedures

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

Accepted Solution Solved
Reply
Contributor
Posts: 68
Accepted Solution

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.

K1K2K3K4K5K6K7K8
R1100123145156245567689890
R23498210234345450509
R3224567786889
R45667894534
R5145256324456
R67689121
R75988
R8122

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(D5Smiley Very Happy8)=AVERAGE(E4:E7)=AVERAGE(F3:F6)=AVERAGE(G3:G5)=AVERAGE(H3:H4)
="Average of last 3="=AVERAGE(B8:B10)=AVERAGE(C7:C9)=AVERAGE(D6Smiley Very Happy8)=AVERAGE(E5:E7)=AVERAGE(F4:F6)=AVERAGE(G3:G5)=AVERAGE(H3:H4)
="Weighted Sum last 4="=SUM(C6:C9)/SUM(B6:B9)=SUM(D5Smiley Very Happy8)/SUM(C5:C8)=SUM(E4:E7)/SUM(D4Smiley Very Happy7)=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(D6Smiley Very Happy8)/SUM(C6:C8)=SUM(E5:E7)/SUM(D5Smiley Very Happy7)=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,046

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

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

View solution in original post


All Replies
Super User
Posts: 11,343

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

…..K1K2K3K4K5K6K7K8
R1100123145156245567689890
R23498210234345450509
R3224567786889
R45667894534
R5145256324456
R67689121
R75988
R8122
Average of last 4=100.50125.00150.25203.25173.00368.67599.00
Average of last 3=85.67144.33178.00193.00149.00368.67599.00
Weighted Sum last 4=1.491.321.181.351.681.181.29
Weighted Sum last 3=1.551.301.211.251.311.131.29
Solution
‎10-31-2014 05:53 AM
Super User
Posts: 10,046

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

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

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
  • 309 views
  • 0 likes
  • 3 in conversation