calculate google and ibm beta and std_residual from market model
Posted: 13 August 2012 12:09 PM   [ Ignore ]
Sr. Member
RankRankRankRank
Total Posts:  169
Joined  2011-09-20

/*Compute the beta and std for google and IBM by market model */
*this code is built on the examples on WRDs.US;
*credits goes to Joost’s examples on WRDs.US;
*an example of using Examples from wrds.us ... smile ;

*local library;
libname beta “S:\projects\beta”;

/* details about the PC-connect (this is always the same),
it will trigger a login prompt */
%let wrds = wrds.wharton.upenn.edu 4016;
options comamid = TCP remote=WRDS;
signon username=_prompt_;

/* everything after ‘rsubmit’ will be executed on WRDS!! */
rsubmit; 
/* data is organized in libraries, locally, as well as on WRDS tell SAS to look in
‘/wrds/comp/sasdata/naa’ when you use ‘comp’ as the library name */
libname comp ‘/wrds/comp/sasdata/naa’;
PROC SQL;  create table example (keep = gvkey datadate fyear sich ceq prcc_f csho ni at) 
        as   select a.*  from comp.funda a where    
    a.gvkey in (“160329” “006066”)  and fyear = 2006
and indfmt=‘INDL’ and datafmt=‘STD’ and popsrc=‘D’ and consol=‘C’ ;
quit;

proc download data= example out= beta.a_google_ibm; run; 
/* stop processing statements on WRDS and continue working locally */
endrsubmit;

*2 obs, easy to handle;

rsubmit;
libname crsp (’/wrds/crsp/sasdata/cc’ ‘/wrds/crsp/sasdata/sd’);
libname ibes ‘/wrds/ibes/sasdata’;

proc upload data=beta.a_google_ibm out=getThese;

PROC SQL;
create table ccMerged as
select a.*, b.lpermno
from getThese a, crsp.ccmxpf_linktable b
where a.gvkey = b.gvkey
and b.lpermno ne .
and b.linktype in (“LC” “LN” “LU” “LX” “LD” “LS”)
and b.linkprim IN (“C”, “P”)
and ((a.datadate >= b.LINKDT) or b.LINKDT = .B) and
((a.datadate <= b.LINKENDDT) or b.LINKENDDT = .E) ;
quit;
/* downloading file for testing/debugging (is not necessary, since the next step is on WRDS also);*/
*proc download data=ccMerged out=myLib3.e_ccMerged;
/* match with dsenames for historic cusip (NCUSIP) ;*/
PROC SQL;
create table dsenames as
select a.*, b.ncusip
from ccMerged a, crsp.dsenames b
where
a.lpermno = b.PERMNO
and b.namedt <= a.datadate <= b.nameendt
and b.ncusip ne “”;
quit;

PROC SQL;
create table ibesdata as
select distinct a.*, b.ticker as ibes_ticker
from dsenames a, ibes.idsum b
where
a.NCUSIP = b.CUSIP
and a.datadate > b.SDATES
;
quit;
proc download data=ibesdata out= beta.with_keys;
run;

endrsubmit;

data with_keys;
set beta.with_keys;
key2 = gvkey||fyear;
run;

data getBeta (keep = key2 lpermno datadate month_1 month_30);
set with_keys;
month_1=INTNX(‘Month’,datadate,-30,‘B’);
month_30=INTNX(‘Month’,datadate,0,‘B’);
format month_1 date.;
format month_30 date.;
if missing(lpermno) then delete;
run;

libname crsp “S:\wrds_libs\crsp\sasdata\a_stock”;

PROC SQL;
create table returnData (keep = key2 lpermno datadate month_1 month_30 date ret)
as
select msf.*, b.*
from crsp.msf a, getBeta b
where b.month_1 <= a.date <= b.month_30
and a.permno = b.lpermno;
quit;

proc sort data = returnData nodup;by key2 date;quit;

data returnData2;
set returnData;
if RET gt -55; * missing: -66, -77, -88 etc;
if (1*RET eq RET) ; * must be numeric;
run;

libname indices “S:\wrds_libs\crsp\sasdata\a_indexes”;
/* append the return on the index*/
PROC SQL;
create table withIndex
(keep = key2 lpermno date ret vwretd) as
select a.*, b.*
from indices.msix a, returnData2 b
where a.caldt = b.date ;
quit;

/* make sure there are enough monthly returns to estimate beta */
proc sql;
create table numMonths as
select
distinct key2, count(*) as number_of_months
from
withIndex
group by key2;
quit;
/* go back to g_withIndex and only select those observations that have s
ufficient number of months; arbitrary cutoff: 12 months*/

proc sql;
create table readyToEstimate as
select a.*
from withIndex a, numMonths b
where a.key2 = b.key2
and b.number_of_months >=12
;
quit;


/* estimate beta’s */

proc sort data = readyToEstimate nodup;by key2 date;quit;

PROC REG OUTest = beta_est data= readyToEstimate;
ID key2;
MODEL ret = vwretd / NOPRINT ;
by key2;
RUN ;


/*/* the coefficients have the names of the variables,*/*/
/*/*hence beta equals vwretd in this dataset*/*/
/*/*renamed here and only keep the relevant variables: key and beta*/*/;

data beta_reported (keep = key2 alpha beta std_Res);
set beta_est;
alpha = intercept;
beta = vwretd;
Std_Res = _RMSE_;
run;

/* merge beta and standard deviation of the residual back */
proc sql;
create table got_beta as
select a.*, b.std_R, b.beta,b.alpha
from
  getBeta a
LEFT JOIN
  beta_reported b
on
a.key2 = b.key2;
quit;

 

 Signature 

Zenghui
A humble student of business

Profile
 
 
Posted: 13 August 2012 12:16 PM   [ Ignore ]   [ # 1 ]
Administrator
Avatar
RankRankRankRank
Total Posts:  901
Joined  2011-09-19

thanks! smile

 Signature 

To reply/post new questions: Please use the group WRDS/SAS on Google Groups! http://groups.google.com/d/forum/wrdssas

Profile