0

I have three tables which look look as follows: 

     A   B   C             A   B   C              A    B    C     
D    1   2   3        D    2   3   4         D    3    4    5
E    4   5   6        E    5   6   7         E    6    7    8 
F    7   8   9        F    8   9   10        F    9    10   11

I want to find the average of these tables on a cell by cell basis in SAS. So output should look as follows: 

     A   B   c
D    2   3   4
E    5   6   7 
F    8   9   10

So the first cell would for example be calculated as follows (1 + 2 + 3) / 3 = 3

At the moment I'm totally stumped about how to do this, but I'm looking at proc means and proc freq, which seem promising.

Thanks up front for the help.

Martin Reindl
  • 989
  • 2
  • 15
  • 33

1 Answers1

1

In SAS/IML this is trivial.

proc iml;
  a = {1 2 3,4 5 6,7 8 9};
  b = {2 3 4,5 6 7,8 9 10};
  c = {3 4 5,6 7 8,9 10 11};
  d = (a+b+c)/3;
 print a b c d;
quit;

In SQL or base SAS there are ways to go about it, also, assuming you typo'ed the third dataset (D F G should be D E F). If you didn't typo that, then it's harder.

data a;
input ID $ a b c;
datalines;
D    1   2   3 
E    4   5   6 
F    7   8   9 
;;;;
run;

data b;
input ID $ a b c;
datalines;
D    2   3   4   
E    5   6   7   
F    8   9   10  
;;;
run;

data c;
input ID $ a b c;
datalines;
D    3    4    5
E    6    7    8 
F    9    10   11
;;;;
run;

data for_d;
  set a b c;
  by id;
run;

proc means data=for_d;
  var a b c;
  class ID;
  output out=d mean=;
run;

The SQL solution is a bit more typing, but ultimately similar (and you can skip the for_d step). Just join a,b,c together and calculate the mean in the query.

Joe
  • 62,789
  • 6
  • 49
  • 67
  • Brilliant. I did actually typo that but thanks for the full response. You just saved me hours of trying to solve this on my own. Thanks so much! – Martin Reindl Feb 20 '17 at 17:05