SAS在金融中的应用三

第一题：

导入数据上证综指，试计算2020期间各月上证综指的单期百分比收益率，算术平均收益率 和几何平均收益率，以及财富指数。（要求：采用retain语句和transpose语句两种形式）

/*导入数据上证综指，试计算2020期间各月上证综指的单期百分比收益率，
算术平均收益率 和几何平均收益率，以及财富指数。（要求：采用retain语句和transpose语句两种形式）*/
data WORK.szzz_1a;                                                                                    
      %let _EFIERR_ = 0; /* set the ERROR detection macro variable */                                                                    
      infile 'D:\mySASDATA\上证综指.csv' delimiter = ',' MISSOVER DSD lrecl=32767 firstobs=2 ;                      
      informat VAR1 $9. ;                                                                                                           
      informat VAR2 $8. ;                                                                                                              
      informat VAR3 yymmdd10. ;                                                                                                       
      informat VAR4 comma12.0 ;                                                                                                      
      format VAR1 $9. ;                                                                                                             
      format VAR2 $8. ;                                                                                                               
      format VAR3 yymmdd10. ;                                                                                                       
      format VAR4 best12. ;                                                                                                         
      input   VAR1 $  VAR2 $    VAR3      VAR4 ;                                                                                                                                                                                                                                                                                                  
      if _ERROR_ then call symputx('_EFIERR_',1);  /* set ERROR detection macro variable */      
 run; 
/*proc import datafile="D:\mySASDATA\上证综指.csv"             
        out=szzz_1a
        dbms=csv
        replace;
		getnames=yes;
run;
*/
data szzz_2a;
set szzz_1a;
rename var1=code var2=name var3=date var4=close;
label var1="编号" var2="简称" var3="日期" var4="收盘价";
year=year(var3);
month=month(var3);
run;
proc sort data=szzz_2a;
by  year month;
run;
data szzz_3a;
set szzz_2a ;
by year month;
where 2019<=year<=2020;
if last.month=1;/*取每个月的最后一个交易日*/
r_pct=dif(close)/lag(close);
r_log=log(close)-log(lag(close));
run;
data szzz_3a;
modify szzz_3a;
if 1=<_n_<=12 then remove;
run;
proc print data=szzz_3a;
var year month  r_pct ;
title '2020期间各月上证综指的单期百分比收益率';
run;
/*使用retain语句*/
data szzz_4a ;
set  szzz_3a;
retain am1;
if _n_=1 then am1=r_pct;
else am1=am1+r_pct;
am=am1/12;
retain gm1;
if _n_=1  then gm1=1+r_pct;
else gm1=gm1*(1+r_pct);
gm=gm1**(1/12)-1;
wealth=gm1;
keep month=12;
proc print data=szzz_4a(firstobs=12);
var  am gm wealth;
title '2020年上证综指算术平均收益率、几何平均收益率与财富指数';
run;
/*使用transpose语句*/
proc transpose data=szzz_3a out=szzz_3b;/*对数据集szzz_3a进行行列转置，转置后的数据集为szzz_3b*/
var r_pct;
run;
data szzz_4b;
set szzz_3b;
am=sum(of col1-col12)/12;/*计算算术平均收益率*/
wealth=(1+col1)*(1+col2)*(1+col3)*(1+col4)*(1+col5)*(1+col6)*(1+col7)*(1+col8)*(1+col9)*(1+col10)*(1+col11)*(1+col12);/*计算财富指数*/
gm=wealth**(1/12)-1;/*计算几何平均收益率*/
proc print data=szzz_4b;
var am gm wealth;
run;

第二题：

导入股票数据，试计算各个股票月度收益率，计算各股票收益率均值，方差和协方差。

/*导入股票数据，试计算各个股票月度收益率，计算各股票收益率均值，方差和协方差*/
proc import out=work.gpsj_1a
			Datafile='D:\mySASDATA\股票数据.xlsx'
			DbmS=xlsx replace;
			getnames=yes;
run;
data gpsj_2a;
set gpsj_1a;
rename var1=code var2=name var3=date var4=close;
label var1="编号" var2="简称" var3="时间" var4="收盘价";
year=year(var3);
month=month(var3);
run;
proc sort data=gpsj_2a;
by name year month;
run;
data gpsj_3a;
set gpsj_2a;
by name year month;
if last.month=1;
run;
data gpsj_4a;
set gpsj_3a;
by name;
lag_close=lag(close);
a=first.name;
if first.name then lag_close='.';
r_pct=(close-lag_close)/lag_close;
r_log=log(close)-log(lag_close);
run;
data gpsj_4a;
modify gpsj_4a;
if a=1  then remove;
run;
proc means data=gpsj_4a  mean std  var;
var r_pct r_log;
output out=res mean(r_pct)=pct_qiwang std(r_pct)=pct_std mean(r_log)=log_qiwang std(r_log)=log_std var(r_pct)=pct_fancha var(r_log)=var_fancha;
by name;
run;
/*计算各支股票之间收益率的协方差(计算百分比收益率的协方差,对数收益率同理易得)*/
%macro gegu(daima,condition,gegurt);
data &daima.;
set  gpsj_4a;
where   code=&condition.;
keep year month r_pct;
rename r_pct=&gegurt.;
run;
%mend;
%gegu(sh_600006,"600006.SH",r_pct600006);
%gegu(sh_600011,"600011.SH",r_pct600011);
%gegu(sh_600000,"600000.SH",r_pct600000);
%gegu(sh_600009,"600009.SH",r_pct600009);
%gegu(sh_600008,"600008.SH",r_pct600008);
%gegu(sh_600012,"600012.SH",r_pct600012);
%gegu(sh_600007,"600006.SH",r_pct600007);
data b;
merge sh_600006 sh_600011 sh_600000 sh_600009 sh_600008 sh_600012 sh_600007;
by year month;
run;
/*因为部分股票在较早的年度的数据有缺失值，而r_pct600012是缺失年份最多的，把缺失值删去*/
data b;
modify b;
if r_pct600012=. then remove;
run;
/*计算百分比收益率协方差*/
proc corr cov data=b outp=jg1;
var r_pct600006  r_pct600011 r_pct600000 r_pct600009 r_pct600008 r_pct600012 r_pct600007;
run;
/*计算对数收益率协方差*/
%macro gegu(daima,condition,gegurt);
data &daima.;
set  gpsj_4a;
where   code=&condition.;
keep year month r_log;
rename r_log=&gegurt.;
run;
%mend;
%gegu(sh_600006,"600006.SH",r_log600006);
%gegu(sh_600011,"600011.SH",r_log600011);
%gegu(sh_600000,"600000.SH",r_log600000);
%gegu(sh_600009,"600009.SH",r_log600009);
%gegu(sh_600008,"600008.SH",r_log600008);
%gegu(sh_600012,"600012.SH",r_log600012);
%gegu(sh_600007,"600006.SH",r_log600007);
data c;
merge sh_600006 sh_600011 sh_600000 sh_600009 sh_600008 sh_600012 sh_600007;
by year month;
run;
/*因为部分股票在较早的年度的数据有缺失值，而r_pct600012是缺失年份最多的，把缺失值删去*/
data c;
modify c;
if r_log600012=. then remove;
run;
proc corr cov data=c outp=jg2;
var r_log600006  r_log600011 r_log600000 r_log600009 r_log600008 r_log600012 r_log600007;
run;
/*因为删去了一些数据,所以导致proc mean 和proc corr 所计算出的均值等会存在不同,但600012.SH这支股票的数据一致的，均值以proc mean 的结果为准*/

部分结果图：

第三题：

导入无风险收益率，无风险收益率可以是shibor利率，也可以是同业拆借的利率，也可以是人民银行的基准利率。前两者是属于市场利率，因此在capm模型中更多偏好用市场利率，在美国通常是采用3个月的美国债券利率作为市场利率，在中国基准利率的时间比较长一些，所以很多学者采用定期存款利率作为无风险利率替代指标进行实证研究，但如果考虑的时间比较近，则会采用shibor利率。请你考虑两种不同的无风险收益率指标检验浦发银行等股票的资本资产定价模型是否成立，并对模型进行自相关和异方差的检验。假设某投资者预测2021年上证综指的超额收益率为0.5%，请预测各股票的超额期望收益率。 （提示：a. 可以采用宏程序完成，b.无风险利率缺省值填充可以采用retain语句。）

/*导入并处理七支股票的数据*/
proc import out=work.gpsj_1a
			Datafile='D:\mySASDATA\股票数据.xlsx'
			DbmS=xlsx replace;
			getnames=yes;
run;
/*处理数据集work.gpsj_1a，与题二过程相同*/
data gpsj_2a;
set gpsj_1a;
rename var1=code var2=name var3=date var4=close;
label var1="编号" var2="简称" var3="时间" var4="收盘价";
year=year(var3);
month=month(var3);
run;
proc sort data=gpsj_2a;
by name year month;
run;
data gpsj_3a;
set gpsj_2a;
by name year month;
if last.month=1;
run;
data gpsj_4a;
set gpsj_3a;
by name;
lag_close=lag(close);
a=first.name;
if first.name then lag_close='.';
r_pct=((close-lag_close)/lag_close)*100;
r_log=log(close)-log(lag_close);
run;
data gpsj_4a;
modify gpsj_4a;
if a=1  then remove;
run;
data gpsj_4a;
set gpsj_4a;
drop close var5 var6 var7 h a;
run;
/*导入无风险利率的数据*/
proc import out=work.Shibor_1b
            Datafile='D:\mySASDATA\上海银行间同业拆放利率.xlsx'
            DbmS=xlsx replace;
			RANGE='sheet1$A8:E3935'n; /*可以把表格中的前几行文字删去，得到格式比较正确的数据，但利率数据格式仍存在问题*/
			getnames=yes;
run;
data Shibor_1b;
set Shibor_1b;
rename VAR1=time  _2021_04_07=tycfrate  _2020_06_04=tyckrate  _2021_03_31=rmbckrate3m  _2015_10_23=rmbckrate1y ;
label var1="时间"   _2021_04_07="上海银行间同业拆放利率"  
        _2020_06_04="同业存款利率" _2021_03_31="人民币存款基准利率(三个月)" _2015_10_23="人民币存款基准利率(1年)" ;
run;
/*解决利率数据格式问题*/
data Shibor_2b;
set Shibor_1b ;
tycfrate_new=input(tycfrate,best.);
tyckrate_new=input(tyckrate,best.);
rmbckrate3m_new=input(rmbckrate3m,best.);
rmbckrate1y_new=input(rmbckrate1y,best.);
rename tycfrate_new=tycfrate;
rename tyckrate_new=tyckrate;
rename rmbckrate3m_new=rmbckrate3m;
rename rmbckrate1y_new=rmbckrate1y;
drop tycfrate tyckrate rmbckrate3m rmbckrate1y;
run; 
data Shibor_2b;
set Shibor_2b ;
label  tycfrate ="上海银行间同业拆放利率"  
         tyckrate="同业存款利率"  rmbckrate3m="人民币存款基准利率(三个月)"   rmbckrate1y="人民币存款基准利率(1年)" ;
run; 
/*解决缺失值问题*/
data Shibor_3b;
set Shibor_2b;
   retain  bc_tycfrate;
   if  tycfrate NE .  then bc_tycfrate=tycfrate ;
   else bc_tycfrate=bc_tycfrate ;
   retain bc_rmbckrate3m;
   if  rmbckrate3m  NE .  then bc_rmbckrate3m=rmbckrate3m;
   else bc_rmbckrate3m=bc_rmbckrate3m;
   retain bc_rmbckrate1y;
    if rmbckrate1y NE . then bc_rmbckrate1y=rmbckrate1y;
    else bc_rmbckrate1y=bc_rmbckrate1y;
keep time bc_tycfrate  bc_rmbckrate3m  bc_rmbckrate1y;
label bc_tycfrate ="上海银行间同业拆放利率" bc_rmbckrate3m="人民币存款基准利率(三个月)"   bc_rmbckrate1y="人民币存款基准利率(1年)";
run;
/*得到各无风险利率的月度数据*/
data Shibor_4b;
set Shibor_3b;
year=year(time);
month=month(time);
run;
proc sort data=Shibor_4b;
by year month;
run;
data Shibor_1c ;
set Shibor_4b ;
by year month;
if last.month=1;
run;
/*两个数据集的合并*/
proc sql;
create table capm1 as
select gpsj_4a.* , Shibor_1c. *
from  gpsj_4a left join Shibor_1c on  gpsj_4a.year=Shibor_1c.year and gpsj_4a.month=Shibor_1c.month;
quit; 
proc sort data=capm1;
by name year month;
run;
/*得到具有所需数据的数据集(计算超额收益率)*/
data capm2;
set capm1;
drop date time lag_close r_log;
run;
/*导入上证综指的数据并进行相关处理，过程同第一题，作为市场利率*/
data WORK.szzz_1a;                                                                                    
      %let _EFIERR_ = 0; /* set the ERROR detection macro variable */                                                                    
      infile 'D:\mySASDATA\上证综指.csv' delimiter = ',' MISSOVER DSD lrecl=32767 firstobs=2 ;                      
      informat VAR1 $9. ;                                                                                                           
      informat VAR2 $8. ;                                                                                                              
      informat VAR3 yymmdd10. ;                                                                                                       
      informat VAR4 comma12.0 ;                                                                                                      
      format VAR1 $9. ;                                                                                                             
      format VAR2 $8. ;                                                                                                               
      format VAR3 yymmdd10. ;                                                                                                       
      format VAR4 best12. ;                                                                                                         
      input   VAR1 $  VAR2 $    VAR3      VAR4 ;                                                                                                                                                                                                                                                                                                  
      if _ERROR_ then call symputx('_EFIERR_',1);  /* set ERROR detection macro variable */      
 run; 
data szzz_2a;
set szzz_1a;
rename var1=code var2=name var3=date var4=close;
label var1="编号" var2="简称" var3="日期" var4="收盘价";
year=year(var3);
month=month(var3);
run;
proc sort data=szzz_2a;
by  year month;
run;
data szzz_3a;
set szzz_2a ;
by year month;
if last.month=1;/*取每个月的最后一个交易日*/
rm=(dif(close)/lag(close))*100;
drop code date name close;
run;
/*合并上证综指的数据集与个股收益率和无风险利率的数据集*/
proc sql;
create table capmgd as
select   capm2.*,szzz_3a.*
from   capm2     left join szzz_3a on capm2.year=szzz_3a.year and capm2.month=szzz_3a.month ;
quit; 
proc sort data=capmgd;
by  name year month;
run;
/*使用人民币存款基准利率(三个月)为无风险利率的CAPM模型所需参数*/
data capmgd3m;
set capmgd;
rs3m=rm-bc_rmbckrate3m;
r_gegu=r_pct-bc_rmbckrate3m;
run;
/*回归并进行异方差和自相关的检验*/
%macro reg(sjj,wfxll);
proc reg data=&sjj.;
model r_gegu=&wfxll.   / dw        spec;
/*回归模型*/     /*自相关检验*/   /*异方差检验*/
slope:test rs3m=1;
by name;
quit;
%mend;
%reg(capmgd3m,rs3m);
/*使用人民币存款基准利率(1年)为无风险利率的CAPM模型所需参数*/
data capmgd1y;
set capmgd;
rs1y=rm-bc_rmbckrate1y;
r_gegu=r_pct-bc_rmbckrate1y;
run;
/*回归并进行异方差和自相关的检验*/
%reg(capmgd1y,rs1y);
/*因为对于同业拆放利率来说，从1999年至2006年九月数据缺失，所以把缺失值删去*/
data capmshibor;
set capmgd;
run;
data capmshibor;
modify capmshibor ;
if bc_tycfrate=. then remove;
run;
/*使用shibor为无风险利率的CAPM模型所需参数*/
data capmshibor;
set capmgd;
rs=rm-bc_tycfrate;
r_gegu=r_pct-bc_tycfrate;
run;
/*回归并进行异方差和自相关的检验*/
%reg (capmshibor,rs);
/*假设某投资者预测2021年上证综指的超额收益率为0.5%，预测各股票的超额期望收益率*/
/*由前面的回归过程可知，
1.若以shibor利率作为无风险利率，得到各股票回归方程的参数为：
 东风汽车: α=0.44285,β=0.94486;
华能国际:α=-0.23736,β=0.88584;
浦发银行:α=0.86093,β=0.97852;
上海机场:α=0.26702,β=0.82985;
首创股份:α=0.88412,β=1.12970;
皖通高速:α=-0.00439,β=0.85699;
中国国贸:α=-0.17810,β=0.83600;
2.若以人民币存款基准利率(三个月)作为无风险利率，得到各股票回归方程的参数为:
东风汽车: α=0.37791,β=0.89196;
华能国际:α=-0.10900,β=0.87279;
浦发银行:α=0.31171,β=0.91986;
上海机场:α=0.49347,β=0.75865;
首创股份:α=0.31023,β=1.08254;
皖通高速:α=-0.02711,β=0.75808;
中国国贸:α=0.07803 0,β=0.89864;
*/
/*若以人民币存款基准利率(1年)作为无风险利率,得到各股票回归方程的参数为:
东风汽车: α=0.33331,β=0.89364;
华能国际:α=-0.16630,β=0.87360;
浦发银行:α=0.27144,β=0.91703;
上海机场:α=0.36639,β=0.75580;
首创股份:α=0.34588,β=1.08189;
皖通高速:α=-0.13801,β=0.76013;
中国国贸:α=0.03298,β=0.89854;*/
%macro yuce(shuju,nm,lx,a,b);
data &shuju.;
name=&nm.;
wfx=&lx.;
ceqwrt=&a.+0.5*&b.;
run;
%mend;
%yuce(shuju1,"东风汽车","shibor",0.44285,0.94486);
%yuce(shuju2,"华能国际","shibor",-0.23736,0.88584);
%yuce(shuju3,"浦发银行","shibor",0.86093,0.97852); 
%yuce(shuju4,"上海机场","shibor",0.26702,0.82985);
%yuce(shuju5,"首创股份","shibor",0.88412,1.12970);
%yuce(shuju6,"皖通高速","shibor",-0.00439,0.85699);
%yuce(shuju7,"中国国贸","shibor",-0.17810,0.83600);
%yuce(shuju8,"东风汽车","3m",0.37791,0.89196);
%yuce(shuju9,"华能国际","3m",-0.10900,0.87279);
%yuce(shuju10,"浦发银行","3m",0.31171,0.91986); 
%yuce(shuju11,"上海机场","3m",0.49347,0.75865);
%yuce(shuju12,"首创股份","3m",0.31023,1.08254);
%yuce(shuju13,"皖通高速","3m",-0.02711,0.75808);
%yuce(shuju14,"中国国贸","3m",0.078030,0.89864);
%yuce(shuju15,"东风汽车","1y",0.33331,0.89364);
%yuce(shuju16,"华能国际","1y",-0.16630,0.87360);
%yuce(shuju17,"浦发银行","1y",0.27144,0.91703); 
%yuce(shuju18,"上海机场","1y",0.36639,0.75580);
%yuce(shuju19,"首创股份","1y",0.34588,1.08189);
%yuce(shuju20,"皖通高速","1y",-0.13801,0.76013);
%yuce(shuju21,"中国国贸","1y",0.03298,0.89854);
data a;
set shuju1 shuju2 shuju3 shuju4 shuju5 shuju6 shuju7 shuju8 shuju9 shuju10 shuju11 shuju12 shuju13 shuju14 shuju15 shuju16 shuju17
shuju18  shuju19 shuju20  shuju21 ;
run;
/*最后汇总得到不同无风险利率下的各股票的超额期望收益率*/
proc sort data=a;
by name;
run;