turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- General Programming
- /
- calculate weighted variables in proc tabulate or p...

Topic Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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!

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to triley

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.

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to ballardw

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!

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to triley

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;