how to calculate weighted average but exclude the object itself using SAS

Question

There are four variables in my dataset. Company shows the company's name. Return is the return of Company at day Date. Weight is the weight of this company in the market.

I want to keep all variables in the original file, and create an additional variable which is the market return (exclude Company itself). Market return corresponding for stock 'a' is the sum of all weighted stocks' return at the same Date in the market exclude stock a. For example, if there are 3 stocks in the market a, b and c. Market Return for stock a is Return(b)* [Weight(b)/(weight(b)+weight(C))] + Return(C)* [weight(C)/(weight(b)+weight(C)]. Similarly, Market Return for stock b is Return(a)* [Weight(a)/(weight(a)+weight(C))] + Return(C)* [weight(C)/(weight(a)+weight(C)].

I try to use proc summary but this function cannot exclude stock a when calculate the market return for stock a.

PROC SUMMARY NWAY DATA  ; 
    CLASS Date ; 

    VAR Return / WEIGHT = weight;

    OUTPUT
       OUT = output
       MEAN (Return) = MarketReturn;
RUN;

Could anyone teach me how to solve this please. I am relatively new to this software, so I dont know if I should use loop or there might be some better alternative.

Joe Joe · Accepted Answer · 2015-06-16T16:29:48

This can be done with a bit of fancy algebra. It's not something that's built-in, though.

Basically:

Construct a "total" market return
Construct a stock by stock return (so just return of A)
Subtract out the portion that A contributes to total.

Thanks to the simple math that generates these lists, it's quite easy to do this.

Total sum = ((mean of A*Awgt) + (mean of remainder*sum of their weights))/(sum of Awgt + sum of rest wgts)

So, solve that for (mean of rest*mean of rest wgts / sum of rest wgts).

Exclusive sum: ((mean of all * sum of all wgts) - (mean of A * sum of A wgts)) / (sum of all wgts - sum of A wgts)

Something like this.

data returns;
  input stock $ return weight;
  datalines;
A .50 1
B .75 2
C .33 1
;;;;
run;

proc means data=returns;
  class stock;
  types () stock; *this is the default;
  weight weight;
  output out=means_out mean= sumwgt= /autoname;
run;

data returns_excl;
  if _n_=1 then set means_out(where=(_type_=0) rename=(return_mean=tot_return return_sumwgt=tot_wgts));
  set means_out(where=(_type_=1));
  return_excl = (tot_return*tot_wgts-return_mean*return_sumwgt)/(tot_wgts-return_sumwgt);
run;

how to calculate weighted average but exclude the object itself using SAS

1 Answers