10-19-2015 01:29 PM
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!
10-19-2015 03:13 PM
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.
10-19-2015 03:18 PM
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!
10-20-2015 06:55 AM
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;