calculate weighted variables in proc tabulate or proc report

Reply
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;

 

Thanks in advance!

Super User
Posts: 11,134

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;

Ask a Question
Discussion stats
  • 3 replies
  • 268 views
  • 1 like
  • 3 in conversation