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 |
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%
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.
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;
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%
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.