BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
thdang
Calcite | Level 5

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!

1 ACCEPTED SOLUTION

Accepted Solutions
SASKiwi
PROC Star

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

6 REPLIES 6
art297
Opal | Level 21

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

thdang
Calcite | Level 5

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

art297
Opal | Level 21

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;

SASKiwi
PROC Star

Something like this might do the trick:

  create table want as

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

  from test

;


art297
Opal | Level 21

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

thdang
Calcite | Level 5

Thank you. It works very well.

sas-innovate-2024.png

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.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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