How to skip missing value?

Solved
Frequent Contributor
Posts: 75

How to skip missing value?

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!

Accepted Solutions
Solution
‎11-08-2012 06:13 PM
Super User
Posts: 3,918

Re: How to skip missing value?

Something like this might do the trick:

create table want as

select sum(weight1 * value1)/sum(weight1 * (not missing(value1)))

from test

;

All Replies
PROC Star
Posts: 8,164

Re: How to skip missing value?

When what is missing?  The Value variables, the Weight variables, The group by variables, or some combination of the above?

Frequent Contributor
Posts: 75

Re: How to skip missing value?

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

PROC Star
Posts: 8,164

Re: How to skip missing value?

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

group by wficn, caldt;

quit;

Solution
‎11-08-2012 06:13 PM
Super User
Posts: 3,918

Re: How to skip missing value?

Something like this might do the trick:

create table want as

select sum(weight1 * value1)/sum(weight1 * (not missing(value1)))

from test

;

PROC Star
Posts: 8,164

Re: How to skip missing value?

'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)))

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.

Frequent Contributor
Posts: 75

Re: How to skip missing value?

Thank you. It works very well.

🔒 This topic is solved and locked.