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

[miris]

Hi,

Does anyone could help to create such program?

Example is in attachment.

[Shmuel]

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;

[miris]

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

[Reeza · Accepted solution]

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;

[Shmuel]

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;