Help using Base SAS procedures

How to skip missing value?

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 75
Accepted Solution

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,252

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

;


View solution in original post


All Replies
PROC Star
Posts: 7,468

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: 7,468

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

              from sasdata.monthly_return_fund_summary_adj)

          group by wficn, caldt;

quit;

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

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: 7,468

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

      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.

Frequent Contributor
Posts: 75

Re: How to skip missing value?

Thank you. It works very well.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 321 views
  • 6 likes
  • 3 in conversation