Solved
New Contributor
Posts: 4

# Need to create SAS program for formula sumproduct(A,B)/sum A BY GROUP X

Hi,

Does anyone could help to create such program?

Example is in attachment.

Accepted Solutions
Solution
‎12-13-2016 08:08 AM
Super User
Posts: 19,105

## Re: Need to create SAS program for formula sumproduct(A,B)/sum A BY GROUP X

You should be able to follow the logic to add the total of A and then add in the division if LAST.

SQL is another option

proc SQL;

create table want as

select a.X, sum(a*b)/sum(a)

from have

group by x;

quit;

Another possibility is proc means since this is similar as using a FREQ or WEIGHT STATEMENT.

proc means data=have sum wgtsum mean;

class x;

var b;

weight a;

run;

All Replies
Posts: 1,467

## Re: Need to create SAS program for formula sumproduct(A,B)/sum A BY GROUP X

Assuming you data is already in sas dataset:

data want;

set have;

by id group;

retain tot_sum;

if first.group then tot_sum = x * y;

else tot_sum = sum(of tot_sum, x * y);

if last.group then output;

run;

New Contributor
Posts: 4

## Re: Need to create SAS program for formula sumproduct(A,B)/sum A BY GROUP X

Thank you very much,

Yes, My data is already in dataset.

It is very helpful but it is not exactly what I need.

What I need is (A1*B1+A2*B2+A3*B3) divided by A1+A2+A3

for every group (X1,X2,X3)

thank you

Solution
‎12-13-2016 08:08 AM
Super User
Posts: 19,105

## Re: Need to create SAS program for formula sumproduct(A,B)/sum A BY GROUP X

You should be able to follow the logic to add the total of A and then add in the division if LAST.

SQL is another option

proc SQL;

create table want as

select a.X, sum(a*b)/sum(a)

from have

group by x;

quit;

Another possibility is proc means since this is similar as using a FREQ or WEIGHT STATEMENT.

proc means data=have sum wgtsum mean;

class x;

var b;

weight a;

run;

Posts: 1,467

## Re: Need to create SAS program for formula sumproduct(A,B)/sum A BY GROUP X

It uses same logic as before. You just need retain one more total and divide totals at last:

data want;

set have;

by id group;

retain tot_sum_mult  tot_sum_x;

if first.group then do;

tot_sum_x = x;

tot_sum_mult = x * y;

end; else do;

tot_sum_x = sum(of tot_sum_x, x);

tot_sum_mult = sum(of tot_sum_mult, x * y);

end;

if last.group then do;

result = tot_sum_mult / tot_sum_x;

output;

end;

drop tot_sum_mult tot_sum_x;

run;

☑ This topic is solved.