Hi, I want to calculate the weighted average of some variable the code looks like this
Proc sql;
Create table want as
Select date, id, sum(value) as value,
sum(Value1*Weight1)/sum(Weight1),
sum(Value2*Weight2)/sum(Weight2),
sum(Value3*Weight3)/sum(Weight3)
from sasdata.monthly_return_fund_summary_adj group by wficn, caldt;
quit;
but this one it also take into account the missing values. How can I adjust the code such that only observed values are use and all the missing values are skipped.
Thank you very much!
Something like this might do the trick:
create table want as
select sum(weight1 * value1)/sum(weight1 * (not missing(value1)))
from test
;
When what is missing? The Value variables, the Weight variables, The group by variables, or some combination of the above?
so when the value1 or value2 or value3 are missing (=.) they wouldn't get count. E.g.
weight1 value1
1 0.1
2 .
3 0.2
then I would like to have (1*0.1+3*0.2)/4 and not (1*0.1+3*0.2)/6
The only way I know how to do that in proc sql requires a lot of typing. Using proc summary would be a lot easier. However, since you asked, I think the following does what you want:
Proc sql;
create table want as
select date, id, sum(value) as value,
sum(Value1*Weight1)/sum(Weight1),
sum(Value2*Weight2)/sum(Weight2),
sum(Value3*Weight3)/sum(Weight3)
from (select wficn, caldt, date, id, value,
value1, value2, value3,
case
when missing(Value1) then 0
else Weight1
end as Weight1,
case
when missing(Value2) then 0
else Weight2
end as Weight2,
case
when missing(Value3) then 0
else Weight3
end as Weight3
from sasdata.monthly_return_fund_summary_adj)
group by wficn, caldt;
quit;
Something like this might do the trick:
create table want as
select sum(weight1 * value1)/sum(weight1 * (not missing(value1)))
from test
;
's offering is definitely better than the one I proposed. Expanded to your data it would be:
Proc sql;
create table want as
select date, id, sum(value) as value,
sum(weight1 * value1)/sum(weight1 * (not missing(value1))),
sum(weight2 * value2)/sum(weight2 * (not missing(value2))),
sum(weight3 * value3)/sum(weight3 * (not missing(value3)))
from sasdata.monthly_return_fund_summary_adj
group by wficn, caldt
;
quit;
Interestingly, related to a comment I made the other day (to some thread), my proposed code was directly from the SAS documentation/examples.
Thank you. It works very well.
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 the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.