/*
       "Measuring Commercial Bank Profitability: Proceed with Caution"
        By David C. Wheelock and R. Alton Gilbert
           Federal Reserve Bank of St. Louis Review  Nov/Dec 2007

* This program is written for SAS v 9.1


*=========================== Loading Raw Data ===================================================*
The two initial datasets are created using the "callreports.sas" program which pulls data from
the call reports in order to have an Average Total Equity measure for all banks. Also necessary
is the program "ubpn.sas" which pulls the desired data from the Uniform Bank Performance Reports.
*================================================================================================*/

/*** CallReports.sas ***/

options nocenter ls=120 ps=5400;
libname call 'G:\CallReport';
***  Choose quarters  ***;
  %let qtrlist = 
											 CALL9512
                  CALL9603 CAll9606	CALL9609 CALL9612
                  CALL9703 CALL9706 CALL9709 CALL9712
                  CALL9803 CALL9806 CALL9809 CALL9812
                  CALL9903 CALL9906 CALL9909 CALL9912
                  CALL0003 CALL0006 CALL0009 CALL0012
                  CALL0103 CALL0106 CALL0109 CALL0112
                  CALL0203 CALL0206 CALL0209 CALL0212
                  CALL0303 CALL0306 CALL0309 CALL0312
                  CALL0403 CALL0406 CALL0409 CALL0412
                  CALL0503 CALL0506 CALL0509 CALL0512;
***  Macro to set quarterly datasets into large dataset  ***;

%macro setlist;

  %let i = 1;
  %let dsname = %scan(&qtrlist,&i);

  %do %while (&dsname ne );
    call.&dsname
    %let i = %eval(&i+1);
    %let dsname = %scan(&qtrlist,&i);
  %end;

%mend setlist;

***  Create main dataset  ***;

data sbanks.call(compress=yes);
  set %setlist;;

  keep DATE RSSD9050 RIAD3210;

  run;
proc sort data=sbanks.call out=sbanks.sortcall;
by RSSD9050;
run;

/**Determine the quarter for the observation**/

data sbanks.call2;
set sbanks.sortcall;
year=floor(date/10000);
qtr=floor((date-year*10000)/100);
if qtr=3 then qtrn=1;
else if qtr=6 then qtrn=2;
else if qtr=9 then qtrn=3;
else qtrn=4;
run;

/**Find the year end observation**/
data sbanks.call3;
set sbanks.call2;
format k 1.;
if qtrn=4 & year>1995 then do;
	k=1;
	output;
	end;
k=0;
output;
run;
/**create 4 lags of the Total Equity variable**/
data sbanks.call4 (drop=date);
set sbanks.call3;
qtr1=lag4(riad3210);
qtr2=lag3(riad3210);
qtr3=lag2(riad3210);
qtr4=lag(riad3210);
cqtr=riad3210;
run;
/**keep only the end of year observations to match the UBPN Data**/
data sbanks.call5;
set sbanks.call4;
if qtrn=4 & k=1;
output;
run;
/**Creat AvgTE by averaging over the Current quarter as well as the previous 4**/
data sbanks.finalaverage (keep=CERT year AvgTEqCall);
set sbanks.call5;
CERT=RSSD9050;
AvgTEqCall=mean(qtr1,qtr2,qtr3,qtr4,cqtr);
run;
proc sort data=sbanks.finalaverage;
by cert year;
run;

/* UBPN.sas  */

options nocenter ls=124 ps=32767;
libname ubpn  'g:\CallReport\ubpn';

***  Choose quarters  ***;
  %let qtrlist =  ubpn9612
				  ubpn9712
                  ubpn9812
                  ubpn9912
                  ubpn0012
                  ubpn0112
                  ubpn0212
                  ubpn0312
                  ubpn0412
                  ubpn0512;
***  Choose variables  ***;
%let keeplist =
CERT
DSB
PEER_GRP
SUBSFLAG

UBD50257
UBD33004

UBR50100
UBR55010
UBD50020
UBR50095
UBD33061
UBD30306
UBD33071
UBD50010
UBR50050
UBR50040
UBR69071
UBR51425
UBR51320
UBR51420
UBR50071
UBD51990;

***  Macro to set quarterly datasets into large dataset  ***;

%macro setlist;

  %let ix = 1;
  %let dsname = %scan(&qtrlist,&ix);

  %do %while (&dsname ne );
    ubpn.&dsname
    %let ix = %eval(&ix+1);
    %let dsname = %scan(&qtrlist,&ix);
  %end;

%mend setlist;

***  Create main dataset  ***;

data sbanks.ubpntest;
  set %setlist;;

  keep entity date date_sas year qtr &keeplist;

  ***  Create entity and date variables for merging with CALL data  ***;
  entity = id_rssd;
  date   = dt;
  ***  Create SAS date variable  ***;
  format date_sas date.;
  date_sas = input(put(date,8.),yymmdd8.);
  ***  Create YEAR and QTR variables  ***;
  year = year(date_sas);
  qtr  = qtr(date_sas);
  run;

/************************************/
/***    Calculations              ***/
/*** Step 1--Process all the data ***/
/************************************/

data sbanks.variables1;
set sbanks.ubpntest;/*load UBPR data*/
run;
proc sort data=sbanks.variables1;
by cert year;
run;
proc sort data=sbanks.finalaverage;
by cert year;
run;
data sbanks.varsandcall;
merge sbanks.finalaverage sbanks.variables1(in=InNBPR);/*merge with call report data*/
by cert year;
if InNBPR then output;
run;
data sbanks.calculations;
set sbanks.varsandcall;

/*----------------------*/
/*------- Part I--------*/
/*- Basic Calculations -*/
/*----------------------*/

test=(UBD50257+UBD33004)-(UBD30306+UBD33071);
/*Dave thinks that 'test' should equal zero*/

/*----------*/
/*--Part II-*/
/*----------*/

/*1.	Net income (after tax) divided by average total assets  UBR50100 */
IncperAss=UBR50100;

/*2.	Net income (after tax) adjusted for Subchapter-S status divided by average total assets UBR50095 */
IncSperAss=UBR50095;

/*3.	Net income (after tax) divided by average total equity UBR55010 */
IncperEq=UBR55010;

/*Create an Average Total Equity Check measure by dividing Net Income (UBD50020) by Net Income
as a percentage of Average Equity (UBR55010) (should be close to the AvgTEqC from the call reports)*/
AvgTEqCheck=(UBD50020/UBR55010)*100;
AvgTEqC=AvgTEqCall;
/*4.	Net income (after tax) adjusted for Subchapter-S 
status divided by average total equity [(UBR50095 times UBD50010) divided by (UBD50020 divided by UBR55010)] */
IncSperEq=(UBR50095*UBD50010)/AvgTEqC;

/*5.	Pre-tax net operating income divided by average total assets UBD33061 divided by UBD50010 */
OpIncperAss=(UBD33061/UBD50010)*100;

/*6.	Pre-tax net operating income divided by average total equity UBD33061 
divided by (UBD50020 divided by UBR55010)*/
OpIncperEq=(UBD33061/AvgTEqC)*100;

/*7.	Pre-tax net operating income less tax equivalent adjustments divided 
by average total assets [UBD33061 minus (UBD30306 + UBD33071)] divided by UBD50010*/
NoTaxperAss=((UBD33061-(UBD30306+UBD33071))/UBD50010)*100;

/*8.	Pre-tax net operating income less tax equivalent adjustments divided by average 
total equity [UBD33061 minus (UBD30306 + UBD33071)] divided by (UBD50020 divided by UBR55010)*/
NoTaxperEq=((UBD33061-(UBD30306+UBD33071))/AvgTEqC)*100;

/*9. Net Interest Margin*/
NetIntMar=ubr69071;

/*10. Net Non Interest Margin is UBR50040-UBR50050*/
NetNonIntMar=ubr50040-ubr50050;

/*11. Efficiency Ratio*/
Efficiency=ubr51425;

/*12. Operating Profit is pre-tax net operating profit/assets +personnel expenses/assets (Opincperass+UBR51320)*/
OpProfit=Opincperass+UBR51320;

/*13. Adj Operating profit is adjusted pre-tax net operating profit/assets +personnel expenses/assets (Notaxperass+UBR51320)*/
AdjOpProfit=Notaxperass+ubr51320;

/*14. This is just Ubr51420*/
AvgPerExperEmp=ubr51420; 

/*15. This is just ubr51320*/
AvgPerExperAss=ubr51320;

/*Modified peer grp (mpeergrp) is defined as follows:
If after 2003
1. combine peer groups 1 through 15 into group 'GOOD' 
2. combine peer groups 2001 through 2005 into 'Denovo'
3. label any remaining peer groups as 'Bizarre'

If before 2004
1. combine peer groups 1 through 24 into group 'GOOD' 
2. combine peer groups 25, 2001 through 2005 into 'Denovo'
3. label any remaining peer groups as 'Bizarre'
4. For any year, code 'Denovo' = 0 for any entry with peer group > year 

Sort banks by end or period total asset size, UBD51990 ($000)                 */

if peer_grp in (2001,2002,2003,2004,2005) and peer_grp > year then do; denovo = 0; end; else do;
if year>2003 then do;
	if PEER_GRP >0 and peer_grp < 16 then good = 1; else good = 0;
	if PEER_GRP in (2001,2002,2003,2004,2005) then denovo = 1; else denovo = 0;
	end;
else do;
	if PEER_GRP >0 and peer_grp < 25 then good = 1; else good = 0;
	if PEER_GRP in (25, 2001,2002,2003,2004,2005) then denovo = 1; else denovo = 0; 
	end;
end;
if denovo=0 and good=0 then bizarre=1; else bizarre=0;
if UBD51990>=1000000 then mpeergrp=1;
if UBD51990<1000000 and UBD51990>=300000 then mpeergrp=2;
if UBD51990<300000 and UBD51990>=100000 then mpeergrp=3;
if UBD51990<100000 and UBD51990>=50000 then mpeergrp=4;
if UBD51990<50000 then mpeergrp=5;
if subsflag=0 then call missing (incsperass, incspereq); 
run;
/*Count all sbanks and cbanks in groups 1-25, 2001, 2002, 2003, 2004, 2005 */
/*                This is used for Figure 1                                */

data sbanks.revisedcount;
set sbanks.calculations;
keep year SUBSFLAG;
if year<1997 then delete;
if bizarre=1 then delete; 
run;
proc sort data=sbanks.revisedcount;
by year SUBSFLAG;
run;
proc means data=sbanks.revisedcount n noprint;
output out=sbanks.revisednumbers;
by year SUBSFLAG;
run;
data sbanks.revisednumbers (drop=_FREQ_);
set sbanks.revisednumbers;
drop _TYPE_ ;
N=_FREQ_;
run;
PROC EXPORT DATA= SBANKS.revisednumbers 
            OUTFILE= "D:\Current Wheelock\Active\S type Banks\total_assets\count.xls" 
            DBMS=EXCEL REPLACE;
     SHEET="S Count GOOD, DENOVO"; 
RUN;

/*Count all sbanks and cbanks*/
data sbanks.scount;
set sbanks.calculations;
keep year SUBSFLAG;
if year<1997 then delete;
run;
proc sort data=sbanks.scount;
by year SUBSFLAG;
run;
proc means data=sbanks.scount n noprint;
output out=sbanks.numbers;
by year SUBSFLAG;
run;
data sbanks.numbers (drop=_FREQ_);
set sbanks.numbers;
drop _TYPE_ ;
N=_FREQ_;
run;
PROC EXPORT DATA= SBANKS.numbers 
            OUTFILE= "D:\Current Wheelock\Active\S type Banks\total_assets\count.xls" 
            DBMS=EXCEL REPLACE;
     SHEET="S Count all banks"; 
RUN;
/*Count only banks in peer groups 1-15*/
data sbanks.scountsmall;
set sbanks.calculations;
keep year subsflag;
if year<1997 then delete;
if peer_grp>15 or peer_grp<1 then delete;
run;
proc sort data=sbanks.scountsmall;
by year subsflag;
run;
proc means data=sbanks.scountsmall n noprint;
output out=sbanks.numbersmall;
by year subsflag;
run;
data sbanks.numbersmall (drop=_freq_);
set sbanks.numbersmall;
drop _type_;
N=_freq_;
run;
PROC EXPORT DATA= SBANKS.numbersmall 
            OUTFILE= "D:\Current Wheelock\Active\S type Banks\total_assets\count.xls" 
            DBMS=EXCEL REPLACE;
     SHEET="S Count Good"; 
RUN;
/* Count De Novo Banks, these are only available in 2001-2005.                     */
/* Peer_Grp numbers represent the year, i.e. Peer_grp=2001---> Denovo bank in 2001 */
data sbanks.countdenovo;
set sbanks.calculations;
keep peer_grp subsflag;
if peer_grp not in(2001,2002,2003,2004,2005) then delete;
run;
proc sort data=sbanks.countdenovo;
by peer_grp subsflag;
run;
proc means data=sbanks.countdenovo n noprint;
output out=sbanks.denovocount;
by peer_grp subsflag;
run;
data sbanks.denovocount (drop=_freq_);
set sbanks.denovocount;
N=_freq_;
run;
PROC EXPORT DATA= SBANKS.denovocount 
            OUTFILE= "D:\Current Wheelock\Active\S type Banks\total_assets\count.xls" 
            DBMS=EXCEL REPLACE;
     SHEET="S Count Denovo"; 
RUN;
/*Count by year and peer_grp all banks not in groups 1-15, for 1997-2000*/
data sbanks.countother;
set sbanks.calculations;
keep year subsflag peer_grp;
if year>2000 or year<1997 then delete;
if peer_grp<16 and peer_grp>0 then delete;
run;
proc sort data=sbanks.countother;
by subsflag year peer_grp;
run;
proc means data=sbanks.countother n noprint;
output out=sbanks.numberother;
by subsflag year peer_grp;
run;
data sbanks.numberother (drop=_freq_ _type_);
set sbanks.numberother;
N=_freq_;
run;
PROC EXPORT DATA= SBANKS.numberother 
            OUTFILE= "D:\Current Wheelock\Active\S type Banks\total_assets\count.xls" 
            DBMS=EXCEL REPLACE;
     SHEET="S Count Denovo,Bizarre 97-00"; 
RUN;
/**Step 2--Use only Peer Groups 1-24 and Trim the data (middle 98%) This eliminates some rather enormous outliers**/

data sbanks.trim1;
set sbanks.calculations;
if good=0 then delete;
/*this removes all the screwy peer groups which are Banker's Banks, Credit Card Banks, de novo banks etc.*/
if year>1996 and subsflag not in (1,0) then delete;
/*this removes banks not identified as S or C (meaning banks with a missing value for SUBSFLAG)*/
run;
proc sort data=sbanks.trim1;
by year;
run;
proc means data=sbanks.trim1 p1 p99 noprint;
output out=sbanks.outlier (drop=_TYPE_ _FREQ_)
p99(IncperAss)=Top1percent
p1(Incperass)=Bottom1percent;
by year;
run;
proc sort data=sbanks.outlier;
by year;
run;
data sbanks.trimmeddata1;
merge sbanks.trim1 (in=intrim) sbanks.outlier;
by year;
if intrim then output;
run;
data sbanks.trimmeddata;
set sbanks.trimmeddata1;
if IncperAss>=Top1percent or incperass<=bottom1percent then delete;
run;

/**          Step 3--Summary stats for various measures                         **/
/*Descriptive Stats by year for all trimmed data (which also trims DeNovo Banks  */
data sbanks.summarystats (keep= mpeergrp cpeergrp CERT SUBSFLAG year IncperAss IncSperAss IncperEq IncSperEq OpIncperAss 
OpIncperEq NoTaxperAss NoTaxperEq NetIntMar NetNonIntMar Efficiency Opprofit Adjopprofit avgperexperemp avgperexperass);
set sbanks.trimmeddata;
if year=1996 then delete; /*1996 is not used for any of the tests until later, since there are no Sbanks until 1997*/
run;
proc sort data=sbanks.summarystats;
by year;
run;
proc means data=sbanks.summarystats noprint median;
output out=sbanks.yearmeans;
output out=sbanks.yearmeansm 
median(IncperAss IncSperAss IncperEq IncSperEq OpIncperAss OpIncperEq NoTaxperAss NoTaxperEq NetIntMar NetNonIntMar Efficiency Opprofit Adjopprofit avgperexperemp avgperexperass)=
IncperAss IncSperAss IncperEq IncSperEq OpIncperAss OpIncperEq NoTaxperAss NoTaxperEq NetIntMar NetNonIntMar Efficiency Opprofit Adjopprofit avgperexperemp avgperexperass;
by year;
run;
data sbanks.yearmeans;
set sbanks.yearmeans;
drop _STAT_;
stat=_STAT_;
run;
data sbanks.yearmeansm;
set sbanks.yearmeansm (drop=_TYPE_ _FREQ_);
format stat $8.;
stat="MEDIAN";
run;
data sbanks.yearmeanst;
set sbanks.yearmeans sbanks.yearmeansm;
by year;
run;
/*Descriptive Stats by year and SUBSFLAG*/
proc sort data=sbanks.summarystats;
by year SUBSFLAG;
run;
proc means data=sbanks.summarystats noprint median;
output out=sbanks.smeans (drop=_TYPE_ _FREQ_ mpeergrp cpeergrp CERT);
output out=sbanks.smeansm 
median(IncperAss IncSperAss IncperEq IncSperEq OpIncperAss OpIncperEq NoTaxperAss NoTaxperEq NetIntMar NetNonIntMar Efficiency Opprofit Adjopprofit avgperexperemp avgperexperass)=
IncperAss IncSperAss IncperEq IncSperEq OpIncperAss OpIncperEq NoTaxperAss NoTaxperEq NetIntMar NetNonIntMar Efficiency Opprofit Adjopprofit avgperexperemp avgperexperass;
by year SUBSFLAG;
run;
data sbanks.smeans;
set sbanks.smeans;
drop _STAT_;
stat=_STAT_;
run;
data sbanks.smeansm;
set sbanks.smeansm (drop=_TYPE_ _FREQ_);
format stat $8.;
stat="MEDIAN";
run;
data sbanks.smeanst;
set sbanks.smeans sbanks.smeansm;
by year SUBSFLAG;
run;
/*Descriptive Stats by year, modified peer grp and S*/
data sbanks.mmeans;
set sbanks.summarystats;
run;
proc sort data=sbanks.mmeans;
by year mpeergrp SUBSFLAG;
run;
proc means data=sbanks.mmeans noprint median;
output out=sbanks.mgrpmeans (drop=_TYPE_ _FREQ_ CERT cpeergrp);
output out=sbanks.mgrpmeansm 
median(IncperAss IncSperAss IncperEq IncSperEq OpIncperAss OpIncperEq NoTaxperAss NoTaxperEq NetIntMar NetNonIntMar Efficiency Opprofit Adjopprofit avgperexperemp avgperexperass)=
IncperAss IncSperAss IncperEq IncSperEq OpIncperAss OpIncperEq NoTaxperAss NoTaxperEq NetIntMar NetNonIntMar Efficiency Opprofit Adjopprofit avgperexperemp avgperexperass;
by year mpeergrp SUBSFLAG;
run;
data sbanks.mgrpmeans;
set sbanks.mgrpmeans;
drop _STAT_;
stat=_STAT_;
run;
data sbanks.mgrpmeansm;
set sbanks.mgrpmeansm;
format stat $8.;
stat="MEDIAN";
run;
data sbanks.mgrpmeanst;
set sbanks.mgrpmeans sbanks.mgrpmeansm;
by year mpeergrp SUBSFLAG;
run;
/*Descriptive Stats by Year for all banks.  Uses ROA adj for S banks and ROA for C banks.  Output used for 'All banks' Table 2 */
data sbanks.allmeans;
set sbanks.summarystats;
keep year mpeergrp subsflag incperass incsperass incpereq incspereq;
run;
data sbanks.allmeans;
set sbanks.allmeans;
if subsflag = 1 then ROAadj = incsperass; else ROAadj = incperass;
if subsflag = 1 then ROEadj = incspereq; else ROEadj = incpereq;
run;
proc sort data=sbanks.allmeans;
by year mpeergrp;
run;
proc means data=sbanks.allmeans noprint median;
output out=sbanks.allmeanss;
output out=sbanks.allmeansm 
median(IncperAss IncSperAss ROAadj IncperEq IncSperEq ROEadj)=IncperAss IncSperAss ROAadj IncperEq IncSperEq ROEadj;
by year mpeergrp;
run;
data sbanks.allmeanss;
set sbanks.allmeanss;
drop _STAT_;
stat=_STAT_;
run;
data sbanks.allmeansm;
set sbanks.allmeansm;
format stat $8.;
stat="MEDIAN";
run;
data sbanks.allmeanst;
set sbanks.allmeanss sbanks.allmeansm;
by year mpeergrp;
run;
/*                  Output Data to Spreadsheet in Excel                  */
PROC EXPORT DATA= SBANKS.yearmeanst 
            OUTFILE= "D:\Current Wheelock\Active\S type Banks\total_assets\Summary Stats(T_A).xls" 
            DBMS=EXCEL REPLACE;
     SHEET="year"; 
RUN;
PROC EXPORT DATA= SBANKS.Smeanst 
            OUTFILE= "D:\Current Wheelock\Active\S type Banks\total_assets\Summary Stats(T_A).xls" 
            DBMS=EXCEL REPLACE;
     SHEET="Syear"; 
RUN;
PROC EXPORT DATA= SBANKS.mgrpmeansm 
            OUTFILE= "D:\Current Wheelock\Active\S type Banks\total_assets\Summary Stats(T_A).xls" 
            DBMS=EXCEL REPLACE;
     SHEET="Mgrp"; 
RUN;
PROC EXPORT DATA= SBANKS.allmeansm
            OUTFILE= "D:\Current Wheelock\Active\S type Banks\total_assets\Summary Stats(T_A).xls" 
            DBMS=EXCEL REPLACE;
     SHEET="Allmeans"; 
RUN;

/**              Step 4  --Replication of Article by DeYoung --                  **/
/** This code follows Dave's 5 step instructions in 'Replication of DeYoung.doc' **/
/**                    Table 4 displays this data                                **/

/*Part 1*/

data sbanks.deyoung1a;
set sbanks.calculations;
format Deyounggrps $17. Rural $3.;
if year^=2005 then delete;
if PEER_GRP>24 or PEER_GRP<1 then delete;
if SUBSFLAG not in(1,0) then delete;
/*Create groups sorted by the following: 
1.)	all banks with assets > $1 billion
2.)	all banks with assets between $500 million and $1 billion
3.)	all banks with assets between $100 million and $500 million
4.)	all banks with assets less than $100 million
5.)	all banks in peer groups 8, 10, 12, 14, 16, 18, 20, 22, and 24 (i.e., all rural banks)

Note: Using End of Quarter total assets, UBD51990 (000$)*/
if UBD51990>=1000000 then Deyounggrps=">1 bill";
if 1000000>UBD51990>=500000 then Deyounggrps="500 mill-1 bill";
if 500000>UBD51990>=100000 then Deyounggrps="100 mill-500 mill";
if UBD51990<=100000 then Deyounggrps="<100 mill";
if PEER_GRP in (8,10,12,14,16,18,20,22,24) then Rural="YES"; else Rural="NO";
run;
data sbanks.deyoungtrimmed;
set sbanks.trimmeddata;
format Deyounggrps $17. Rural $3.;
if year^=2005 then delete;
if PEER_GRP>24 or PEER_GRP<1 then delete;
if SUBSFLAG not in(1,0) then delete;
/*Create groups sorted by the following: 
1.)	all banks with assets > $1 billion
2.)	all banks with assets between $500 million and $1 billion
3.)	all banks with assets between $100 million and $500 million
4.)	all banks with assets less than $100 million
5.)	all banks in peer groups 8, 10, 12, 14, 16, 18, 20, 22, and 24 (i.e., all rural banks)

Note: Using End of Quarter total assets, UBD51990 (000$)*/
if UBD51990>=1000000 then Deyounggrps=">1 bill";
if 1000000>UBD51990>=500000 then Deyounggrps="500 mill-1 bill";
if 500000>UBD51990>=100000 then Deyounggrps="100 mill-500 mill";
if UBD51990<=100000 then Deyounggrps="<100 mill";
if PEER_GRP in (8,10,12,14,16,18,20,22,24) then Rural="YES"; else Rural="NO";
run;
data sbanks.deyoung1b;
merge sbanks.deyoungtrimmed (in=indeyoung) sbanks.outliers;
by year;
if indeyoung then output;
run;
data sbanks.deyoung1 (drop=Top1percent Bottom1percent indeyoung);
set sbanks.deyoung1b;
if IncperAss>=Top1percent or IncperAss<=Bottom1percent then delete;
run;

                   /**           Part 2            **/
/* Calculate median, mean, and variance of ROA (variable IncperAssets) and ROE (IncperEq) for each group #1-#5. */
                   /** Output data to spreadsheet  **/
proc sort data=sbanks.deyoung1;
by Deyounggrps;
run;
proc means data=sbanks.deyoung1 noprint median;
output out=sbanks.deyoung3a (drop=_TYPE_ _FREQ_);
output out=sbanks.deyoung3b
median(IncperAss IncperEQ)=IncperAss IncperEq;
by Year Deyounggrps;
run;
data sbanks.deyoung3a;
set sbanks.deyoung3a;
drop _STAT_;
stat=_STAT_;
run;
data sbanks.deyoung3b;
set sbanks.deyoung3b (drop=_TYPE_ _FREQ_);
format stat $8.;
stat="MEDIAN";
run;
data sbanks.deyoung3 (keep=year deyounggrps IncperAss IncperEq stat);
set sbanks.deyoung3a sbanks.deyoung3b;
by year Deyounggrps;
run;
PROC EXPORT DATA= SBANKS.deyoung3 
            OUTFILE= "D:\Current Wheelock\Active\S type Banks\total_assets\Replication of DeYoung(T_A).xls" 
            DBMS=EXCEL REPLACE;
     SHEET="Part2assets_trimmed"; 
RUN;
proc sort data=sbanks.deyoung1;
by Rural;
run;
proc means data=sbanks.deyoung1 noprint median;
output out=sbanks.deyoung3c (drop=_TYPE_ _FREQ_);
output out=sbanks.deyoung3d
median(IncperAss IncperEQ)=IncperAss IncperEq;
by Year Rural;
run;
data sbanks.deyoung3c;
set sbanks.deyoung3c;
drop _STAT_;
stat=_STAT_;
run;
data sbanks.deyoung3d;
set sbanks.deyoung3d (drop=_TYPE_ _FREQ_);
format stat $8.;
stat="MEDIAN";
run;
data sbanks.deyoung33 (keep=year Rural IncperAss IncperEq stat);
set sbanks.deyoung3c sbanks.deyoung3d;
by year Rural;
run;
PROC EXPORT DATA= SBANKS.deyoung33
            OUTFILE= "D:\Current Wheelock\Active\S type Banks\total_assets\Replication of DeYoung(T_A).xls" 
            DBMS=EXCEL REPLACE;
     SHEET="Part2rural_trimmed"; 
RUN;
/*                        Part 3                                                               * 
*     For each group, calculate mean ROA (IncperAssets), mean ROA adjusted (IncSperAssets),    *
*     mean ROE (Incper Eq), and mean ROE adjusted (IncSperEq)                                  *
*     for all banks whose ROE exceeds the group median ROE.                                    *
*     Note:  include all banks, not just S-banks, in the calculations 		    			   *					
*                                                                                              */ 
data sbanks.deyoung4a;
set sbanks.deyoung3;
if stat^="MEDIAN" then delete;
run;
data sbanks.deyoung4b (drop=stat year IncperEq);
set sbanks.deyoung4a;
ROEmedian=IncperEq;
run;
proc sort data=sbanks.deyoung1;
by Deyounggrps;
run;
proc sort data=sbanks.deyoung4b;
by Deyounggrps;
run;
data sbanks.deyoung4c;
merge sbanks.deyoung4b sbanks.deyoung1;
by Deyounggrps;
run;
data sbanks.deyoung4d
	(keep=year SUBSFLAG Deyounggrps ROEmedian
	IncperAss IncSperAss IncperEq IncSperEq);
set sbanks.deyoung4c;
run;
data sbanks.deyoung4e (drop=IncSperAss IncSperEq);
set sbanks.deyoung4d;
if SUBSFLAG=1 then mIncSperEq=IncSperEq;
else mIncSperEq=IncperEq;
if SUBSFLAG=1 then mIncSperAss=IncSperAss;
else mIncSperAss=IncperAss;
run;
data sbanks.deyoung4f (drop=ROEmedian SUBSFLAG);
set sbanks.deyoung4e;
if IncperEq<ROEmedian then delete;
run;
proc means data=sbanks.deyoung4f noprint;
output out=sbanks.deyoung4 (drop=_TYPE_ _FREQ_);
by year Deyounggrps;
run;
PROC EXPORT DATA= SBANKS.deyoung4 
            OUTFILE= "D:\Current Wheelock\Active\S type Banks\total_assets\Replication of DeYoung(T_A).xls" 
            DBMS=EXCEL REPLACE;
     SHEET="Part3assets_trimmed"; 
RUN;
data sbanks.deyoung4g;
set sbanks.deyoung33;
if stat^="MEDIAN" then delete;
run;
data sbanks.deyoung4h (drop=stat year IncperEq);
set sbanks.deyoung4g;
ROEmedian=IncperEq;
run;
proc sort data=sbanks.deyoung1;
by Rural;
run;
proc sort data=sbanks.deyoung4h;
by Rural;
run;
data sbanks.deyoung4i;
merge sbanks.deyoung4h sbanks.deyoung1;
by Rural;
run;
data sbanks.deyoung4j
	(keep=year SUBSFLAG Rural ROEmedian
	IncperAss IncSperAss IncperEq IncSperEq);
set sbanks.deyoung4i;
run;
data sbanks.deyoung4k (drop=IncSperAss IncSperEq);
set sbanks.deyoung4j;
if SUBSFLAG=1 then mIncSperEq=IncSperEq;
else mIncSperEq=IncperEq;
if SUBSFLAG=1 then mIncSperAss=IncSperAss;
else mIncSperAss=IncperAss;
run;
data sbanks.deyoung4l (drop=ROEmedian SUBSFLAG);
set sbanks.deyoung4k;
if IncperEq<ROEmedian then delete;
run;
proc means data=sbanks.deyoung4l noprint;
output out=sbanks.deyoung44 (drop=_TYPE_ _FREQ_);
by year Rural;
run;
PROC EXPORT DATA= SBANKS.deyoung44 
            OUTFILE= "D:\Current Wheelock\Active\S type Banks\total_assets\Replication of DeYoung(T_A).xls" 
            DBMS=EXCEL REPLACE;
     SHEET="Part3rural_trimmed"; 
RUN;
/*                                 Part 4                                                                        */
/* Calculate the means listed in Step 3 for all banks in each group whose ROE is less than the group median ROE. */

data sbanks.deyoung5a (drop=ROEmedian SUBSFLAG);
set sbanks.deyoung4e;
if IncperEq>=ROEmedian then delete;
run;
proc means data=sbanks.deyoung5a noprint;
output out=sbanks.deyoung5 (drop=_TYPE_ _FREQ_);
by year Deyounggrps;
run;
PROC EXPORT DATA= SBANKS.deyoung5 
            OUTFILE= "D:\Current Wheelock\Active\S type Banks\total_assets\Replication of DeYoung(T_A).xls" 
            DBMS=EXCEL REPLACE;
     SHEET="Part4assets_trimmed"; 
RUN;
data sbanks.deyoung5b (drop=ROEmedian SUBSFLAG);
set sbanks.deyoung4k;
if IncperEq>=ROEmedian then delete;
run;
proc means data=sbanks.deyoung5b noprint;
output out=sbanks.deyoung55 (drop=_TYPE_ _FREQ_);
by year Rural;
run;
PROC EXPORT DATA= SBANKS.deyoung55 
            OUTFILE= "D:\Current Wheelock\Active\S type Banks\total_assets\Replication of DeYoung(T_A).xls" 
            DBMS=EXCEL REPLACE;
     SHEET="Part4rural_trimmed"; 
RUN;
/*Repeat Step Two using the untrimmed data */
proc sort data=sbanks.deyoung1a;
by Deyounggrps;
run;
proc means data=sbanks.deyoung1a noprint median;
output out=sbanks.deyoung3a (drop=_TYPE_ _FREQ_);
output out=sbanks.deyoung3b
median(IncperAss IncperEQ)=IncperAss IncperEq;
by Year Deyounggrps;
run;
data sbanks.deyoung3a;
set sbanks.deyoung3a;
drop _STAT_;
stat=_STAT_;
run;
data sbanks.deyoung3b;
set sbanks.deyoung3b (drop=_TYPE_ _FREQ_);
format stat $8.;
stat="MEDIAN";
run;
data sbanks.deyoung3 (keep=year deyounggrps IncperAss IncperEq stat);
set sbanks.deyoung3a sbanks.deyoung3b;
by year Deyounggrps;
run;
PROC EXPORT DATA= SBANKS.deyoung3 
            OUTFILE= "D:\Current Wheelock\Active\S type Banks\total_assets\Replication of DeYoung(T_A).xls" 
            DBMS=EXCEL REPLACE;
     SHEET="Part2assets_untrimmed"; 
RUN;
proc sort data=sbanks.deyoung1a;
by Rural;
run;
proc means data=sbanks.deyoung1a noprint median;
output out=sbanks.deyoung3c (drop=_TYPE_ _FREQ_);
output out=sbanks.deyoung3d
median(IncperAss IncperEQ)=IncperAss IncperEq;
by Year Rural;
run;
data sbanks.deyoung3c;
set sbanks.deyoung3c;
drop _STAT_;
stat=_STAT_;
run;
data sbanks.deyoung3d;
set sbanks.deyoung3d (drop=_TYPE_ _FREQ_);
format stat $8.;
stat="MEDIAN";
run;
data sbanks.deyoung33 (keep=year Rural IncperAss IncperEq stat);
set sbanks.deyoung3c sbanks.deyoung3d;
by year Rural;
run;
PROC EXPORT DATA= SBANKS.deyoung33
            OUTFILE= "D:\Current Wheelock\Active\S type Banks\total_assets\Replication of DeYoung(T_A).xls" 
            DBMS=EXCEL REPLACE;
     SHEET="Part2rural_untrimmed"; 
RUN;
/*Repeat Step 3 using the untrimmed data*/
data sbanks.deyoung4a;
set sbanks.deyoung3;
if stat^="MEDIAN" then delete;
run;
data sbanks.deyoung4b (drop=stat year IncperEq);
set sbanks.deyoung4a;
ROEmedian=IncperEq;
run;
proc sort data=sbanks.deyoung1a;
by Deyounggrps;
run;
proc sort data=sbanks.deyoung4b;
by Deyounggrps;
run;
data sbanks.deyoung4c;
merge sbanks.deyoung4b sbanks.deyoung1a;
by Deyounggrps;
run;
data sbanks.deyoung4d
	(keep=year SUBSFLAG Deyounggrps ROEmedian
	IncperAss IncSperAss IncperEq IncSperEq);
set sbanks.deyoung4c;
run;
data sbanks.deyoung4e (drop=IncSperAss IncSperEq);
set sbanks.deyoung4d;
if SUBSFLAG=1 then mIncSperEq=IncSperEq;
else mIncSperEq=IncperEq;
if SUBSFLAG=1 then mIncSperAss=IncSperAss;
else mIncSperAss=IncperAss;
run;
data sbanks.deyoung4f (drop=ROEmedian SUBSFLAG);
set sbanks.deyoung4e;
if IncperEq<ROEmedian then delete;
run;
proc means data=sbanks.deyoung4f noprint;
output out=sbanks.deyoung4 (drop=_TYPE_ _FREQ_);
by year Deyounggrps;
run;
PROC EXPORT DATA= SBANKS.deyoung4 
            OUTFILE= "D:\Current Wheelock\Active\S type Banks\total_assets\Replication of DeYoung(T_A).xls" 
            DBMS=EXCEL REPLACE;
     SHEET="Part3assets_untrimmed"; 
RUN;
data sbanks.deyoung4g;
set sbanks.deyoung33;
if stat^="MEDIAN" then delete;
run;
data sbanks.deyoung4h (drop=stat year IncperEq);
set sbanks.deyoung4g;
ROEmedian=IncperEq;
run;
proc sort data=sbanks.deyoung1a;
by Rural;
run;
proc sort data=sbanks.deyoung4h;
by Rural;
run;
data sbanks.deyoung4i;
merge sbanks.deyoung4h sbanks.deyoung1a;
by Rural;
run;
data sbanks.deyoung4j
	(keep=year SUBSFLAG Rural ROEmedian
	IncperAss IncSperAss IncperEq IncSperEq);
set sbanks.deyoung4i;
run;
data sbanks.deyoung4k (drop=IncSperAss IncSperEq);
set sbanks.deyoung4j;
if SUBSFLAG=1 then mIncSperEq=IncSperEq;
else mIncSperEq=IncperEq;
if SUBSFLAG=1 then mIncSperAss=IncSperAss;
else mIncSperAss=IncperAss;
run;
data sbanks.deyoung4l (drop=ROEmedian SUBSFLAG);
set sbanks.deyoung4k;
if IncperEq<ROEmedian then delete;
run;
proc means data=sbanks.deyoung4l noprint;
output out=sbanks.deyoung44 (drop=_TYPE_ _FREQ_);
by year Rural;
run;
PROC EXPORT DATA= SBANKS.deyoung44 
            OUTFILE= "D:\Current Wheelock\Active\S type Banks\total_assets\Replication of DeYoung(T_A).xls" 
            DBMS=EXCEL REPLACE;
     SHEET="Part3rural_untrimmed"; 
RUN;
/*Repeat Step 4 using the untrimmed data */
data sbanks.deyoung5a (drop=ROEmedian SUBSFLAG);
set sbanks.deyoung4e;
if IncperEq>=ROEmedian then delete;
run;
proc means data=sbanks.deyoung5a noprint;
output out=sbanks.deyoung5 (drop=_TYPE_ _FREQ_);
by year Deyounggrps;
run;
PROC EXPORT DATA= SBANKS.deyoung5 
            OUTFILE= "D:\Current Wheelock\Active\S type Banks\total_assets\Replication of DeYoung(T_A).xls" 
            DBMS=EXCEL REPLACE;
     SHEET="Part4assets_untrimmed"; 
RUN;
data sbanks.deyoung5b (drop=ROEmedian SUBSFLAG);
set sbanks.deyoung4k;
if IncperEq>=ROEmedian then delete;
run;
proc means data=sbanks.deyoung5b noprint;
output out=sbanks.deyoung55 (drop=_TYPE_ _FREQ_);
by year Rural;
run;
PROC EXPORT DATA= SBANKS.deyoung55 
            OUTFILE= "D:\Current Wheelock\Active\S type Banks\total_assets\Replication of DeYoung(T_A).xls" 
            DBMS=EXCEL REPLACE;
     SHEET="Part4rural_untrimmed"; 
RUN;
/*************************/
/***GROWTH IN SBANKS******/
/*************************/

/*This section calculates the total assets for S and C banks across years and size groups, no banks in 'bizarre' group*/
/******NOTE: These are not trimmed*****/

data sbanks.summedassets (keep=subsflag year mpeergrp UBD50010);
set sbanks.calculations;
if year<1997 then delete;
if bizarre = 1 then delete;
run;
data sbanks.summedassets1(drop=subsflag);
set sbanks.summedassets;
if subsflag=1 then s=1; else s=0;
ns=1;
if subsflag=1 then sassets=UBD50010; else sassets=0;
assets=UBD50010;
run;
proc sort data=sbanks.summedassets1;
by year mpeergrp;
run;
proc means data=sbanks.summedassets1 noprint sum;
output out=sbanks.summedassets2 (drop=_TYPE_ _freq_)
sum (s ns assets sassets)=S Allbanks AllAssets SAssets;
by year mpeergrp;
run;
data sbanks.summedassets3;
set sbanks.summedassets2;
PercentBanks=S/Allbanks;
PercentAssets=Sassets/allassets;
run;
PROC EXPORT DATA= SBANKS.summedassets3 
            OUTFILE= "D:\Current Wheelock\Active\S type Banks\total_assets\Sbank Growth(T_A).xls" 
            DBMS=EXCEL REPLACE;
     SHEET="SAS"; 
RUN;
/*************************/
/***THE MAIN COMPARISON***/
/*************************/
/*This section compares the means of all the variables of Sbanks against Cbanks*/

/*First we compute the means of all the variables for Sbanks and Cbanks separately*/

data sbanks.comparison (drop=opincperass opincpereq cpeergrp);
set sbanks.summarystats;
run;
data sbanks.scomparison(drop=incperass incpereq)  sbanks.ccomparison(drop=incsperass incspereq);
set sbanks.comparison;
if subsflag=1 then output sbanks.scomparison;
else output sbanks.ccomparison;
run;
proc sort data=sbanks.scomparison;
by year mpeergrp;
run;
proc sort data=sbanks.ccomparison;
by year mpeergrp;
run;
/*Calculate mean, stds, and nobs of Sbanks*/
proc means data=sbanks.scomparison mean n std noprint;
output out=sbanks.scompnobsstds (drop=_type_ _freq_)
n(IncSperAss IncSperEq NoTaxperAss NoTaxperEq NetIntMar NetNonIntMar
Efficiency Opprofit Adjopprofit avgperexperemp avgperexperass)=
nIncperAsss nIncsperEqs nNoTaxperAsss nNoTaxperEqs nNetIntMars nNetNonIntMars
nEfficiencys nOpprofits nAdjopprofits navgperexperemps navgperexperasss
std(IncSperAss IncSperEq NoTaxperAss NoTaxperEq NetIntMar NetNonIntMar
Efficiency Opprofit Adjopprofit avgperexperemp avgperexperass)=
stdIncperAsss stdIncsperEqs stdNoTaxperAsss stdNoTaxperEqs stdNetIntMars stdNetNonIntMars
stdEfficiencys stdOpprofits stdAdjopprofits stdavgperexperemps stdavgperexperasss;

output out=sbanks.scompmeans (drop=_Type_ _freq_)
mean(IncSperAss IncSperEq NoTaxperAss NoTaxperEq NetIntMar NetNonIntMar
Efficiency Opprofit Adjopprofit avgperexperemp avgperexperass)=
IncperAsss IncsperEqs NoTaxperAsss NoTaxperEqs NetIntMars NetNonIntMars
Efficiencys Opprofits Adjopprofits avgperexperemps avgperexperasss;
by year mpeergrp;
run;
/*Calculate mean, stds, and nobs of Cbanks*/
proc means data=sbanks.ccomparison mean n std noprint;
output out=sbanks.ccompnobsstds (drop=_type_ _freq_)
n(IncperAss IncperEq NoTaxperAss NoTaxperEq NetIntMar NetNonIntMar
Efficiency Opprofit Adjopprofit avgperexperemp avgperexperass)=
nIncperAss nIncperEq nNoTaxperAss nNoTaxperEq nNetIntMar nNetNonIntMar
nEfficiency nOpprofit nAdjopprofit navgperexperemp navgperexperass
std(IncperAss IncperEq NoTaxperAss NoTaxperEq NetIntMar NetNonIntMar
Efficiency Opprofit Adjopprofit avgperexperemp avgperexperass)=
stdIncperAss stdIncperEq stdNoTaxperAss stdNoTaxperEq stdNetIntMar stdNetNonIntMar
stdEfficiency stdOpprofit stdAdjopprofit stdavgperexperemp stdavgperexperass;

output out=sbanks.ccompmeans (drop=_Type_ _freq_)
mean(IncperAss IncperEq NoTaxperAss NoTaxperEq NetIntMar NetNonIntMar
Efficiency Opprofit Adjopprofit avgperexperemp avgperexperass)=
IncperAss IncperEq NoTaxperAss NoTaxperEq NetIntMar NetNonIntMar
Efficiency Opprofit Adjopprofit avgperexperemp avgperexperass;

by year mpeergrp;
run;
/*Calculate Differences of means*/
data sbanks.meandifferences (keep=year mpeergrp IncperAssd IncperEqd NoTaxperAssd NoTaxperEqd NetIntMard NetNonIntMard
Efficiencyd Opprofitd Adjopprofitd avgperexperempd avgperexperassd);

merge sbanks.ccompmeans sbanks.scompmeans;
by year mpeergrp;

array smean{11} IncperAsss IncsperEqs NoTaxperAsss NoTaxperEqs NetIntMars NetNonIntMars
Efficiencys Opprofits Adjopprofits avgperexperemps avgperexperasss;
array cmean{11} IncperAss IncperEq NoTaxperAss NoTaxperEq NetIntMar NetNonIntMar
Efficiency Opprofit Adjopprofit avgperexperemp avgperexperass;

array diff{11} IncperAssd IncperEqd NoTaxperAssd NoTaxperEqd NetIntMard NetNonIntMard
Efficiencyd Opprofitd Adjopprofitd avgperexperempd avgperexperassd;
do index=1 to 11;
	diff{index}=cmean{index}-smean{index};
end;
run;
/*Calculate std and degrees of freedom for difference*/
data sbanks.stddifferences (keep=year mpeergrp DstdIncperAss DstdIncperEq DstdNoTaxperAss DstdNoTaxperEq 
DstdNetIntMar DstdNetNonIntMar DstdEfficiency DstdOpprofit DstdAdjopprofit Dstdavgperexperemp Dstdavgperexperass
dfIncperAss dfIncperEq dfNoTaxperAss dfNoTaxperEq dfNetIntMar dfNetNonIntMar
dfEfficiency dfOpprofit dfAdjopprofit dfavgperexperemp dfavgperexperass);

merge sbanks.ccompnobsstds sbanks.scompnobsstds;
by year mpeergrp;

array snobs{11} nIncperAsss nIncsperEqs nNoTaxperAsss nNoTaxperEqs nNetIntMars nNetNonIntMars
nEfficiencys nOpprofits nAdjopprofits navgperexperemps navgperexperasss;
array cnobs{11} nIncperAss nIncperEq nNoTaxperAss nNoTaxperEq nNetIntMar nNetNonIntMar
nEfficiency nOpprofit nAdjopprofit navgperexperemp navgperexperass;
array sstds{11} stdIncperAsss stdIncsperEqs stdNoTaxperAsss stdNoTaxperEqs stdNetIntMars stdNetNonIntMars
stdEfficiencys stdOpprofits stdAdjopprofits stdavgperexperemps stdavgperexperasss;
array cstds{11} stdIncperAss stdIncperEq stdNoTaxperAss stdNoTaxperEq stdNetIntMar stdNetNonIntMar
stdEfficiency stdOpprofit stdAdjopprofit stdavgperexperemp stdavgperexperass;

array stddiff{11} DstdIncperAss DstdIncperEq DstdNoTaxperAss DstdNoTaxperEq DstdNetIntMar DstdNetNonIntMar
DstdEfficiency DstdOpprofit DstdAdjopprofit Dstdavgperexperemp Dstdavgperexperass;
array dfdiff{11} dfIncperAss dfIncperEq dfNoTaxperAss dfNoTaxperEq dfNetIntMar dfNetNonIntMar
dfEfficiency dfOpprofit dfAdjopprofit dfavgperexperemp dfavgperexperass;

do index=1 to 11;
	/*STD for difference*/
	stddiff{index}=sqrt( ((sstds{index})**2)/snobs{index} + ((cstds{index})**2)/cnobs{index} );
	/*Degrees of Freedom*/
	dfdiff{index}=((((sstds{index})**2)/snobs{index} + ((cstds{index})**2)/(cnobs{index}))**2)/
	((((((sstds{index})**2)/(snobs{index}))**2)/(snobs{index}-1))+(((((cstds{index})**2)/(cnobs{index}))**2)/(cnobs{index}-1)));
end;
run;
/*Calculate tstats and pvalues*/
/*This doesn't like the missing values, but it will run anyway, just ignore the blue*/
data sbanks.pvaldifferences (keep= year mpeergrp IncperAssd IncperEqd NoTaxperAssd 
NoTaxperEqd NetIntMard NetNonIntMard Efficiencyd Opprofitd Adjopprofitd avgperexperempd 
avgperexperassd pIncperAss pIncperEq pNoTaxperAss pNoTaxperEq pNetIntMar pNetNonIntMar
pEfficiency pOpprofit pAdjopprofit pavgperexperemp pavgperexperass);

merge sbanks.stddifferences sbanks.meandifferences;
by year mpeergrp;

array diff{11} IncperAssd IncperEqd NoTaxperAssd NoTaxperEqd NetIntMard NetNonIntMard
Efficiencyd Opprofitd Adjopprofitd avgperexperempd avgperexperassd;
array stddiff{11} DstdIncperAss DstdIncperEq DstdNoTaxperAss DstdNoTaxperEq DstdNetIntMar DstdNetNonIntMar
DstdEfficiency DstdOpprofit DstdAdjopprofit Dstdavgperexperemp Dstdavgperexperass;
array dfdiff{11} dfIncperAss dfIncperEq dfNoTaxperAss dfNoTaxperEq dfNetIntMar dfNetNonIntMar
dfEfficiency dfOpprofit dfAdjopprofit dfavgperexperemp dfavgperexperass;


array tstat{11} tIncperAss tIncperEq tNoTaxperAss tNoTaxperEq tNetIntMar tNetNonIntMar
tEfficiency tOpprofit tAdjopprofit tavgperexperemp tavgperexperass;
array pval{11} pIncperAss pIncperEq pNoTaxperAss pNoTaxperEq pNetIntMar pNetNonIntMar
pEfficiency pOpprofit pAdjopprofit pavgperexperemp pavgperexperass;

do i=1 to 11;
	/*tstat for difference*/
	tstat{i}=diff{i}/stddiff{i};
	/*pval for difference*/
	pval{i}=PDF('T',tstat{i},dfdiff{i});
end;
run;
proc export DATA=SBANKS.ccompmeans
			outfile= "D:\Current Wheelock\Active\S type Banks\total_assets\Test for Differences(T_A).xls"
			DBMS=EXCEL REPLACE;
	 SHEET="CMEANS";
run;
proc export DATA=SBANKS.scompmeans
			outfile= "D:\Current Wheelock\Active\S type Banks\Mtotal_assets\Test for Differences(T_A).xls"
			DBMS=EXCEL REPLACE;
	 SHEET="SMEANS";
run;
proc export DATA= SBANKS.pvaldifferences 
            OUTFILE= "D:\Current Wheelock\Active\S type Banks\total_assets\Test for Differences(T_A).xls" 
            DBMS=EXCEL REPLACE;
     SHEET="Differences"; 
RUN;

/*****************************/
/***COMPARING FUTURE SBANKS***/
/*****************************/
/*This section compares the means of all the variables of Cbanks against Cbanks which become Sbanks in the next year*/

/*First we compute the means of all the variables for Sbanks and Cbanks separately*/

data sbanks.futurecomp (keep=cert mpeergrp SUBSFLAG year IncperAss IncSperAss IncperEq IncSperEq
NoTaxperAss NoTaxperEq NetIntMar NetNonIntMar Efficiency Opprofit Adjopprofit avgperexperemp avgperexperass);
set sbanks.trimmeddata;
if year>1996 and subsflag not in (0,1) then delete;
run;
proc sort data=sbanks.futurecomp;/*make sure that the non sbanks have data for the sbank stats*/
by cert descending year;
run;
data sbanks.statuschangea;
set sbanks.futurecomp;
by cert;
slag=lag(subsflag);
if first.cert then slag=.;
run;
proc sort data=sbanks.statuschangea;
by cert year;
run;
data sbanks.statuschangeb;
set sbanks.statuschangea;
if year^=2005 and subsflag^=1 then output;
run;
data sbanks.statuschangec sbanks.statuschanges;
set sbanks.statuschangeb (drop=incsperass incspereq);
if SLAG=1 then output sbanks.statuschanges;
else output sbanks.statuschangec;
run;
proc sort data=sbanks.statuschanges;
by year mpeergrp;
run;
proc sort data=sbanks.statuschangec;
by year mpeergrp;
run;
/*Calculate mean, stds, and nobs of Sbanks*/
proc means data=sbanks.statuschanges mean n std noprint;
output out=sbanks.sstatusnobsstds (drop=_type_ _freq_)
n(IncperAss IncperEq NoTaxperAss NoTaxperEq NetIntMar NetNonIntMar
Efficiency Opprofit Adjopprofit avgperexperemp avgperexperass)=
nIncperAsss nIncsperEqs nNoTaxperAsss nNoTaxperEqs nNetIntMars nNetNonIntMars
nEfficiencys nOpprofits nAdjopprofits navgperexperemps navgperexperasss
std(IncperAss IncperEq NoTaxperAss NoTaxperEq NetIntMar NetNonIntMar
Efficiency Opprofit Adjopprofit avgperexperemp avgperexperass)=
stdIncperAsss stdIncsperEqs stdNoTaxperAsss stdNoTaxperEqs stdNetIntMars stdNetNonIntMars
stdEfficiencys stdOpprofits stdAdjopprofits stdavgperexperemps stdavgperexperasss;

output out=sbanks.sstatusmeans (drop=_Type_ _freq_)
n(IncperAss IncperEq NoTaxperAss NoTaxperEq NetIntMar NetNonIntMar
Efficiency Opprofit Adjopprofit avgperexperemp avgperexperass)=
nIncperAsss nIncsperEqs nNoTaxperAsss nNoTaxperEqs nNetIntMars nNetNonIntMars
nEfficiencys nOpprofits nAdjopprofits navgperexperemps navgperexperasss
mean(IncperAss IncperEq NoTaxperAss NoTaxperEq NetIntMar NetNonIntMar
Efficiency Opprofit Adjopprofit avgperexperemp avgperexperass)=
IncperAsss IncsperEqs NoTaxperAsss NoTaxperEqs NetIntMars NetNonIntMars
Efficiencys Opprofits Adjopprofits avgperexperemps avgperexperasss;
by year mpeergrp;
run;
/*Calculate mean, stds, and nobs of Cbanks*/
proc means data=sbanks.statuschangec mean n std noprint;
output out=sbanks.cstatusnobsstds (drop=_type_ _freq_)
n(IncperAss IncperEq NoTaxperAss NoTaxperEq NetIntMar NetNonIntMar
Efficiency Opprofit Adjopprofit avgperexperemp avgperexperass)=
nIncperAss nIncperEq nNoTaxperAss nNoTaxperEq nNetIntMar nNetNonIntMar
nEfficiency nOpprofit nAdjopprofit navgperexperemp navgperexperass
std(IncperAss IncperEq NoTaxperAss NoTaxperEq NetIntMar NetNonIntMar
Efficiency Opprofit Adjopprofit avgperexperemp avgperexperass)=
stdIncperAss stdIncperEq stdNoTaxperAss stdNoTaxperEq stdNetIntMar stdNetNonIntMar
stdEfficiency stdOpprofit stdAdjopprofit stdavgperexperemp stdavgperexperass;

output out=sbanks.cstatusmeans (drop=_Type_ _freq_)
n(IncperAss IncperEq NoTaxperAss NoTaxperEq NetIntMar NetNonIntMar
Efficiency Opprofit Adjopprofit avgperexperemp avgperexperass)=
nIncperAss nIncperEq nNoTaxperAss nNoTaxperEq nNetIntMar nNetNonIntMar
nEfficiency nOpprofit nAdjopprofit navgperexperemp navgperexperass
mean(IncperAss IncperEq NoTaxperAss NoTaxperEq NetIntMar NetNonIntMar
Efficiency Opprofit Adjopprofit avgperexperemp avgperexperass)=
IncperAss IncperEq NoTaxperAss NoTaxperEq NetIntMar NetNonIntMar
Efficiency Opprofit Adjopprofit avgperexperemp avgperexperass;

by year mpeergrp;
run;
/*Calculate Differences of means*/
data sbanks.meanstatusdifferences (keep=year mpeergrp IncperAssd IncperEqd NoTaxperAssd NoTaxperEqd NetIntMard NetNonIntMard
Efficiencyd Opprofitd Adjopprofitd avgperexperempd avgperexperassd);

merge sbanks.cstatusmeans sbanks.sstatusmeans;
by year mpeergrp;

array smean{11} IncperAsss IncsperEqs NoTaxperAsss NoTaxperEqs NetIntMars NetNonIntMars
Efficiencys Opprofits Adjopprofits avgperexperemps avgperexperasss;
array cmean{11} IncperAss IncperEq NoTaxperAss NoTaxperEq NetIntMar NetNonIntMar
Efficiency Opprofit Adjopprofit avgperexperemp avgperexperass;

array diff{11} IncperAssd IncperEqd NoTaxperAssd NoTaxperEqd NetIntMard NetNonIntMard
Efficiencyd Opprofitd Adjopprofitd avgperexperempd avgperexperassd;
do index=1 to 11;
	diff{index}=cmean{index}-smean{index};
end;
run;
/*Calculate std and degrees of freedom for difference*/
data sbanks.stdstatusdifferences (keep=year mpeergrp DstdIncperAss DstdIncperEq DstdNoTaxperAss DstdNoTaxperEq 
DstdNetIntMar DstdNetNonIntMar DstdEfficiency DstdOpprofit DstdAdjopprofit Dstdavgperexperemp Dstdavgperexperass
dfIncperAss dfIncperEq dfNoTaxperAss dfNoTaxperEq dfNetIntMar dfNetNonIntMar
dfEfficiency dfOpprofit dfAdjopprofit dfavgperexperemp dfavgperexperass nIncperAsss nIncsperEqs nNoTaxperAsss
nNoTaxperEqs nNetIntMars nNetNonIntMars nEfficiencys nOpprofits nAdjopprofits navgperexperemps navgperexperasss);

merge sbanks.cstatusnobsstds sbanks.sstatusnobsstds;
by year mpeergrp;

array snobs{11} nIncperAsss nIncsperEqs nNoTaxperAsss nNoTaxperEqs nNetIntMars nNetNonIntMars
nEfficiencys nOpprofits nAdjopprofits navgperexperemps navgperexperasss;
array cnobs{11} nIncperAss nIncperEq nNoTaxperAss nNoTaxperEq nNetIntMar nNetNonIntMar
nEfficiency nOpprofit nAdjopprofit navgperexperemp navgperexperass;
array sstds{11} stdIncperAsss stdIncsperEqs stdNoTaxperAsss stdNoTaxperEqs stdNetIntMars stdNetNonIntMars
stdEfficiencys stdOpprofits stdAdjopprofits stdavgperexperemps stdavgperexperasss;
array cstds{11} stdIncperAss stdIncperEq stdNoTaxperAss stdNoTaxperEq stdNetIntMar stdNetNonIntMar
stdEfficiency stdOpprofit stdAdjopprofit stdavgperexperemp stdavgperexperass;

array stddiff{11} DstdIncperAss DstdIncperEq DstdNoTaxperAss DstdNoTaxperEq DstdNetIntMar DstdNetNonIntMar
DstdEfficiency DstdOpprofit DstdAdjopprofit Dstdavgperexperemp Dstdavgperexperass;
array dfdiff{11} dfIncperAss dfIncperEq dfNoTaxperAss dfNoTaxperEq dfNetIntMar dfNetNonIntMar
dfEfficiency dfOpprofit dfAdjopprofit dfavgperexperemp dfavgperexperass;

do index=1 to 11;
	/*STD for difference*/
	stddiff{index}=sqrt( (sstds{index}**2)/snobs{index} + (cstds{index}**2)/cnobs{index} );
	/*Degrees of Freedom*/
	dfdiff{index}=(((sstds{index}**2)/snobs{index} + (cstds{index}**2)/cnobs{index})**2)/
	(((((sstds{index}**2)/snobs{index})**2)/(snobs{index}-1))+((((cstds{index}**2)/cnobs{index})**2)/(cnobs{index}-1)));
end;
run;
/*Calculate tstats and pvalues*/
/*This doesn't like the missing values, but it will run anyway, just ignore the blue*/

data sbanks.pvalstatusdifferences (keep= year mpeergrp IncperAssd IncperEqd NoTaxperAssd 
NoTaxperEqd NetIntMard NetNonIntMard Efficiencyd Opprofitd Adjopprofitd avgperexperempd 
avgperexperassd pIncperAss pIncperEq pNoTaxperAss pNoTaxperEq pNetIntMar pNetNonIntMar
pEfficiency pOpprofit pAdjopprofit pavgperexperemp pavgperexperass);

merge sbanks.stdstatusdifferences sbanks.meanstatusdifferences;
by year mpeergrp;

array diff{11} IncperAssd IncperEqd NoTaxperAssd NoTaxperEqd NetIntMard NetNonIntMard
Efficiencyd Opprofitd Adjopprofitd avgperexperempd avgperexperassd;
array stddiff{11} DstdIncperAss DstdIncperEq DstdNoTaxperAss DstdNoTaxperEq DstdNetIntMar DstdNetNonIntMar
DstdEfficiency DstdOpprofit DstdAdjopprofit Dstdavgperexperemp Dstdavgperexperass;
array dfdiff{11} dfIncperAss dfIncperEq dfNoTaxperAss dfNoTaxperEq dfNetIntMar dfNetNonIntMar
dfEfficiency dfOpprofit dfAdjopprofit dfavgperexperemp dfavgperexperass;


array tstat{11} tIncperAss tIncperEq tNoTaxperAss tNoTaxperEq tNetIntMar tNetNonIntMar
tEfficiency tOpprofit tAdjopprofit tavgperexperemp tavgperexperass;
array pval{11} pIncperAss pIncperEq pNoTaxperAss pNoTaxperEq pNetIntMar pNetNonIntMar
pEfficiency pOpprofit pAdjopprofit pavgperexperemp pavgperexperass;

do i=1 to 11;
	/*tstat for difference*/
	tstat{i}=diff{i}/stddiff{i};
	/*pval for difference*/
	pval{i}=PDF('T',tstat{i},dfdiff{i});
end;
run;
proc export DATA=SBANKS.cstatusmeans
			outfile= "D:\Current Wheelock\Active\S type Banks\total_assets\Status Change(T_A).xls"
			DBMS=EXCEL REPLACE;
	 SHEET="CMEANS";
run;
proc export DATA=SBANKS.sstatusmeans
			outfile= "D:\Current Wheelock\Active\S type Banks\total_assets\Status Change(T_A).xls"
			DBMS=EXCEL REPLACE;
	 SHEET="SMEANS";
run;
proc export DATA= SBANKS.pvalstatusdifferences 
            OUTFILE= "D:\Current Wheelock\Active\S type Banks\total_assets\Status Change(T_A).xls" 
            DBMS=EXCEL REPLACE;
     SHEET="Differences"; 
RUN;

/* ===================================================================================== * 
 *  Divide banks into two classes based on assets, named big and small.                  *
 *  Calculate the median by group for adjusted and unadjusted measures of ROA and ROE    *
 *  This data is used to creates Figures 2-4.                                            *
 * ===================================================================================== */
data sbanks.bigandsmall_1 (keep=IncperAss IncSperass incpereq incspereq
opincperass notaxperass subsflag year size);
set sbanks.calculations;
format size $5.;
if year>2003 then do;
	if peer_grp>15 or peer_grp<1 then delete;
	end;
else do;
	if peer_grp>24 or peer_grp<1 then delete;
	end;
if UBD51990>1000000 then size="BIG";
else size="SMALL";
run;
proc sort data=sbanks.bigandsmall_1;
by size year;
run;
proc means data=sbanks.bigandsmall_1 noprint median;
output out=sbanks.bigandsmall_2 median(Incperass incpereq)=ROA ROE;
by size year;
run;
data sbanks.bigandsmall_2 (drop=_TYPE_ _freq_);
set sbanks.bigandsmall_2;
Obs=_freq_;
run;
PROC EXPORT DATA= SBANKS.bigandsmall_2
            OUTFILE= "D:\Current Wheelock\Active\S type Banks\total_assets\bigandsmall(T_A).xls" 
            DBMS=EXCEL REPLACE;
     SHEET="Step 2"; 
RUN;
/* This step makes the variable mROE and mROA (adjusted ROA and ROE for S-bank status
   by combining the unadjusted measures for non-Sbanks with the S-bank adjusted measure */
data sbanks.bigandsmall_3;
set sbanks.bigandsmall_1;
if subsflag = 1 then do; mROA = incSperass; mROE = incSpereq; end;
	else do; mROA = incperass; mROE = incpereq; end;
run;
proc means data=sbanks.bigandsmall_3 noprint median;
output out=sbanks.bigandsmall_3 median(mROA mROE)=mROA mROE;
by  size year;
run;
data sbanks.bigandsmall_3 (drop=_TYPE_ _freq_);
set sbanks.bigandsmall_3;
Obs=_freq_;
run;
PROC EXPORT DATA= SBANKS.bigandsmall_3
            OUTFILE= "D:\Current Wheelock\Active\S type Banks\total_assets\bigandsmall(T_A).xls" 
            DBMS=EXCEL REPLACE;
     SHEET="Step 3"; 
RUN;
proc means data=sbanks.bigandsmall_1 noprint median;
output out=sbanks.bigandsmall_4 median(opincperass notaxperass)= OpIncperAss NoTaxperAss;
by  size year;
run;
data sbanks.bigandsmall_4 (drop=_TYPE_ _freq_);
set sbanks.bigandsmall_4;
Obs=_freq_;
run;
PROC EXPORT DATA= SBANKS.bigandsmall_4
            OUTFILE= "D:\Current Wheelock\Active\S type Banks\total_assets\bigandsmall(T_A).xls" 
            DBMS=EXCEL REPLACE;
     SHEET="Step 4&5"; 
RUN;