topic Re: How to skip missing value? in SAS Procedures

How to skip missing value?

thdang — Thu, 08 Nov 2012 21:58:41 GMT

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!

Re: How to skip missing value?

art297 — Thu, 08 Nov 2012 22:16:42 GMT

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

Re: How to skip missing value?

thdang — Thu, 08 Nov 2012 22:28:24 GMT

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

Re: How to skip missing value?

art297 — Thu, 08 Nov 2012 23:05:37 GMT

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;

Re: How to skip missing value?

SASKiwi — Thu, 08 Nov 2012 23:13:47 GMT

Something like this might do the trick:

create table want as

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

from test

;

Re: How to skip missing value?

art297 — Thu, 08 Nov 2012 23:41:24 GMT

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

Re: How to skip missing value?

thdang — Fri, 09 Nov 2012 12:18:43 GMT

Thank you. It works very well.