Contributor
Posts: 50

# calculate weighted variables in proc tabulate or proc report

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;

Super User
Posts: 13,498

## Re: calculate weighted variables in proc tabulate or proc report

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.

Contributor
Posts: 50

## Re: calculate weighted variables in proc tabulate or proc report

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!

Contributor
Posts: 44

## Re: calculate weighted variables in proc tabulate or proc report

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;

Discussion stats
• 3 replies
• 287 views
• 1 like
• 3 in conversation