market-weighted industry returns excluding ith firm

[amanjot_42]

Hi all,

I am trying to compute market value-weighted industry returns on a daily basis (7 years data). My sample contains returns for all the firms in the universe (firm_id), returns in percentage, industry_codes and market capitalization in dollars.

Sample

Firm_id     date            market_cap($)   returns       industry_code

11           02/04/2013      4500                   1.8              77

11           03/04/2013      4510                   -1                77

 ................

12          02/04/2013       6500                  2.5               88

12           03/04/2013      6530                   6                88

.................

15          02/04/2013       8500                  -6               77

15           03/04/2013      8530                   3               77

.................................

19          02/04/2013       15500                 4               77

19           03/04/2013      15530                -4               77

So, I want to compute industry returns for 02/04/2013 as - market_cap weighted returns for firms within the same industry_code but excluding the ith firm. For instance, industry returns for a firm coded 11 will be computed as (-6+4)/2 for 02/04/2013.

Regards,

Amanjot

[yabwon]

Hi,

do you mean something like below?

data have;
  input Firm_id date : ddmmyy10. market_cap returns industry_code;
  format date ddmmyyc10. market_cap dollar12.2;
cards4;
11 02/04/2013 4500 1.8 77
11           03/04/2013      4510                   -1                77
12          02/04/2013       6500                  2.5               88
12           03/04/2013      6530                   6                88
15          02/04/2013       8500                  -6               77
15           03/04/2013      8530                   3               77
19          02/04/2013       15500                 4               77
19           03/04/2013      15530                -4               77
;;;;
run;

proc sql;
create table want as
select 
  a.industry_code
, a.Firm_id
, a.date
, mean(b.returns) as marketValWghtdIndReturns
from
  have as a
  join
  have as b
  on 
  a.industry_code = b.industry_code
  and
  a.date = b.date
  and
  a.Firm_id ne b.Firm_id
group by 
  a.industry_code
, a.Firm_id
, a.date
order by
  a.industry_code
, a.Firm_id
, a.date
;
quit;
proc print data = want;
run;

All the best

Bart

[amanjot_42]

Hi,

Thanks for your email!

The code is not working as per expectations. The number of observations are increasing by 54 times of the original, and it's computing mean of all the firms without excluding the ith firm.

Regards,

[yabwon]

Hi,

Are there any additional assumptions on the data? Maybe I misunderstood your goal?

From the example data you provided results is:

                                        market
                                       ValWghtd
industry_                                Ind
   code      Firm_id          date     Returns

    77          11      02:04:2013       -1.0 /* for id=11 take data from Apr 2nd from id 15 and 19: (-6+2)/2 */
    77          11      03:04:2013       -0.5 /* for id=11 take data from Apr 3rd from id 15 and 19: (3+(-4))/2 */
    77          15      02:04:2013        2.9 /* ... */
    77          15      03:04:2013       -2.5
    77          19      02:04:2013       -2.1
    77          19      03:04:2013        1.0

Should it be different ?

All the best

Bart

[mkeintz]

I suggest you get industry totals via proc summary, as that's what it is mean for.

Then use the output of proc summary (data set need below) in combination with the original data set to get what you want.

Something like:

proc summary data=have noprint nway;
  class date ind;
  var ret
  weight mktcap;
  output out=need sum=_wgted_total  sumwgt=_total_wgt;
run;

%let min_ind=10;
%let max_ind=90;
%let begdate=01jan2013;
%let enddate=31dec2019;

data want (drop=_:);
  set need (in=inneed)
      have (in=inhave);
  array wgtsum {&min_ind:&max_ind,%sysevalf("&begdate"d):%sysevalf("&enddate"d)} _temporary_;
  array sumwgt {&min_ind:&max_ind,%sysevalf("&begdate"d):%sysevalf("&enddate"d)} _temporary_;
  array freq   {&min_ind:&max_ind,%sysevalf("&begdate"d):%sysevalf("&enddate"d)} _temporary_;	
  if inneed then do;
    wgtsum{ind,date}=_wgted_total;
	sumwgt{ind,date}=_total_wgt;
	freq{ind,date}=_freq_;
  end;
  if inhave=1;
  industry_ret=wgtsum{ind,date}/sumwgt{ind,date};
  if freq{ind,date}>1 then 
    indret_except_this_firm= (wgtsum{ind,date}-msrp*weight)/(sumwgt{ind,date}-weight);
run;

The proc summary code says to classify the statistics by DATE and IND.  The variable to be "summarized" is RET, weighted by MKTCAP.  You can specify many statistics to be put in the output data set.  Here I've requested only the weighted sum of RET,  and the sum of the weights.  I.e. the numerator and denominator of RET for each DATE*IND combination.

In the middle section set the macro variables to known upper and lower limints of industry code, and date range.

And the DATA WNAT step merely sticks the summary output into 2-way matrices.  These are then used when HAVE is re-read.

This program is untested, since you haven't provided a sample data set in sas data step form.