DATA Step, Macro, Functions and more

Please help me to improve the code. Thanks

Reply
N/A
Posts: 1

Please help me to improve the code. Thanks

1. the code is same for each year. Can we use a loop?

2. I want to use 1% 9% 10% ... to split the group. In this code below, I have to split the group manually for each year. Is there an easier and efficient way to do that?


Thank you very much!!!


proc import datafile="C:\Users\YUZIHAO\SAS\summer_intern\OM" dbms = xlsx replace out=OM;

  getnames = yes;

run;

data om1976;

set om (keep=name cusip ticker OM1976);

year=1976;

om=OM1976;

newom=input(om,8.);

drop om;

rename newom=om;

run;

data om1977;

set om (keep=name cusip ticker OM1977);

year=1977;

om=OM1977;

newom=input(om,8.);

drop om;

rename newom=om;

run;

data om1978;

set om (keep=name cusip ticker OM1978);

year=1978;

om=OM1978;

run;

data om1979;

set om (keep=name cusip ticker OM1979);

year=1979;

om=OM1979;

run;

data om1980;

set om (keep=name cusip ticker OM1980);

year=1980;

om=OM1980;

run;

data om1981;

set om (keep=name cusip ticker OM1981);

year=1981;

om=OM1981;

run;

data om1982;

set om (keep=name cusip ticker OM1982);

year=1982;

om=OM1982;

run;

data om1983;

set om (keep=name cusip ticker OM1983);

year=1983;

om=OM1983;

run;

data om1984;

set om (keep=name cusip ticker OM1984);

year=1984;

om=OM1984;

run;

data om1985;

set om (keep=name cusip ticker OM1985);

year=1985;

om=OM1985;

run;

data om1986;

set om (keep=name cusip ticker OM1986);

year=1986;

om=OM1986;

run;

data om1987;

set om (keep=name cusip ticker OM1987);

year=1987;

om=OM1987;

run;

data om1988;

set om (keep=name cusip ticker OM1988);

year=1988;

om=OM1988;

run;

data om1989;

set om (keep=name cusip ticker OM1989);

year=1989;

om=OM1989;

run;

data om1990;

set om (keep=name cusip ticker OM1990);

year=1990;

om=OM1990;

run;

data om1991;

set om (keep=name cusip ticker OM1991);

year=1991;

om=OM1991;

run;

data om1992;

set om (keep=name cusip ticker OM1992);

year=1992;

om=OM1992;

run;

data om1993;

set om (keep=name cusip ticker OM1993);

year=1993;

om=OM1993;

run;

data om1994;

set om (keep=name cusip ticker OM1994);

year=1994;

om=OM1994;

run;

data om1995;

set om (keep=name cusip ticker OM1995);

year=1995;

om=OM1995;

run;

data om1996;

set om (keep=name cusip ticker OM1996);

year=1996;

om=OM1996;

run;

data om1997;

set om (keep=name cusip ticker OM1997);

year=1997;

om=OM1997;

run;

data om1998;

set om (keep=name cusip ticker OM1998);

year=1998;

om=OM1998;

run;

data om1999;

set om (keep=name cusip ticker OM1999);

year=1999;

om=OM1999;

run;

data om2000;

set om (keep=name cusip ticker OM2000);

year=2000;

om=OM2000;

run;

data om2001;

set om (keep=name cusip ticker OM2001);

year=2001;

om=OM2001;

run;

data om2002;

set om (keep=name cusip ticker OM2002);

year=2002;

om=OM2002;

run;

data om2003;

set om (keep=name cusip ticker OM2003);

year=2003;

om=OM2003;

run;

data om2004;

set om (keep=name cusip ticker OM2004);

year=2004;

om=OM2004;

run;

data om2005;

set om (keep=name cusip ticker OM2005);

year=2005;

om=OM2005;

run;

data om2006;

set om (keep=name cusip ticker OM2006);

year=2006;

om=OM2006;

run;

data om2007;

set om (keep=name cusip ticker OM2007);

year=2007;

om=OM2007;

run;

data om2008;

set om (keep=name cusip ticker OM2008);

year=2008;

om=OM2008;

run;

data om2009;

set om (keep=name cusip ticker OM2009);

year=2009;

om=OM2009;

run;

data om2010;

set om (keep=name cusip ticker OM2010);

year=2010;

om=OM2010;

run;

data om2011;

set om (keep=name cusip ticker OM2011);

year=2011;

om=OM2011;

run;

data om2012;

set om (keep=name cusip ticker OM2012);

year=2012;

om=OM2012;

run;

data om2013;

set om (keep=name cusip ticker OM2013);

year=2013;

om=OM2013;

run;

data om1976;

set om1976 (drop=OM1976);

run;

data om1977;

set om1977 (drop=OM1977);

run;

data om1978;

set om1978 (drop=OM1978);

run;

data om1979;

set om1979 (drop=OM1979);

run;

data om1980;

set om1980 (drop=OM1980);

run;

data om1981;

set om1981 (drop=OM1981);

run;

data om1982;

set om1982 (drop=OM1982);

run;

data om1983;

set om1983 (drop=OM1983);

run;

data om1984;

set om1984 (drop=OM1984);

run;

data om1985;

set om1985 (drop=OM1985);

run;

data om1986;

set om1986 (drop=OM1986);

run;

data om1987;

set om1987 (drop=OM1987);

run;

data om1988;

set om1988 (drop=OM1988);

run;

data om1989;

set om1989 (drop=OM1989);

run;

data om1990;

set om1990 (drop=OM1990);

run;

data om1991;

set om1991 (drop=OM1991);

run;

data om1992;

set om1992 (drop=OM1992);

run;

data om1993;

set om1993 (drop=OM1993);

run;

data om1994;

set om1994 (drop=OM1994);

run;

data om1995;

set om1995 (drop=OM1995);

run;

data om1996;

set om1996 (drop=OM1996);

run;

data om1997;

set om1997 (drop=OM1997);

run;

data om1998;

set om1998 (drop=OM1998);

run;

data om1999;

set om1999 (drop=OM1999);

run;

data om2000;

set om2000 (drop=OM2000);

run;

data om2001;

set om2001 (drop=OM2001);

run;

data om2002;

set om2002 (drop=OM2002);

run;

data om2003;

set om2003 (drop=OM2003);

run;

data om2004;

set om2004 (drop=OM2004);

run;

data om2005;

set om2005 (drop=OM2005);

run;

data om2006;

set om2006 (drop=OM2006);

run;

data om2007;

set om2007 (drop=OM2007);

run;

data om2008;

set om2008 (drop=OM2008);

run;

data om2009;

set om2009 (drop=OM2009);

run;

data om2010;

set om2010 (drop=OM2010);

run;

data om2011;

set om2011 (drop=OM2011);

run;

data om2012;

set om2012 (drop=OM2012);

run;

data om2013;

set om2013 (drop=OM2013);

run;

proc sort data=om1976;

by ticker year;

proc sort data=om1977;

by ticker year;

proc sort data=om1978;

by ticker year;

proc sort data=om1979;

by ticker year;

proc sort data=om1980;

by ticker year;

proc sort data=om1981;

by ticker year;

proc sort data=om1982;

by ticker year;

proc sort data=om1983;

by ticker year;

proc sort data=om1984;

by ticker year;

proc sort data=om1985;

by ticker year;

proc sort data=om1986;

by ticker year;

proc sort data=om1987;

by ticker year;

proc sort data=om1988;

by ticker year;

proc sort data=om1989;

by ticker year;

proc sort data=om1990;

by ticker year;

proc sort data=om1991;

by ticker year;

proc sort data=om1992;

by ticker year;

proc sort data=om1993;

by ticker year;

proc sort data=om1994;

by ticker year;

proc sort data=om1995;

by ticker year;

proc sort data=om1996;

by ticker year;

proc sort data=om1997;

by ticker year;

proc sort data=om1998;

by ticker year;

proc sort data=om1999;

by ticker year;

proc sort data=om2000;

by ticker year;

proc sort data=om2001;

by ticker year;

proc sort data=om2002;

by ticker year;

proc sort data=om2003;

by ticker year;

proc sort data=om2004;

by ticker year;

proc sort data=om2005;

by ticker year;

proc sort data=om2006;

by ticker year;

proc sort data=om2007;

by ticker year;

proc sort data=om2008;

by ticker year;

proc sort data=om2009;

by ticker year;

proc sort data=om2010;

by ticker year;

proc sort data=om2011;

by ticker year;

proc sort data=om2012;

by ticker year;

proc sort data=om2013;

by ticker year;

data om_mv;

merge om1976 om1977 om1978 om1979 om1980 om1981 om1982 om1983 om1984

om1985 om1986 om1987 om1988 om1989 om1990 om1991 om1992 om1993 om1994

om1995 om1996 om1997 om1998 om1999 om2000 om2001 om2002 om2003 om2004

om2005 om2006 om2007 om2008 om2009 om2010 om2011 om2012 om2013;

by ticker year;

run;

proc sort data=om_mv;

  by ticker year;

proc expand data=om_mv(where=(om ne .)) out=MA_Alt method = none ;

  by ticker;

  convert om = om_mv5 / transformout = (movave 5);

RUN;

proc import datafile="C:\Users\YUZIHAO\SAS\summer_intern\mrt" dbms = xlsx replace out=mrt;

  getnames = yes;

run;

data mrt1985;

set mrt(keep=name ticker cusip industry ytr1986 marcap1985);

run;

data mv1985;

set ma_alt(where=(year=1985));

run;

proc sort data=mrt1985;

by ticker;

proc sort data=mv1985;

by ticker;

run;

data mv_1985;

merge mrt1985 mv1985;

by ticker;

run;

proc sort data=mv_1985;

by marcap1985;

run;

proc rank groups=4 out=quartiles1985 data=mv_1985;

   var marcap1985;

   ranks quartiles;

   run;

data mv1985;

set quartiles1985;

if quartiles=. then delete;

if quartiles=0 then delete;

if cmiss(of _all_) then delete;

run;

proc sort data=mv1985;

by om_mv5;

run;

proc rank groups=10 out=rank1985 data=mv1985;

   var om_mv5;

   ranks decile;

   run;

proc means data=rank1985 noprint nway ;

class decile;

var ytr1986;

output out=return1986 mean=ytr1986_mean;

run;

proc means data=mv1985 n p1 p10 p90 p99;

var om_mv5;

run;

proc means data=rank1985(where=(om_mv5<-83.3300000)) noprint;

var ytr1986;

output out=return1986_1 mean=ytr1986_mean;

run;

proc means data=rank1985(where=(-83.3300000<=om_mv5<3.1720000)) noprint;

var ytr1986;

output out=return1986_2 mean=ytr1986_mean;

run;

proc means data=rank1985(where=(30.1320000<=om_mv5<60.7400000)) noprint;

var ytr1986;

output out=return1986_3 mean=ytr1986_mean;

run;

proc means data=rank1985(where=(60.7400000<=om_mv5)) noprint;

var ytr1986;

output out=return1986_4 mean=ytr1986_mean;

run;

data return_1986;

set return1986_1 return1986_2 return1986 return1986_3 return1986_4;

run;

/*merge the om_mv and the return and marcap*/

data mrt1986;

set mrt(keep=name ticker cusip industry ytr1987 marcap1986);

run;

data mv1986;

set ma_alt(where=(year=1986));

run;

proc sort data=mrt1986;

by ticker;

proc sort data=mv1986;

by ticker;

run;

data mv_1986;

merge mrt1986 mv1986;

by ticker;

run;

proc sort data=mv_1986;

by marcap1986;

run;

proc rank groups=4 out=quartiles1986 data=mv_1986;

   var marcap1986;

   ranks quartiles;

   run;

data mv1986;

set quartiles1986;

if quartiles=. then delete;

if quartiles=0 then delete;

if cmiss(of _all_) then delete;

run;

/*calculate 10 portfolio return*/

proc sort data=mv1986;

by om_mv5;

run;

proc rank groups=10 out=rank1986 data=mv1986;

   var om_mv5;

   ranks decile;

   run;

proc means data=rank1986 noprint nway ;

class decile;

var ytr1987;

output out=return1987 mean=ytr1987_mean;

run;

/*calculate the 1% 9% */

proc means data=mv1986 n p1 p10 p90 p99;

var om_mv5;

run;

proc means data=rank1986(where=(om_mv5< -722.4400000)) noprint;

var ytr1987;

output out=return1987_1 mean=ytr1987_mean;

run;

proc means data=rank1986(where=(-722.4400000<=om_mv5<2.2280000)) noprint;

var ytr1987;

output out=return1987_2 mean=ytr1987_mean;

run;

proc means data=rank1986(where=(24.7000000<=om_mv5<46.1120000)) noprint;

var ytr1987;

output out=return1987_3 mean=ytr1987_mean;

run;

proc means data=rank1986(where=(46.1120000<=om_mv5)) noprint;

var ytr1987;

output out=return1987_4 mean=ytr1987_mean;

run;

data return_1987;

set return1987_1 return1987_2 return1987 return1987_3 return1987_4;

run;

/*1987om 1988return*/

data mrt1987;

set mrt(keep=name ticker cusip industry ytr1988 marcap1987);

run;

data mv1987;

set ma_alt(where=(year=1987));

run;

proc sort data=mrt1987;

by ticker;

proc sort data=mv1987;

by ticker;

run;

data mv_1987;

merge mrt1987 mv1987;

by ticker;

run;

proc sort data=mv_1987;

by marcap1987;

run;

proc rank groups=4 out=quartiles1987 data=mv_1987;

   var marcap1987;

   ranks quartiles;

   run;

data mv1987;

set quartiles1987;

if quartiles=. then delete;

if quartiles=0 then delete;

if cmiss(of _all_) then delete;

run;

proc sort data=mv1987;

by om_mv5;

run;

proc rank groups=10 out=rank1987 data=mv1987;

   var om_mv5;

   ranks decile;

   run;

proc means data=rank1987 noprint nway ;

class decile;

var ytr1988;

output out=return1988 mean=ytr1988_mean;

run;

proc means data=mv1987 n p1 p10 p90 p99;

var om_mv5;

run;

proc means data=rank1987(where=(om_mv5< -477.4800000)) noprint;

var ytr1988;

output out=return1988_1 mean=ytr1988_mean;

run;

proc means data=rank1987(where=(-477.4800000<=om_mv5<0.7820000)) noprint;

var ytr1988;

output out=return1988_2 mean=ytr1988_mean;

run;

proc means data=rank1987(where=(21.4860000<=om_mv5<42.7350000)) noprint;

var ytr1988;

output out=return1988_3 mean=ytr1988_mean;

run;

proc means data=rank1987(where=(42.7350000<=om_mv5)) noprint;

var ytr1988;

output out=return1988_4 mean=ytr1988_mean;

run;

data return_1988;

set return1988_1 return1988_2 return1988 return1988_3 return1988_4;

run;

/*1988om 1989return*/

data mrt1988;

set mrt(keep=name ticker cusip industry ytr1989 marcap1988);

run;

data mv1988;

set ma_alt(where=(year=1988));

run;

proc sort data=mrt1988;

by ticker;

proc sort data=mv1988;

by ticker;

run;

data mv_1988;

merge mrt1988 mv1988;

by ticker;

run;

proc sort data=mv_1988;

by marcap1988;

run;

proc rank groups=4 out=quartiles1988 data=mv_1988;

   var marcap1988;

   ranks quartiles;

   run;

data mv1988;

set quartiles1988;

if quartiles=. then delete;

if quartiles=0 then delete;

if cmiss(of _all_) then delete;

run;

proc sort data=mv1988;

by om_mv5;

run;

proc rank groups=10 out=rank1988 data=mv1988;

   var om_mv5;

   ranks decile;

   run;

proc means data=rank1988 noprint nway ;

class decile;

var ytr1989;

output out=return1989 mean=ytr1989_mean;

run;

proc means data=mv1988 n p1 p10 p90 p99;

var om_mv5;

run;

proc means data=rank1988(where=(om_mv5<-238.5900000)) noprint;

var ytr1989;

output out=return1989_1 mean=ytr1989_mean;

run;

proc means data=rank1988(where=(-238.5900000<=om_mv5<1.0020000)) noprint;

var ytr1989;

output out=return1989_2 mean=ytr1989_mean;

run;

proc means data=rank1988(where=(20.6540000<=om_mv5<41.5966667)) noprint;

var ytr1989;

output out=return1989_3 mean=ytr1989_mean;

run;

proc means data=rank1988(where=(41.5966667<=om_mv5)) noprint;

var ytr1989;

output out=return1989_4 mean=ytr1989_mean;

run;

data return_1989;

set return1989_1 return1989_2 return1989 return1989_3 return1989_4;

run;

/*1989om 1990return*/

data mrt1989;

set mrt(keep=name ticker cusip industry ytr1990 marcap1989);

run;

data mv1989;

set ma_alt(where=(year=1989));

run;

proc sort data=mrt1989;

by ticker;

proc sort data=mv1989;

by ticker;

run;

data mv_1989;

merge mrt1989 mv1989;

by ticker;

run;

proc sort data=mv_1989;

by marcap1989;

run;

proc rank groups=4 out=quartiles1989 data=mv_1989;

   var marcap1989;

   ranks quartiles;

   run;

data mv1989;

set quartiles1989;

if quartiles=. then delete;

if quartiles=0 then delete;

if cmiss(of _all_) then delete;

run;

proc sort data=mv1989;

by om_mv5;

run;

proc rank groups=10 out=rank1989 data=mv1989;

   var om_mv5;

   ranks decile;

   run;

proc means data=rank1989 noprint nway ;

class decile;

var ytr1990;

output out=return1990 mean=ytr1990_mean;

run;

proc means data=mv1989 n p1 p10 p90 p99;

var om_mv5;

run;

proc means data=rank1989(where=(om_mv5< -260.0000000)) noprint;

var ytr1990;

output out=return1990_1 mean=ytr1990_mean;

run;

proc means data=rank1989(where=(-260.0000000<=om_mv5<0.1820000)) noprint;

var ytr1990;

output out=return1990_2 mean=ytr1990_mean;

run;

proc means data=rank1989(where=(25.3766667<=om_mv5<55.2400000)) noprint;

var ytr1990;

output out=return1990_3 mean=ytr1990_mean;

run;

proc means data=rank1989(where=(55.2400000<=om_mv5)) noprint;

var ytr1990;

output out=return1990_4 mean=ytr1990_mean;

run;

data return_1990;

set return1990_1 return1990_2 return1990 return1990_3 return1990_4;

run;

/*1990om 1991return*/

data mrt1990;

set mrt(keep=name ticker cusip industry ytr1991 marcap1990);

run;

data mv1990;

set ma_alt(where=(year=1990));

run;

proc sort data=mrt1990;

by ticker;

proc sort data=mv1990;

by ticker;

run;

data mv_1990;

merge mrt1990 mv1990;

by ticker;

run;

proc sort data=mv_1990;

by marcap1990;

run;

proc rank groups=4 out=quartiles1990 data=mv_1990;

   var marcap1990;

   ranks quartiles;

   run;

data mv1990;

set quartiles1990;

if quartiles=. then delete;

if quartiles=0 then delete;

if cmiss(of _all_) then delete;

run;

proc sort data=mv1990;

by om_mv5;

run;

proc rank groups=10 out=rank1990 data=mv1990;

   var om_mv5;

   ranks decile;

   run;

proc means data=rank1990 noprint nway ;

class decile;

var ytr1991;

output out=return1991 mean=ytr1991_mean;

run;

proc means data=mv1990 n p1 p10 p90 p99;

var om_mv5;

run;

proc means data=rank1990(where=(om_mv5< -410.8300000)) noprint;

var ytr1991;

output out=return1991_1 mean=ytr1991_mean;

run;

proc means data=rank1990(where=(-410.8300000<=om_mv5<-0.6220000)) noprint;

var ytr1991;

output out=return1991_2 mean=ytr1991_mean;

run;

proc means data=rank1990(where=(25.8680000<=om_mv5<60.6600000)) noprint;

var ytr1991;

output out=return1991_3 mean=ytr1991_mean;

run;

proc means data=rank1990(where=(60.6600000<=om_mv5)) noprint;

var ytr1991;

output out=return1991_4 mean=ytr1991_mean;

run;

data return_1991;

set return1991_1 return1991_2 return1991 return1991_3 return1991_4;

run;

/*1991om 1992return*/

data mrt1991;

set mrt(keep=name ticker cusip industry ytr1992 marcap1991);

run;

data mv1991;

set ma_alt(where=(year=1991));

run;

proc sort data=mrt1991;

by ticker;

proc sort data=mv1991;

by ticker;

run;

data mv_1991;

merge mrt1991 mv1991;

by ticker;

run;

proc sort data=mv_1991;

by marcap1991;

run;

proc rank groups=4 out=quartiles1991 data=mv_1991;

   var marcap1991;

   ranks quartiles;

   run;

data mv1991;

set quartiles1991;

if quartiles=. then delete;

if quartiles=0 then delete;

if cmiss(of _all_) then delete;

run;

proc sort data=mv1991;

by om_mv5;

run;

proc rank groups=10 out=rank1991 data=mv1991;

   var om_mv5;

   ranks decile;

   run;

proc means data=rank1991 noprint nway ;

class decile;

var ytr1992;

output out=return1992 mean=ytr1992_mean;

run;

proc means data=mv1991 n p1 p10 p90 p99;

var om_mv5;

run;

proc means data=rank1991(where=(om_mv5<-520.2400000)) noprint;

var ytr1992;

output out=return1992_1 mean=ytr1992_mean;

run;

proc means data=rank1991(where=(-520.2400000<=om_mv5<0.3300000)) noprint;

var ytr1992;

output out=return1992_2 mean=ytr1992_mean;

run;

proc means data=rank1991(where=(25.2350000<=om_mv5<54.9150000)) noprint;

var ytr1992;

output out=return1992_3 mean=ytr1992_mean;

run;

proc means data=rank1991(where=(54.9150000<=om_mv5)) noprint;

var ytr1992;

output out=return1992_4 mean=ytr1992_mean;

run;

data return_1992;

set return1992_1 return1992_2 return1992 return1992_3 return1992_4;

run;

/*1992om 1993return*/

data mrt1992;

set mrt(keep=name ticker cusip industry ytr1993 marcap1992);

run;

data mv1992;

set ma_alt(where=(year=1992));

run;

proc sort data=mrt1992;

by ticker;

proc sort data=mv1992;

by ticker;

run;

data mv_1992;

merge mrt1992 mv1992;

by ticker;

run;

proc sort data=mv_1992;

by marcap1992;

run;

proc rank groups=4 out=quartiles1992 data=mv_1992;

   var marcap1992;

   ranks quartiles;

   run;

data mv1992;

set quartiles1992;

if quartiles=. then delete;

if quartiles=0 then delete;

if cmiss(of _all_) then delete;

run;

proc sort data=mv1992;

by om_mv5;

run;

proc rank groups=10 out=rank1992 data=mv1992;

   var om_mv5;

   ranks decile;

   run;

proc means data=rank1992 noprint nway ;

class decile;

var ytr1993;

output out=return1993 mean=ytr1993_mean;

run;

proc means data=mv1992 n p1 p10 p90 p99;

var om_mv5;

run;

proc means data=rank1992(where=(om_mv5<-693.7600000)) noprint;

var ytr1993;

output out=return1993_1 mean=ytr1993_mean;

run;

proc means data=rank1992(where=(-693.7600000<=om_mv5<-2.1900000)) noprint;

var ytr1993;

output out=return1993_2 mean=ytr1993_mean;

run;

proc means data=rank1992(where=(25.7820000<=om_mv5<62.4400000)) noprint;

var ytr1993;

output out=return1993_3 mean=ytr1993_mean;

run;

proc means data=rank1992(where=(62.4400000<=om_mv5)) noprint;

var ytr1993;

output out=return1993_4 mean=ytr1993_mean;

run;

data return_1993;

set return1993_1 return1993_2 return1993 return1993_3 return1993_4;

run;

/*1993om 1994return*/

data mrt1993;

set mrt(keep=name ticker cusip industry ytr1994 marcap1993);

run;

data mv1993;

set ma_alt(where=(year=1993));

run;

proc sort data=mrt1993;

by ticker;

proc sort data=mv1993;

by ticker;

run;

data mv_1993;

merge mrt1993 mv1993;

by ticker;

run;

proc sort data=mv_1993;

by marcap1993;

run;

proc rank groups=4 out=quartiles1993 data=mv_1993;

   var marcap1993;

   ranks quartiles;

   run;

data mv1993;

set quartiles1993;

if quartiles=. then delete;

if quartiles=0 then delete;

if cmiss(of _all_) then delete;

run;

proc sort data=mv1993;

by om_mv5;

run;

proc rank groups=10 out=rank1993 data=mv1993;

   var om_mv5;

   ranks decile;

   run;

proc means data=rank1993 noprint nway ;

class decile;

var ytr1994;

output out=return1994 mean=ytr1994_mean;

run;

proc means data=mv1993 n p1 p10 p90 p99;

var om_mv5;

run;

proc means data=rank1993(where=(om_mv5< -734.3100000)) noprint;

var ytr1994;

output out=return1994_1 mean=ytr1994_mean;

run;

proc means data=rank1993(where=(-734.3100000<=om_mv5<-1.1950000)) noprint;

var ytr1994;

output out=return1994_2 mean=ytr1994_mean;

run;

proc means data=rank1993(where=(25.7740000<=om_mv5<57.9700000)) noprint;

var ytr1994;

output out=return1994_3 mean=ytr1994_mean;

run;

proc means data=rank1993(where=(57.9700000<=om_mv5)) noprint;

var ytr1994;

output out=return1994_4 mean=ytr1994_mean;

run;

data return_1994;

set return1994_1 return1994_2 return1994 return1994_3 return1994_4;

run;

/*1994om 1995return*/

data mrt1994;

set mrt(keep=name ticker cusip industry ytr1995 marcap1994);

run;

data mv1994;

set ma_alt(where=(year=1994));

run;

proc sort data=mrt1994;

by ticker;

proc sort data=mv1994;

by ticker;

run;

data mv_1994;

merge mrt1994 mv1994;

by ticker;

run;

proc sort data=mv_1994;

by marcap1994;

run;

proc rank groups=4 out=quartiles1994 data=mv_1994;

   var marcap1994;

   ranks quartiles;

   run;

data mv1994;

set quartiles1994;

if quartiles=. then delete;

if quartiles=0 then delete;

if cmiss(of _all_) then delete;

run;

proc sort data=mv1994;

by om_mv5;

run;

proc rank groups=10 out=rank1994 data=mv1994;

   var om_mv5;

   ranks decile;

   run;

proc means data=rank1994 noprint nway ;

class decile;

var ytr1995;

output out=return1995 mean=ytr1995_mean;

run;

proc means data=mv1994 n p1 p10 p90 p99;

var om_mv5;

run;

proc means data=rank1994(where=(om_mv5< -374.3800000)) noprint;

var ytr1995;

output out=return1995_1 mean=ytr1995_mean;

run;

proc means data=rank1994(where=(-374.3800000<=om_mv5<0.9753333)) noprint;

var ytr1995;

output out=return1995_2 mean=ytr1995_mean;

run;

proc means data=rank1994(where=(28.1020000<=om_mv5<66.1900000)) noprint;

var ytr1995;

output out=return1995_3 mean=ytr1995_mean;

run;

proc means data=rank1994(where=(66.1900000<=om_mv5)) noprint;

var ytr1995;

output out=return1995_4 mean=ytr1995_mean;

run;

data return_1995;

set return1995_1 return1995_2 return1995 return1995_3 return1995_4;

run;

/*1995om 1996return*/

data mrt1995;

set mrt(keep=name ticker cusip industry ytr1996 marcap1995);

run;

data mv1995;

set ma_alt(where=(year=1995));

run;

proc sort data=mrt1995;

by ticker;

proc sort data=mv1995;

by ticker;

run;

data mv_1995;

merge mrt1995 mv1995;

by ticker;

run;

proc sort data=mv_1995;

by marcap1995;

run;

proc rank groups=4 out=quartiles1995 data=mv_1995;

   var marcap1995;

   ranks quartiles;

   run;

data mv1995;

set quartiles1995;

if quartiles=. then delete;

if quartiles=0 then delete;

if cmiss(of _all_) then delete;

run;

proc sort data=mv1995;

by om_mv5;

run;

proc rank groups=10 out=rank1995 data=mv1995;

   var om_mv5;

   ranks decile;

   run;

proc means data=rank1995 noprint nway ;

class decile;

var ytr1996;

output out=return1996 mean=ytr1996_mean;

run;

proc means data=mv1995 n p1 p10 p90 p99;

var om_mv5;

run;

proc means data=rank1995(where=(om_mv5< -810.2500000)) noprint;

var ytr1996;

output out=return1996_1 mean=ytr1996_mean;

run;

proc means data=rank1995(where=(-810.2500000<=om_mv5<-0.0225000)) noprint;

var ytr1996;

output out=return1996_2 mean=ytr1996_mean;

run;

proc means data=rank1995(where=(28.9980000<=om_mv5<67.4880000)) noprint;

var ytr1996;

output out=return1996_3 mean=ytr1996_mean;

run;

proc means data=rank1995(where=(67.4880000<=om_mv5)) noprint;

var ytr1996;

output out=return1996_4 mean=ytr1996_mean;

run;

data return_1996;

set return1996_1 return1996_2 return1996 return1996_3 return1996_4;

run;

/*1996om 1997return*/

data mrt1996;

set mrt(keep=name ticker cusip industry ytr1997 marcap1996);

run;

data mv1996;

set ma_alt(where=(year=1996));

run;

proc sort data=mrt1996;

by ticker;

proc sort data=mv1996;

by ticker;

run;

data mv_1996;

merge mrt1996 mv1996;

by ticker;

run;

proc sort data=mv_1996;

by marcap1996;

run;

proc rank groups=4 out=quartiles1996 data=mv_1996;

   var marcap1996;

   ranks quartiles;

   run;

data mv1996;

set quartiles1996;

if quartiles=. then delete;

if quartiles=0 then delete;

if cmiss(of _all_) then delete;

run;

proc sort data=mv1996;

by om_mv5;

run;

proc rank groups=10 out=rank1996 data=mv1996;

   var om_mv5;

   ranks decile;

   run;

proc means data=rank1996 noprint nway ;

class decile;

var ytr1997;

output out=return1997 mean=ytr1997_mean;

run;

proc means data=mv1996 n p1 p10 p90 p99;

var om_mv5;

run;

proc means data=rank1996(where=(om_mv5< -1948.48)) noprint;

var ytr1997;

output out=return1997_1 mean=ytr1997_mean;

run;

proc means data=rank1996(where=(-1948.48<=om_mv5<-7.6500000)) noprint;

var ytr1997;

output out=return1997_2 mean=ytr1997_mean;

run;

proc means data=rank1996(where=(30.8220000<=om_mv5<66.9250000)) noprint;

var ytr1997;

output out=return1997_3 mean=ytr1997_mean;

run;

proc means data=rank1996(where=(66.9250000<=om_mv5)) noprint;

var ytr1997;

output out=return1997_4 mean=ytr1997_mean;

run;

data return_1997;

set return1997_1 return1997_2 return1997 return1997_3 return1997_4;

run;

/*1997om 1998return*/

data mrt1997;

set mrt(keep=name ticker cusip industry ytr1998 marcap1997);

run;

data mv1997;

set ma_alt(where=(year=1997));

run;

proc sort data=mrt1997;

by ticker;

proc sort data=mv1997;

by ticker;

run;

data mv_1997;

merge mrt1997 mv1997;

by ticker;

run;

proc sort data=mv_1997;

by marcap1997;

run;

proc rank groups=4 out=quartiles1997 data=mv_1997;

   var marcap1997;

   ranks quartiles;

   run;

data mv1997;

set quartiles1997;

if quartiles=. then delete;

if quartiles=0 then delete;

if cmiss(of _all_) then delete;

run;

proc sort data=mv1997;

by om_mv5;

run;

proc rank groups=10 out=rank1997 data=mv1997;

   var om_mv5;

   ranks decile;

   run;

proc means data=rank1997 noprint nway ;

class decile;

var ytr1998;

output out=return1998 mean=ytr1998_mean;

run;

proc means data=mv1997 n p1 p10 p90 p99;

var om_mv5;

run;

proc means data=rank1997(where=(om_mv5< -2174.45)) noprint;

var ytr1998;

output out=return1998_1 mean=ytr1998_mean;

run;

proc means data=rank1997(where=(-2174.45<=om_mv5<-12.2250000)) noprint;

var ytr1998;

output out=return1998_2 mean=ytr1998_mean;

run;

proc means data=rank1997(where=(31.6750000<=om_mv5<68.3880000)) noprint;

var ytr1998;

output out=return1998_3 mean=ytr1998_mean;

run;

proc means data=rank1997(where=(68.3880000<=om_mv5)) noprint;

var ytr1998;

output out=return1998_4 mean=ytr1998_mean;

run;

data return_1998;

set return1998_1 return1998_2 return1998 return1998_3 return1998_4;

run;

/*1998om 1999return*/

data mrt1998;

set mrt(keep=name ticker cusip industry ytr1999 marcap1998);

run;

data mv1998;

set ma_alt(where=(year=1998));

run;

proc sort data=mrt1998;

by ticker;

proc sort data=mv1998;

by ticker;

run;

data mv_1998;

merge mrt1998 mv1998;

by ticker;

run;

proc sort data=mv_1998;

by marcap1998;

run;

proc rank groups=4 out=quartiles1998 data=mv_1998;

   var marcap1998;

   ranks quartiles;

   run;

data mv1998;

set quartiles1998;

if quartiles=. then delete;

if quartiles=0 then delete;

if cmiss(of _all_) then delete;

run;

proc sort data=mv1998;

by om_mv5;

run;

proc rank groups=10 out=rank1998 data=mv1998;

   var om_mv5;

   ranks decile;

   run;

proc means data=rank1998 noprint nway ;

class decile;

var ytr1999;

output out=return1999 mean=ytr1999_mean;

run;

proc means data=mv1998 n p1 p10 p90 p99;

var om_mv5;

run;

proc means data=rank1998(where=(om_mv5<-2129.85)) noprint;

var ytr1999;

output out=return1999_1 mean=ytr1999_mean;

run;

proc means data=rank1998(where=(-2129.85<=om_mv5<-16.2166667)) noprint;

var ytr1999;

output out=return1999_2 mean=ytr1999_mean;

run;

proc means data=rank1998(where=(31.5400000<=om_mv5<66.2400000)) noprint;

var ytr1999;

output out=return1999_3 mean=ytr1999_mean;

run;

proc means data=rank1998(where=(66.2400000<=om_mv5)) noprint;

var ytr1999;

output out=return1999_4 mean=ytr1999_mean;

run;

data return_1999;

set return1999_1 return1999_2 return1999 return1999_3 return1999_4;

run;

/*1999om 2000return*/

data mrt1999;

set mrt(keep=name ticker cusip industry ytr2000 marcap1999);

run;

data mv1999;

set ma_alt(where=(year=1999));

run;

proc sort data=mrt1999;

by ticker;

proc sort data=mv1999;

by ticker;

run;

data mv_1999;

merge mrt1999 mv1999;

by ticker;

run;

proc sort data=mv_1999;

by marcap1999;

run;

proc rank groups=4 out=quartiles1999 data=mv_1999;

   var marcap1999;

   ranks quartiles;

   run;

data mv1999;

set quartiles1999;

if quartiles=. then delete;

if quartiles=0 then delete;

if cmiss(of _all_) then delete;

run;

proc sort data=mv1999;

by om_mv5;

run;

proc rank groups=10 out=rank1999 data=mv1999;

   var om_mv5;

   ranks decile;

   run;

proc means data=rank1999 noprint nway ;

class decile;

var ytr2000;

output out=return2000 mean=ytr2000_mean;

run;

proc means data=mv1999 n p1 p10 p90 p99;

var om_mv5;

run;

proc means data=rank1999(where=(om_mv5< -4223.28)) noprint;

var ytr2000;

output out=return2000_1 mean=ytr2000_mean;

run;

proc means data=rank1999(where=(-4223.28<=om_mv5<-60.3425000)) noprint;

var ytr2000;

output out=return2000_2 mean=ytr2000_mean;

run;

proc means data=rank1999(where=(30.4500000<=om_mv5<66.9500000)) noprint;

var ytr2000;

output out=return2000_3 mean=ytr2000_mean;

run;

proc means data=rank1999(where=(66.9500000<=om_mv5)) noprint;

var ytr2000;

output out=return2000_4 mean=ytr2000_mean;

run;

data return_2000;

set return2000_1 return2000_2 return2000 return2000_3 return2000_4;

run;

/*2000 2001*/

data mrt2000;

set mrt(keep=name ticker cusip industry ytr2001 marcap2000);

run;

data mv2000;

set ma_alt(where=(year=2000));

run;

proc sort data=mrt2000;

by ticker;

proc sort data=mv2000;

by ticker;

run;

data mv_2000;

merge mrt2000 mv2000;

by ticker;

run;

proc sort data=mv_2000;

by marcap2000;

run;

proc rank groups=4 out=quartiles2000 data=mv_2000;

   var marcap2000;

   ranks quartiles;

   run;

data mv2000;

set quartiles2000;

if quartiles=. then delete;

if quartiles=0 then delete;

if cmiss(of _all_) then delete;

run;

proc sort data=mv2000;

by om_mv5;

run;

proc rank groups=10 out=rank2000 data=mv2000;

   var om_mv5;

   ranks decile;

   run;

proc means data=rank2000 noprint nway ;

class decile;

var ytr2001;

output out=return2001 mean=ytr2001_mean;

run;

proc means data=mv2000 n p1 p10 p90 p99;

var om_mv5;

run;

proc means data=rank2000(where=(om_mv5<-6127.08)) noprint;

var ytr2001;

output out=return2001_1 mean=ytr2001_mean;

run;

proc means data=rank2000(where=(-6127.08<=om_mv5<-90.0580000)) noprint;

var ytr2001;

output out=return2001_2 mean=ytr2001_mean;

run;

proc means data=rank2000(where=(30.9260000<=om_mv5<63.0666667)) noprint;

var ytr2001;

output out=return2001_3 mean=ytr2001_mean;

run;

proc means data=rank2000(where=(63.0666667<=om_mv5)) noprint;

var ytr2001;

output out=return2001_4 mean=ytr2001_mean;

run;

data return_2001;

set return2001_1 return2001_2 return2001 return2001_3 return2001_4;

run;

/*2001 2002*/

data mrt2001;

set mrt(keep=name ticker cusip industry ytr2002 marcap2001);

run;

data mv2001;

set ma_alt(where=(year=2001));

run;

proc sort data=mrt2001;

by ticker;

proc sort data=mv2001;

by ticker;

run;

data mv_2001;

merge mrt2001 mv2001;

by ticker;

run;

proc sort data=mv_2001;

by marcap2001;

run;

proc rank groups=4 out=quartiles2001 data=mv_2001;

   var marcap2001;

   ranks quartiles;

   run;

data mv2001;

set quartiles2001;

if quartiles=. then delete;

if quartiles=0 then delete;

if cmiss(of _all_) then delete;

run;

proc sort data=mv2001;

by om_mv5;

run;

proc rank groups=10 out=rank2001 data=mv2001;

   var om_mv5;

   ranks decile;

   run;

proc means data=rank2001 noprint nway ;

class decile;

var ytr2002;

output out=return2002 mean=ytr2002_mean;

run;

proc means data=mv2001 n p1 p10 p90 p99;

var om_mv5;

run;

proc means data=rank2001(where=(om_mv5<-7219.54)) noprint;

var ytr2002;

output out=return2002_1 mean=ytr2002_mean;

run;

proc means data=rank2001(where=(-7219.54<=om_mv5<-78.9266667)) noprint;

var ytr2002;

output out=return2002_2 mean=ytr2002_mean;

run;

proc means data=rank2001(where=(29.4700000<=om_mv5<55.5380000)) noprint;

var ytr2002;

output out=return2002_3 mean=ytr2002_mean;

run;

proc means data=rank2001(where=(55.5380000<=om_mv5)) noprint;

var ytr2002;

output out=return2002_4 mean=ytr2002_mean;

run;

data return_2002;

set return2002_1 return2002_2 return2002 return2002_3 return2002_4;

run;

/*2002 2003*/

data mrt2002;

set mrt(keep=name ticker cusip industry ytr2003 marcap2002);

run;

data mv2002;

set ma_alt(where=(year=2002));

run;

proc sort data=mrt2002;

by ticker;

proc sort data=mv2002;

by ticker;

run;

data mv_2002;

merge mrt2002 mv2002;

by ticker;

run;

proc sort data=mv_2002;

by marcap2002;

run;

proc rank groups=4 out=quartiles2002 data=mv_2002;

   var marcap2002;

   ranks quartiles;

   run;

data mv2002;

set quartiles2002;

if quartiles=. then delete;

if quartiles=0 then delete;

if cmiss(of _all_) then delete;

run;

proc sort data=mv2002;

by om_mv5;

run;

proc rank groups=10 out=rank2002 data=mv2002;

   var om_mv5;

   ranks decile;

   run;

proc means data=rank2002 noprint nway ;

class decile;

var ytr2003;

output out=return2003 mean=ytr2003_mean;

run;

proc means data=mv2002 n p1 p10 p90 p99;

var om_mv5;

run;

proc means data=rank2002(where=(om_mv5< -3682.18)) noprint;

var ytr2003;

output out=return2003_1 mean=ytr2003_mean;

run;

proc means data=rank2002(where=(-3682.18<=om_mv5<-45.2180000)) noprint;

var ytr2003;

output out=return2003_2 mean=ytr2003_mean;

run;

proc means data=rank2002(where=(30.8910000<=om_mv5<59.7900000)) noprint;

var ytr2003;

output out=return2003_3 mean=ytr2003_mean;

run;

proc means data=rank2002(where=(59.7900000<=om_mv5)) noprint;

var ytr2003;

output out=return2003_4 mean=ytr2003_mean;

run;

data return_2003;

set return2003_1 return2003_2 return2003 return2003_3 return2003_4;

run;

/*2003 2004*/

data mrt2003;

set mrt(keep=name ticker cusip industry ytr2004 marcap2003);

run;

data mv2003;

set ma_alt(where=(year=2003));

run;

proc sort data=mrt2003;

by ticker;

proc sort data=mv2003;

by ticker;

run;

data mv_2003;

merge mrt2003 mv2003;

by ticker;

run;

proc sort data=mv_2003;

by marcap2003;

run;

proc rank groups=4 out=quartiles2003 data=mv_2003;

   var marcap2003;

   ranks quartiles;

   run;

data mv2003;

set quartiles2003;

if quartiles=. then delete;

if quartiles=0 then delete;

if cmiss(of _all_) then delete;

run;

proc sort data=mv2003;

by om_mv5;

run;

proc rank groups=10 out=rank2003 data=mv2003;

   var om_mv5;

   ranks decile;

   run;

proc means data=rank2003 noprint nway ;

class decile;

var ytr2004;

output out=return2004 mean=ytr2004_mean;

run;

proc means data=mv2003 n p1 p10 p90 p99;

var om_mv5;

run;

proc means data=rank2003(where=(om_mv5<-3589.43)) noprint;

var ytr2004;

output out=return2004_1 mean=ytr2004_mean;

run;

proc means data=rank2003(where=(-3589.43<=om_mv5<-49.4900000)) noprint;

var ytr2004;

output out=return2004_2 mean=ytr2004_mean;

run;

proc means data=rank2003(where=(31.5920000<=om_mv5<61.6875000)) noprint;

var ytr2004;

output out=return2004_3 mean=ytr2004_mean;

run;

proc means data=rank2003(where=(61.6875000<=om_mv5)) noprint;

var ytr2004;

output out=return2004_4 mean=ytr2004_mean;

run;

data return_2004;

set return2004_1 return2004_2 return2004 return2004_3 return2004_4;

run;

/*2004 2005*/

data mrt2004;

set mrt(keep=name ticker cusip industry ytr2005 marcap2004);

run;

data mv2004;

set ma_alt(where=(year=2004));

run;

proc sort data=mrt2004;

by ticker;

proc sort data=mv2004;

by ticker;

run;

data mv_2004;

merge mrt2004 mv2004;

by ticker;

run;

proc sort data=mv_2004;

by marcap2004;

run;

proc rank groups=4 out=quartiles2004 data=mv_2004;

   var marcap2004;

   ranks quartiles;

   run;

data mv2004;

set quartiles2004;

if quartiles=. then delete;

if quartiles=0 then delete;

if cmiss(of _all_) then delete;

run;

proc sort data=mv2004;

by om_mv5;

run;

proc rank groups=10 out=rank2004 data=mv2004;

   var om_mv5;

   ranks decile;

   run;

proc means data=rank2004 noprint nway ;

class decile;

var ytr2005;

output out=return2005 mean=ytr2005_mean;

run;

proc means data=mv2004 n p1 p10 p90 p99;

var om_mv5;

run;

proc means data=rank2004(where=(om_mv5< -3090.32)) noprint;

var ytr2005;

output out=return2005_1 mean=ytr2005_mean;

run;

proc means data=rank2004(where=(-3090.32<=om_mv5<-44.8520000)) noprint;

var ytr2005;

output out=return2005_2 mean=ytr2005_mean;

run;

proc means data=rank2004(where=(31.9080000<=om_mv5<63.6680000)) noprint;

var ytr2005;

output out=return2005_3 mean=ytr2005_mean;

run;

proc means data=rank2004(where=(63.6680000<=om_mv5)) noprint;

var ytr2005;

output out=return2005_4 mean=ytr2005_mean;

run;

data return_2005;

set return2005_1 return2005_2 return2005 return2005_3 return2005_4;

run;

/*2005 2006*/

data mrt2005;

set mrt(keep=name ticker cusip industry ytr2006 marcap2005);

run;

data mv2005;

set ma_alt(where=(year=2005));

run;

proc sort data=mrt2005;

by ticker;

proc sort data=mv2005;

by ticker;

run;

data mv_2005;

merge mrt2005 mv2005;

by ticker;

run;

proc sort data=mv_2005;

by marcap2005;

run;

proc rank groups=4 out=quartiles2005 data=mv_2005;

   var marcap2005;

   ranks quartiles;

   run;

data mv2005;

set quartiles2005;

if quartiles=. then delete;

if quartiles=0 then delete;

if cmiss(of _all_) then delete;

run;

proc sort data=mv2005;

by om_mv5;

run;

proc rank groups=10 out=rank2005 data=mv2005;

   var om_mv5;

   ranks decile;

   run;

proc means data=rank2005 noprint nway ;

class decile;

var ytr2006;

output out=return2006 mean=ytr2006_mean;

run;

proc means data=mv2005 n p1 p10 p90 p99;

var om_mv5;

run;

proc means data=rank2005(where=(om_mv5<-4422.74)) noprint;

var ytr2006;

output out=return2006_1 mean=ytr2006_mean;

run;

proc means data=rank2005(where=(-4422.74<=om_mv5<-37.2325000)) noprint;

var ytr2006;

output out=return2006_2 mean=ytr2006_mean;

run;

proc means data=rank2005(where=(33.3620000<=om_mv5<68.5880000)) noprint;

var ytr2006;

output out=return2006_3 mean=ytr2006_mean;

run;

proc means data=rank2005(where=(68.5880000<=om_mv5)) noprint;

var ytr2006;

output out=return2006_4 mean=ytr2006_mean;

run;

data return_2006;

set return2006_1 return2006_2 return2006 return2006_3 return2006_4;

run;

/*2006 2007*/

data mrt2006;

set mrt(keep=name ticker cusip industry ytr2007 marcap2006);

run;

data mv2006;

set ma_alt(where=(year=2006));

run;

proc sort data=mrt2006;

by ticker;

proc sort data=mv2006;

by ticker;

run;

data mv_2006;

merge mrt2006 mv2006;

by ticker;

run;

proc sort data=mv_2006;

by marcap2006;

run;

proc rank groups=4 out=quartiles2006 data=mv_2006;

   var marcap2006;

   ranks quartiles;

   run;

data mv2006;

set quartiles2006;

if quartiles=. then delete;

if quartiles=0 then delete;

if cmiss(of _all_) then delete;

run;

proc sort data=mv2006;

by om_mv5;

run;

proc rank groups=10 out=rank2006 data=mv2006;

   var om_mv5;

   ranks decile;

   run;

proc means data=rank2006 noprint nway ;

class decile;

var ytr2007;

output out=return2007 mean=ytr2007_mean;

run;

proc means data=mv2006 n p1 p10 p90 p99;

var om_mv5;

run;

proc means data=rank2006(where=(om_mv5< -2835.72)) noprint;

var ytr2007;

output out=return2007_1 mean=ytr2007_mean;

run;

proc means data=rank2006(where=(-2835.72<=om_mv5<-21.7520000)) noprint;

var ytr2007;

output out=return2007_2 mean=ytr2007_mean;

run;

proc means data=rank2006(where=(34.2520000<=om_mv5<69.0600000)) noprint;

var ytr2007;

output out=return2007_3 mean=ytr2007_mean;

run;

proc means data=rank2006(where=(69.0600000<=om_mv5)) noprint;

var ytr2007;

output out=return2007_4 mean=ytr2007_mean;

run;

data return_2007;

set return2007_1 return2007_2 return2007 return2007_3 return2007_4;

run;

/*2007 2008*/

data mrt2007;

set mrt(keep=name ticker cusip industry ytr2008 marcap2007);

run;

data mv2007;

set ma_alt(where=(year=2007));

run;

proc sort data=mrt2007;

by ticker;

proc sort data=mv2007;

by ticker;

run;

data mv_2007;

merge mrt2007 mv2007;

by ticker;

run;

proc sort data=mv_2007;

by marcap2007;

run;

proc rank groups=4 out=quartiles2007 data=mv_2007;

   var marcap2007;

   ranks quartiles;

   run;

data mv2007;

set quartiles2007;

if quartiles=. then delete;

if quartiles=0 then delete;

if cmiss(of _all_) then delete;

run;

proc sort data=mv2007;

by om_mv5;

run;

proc rank groups=10 out=rank2007 data=mv2007;

   var om_mv5;

   ranks decile;

   run;

proc means data=rank2007 noprint nway ;

class decile;

var ytr2008;

output out=return2008 mean=ytr2008_mean;

run;

proc means data=mv2007 n p1 p10 p90 p99;

var om_mv5;

run;

proc means data=rank2007(where=(om_mv5< -3265.45)) noprint;

var ytr2008;

output out=return2008_1 mean=ytr2008_mean;

run;

proc means data=rank2007(where=( -3265.45<=om_mv5<-19.7320000)) noprint;

var ytr2008;

output out=return2008_2 mean=ytr2008_mean;

run;

proc means data=rank2007(where=(34.7420000<=om_mv5<66.1080000)) noprint;

var ytr2008;

output out=return2008_3 mean=ytr2008_mean;

run;

proc means data=rank2007(where=(66.1080000<=om_mv5)) noprint;

var ytr2008;

output out=return2008_4 mean=ytr2008_mean;

run;

data return_2008;

set return2008_1 return2008_2 return2008 return2008_3 return2008_4;

run;

/*2008 2009*/

data mrt2008;

set mrt(keep=name ticker cusip industry ytr2009 marcap2008);

run;

data mv2008;

set ma_alt(where=(year=2008));

run;

proc sort data=mrt2008;

by ticker;

proc sort data=mv2008;

by ticker;

run;

data mv_2008;

merge mrt2008 mv2008;

by ticker;

run;

proc sort data=mv_2008;

by marcap2008;

run;

proc rank groups=4 out=quartiles2008 data=mv_2008;

   var marcap2008;

   ranks quartiles;

   run;

data mv2008;

set quartiles2008;

if quartiles=. then delete;

if quartiles=0 then delete;

if cmiss(of _all_) then delete;

run;

proc sort data=mv2008;

by om_mv5;

run;

proc rank groups=10 out=rank2008 data=mv2008;

   var om_mv5;

   ranks decile;

   run;

proc means data=rank2008 noprint nway ;

class decile;

var ytr2009;

output out=return2009 mean=ytr2009_mean;

run;

proc means data=mv2008 n p1 p10 p90 p99;

var om_mv5;

run;

proc means data=rank2008(where=(om_mv5< -1978.29)) noprint;

var ytr2009;

output out=return2009_1 mean=ytr2009_mean;

run;

proc means data=rank2008(where=( -1978.29<=om_mv5<-12.5240000)) noprint;

var ytr2009;

output out=return2009_2 mean=ytr2009_mean;

run;

proc means data=rank2008(where=(33.4060000<=om_mv5<65.0180000)) noprint;

var ytr2009;

output out=return2009_3 mean=ytr2009_mean;

run;

proc means data=rank2008(where=(65.0180000<=om_mv5)) noprint;

var ytr2009;

output out=return2009_4 mean=ytr2009_mean;

run;

data return_2009;

set return2009_1 return2009_2 return2009 return2009_3 return2009_4;

run;

/*2009 2010*/

data mrt2009;

set mrt(keep=name ticker cusip industry ytr2010 marcap2009);

run;

data mv2009;

set ma_alt(where=(year=2009));

run;

proc sort data=mrt2009;

by ticker;

proc sort data=mv2009;

by ticker;

run;

data mv_2009;

merge mrt2009 mv2009;

by ticker;

run;

proc sort data=mv_2009;

by marcap2009;

run;

proc rank groups=4 out=quartiles2009 data=mv_2009;

   var marcap2009;

   ranks quartiles;

   run;

data mv2009;

set quartiles2009;

if quartiles=. then delete;

if quartiles=0 then delete;

if cmiss(of _all_) then delete;

run;

proc sort data=mv2009;

by om_mv5;

run;

proc rank groups=10 out=rank2009 data=mv2009;

   var om_mv5;

   ranks decile;

   run;

proc means data=rank2009 noprint nway ;

class decile;

var ytr2010;

output out=return2010 mean=ytr2010_mean;

run;

proc means data=mv2009 n p1 p10 p90 p99;

var om_mv5;

run;

proc means data=rank2009(where=(om_mv5< -3752.64)) noprint;

var ytr2010;

output out=return2010_1 mean=ytr2010_mean;

run;

proc means data=rank2009(where=(-3752.64<=om_mv5<-19.8840000)) noprint;

var ytr2010;

output out=return2010_2 mean=ytr2010_mean;

run;

proc means data=rank2009(where=(31.2400000<=om_mv5<63.7425000)) noprint;

var ytr2010;

output out=return2010_3 mean=ytr2010_mean;

run;

proc means data=rank2009(where=(63.7425000<=om_mv5)) noprint;

var ytr2010;

output out=return2010_4 mean=ytr2010_mean;

run;

data return_2010;

set return2010_1 return2010_2 return2010 return2010_3 return2010_4;

run;

/*2010 2011*/

data mrt2010;

set mrt(keep=name ticker cusip industry ytr2011 marcap2010);

run;

data mv2010;

set ma_alt(where=(year=2010));

run;

proc sort data=mrt2010;

by ticker;

proc sort data=mv2010;

by ticker;

run;

data mv_2010;

merge mrt2010 mv2010;

by ticker;

run;

proc sort data=mv_2010;

by marcap2010;

run;

proc rank groups=4 out=quartiles2010 data=mv_2010;

   var marcap2010;

   ranks quartiles;

   run;

data mv2010;

set quartiles2010;

if quartiles=. then delete;

if quartiles=0 then delete;

if cmiss(of _all_) then delete;

run;

proc sort data=mv2010;

by om_mv5;

run;

proc rank groups=10 out=rank2010 data=mv2010;

   var om_mv5;

   ranks decile;

   run;

proc means data=rank2010 noprint nway ;

class decile;

var ytr2011;

output out=return2011 mean=ytr2011_mean;

run;

proc means data=mv2010 n p1 p10 p90 p99;

var om_mv5;

run;

proc means data=rank2010(where=(om_mv5< -3505.68)) noprint;

var ytr2011;

output out=return2011_1 mean=ytr2011_mean;

run;

proc means data=rank2010(where=(-3505.68<=om_mv5<-18.1820000)) noprint;

var ytr2011;

output out=return2011_2 mean=ytr2011_mean;

run;

proc means data=rank2010(where=(30.9960000<=om_mv5<67.1520000)) noprint;

var ytr2011;

output out=return2011_3 mean=ytr2011_mean;

run;

proc means data=rank2010(where=(67.1520000<=om_mv5)) noprint;

var ytr2011;

output out=return2011_4 mean=ytr2011_mean;

run;

data return_2011;

set return2011_1 return2011_2 return2011 return2011_3 return2011_4;

run;

/*2011 2012*/

data mrt2011;

set mrt(keep=name ticker cusip industry ytr2012 marcap2011);

run;

data mv2011;

set ma_alt(where=(year=2011));

run;

proc sort data=mrt2011;

by ticker;

proc sort data=mv2011;

by ticker;

run;

data mv_2011;

merge mrt2011 mv2011;

by ticker;

run;

proc sort data=mv_2011;

by marcap2011;

run;

proc rank groups=4 out=quartiles2011 data=mv_2011;

   var marcap2011;

   ranks quartiles;

   run;

data mv2011;

set quartiles2011;

if quartiles=. then delete;

if quartiles=0 then delete;

if cmiss(of _all_) then delete;

run;

proc sort data=mv2011;

by om_mv5;

run;

proc rank groups=10 out=rank2011 data=mv2011;

   var om_mv5;

   ranks decile;

   run;

proc means data=rank2011 noprint nway ;

class decile;

var ytr2012;

output out=return2012 mean=ytr2012_mean;

run;

proc means data=mv2011 n p1 p10 p90 p99;

var om_mv5;

run;

proc means data=rank2011(where=(om_mv5<-2657.76)) noprint;

var ytr2012;

output out=return2012_1 mean=ytr2012_mean;

run;

proc means data=rank2011(where=( -2657.76<=om_mv5<-16.0100000)) noprint;

var ytr2012;

output out=return2012_2 mean=ytr2012_mean;

run;

proc means data=rank2011(where=(31.0300000<=om_mv5<68.3966667)) noprint;

var ytr2012;

output out=return2012_3 mean=ytr2012_mean;

run;

proc means data=rank2011(where=(68.3966667<=om_mv5)) noprint;

var ytr2012;

output out=return2012_4 mean=ytr2012_mean;

run;

data return_2012;

set return2012_1 return2012_2 return2012 return2012_3 return2012_4;

run;

/*2012 2013*/

data mrt2012;

set mrt(keep=name ticker cusip industry ytr2013 marcap2012);

run;

data mv2012;

set ma_alt(where=(year=2012));

run;

proc sort data=mrt2012;

by ticker;

proc sort data=mv2012;

by ticker;

run;

data mv_2012;

merge mrt2012 mv2012;

by ticker;

run;

proc sort data=mv_2012;

by marcap2012;

run;

proc rank groups=4 out=quartiles2012 data=mv_2012;

   var marcap2012;

   ranks quartiles;

   run;

data mv2012;

set quartiles2012;

if quartiles=. then delete;

if quartiles=0 then delete;

if cmiss(of _all_) then delete;

run;

proc sort data=mv2012;

by om_mv5;

run;

proc rank groups=10 out=rank2012 data=mv2012;

   var om_mv5;

   ranks decile;

   run;

proc means data=rank2012 noprint nway ;

class decile;

var ytr2013;

output out=return2013 mean=ytr2013_mean;

run;

proc means data=mv2012 n p1 p10 p90 p99;

var om_mv5;

run;

proc means data=rank2012(where=(om_mv5< -2351.01)) noprint;

var ytr2013;

output out=return2013_1 mean=ytr2013_mean;

run;

proc means data=rank2012(where=( -2351.01<=om_mv5<-16.7220000)) noprint;

var ytr2013;

output out=return2013_2 mean=ytr2013_mean;

run;

proc means data=rank2012(where=(31.5940000<=om_mv5<74.9360000)) noprint;

var ytr2013;

output out=return2013_3 mean=ytr2013_mean;

run;

proc means data=rank2012(where=(74.9360000<=om_mv5)) noprint;

var ytr2013;

output out=return2013_4 mean=ytr2013_mean;

run;

data return_2013;

set return2013_1 return2013_2 return2013 return2013_3 return2013_4;

run;

/*2013 2014*/

data mrt2013;

set mrt(keep=name ticker cusip industry ytr2014 marcap2013);

run;

data mv2013;

set ma_alt(where=(year=2013));

run;

proc sort data=mrt2013;

by ticker;

proc sort data=mv2013;

by ticker;

run;

data mv_2013;

merge mrt2013 mv2013;

by ticker;

run;

proc sort data=mv_2013;

by marcap2013;

run;

proc rank groups=4 out=quartiles2013 data=mv_2013;

   var marcap2013;

   ranks quartiles;

   run;

data mv2013;

set quartiles2013;

if quartiles=. then delete;

if quartiles=0 then delete;

if cmiss(of _all_) then delete;

run;

proc sort data=mv2013;

by om_mv5;

run;

proc rank groups=10 out=rank2013 data=mv2013;

   var om_mv5;

   ranks decile;

   run;

proc means data=rank2013 noprint nway ;

class decile;

var ytr2014;

output out=return2014 mean=ytr2014_mean;

run;

proc means data=mv2013 n p1 p10 p90 p99;

var om_mv5;

run;

proc means data=rank2013(where=(om_mv5<-3363.40)) noprint;

var ytr2014;

output out=return2014_1 mean=ytr2014_mean;

run;

proc means data=rank2013(where=( -3363.40<=om_mv5<-16.0240000)) noprint;

var ytr2014;

output out=return2014_2 mean=ytr2014_mean;

run;

proc means data=rank2013(where=(31.7120000<=om_mv5<72.1140000)) noprint;

var ytr2014;

output out=return2014_3 mean=ytr2014_mean;

run;

proc means data=rank2013(where=(72.1140000<=om_mv5)) noprint;

var ytr2014;

output out=return2014_4 mean=ytr2014_mean;

run;

data return_2014;

set return2014_1 return2014_2 return2014 return2014_3 return2014_4;

run;

data final_ommv5;

set return_1986 return_1987 return_1988 return_1989 return_1990 return_1991 return_1992

return_1993 return_1994 return_1995 return_1996 return_1997 return_1998 return_1999 return_2000

return_2001 return_2002 return_2003 return_2004 return_2005 return_2006 return_2007 return_2008 return_2009

return_2010 return_2011 return_2012 return_2013 return_2014;

run;

proc export data=final_ommv5 outfile="C:\Users\YUZIHAO\SAS\summer_intern\final_ommv5"

label dbms = xlsx replace;

run;

Super User
Posts: 17,865

Re: Please help me to improve the code. Thanks

Well the first step looks like a transpose that is done in an incredibly manual way.

 

data om1978;

set om (keep=name cusip ticker OM1978);

year=1978;

om=OM1978;

run;

 

Try replacing all of those steps with a single proc transpose instead for starters. I also don't understand how the merge would work, wouldnt' that overwrite the OM variables?  I would think you would have appended the data sets. I haven't had any caffiene yet for the day though....

Valued Guide
Posts: 858

Re: Please help me to improve the code. Thanks

[ Edited ]

Here's something to get throught he redundancy.  I haven't gone through the rest, a little explanation of what you have and what you are trying to achieve would be helpful:

 


data om1976;
set om (keep=name cusip ticker OM1976);
year=1976;
om=OM1976;
newom=input(om,8.);
drop om;
rename newom=om;
run;
data om1977;
set om (keep=name cusip ticker OM1977);
year=1977;
om=OM1977;
newom=input(om,8.);
drop om;
rename newom=om;
run;

%macro first;
%do i = 1978 %to 2013;
data om&i;
set om (keep=name cusip ticker OM&i);
year=&i;
om=OM&i;
run;
%end;
%mend;
%first;

/*I would wait to do this drop until after you have it all together, that way you can drop OM:*/
/*I would also wait to sort until everything is combined*/

%macro two;
data om_mv;
set
%do i= 1976 %to 2013;
om&i
%end;;
run;
%mend;
%two;

proc sort data=om_mv;by ticker year;


Ask a Question
Discussion stats
  • 2 replies
  • 163 views
  • 0 likes
  • 3 in conversation