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.
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.