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

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

miris — Tue, 13 Dec 2016 10:20:55 GMT

Hi,

Does anyone could help to create such program?

Example is in attachment.

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

Shmuel — Tue, 13 Dec 2016 10:41:30 GMT

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;

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

miris — Tue, 13 Dec 2016 12:17:28 GMT

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

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

Reeza — Tue, 13 Dec 2016 12:27:34 GMT

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;

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

Shmuel — Tue, 13 Dec 2016 12:44:51 GMT

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;