Excluding current observation in calculating a weighted average by a group

[JMiraglia]

Hi all -

Thanks in advance for any guidance you may provide. I have searched through the forums to find specifically what I was looking for and could not, so please point me to the page if you can.

Where as WAVG by product is simply sum(risk*quantity)/sum(quantity)

And WAVG by Product (excluding current ID) follows the same logic sans ID 1 for example.

If you could provide me some guidance as to how to implement the WAVG by Product (excluding the current ID), this would be very helpful.

Thanks,

Jeremy

ID	Product	Risk	Quantity	WAVG by Product	WAVG by Product (excluding Current ID)
1	A	10%	200	0.222222222	0.257142857
2	A	20%	300	0.222222222	0.233333333
3	A	30%	400	0.222222222	0.16
4	B	30%	500	0.526923077	0.580952381
5	B	40%	600	0.526923077	Did not calc
6	B	60%	700	0.526923077	Did not calc
7	B	70%	800	0.526923077	0.45

[Astounding]

One approach would be to accumulate the total numerator and total denominator for each product.  Then compute by subtracting the values in the current observation from the numerator and denominator.  For example:

data want;
total_numerator = 0;
total_denominator = 0;
do until (last.product) ;
   set have;
   by product;
   total_numerator + risk * quantity;
   total_denominator + quantity;
end;
do until (last.product) ;
   set have;
   by product;
   wavg = (total_numerator - risk * quantity) / (total_denominator - quantity);
   output;
end;
run;

The top loop reads all observations for a PRODUCT.

The bottom loop reads exactly the same observations as the top loop, calculates, and outputs.

Note that multiplication gets performed before subtraction, so the calculations execute properly.  It wouldn't hurt to add parentheses to clarify that.

Of course your percentages have to be actual numbers.  A value such as 0.3 would be fine, but a character string such as "30%" will not work.

[novinosrin]

SQL 

data have;
input ID $	Product $	Risk percent.	Quantity;*	WAVG by Product	WAVG by Product (excluding Current ID) ;
format risk percent5.;
cards;
1	A	10%	200	0.222222222	0.257142857
2	A	20%	300	0.222222222	0.233333333
3	A	30%	400	0.222222222	0.16
4	B	30%	500	0.526923077	0.580952381
5	B	40%	600	0.526923077	Did not calc
6	B	60%	700	0.526923077	Did not calc
7	B	70%	800	0.526923077	0.45
;

proc sql;
create table want as
select a.* ,sum(b.risk*b.quantity)/sum(b.quantity) as wavg2
from 
(select *, sum(risk*quantity)/sum(quantity) as wavg1 from have a group by product) a left join have b
on a.product=b.product and a.id ne b.id
group by a.product,a.id,a.risk,a.quantity;
quit;

[PGStats · Accepted solution]

Use SAS/SQL auto-remerging to your advantage:

proc sql;
select id, product,
    sum(risk*quantity)/sum(quantity) as wavg format=percent8.2,
    (sum(risk*quantity)-risk*quantity) / 
    (sum(quantity)-quantity) as wavgProd format=percent8.2
from have
group by product
order by product, id;
quit;

                       ID        Product       wavg  wavgProd
                       --------------------------------------
                       1         A          22.22%    25.71%
                       2         A          22.22%    23.33%
                       3         A          22.22%    16.00%
                       4         B          52.69%    58.10%
                       5         B          52.69%    56.50%
                       6         B          52.69%    50.00%
                       7         B          52.69%    45.00%

[JMiraglia]

Thank you so much! This worked like a charm.

Thanks for all the suggestions as well -- really helped me look at my code critically.

JM