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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.