General SAS Programming

[triley]

I have a dataset (provided below) that I'm trying do a tabulate or a proc report on to find the average by group. The part i'm having trouble with is that I need to calculate a variable using the values of the other variables and display that. The "a" and "b" variables are the grouping variables.

The value that I need to calculate using the data below is z, and z = (y-x)/w

The final product needs to look like this (the table values are of "z"):

	b
a	1	2	Grand Total
0.05	3.5%	4.7%	4.1%
0.1	4.8%	4.6%	4.7%
0.15	2.9%	0.7%	1.8%
Grand Total	3.7%	3.4%	3.5%

data test;
   infile datalines delimiter=',';
input a b w x y;
   datalines;
.05,1,100,80,70
.05,1,90,70,80
.05,1,95,80,90
.1,1,92,80,70
.1,1,87,70,80
.1,1,94,80,93
.15,1,101,80,78
.15,1,110,75,83
.15,1,103,87,90
.05,2,92,80,70
.05,2,87,70,80
.05,2,95,80,93
.1,2,111,85,78
.1,2,110,78,87
.1,2,104,77,90
.15,2,100,80,73
.15,2,92,78,80
.15,2,90,83,90
;
run;

Thanks in advance!

[ballardw]

I would be very tempted to calculate the Z variable in a datastep and then summarize that. Proc Tabulate will not do what you are wanting.

[triley]

That doesn't exactly work because of the weighting...if I just calculated z before hand, I would lose the accuracy of the weights (since i would in essence be doing an "average of an average" for each group which isn't mathmatically what i'm looking for.

Thank you for your suggestion though!

[AskoLötjönen]

So, I'm assuming that if you are calculating Averages for X, Y and W by groups you are getting correct answers.

Tabulate result is quite ugly, but you can finalize it.

proc sql;
  create table want1 as
  select case when a=.05 then '.05  '
              when a=.1  then '.1   '
              when a=.15 then '.15  '
         end as ACol,
         case when b=1 then '1    '
              when b=2 then '2    '
          end as Bcol,
         (mean(y)-Mean(x))/Mean(w) as z
  from   test
  group by a, b
  union
  select case when a=.05 then '.05  '
              when a=.1  then '.1   '
              when a=.15 then '.15  '
         end as ACol,
         'Total'  as BCol,
         (mean(y)-Mean(x))/Mean(w) as z
  from   test
  group by a
  union
  select 'Total' as ACol,
         case when b=1 then '1    '
              when b=2 then '2    '
         end as Bcol,
         (mean(y)-Mean(x))/Mean(w) as z
  from   test
  group by b
  union
  select 'Total' as ACol,
         'Total'  as BCol,
         (mean(y)-Mean(x))/Mean(w) as z
  from   test;
quit;

proc tabulate data=want1;
  class Acol bcol;
  var z;
  table acol,bcol*z*f=4.3;
  run;