This file contains the programs for Kevin Kliesen's and Alton Gilbert's
article in the January/February 1996 Review "Are Some Agricultural
Banks Too Agricultural?".

The programs were written for SAS609.

The data needed to run these programs is confidential and therefore,
not included with this file.





********************************************************************
********************************************************************
********************************************************************
*******THIS PROGRAM CREATES THE MAPS FOR FIGURE 1.******************
********************************************************************
********************************************************************
********************************************************************;

filename grafout pipe 'lp -dps1';
goptions reset=all nocell nocharacters targetdevice=ps rotate=landscape
 gsfmode=replace gaccess=gsasfile hsize=9.5 in vsize=7.5 in
 gsflen=80 dev=xcolor vpos=120 hpos=150 gsfname=grafout;

 libname maps '/users/h1hlb00/maps';
 filename in '/users/h1hlb00/ragklk';
 libname map '/users/h1hlb00/templates';

FILENAME IN3 '/users/h1hlb00/ragklk';

proc format;
  value $statfmt
  01='AL' 02='AK' 04='AZ' 05='AR' 06='CA' 08='CO' 09='CT' 10='DE' 11='DC'
  12='FL' 13='GA' 15='HI' 16='ID' 17='IL' 18='IN' 19='IA' 20='KS' 21='KY'
  22='LA' 23='ME' 24='MD' 25='MA' 26='MI' 27='MN' 28='MS' 29='MO' 30='MT'
  31='NE' 32='NV' 33='NH' 34='NJ' 35='NM' 36='NY' 37='NC' 38='ND' 39='OH'
  40='OK' 41='OR' 42='PA' 44='RI' 45='SC' 46='SD' 47='TN' 48='TX' 49='UT'
  50='VT' 51='VA' 53='WA' 54='WV' 55='WI' 56='WY';
RUN;

********************************************************
*THIS SHOWS THE DISTRIBUTION OF THE AGBANKS BY STATE AND
    FEDERAL RESERVE DISTRICT FOR 1994.
*AG BANKS HAVE LESS THAN $500 MILLION IN ASSETS.
*BANK94 IS DATA FOR DECEMBER 1994.
*THE DATA USED IN THE CHART IS CREATED IN THE PROGRAM
*    THAT CREATES THE 1994 FOR TABLES 4, 5, AND 6.
********************************************************;
DATA ALLAGBNK;
  infile in3(agbnk94) firstobs=1; input bank;
  LENGTH BANK9000 $  8.;
  BANK9000=(BANK);
  STATE=SUBSTR(BANK9000,3,2);
FORMAT STATE $STATFMT.;
RUN;

PROC SORT DATA=ALLAGBNK; BY STATE; RUN;
PROC FREQ DATA=ALLAGBNK NOPRINT;
 TABLES STATE/LIST SPARSE OUT=STATE;
 TITLE '1994 DISTRIBUTION BY STATE';
 TITLE2 "AG BANKS";
RUN;

***********************************************************************;
*THIS OUTPUTS A DATA SET FOR 1994 IN ORDER TO CREATE A DISTRIBUTION MAP;
***********************************************************************;
DATA AGS; SET STATE;
 SHARE=COUNT/3530;   *3530 = THE NUMBER OF AG BANKS;
GROUP=0;
  IF SHARE>=.10 THEN GROUP=1;
  ELSE IF .075<=SHARE<.10 THEN GROUP=2;
  ELSE IF .05<=SHARE<.75 THEN GROUP=3;
  ELSE IF .025<=SHARE<.5 THEN GROUP=4;
  ELSE IF SHARE<=.25 THEN GROUP=5;
RUN;


***********************************************************************;
*THIS SHOWS THE DISTRIBUTION OF THE NON-AG BANKS BY STATE AND
    FEDERAL RESERVE DISTRICT FOR 1994;
*SMALL NONAG BANKS HAVE LESS THAN $500 MILLION IN ASSETS;
*BANK94 IS DATA FOR DECEMBER 1994;
***********************************************************************;
DATA ALLAGBNK;
  INFILE IN3(SNABNK94) FIRSTOBS=1; INPUT BANK;
  LENGTH BANK9000 $  8.;
  BANK9000=(BANK);
  STATE=SUBSTR(BANK9000,3,2);
FORMAT STATE $STATFMT.;
RUN;

PROC SORT DATA=ALLAGBNK; BY DISTRICT STATE; RUN;
PROC FREQ DATA=ALLAGBNK NOPRINT;
 TABLES STATE/LIST SPARSE OUT=STATE;
TITLE '1994 DISTRIBUTION BY STATE';
TITLE2 "NON-AG BANKS";
RUN;

***********************************************************************;
*THIS OUTPUTS A DATA SET FOR 1994 IN ORDER TO CREATE A DISTRIBUTION MAP;
***********************************************************************;
DATA NONAG; SET STATE;
SHARE=COUNT/6186;   *6186 = THE NUMBER OF SMALL NONAG IN 1994;
GROUP=0;
  IF SHARE>=.10 THEN GROUP=1;
  ELSE IF .075<=SHARE<.10 THEN GROUP=2;
  ELSE IF .05<=SHARE<.75 THEN GROUP=3;
  ELSE IF .025<=SHARE<.5 THEN GROUP=4;
  ELSE IF SHARE<=.25 THEN GROUP=5;
RUN;


proc format;
  value sharefmt 1='> 10%'
                 2='7.5% to 10%'
                 3='5 %  to 7.5%'
                 4='2.5% to 5%'
                 5='< 2.5%';
 run;

data mapdata1;
  set ags(keep=name group share);
    code=stfips(name);  format code 2.;
    state=code;
  format share sharefmt.;
run;
data mapdata2;
  set nonag(keep=name group share);
    code=stfips(name);  format code 2.;
    state=code;
  format share sharefmt.;
run;

proc freq data=mapdata1;
  tables share/list sparse;
run;
proc freq data=mapdata2;
  tables share/list sparse;
run;

pattern1 v=m3x45 c=grey;              *1994 small nonag patterns=1 2 5 3 4;
pattern2 v=m2n0 c=grey;
pattern3 v=msolid c=dagr;             *1994 ag bank patterns=1 2 3 4 5;
pattern4 v=msolid c=black;
pattern5 v=msolid c=ligr;

legend value=(j=l h=2 font=simplex color=black) label=none;

proc gmap map=maps.us data=mapdata1 gout=plot all;
  id state;
  choro share/discrete coutline=black cempty=black legend=legend;
  title1 h=4.0 j=l f=simplex c=black "Figure 1 ";
  title2 h=4.0 j=l f=simplex c=black "Distribution of Agricultural Banks in the United States--1994";
  title3 h=8.6 j=c ' ';
 run;

proc gmap map=maps.us data=mapdata2 gout=plot all;
  id state;
  choro share/discrete coutline=black cempty=black legend=legend;
  title1 h=4.0 j=l f=simplex c=black ' ';
  title2 h=4.0 j=c f=simplex c=black "Distribution of Small Nonagricultural Banks in the United States--1994";
  title3 h=8.6 j=c ' ';
 run;

**template name is map, ag map is 1, small nonag map is 2;
proc greplay igout=plot tc=map.template template=map;
run;

  
  
  



************************************************************************
************************************************************************
************************************************************************
** THIS PROGRAM CREATES THE DATA FOR FIGURE 4.                        **
** THIS PROGRAM DUMPS 1994 AGBANK DATA TO A PDS SO DELTAGRAPH CHARTS  **
** CAN BE CREATED FOR ALTON GILBERT'S AND KEVIN KLIESEN'S AGRICULTURAL**
** BANKING PAPER (SUMMER/FALL 1994).                                  **
************************************************************************
************************************************************************
************************************************************************;

LIBNAME IN 'H1CAW01.AGBANK.DATA' DISP=SHR;
LIBNAME IN3 'H1HLB00.AGBANK.DATA';
FILENAME IN1 'H1HLB00.AGBANK.MACRO';
FILENAME IN2 'H1HLB00.AGDATA.CNTL';


PROC FORMAT;
     VALUE ZEROFMT
     0 = '           .'; *BE CAREFUL WITH THIS FORMAT! TOO MANY;
                         *SPACES CAN CORRUPT THE DATA AND RATIOS;
RUN;

%INC IN1(AGBK94A);
%INC IN1(AGBK94B);
%INC IN1(AGBK94C);
%INC IN1(AGBK94D);
%INC IN1(AGBK94E);
%INC IN1(AGBK94F);
************************************************************************
** READING IN CALL REPORT DATA FOR 1993.4-1994.4                      **
************************************************************************;
DATA AGBK9312;
     SET IN.RCRI9312(DROP=RIAD4074 RIAD4340);
RUN;
DATA AGBK9403;
     SET IN3.RCRI9403 (RENAME=(BANK=BANK9000));
RUN;
DATA AGBK9406;
     SET IN3.RCRI9406 (RENAME=(BANK=BANK9000));
RUN;
DATA AGBK9409;
     SET IN3.RCRI9409 (RENAME=(BANK=BANK9000));
RUN;
DATA AGBK9412;
    SET IN3.RCRI9412 (RENAME=(BANK=BANK9000));
                              *NEED FLOW VARIABLES IN 4TH QTR ONLY;
RUN;
DATA MERGE1;
     SET AGBK9312 AGBK9403 AGBK9406 AGBK9409 AGBK9412;
************************************************************************
** THE VARIABLE, DATE, IS A NUMERIC VARIABLE WHEN IT IS PULLED OFF OF **
** THE TAPE AT THE BOARD.  THEREFORE, TO CREATE A SAS DATE VALUE, YOU **
** HAVE TO FIRST USE A PUT FUNCTION TO RETURN THE NUMERIC DATE VALUE  **
** AS A CHARACTER STRING.  THEN, YOU HAVE TO USE THE INPUT            **
** FUNCTION TO RETURN THE CHARACTER STRING AS A SAS DATE VALUE.       **
** AFTER YOU HAVE CREATED A SASDATE VARIABLE, YOU CAN THEN CREATE A   **
** YEAR AND QUARTER VARIABLE.                                         **
************************************************************************;
     NUMDATE=PUT(DATE,Z6.);
     SASDATE=INPUT(NUMDATE,YYMMDD6.);
     YEAR=YEAR(SASDATE);
     QTR=QTR(SASDATE);
************************************************************************
** NICK WALRAVEN AND MICHELE RICCI SELECTED ONLY BANKS WHERE          **
** BANK9420<=23 AND BANK9210<=56 FOR THE AGRICULTURAL FINANCE         **
** DATABOOK.  ALTON ELECTED TO SUBSET BY THE SAME VARIABLES TO BE     **
** CONSISTENT.                                                        **
************************************************************************;
     IF BANK9420<24 & BANK9210<57;
RUN;

     PROC SORT DATA=MERGE1;     *ENABLES USE OF LAST.BANK AND LAST.DATE;
          BY BANK9000 DESCENDING DATE;
     RUN;

DATA MERGE2;
     SET MERGE1;
          BY BANK9000 DESCENDING DATE;
************************************************************************
** THE FOLLOWING STATEMENTS:                                          **
**         (1) CREATE A COUNTER, T, WITH POSSIBLE VALUES FROM 1-5     **
**         (2) DELETE BANKS WITH MISSING OBSERVATIONS IN A PARTICULAR **
**             QUARTER                                                **
**         (3) SUM THE COUNTER VARIABLE AND OUTPUT IT TO A NEW DATASET**
************************************************************************;
     RETAIN T 1;
     T+1;
     IF LAST.BANK9000 AND LAST.DATE THEN T=1;
     IF YEAR=1994 & (QTR=1³QTR=2³QTR=3³QTR=4) &
        (RCON0081=.³RCON1403=.³RCON1407=.³RCON1420=.³RCON1590=.³
        RCON2122=.³RCON2145=.³RCON2150=.³RCON2170=.³RCON3123=.³
        RCON3210=.) THEN DELETE;
RUN;

     PROC MEANS SUM NOPRINT DATA=MERGE2;
          VAR T;
          BY BANK9000;
          OUTPUT OUT=SUMOUT SUM=SUMT;
     RUN;

DATA MERGE3;
     MERGE MERGE2 SUMOUT;
          BY BANK9000;
************************************************************************
** IF SUMT (CREATED ABOVE) <15, THEN THIS IMPLIES THAT THE BANK HAD   **
** MISSING OBSERVATIONS IN AT LEAST ONE QUARTER IN 1993.  SINCE ONLY  **
** MISSING OBSERVATIONS WERE DELETED ABOVE, THERE MAY VERY WELL BE    **
** NON-MISSING OBSERVATIONS FOR OTHER QUARTERS (I.E., IT IS ENTIRELY  **
** POSSIBLE THAT A BANK ONLY HAD MISSING CALL REPORT DATA FOR SAY THE **
** 2ND QUARTER).  HOWEVER, ONLY BANKS WITH NON-MISSING OBSERVATIONS   **
** IN ALL FOUR QUARTERS CAN REMAIN IN THE SAMPLE.  THEREFORE, BANKS   **
** WHERE SUMT < 15 MUST BE DELETED BECAUSE ONLY BANKS WHERE SUMT=15   **
** HAVE NON-MISSING OBSERVATIONS FOR ALL 4 QUARTERS IN THE YEAR.      **
************************************************************************;
      IF SUMT < 15 THEN DELETE;
RUN;

DATA USAGBANK;
     SET MERGE3(WHERE=(YEAR=1994 & QTR=2));BY BANK9000;
     RCON2122=LEFT(INPUT(PUT(RCON2122,ZEROFMT.),BEST12.));
     USRATIO= ((SUM(RCON1420,RCON1590))/RCON2122)*100;
RUN;
     PROC MEANS DATA=USAGBANK;
          VAR USRATIO;
          OUTPUT OUT=USAGBK MEAN=USRATIO;
          TITLE1 'US RATIO IN 1994';
     RUN;

************************************************************************
** FRACTIONALIZATION BEGINS HERE!!                                    **
************************************************************************;
DATA AGBK9312;
     SET MERGE3(WHERE=(YEAR=1993 & QTR=4));
     AGBANK=1;
     %AGBK94A;
     RUN;

DATA AGBK9403;
     SET MERGE3(WHERE=(YEAR=1994 & QTR=1));
     AGBANK=2;
     %AGBK94B;
     RUN;

DATA AGBK9406;
     SET MERGE3(WHERE=(YEAR=1994 & QTR=2));
     AGBANK=3;
     %AGBK94C;
     RUN;

DATA AGBK9409;
     SET MERGE3(WHERE=(YEAR=1994 & QTR=3));
     AGBANK=4;
     %AGBK94D;
     RUN;

DATA AGBK9412;
     SET MERGE3(WHERE=(YEAR=1994 & QTR=4));
     AGBANK=5;
     %AGBK94E;
     RUN;
************************************************************************
** FRACTIONALIZATION ENDS HERE!!                                      **
************************************************************************;

DATA MERGAGBK; **CHECK THIS CAREFULLY!!;
     MERGE AGBK9312 AGBK9403 AGBK9406 AGBK9409 AGBK9412;
          BY BANK9000;
RUN;

DATA AGBK94A;
************************************************************************
** THE FOLLOWING IF STATEMENT SETS IN THE US AGGREGATE RATIO OF       **
** (RCON1420+RCON1590)/RCON2122 AND MERGES IT TO ALL OBSERVATIONS IN  **
** THE DATA SET MERGAGBK (CREATED IN THE DATA STEP ABOVE).            **
************************************************************************;
     IF _N_=1 THEN SET USAGBK;
     DROP _TYPE_ _FREQ_;
     SET MERGAGBK;
     IF AGBANK=5;
     %MACRO QTRSUM;
          %LET VNAMES=
               R1420 R1590 R2122 R2170 R3210 R1407 R1403
               R2150 R3123 R0081 R2145;

          %DO AG=1 %TO 11;
               %LET VNAME= %SCAN(&VNAMES,&AG);
               &VNAME=SUM(&VNAME.A,&VNAME.B,&VNAME.C,&VNAME.D,
                          &VNAME.E);
               DROP &VNAME.A &VNAME.B &VNAME.C &VNAME.D &VNAME.E;
          %END;
    %MEND QTRSUM;

   *INVOKE QTRSUM MACRO CREATED ABOVE;
    %QTRSUM;
************************************************************************
** IDENTIFY AGRICULTURAL BANKS USING DEFINITION SUPPLIED BY ALTON     **
** GILBERT                                                            **
************************************************************************;
    R2122=LEFT(INPUT(PUT(R2122,ZEROFMT.),BEST12.));
    R2170=LEFT(INPUT(PUT(R2170,ZEROFMT.),BEST12.));
    AGLOAN=SUM(R1420,R1590);
    AGBKRATO= (AGLOAN/R2122)*100;
    IF AGBKRATO > USRATIO THEN AGBNK=1;               *AN AG BANK*;
         ELSE IF AGBKRATO <= USRATIO THEN AGBNK=0;    *NOT AN AG BANK*;
************************************************************************
**  CREATING RANGES OF AGRICULTURAL LOANS (R1420+R1590) TO TOTAL      **
**  LOANS (R2122) (TO BE USED LATER IN %AGBK93F)                      **
************************************************************************;
    IF AGBKRATO >= 0 & AGBKRATO <= 10 THEN RANGE=1;
    ELSE IF 10 < AGBKRATO <= 15 THEN RANGE=2;
    ELSE IF 15 < AGBKRATO <= 20 THEN RANGE=3;
    ELSE IF 20 < AGBKRATO <= 25 THEN RANGE=4;
    ELSE IF 25 < AGBKRATO <= 30 THEN RANGE=5;
    ELSE IF 30 < AGBKRATO <= 40 THEN RANGE=6;
    ELSE IF 40 < AGBKRATO <= 50 THEN RANGE=7;
    ELSE IF 50 < AGBKRATO <= 60 THEN RANGE=8;
    ELSE IF 60 < AGBKRATO <= 70 THEN RANGE=9;
    ELSE IF AGBKRATO > 70 THEN RANGE=10;
    ELSE IF AGBKRATO = .  THEN RANGE=11;
************************************************************************
**  CALCULATING VARIOUS RATIOS OUTLINED IN ALTON'S 7/15/94 MEMO FOR 3 **
**  GROUPS OF BANKS:  (1) AGRICULTURAL BANKS, (2) COMMERCIAL BANKS    **
**  (NOT AG BANKS!) WITH TOTAL ASSETS <= 500 MILLION, AND (3)         **
**  COMMERICAL BANKS (NOT AG BANKS!) WITH TOTAL ASSETS <= INDEXED     **
**  AVERAGE TOTAL ASSETS OF THE BANKING INDUSTRY (IN THAT YEAR).      **
**  NOTE, HOWEVER, THAT GROUP (3) = GROUP (2) FOR 1993 BECAUSE 1993   **
**  IS THE BASE PERIOD FOR GROUP (3).  SEE ALTON'S MEMO FOR FURTHER   **
**  DETAILS.                                                          **
************************************************************************;

* AGRICULTURAL LOANS / TOTAL ASSETS;
     AGLNASS= (AGLOAN/R2170);

* RETURN ON EQUITY= (EQUITY/TOTAL ASSETS);
     ROE= (R3210/R2170);

RUN;

************************************************************************
** THIS OUTPUTS THE DATA FOR THE PLOT (FIGURE 4)                      **
************************************************************************;
DATA AGBK94B;
     SET AGBK94A(WHERE=(AGBNK=1));
     FILE IN2(FIGURE);
     PUT @1 BANK9000 @15 ROE @30 AGLNASS;
RUN;

************************************************************************
** THIS CREATES THE VERTICAL AND HORIZONTAL LINES ON THE PLOT         **
************************************************************************;
PROC MEANS DATA=AGBK94B;
     VAR ROE AGLNASS;
RUN;



 
 
 

************************************************************************
************************************************************************
************************************************************************
** THIS CREATES TABLE 1 IN THE ARTICLE.                               **
** THIS PROGRAM PRODUCES A 'BALANCE SHEET' FOR KEVIN KLIESEN'S AG BANK**
** PAPER WITH ALTON GILBERT.  TO RUN THE PROGRAM, CHANGE THE MACRO    **
** VARIABLE, YEAR, TO THE YEAR THAT YOU WOULD LIKE TO PRODUCE A       **
** BALANCE SHEET FOR.                                                 **
************************************************************************
************************************************************************
************************************************************************;

LIBNAME IN 'H1CAW01.LIBRARY.REGAN';
FILENAME IN3 'H1HLB00.AGBANK.CNTL';
FILENAME IN2 'H1HLB00.AGDATA.CNTL';



PROC FORMAT;
     VALUE ZEROFMT
     0 = '           .'; *BE CAREFUL WITH THIS FORMAT! TOO MANY;
                         *SPACES CAN CORRUPT THE DATA AND RATIOS;
RUN;

******************************************************************************
** READING IN CALL REPORT DATA THAT IDENTIFIES CREDIT CARD BANKS FOR 1994.2
******************************************************************************;
DATA CREDBANK; INFILE IN3(CREDIT) FIRSTOBS=1;
  INPUT BANK TYPE;
RUN;

************************************************************************
** READING IN CALL REPORT SECURITIES DATA FOR 1994.2                  **
************************************************************************;
DATA A; INFILE IN2(SECU694) FIRSTOBS=507;
  INPUT OBS DATE BANK BANK9000 RCFD1754 RCFD1771 RCFD1772 RCFD1773;

************************************************************************
** READING IN CALL REPORT DATA FOR 1994.2                          **
************************************************************************;
DATA B; SET IN.RAGKLK94;
RUN;

PROC SORT DATA=A; BY BANK;
RUN;
PROC SORT DATA=B; BY BANK;
RUN;
PROC SORT DATA=CREDBANK; BY BANK;
RUN;

DATA MERGE1;
  MERGE A B(WHERE=(BANK9420<=24 & BANK9210<57)) CREDBANK;
            *KEEPING ONLY DOMESTIC COMM. BANKS;
  BY BANK;

  ***KEEP NON CREDIT CARD BANKS***;
  IF TYPE=0;

  RCON0391=SUM(RCFD1771,RCFD1773);

          RCON2200=LEFT(INPUT(PUT(RCON2200,ZEROFMT.),BEST12.));
          RCON2170=LEFT(INPUT(PUT(RCON2170,ZEROFMT.),BEST12.));
          RCON2948=LEFT(INPUT(PUT(RCON2948,ZEROFMT.),BEST12.));
          * CREATING VARIABLES FOR KLK'S &YEAR BALANCE SHEET;
               CASH    =(SUM(RCON0020,RCON0080)/RCON2170);
               SECURE2 =(RCON0391/RCON2170); *TOT SECURITIES, MARKET -NOT
                                              AVAILABLE UNTIL 19840331;
               LOANS   =(RCON2122/RCON2170);
               AGLOANS= (SUM(RCON1420,RCON1590)/RCON2170)*100;
               EQUITY  =(RCON3210/RCON2170);
               DEPOSITS=(RCON2200/RCON2948);
               ASSETS = RCON2170;
RUN;

     PROC SORT DATA=MERGE1;
          BY BANK DATE;
     RUN;

DATA USAGBANK;
     SET MERGE1;
     RCON2122=LEFT(INPUT(PUT(RCON2122,ZEROFMT.),BEST12.));
     USRATIO= ((SUM(RCON1420,RCON1590))/RCON2122)*100;
     PROC MEANS NOPRINT DATA=USAGBANK;
          VAR USRATIO;
          OUTPUT OUT=USAGBK MEAN=USRATIO;
RUN;

DATA AGBK94A;
************************************************************************
** THE FOLLOWING IF STATEMENT SETS IN THE US AGGREGATE RATIO OF       **
** (RCON1420+RCON1590)/RCON2122 AND MERGES IT TO ALL OBSERVATIONS IN  **
** THE DATA SET MERGAGBK (CREATED IN THE DATA STEP ABOVE).            **
************************************************************************;
     IF _N_=1 THEN SET USAGBK;
     DROP _TYPE_ _FREQ_;
     SET MERGE1;

************************************************************************
** IDENTIFY AGRICULTURAL BANKS USING DEFINITION SUPPLIED BY ALTON     **
** GILBERT                                                            **
************************************************************************;
    RCON2122=LEFT(INPUT(PUT(RCON2122,ZEROFMT.),BEST12.));
    AGBKRATO= (SUM(RCON1420,RCON1590)/RCON2122)*100;
    IF AGBKRATO > USRATIO THEN AGBNK=1;               *AN AG BANK*;
         ELSE IF AGBKRATO <= USRATIO THEN AGBNK=0;    *NOT AN AG BANK*;

     LABEL AGBKRATO='AGRI. BANK RATIO'
           CASH    ='CASH AS A PERCENT OF TOTAL ASSETS (TA)'
           SECURE2 ='TOT. SECURITIES, MKT. VALUE AS % OF TA'
           LOANS   ='TOTAL LOANS AS A PERCENT OF TA'
           AGLOANS ='AGRICULTURAL LOANS AS A PERCENT OF TA'
           DEPOSITS='TOTAL DEPOSITS AS A PERCENT OF TOT. LIAB.'
           EQUITY  ='EQUITY CAPITAL AS A PERCENT OF TA'
           ASSETS  ='TOTAL ASSETS'
           LOANDEP ='LOAN-TO-DEPOSIT RATIO';

************************************************************************
**  THE FOLLOWING IF STATEMENT IDENTIFIES COMMERICAL BANKS (NOT AG    **
**  BANKS!-I.E., AGBNK=0) WITH TOTAL ASSETS <= 500 MILLION,           **
**  COMMERCIAL BANKS WITH 1 BILLION >= TOTAL ASSETS > 500 MILLION     **
**  AND AG BANKS WITH ASSETS <= 500 MILLION                           **
************************************************************************;
     IF AGBNK=0 AND ASSETS   <= 500000 THEN ASSETSZ=1;
           ELSE IF AGBNK=0 AND 1000000>=ASSETS > 500000 THEN ASSETSZ=0;
           ELSE IF AGBNK=1 AND 500000 >= ASSETS >= 0 THEN ASSETSZ=2;
RUN;


************************************************************************
**  SUMMARY STATISTICS FOR AGRICULTURAL BANKS
**           WITH TOTAL ASSETS <= 500 MILLION                         **
************************************************************************;
     PROC MEANS DATA=AGBK94A(WHERE=(ASSETSZ=2));
          VAR CASH SECURE2 LOANS AGLOANS EQUITY DEPOSITS ASSETS;
          TITLE  'BALANCE SHEET AS OF 6/30/94';
          TITLE2 'FOR AGRICULTURAL BANKS ';
          TITLE3 'WITH TOTAL ASSETS <= 500 MIL';
     RUN;

************************************************************************
**  SUMMARY STATISTICS FOR COMMERICAL BANKS (NOT AG BANKS!-I.E.,      **
**  AGBNK=0) WITH TOTAL ASSETS <= 500 MILLION                         **
************************************************************************;
     PROC MEANS DATA=AGBK94A(WHERE=(ASSETSZ=1));
          VAR CASH SECURE2 LOANS AGLOANS EQUITY DEPOSITS ASSETS;
          TITLE  'BALANCE SHEET AS OF 6/30/94';
          TITLE2 'FOR NON-AGRICULTURAL BANKS ';
          TITLE3 'WITH TOTAL ASSETS <= 500 MIL';
     RUN;

************************************************************************
**  SUMMARY STATISTICS FOR COMMERICAL BANKS (NOT AG BANKS!-I.E.,      **
**  AGBNK=0) WITH TOTAL ASSETS <= 500 MILLION                         **
************************************************************************;
     PROC MEANS DATA=AGBK94A(WHERE=(ASSETSZ=0));
          VAR CASH SECURE2 LOANS AGLOANS EQUITY DEPOSITS ASSETS;
          TITLE  'BALANCE SHEET AS OF 6/30/94';
          TITLE2 'FOR NON-AGRICULTURAL BANKS ';
          TITLE3 'WITH 1 BILLION >= TOTAL ASSETS > 500 MIL';
     RUN;







************************************************************************
************************************************************************
************************************************************************
** THIS CREATES THE 1970 DATA IN TABLES 4 AND 5.                      **
************************************************************************
************************************************************************
************************************************************************;

LIBNAME IN 'H1CAW01.AGBANK.DATA' DISP=SHR;
FILENAME IN1 'H1CAW01.RAGKLK.MACRO' DISP=SHR;
LIBNAME IN2 'H1HLB00.AGBANK.DATA';

PROC FORMAT;
  VALUE SIZEFMT
  3="AG BANKS"
  1="SMALL NON-AG BANKS"
  2="OTHER NON-AG BANKS";
RUN;

************************************************************************
** THIS PROGRAM IS USED TO COMPUTE VARIOUS FINANCIAL RATIOS AND       **
** SUMMARY STATISTICS FOR ALTON GILBERT'S AND KEVIN KLIESEN'S         **
** AGRICULTURAL BANKING PAPER (SUMMER/FALL 1994).  IT                 **
** IS USED FIRST TO IDENTIFY BANKS WITHOUT CALL REPORT DATA           **
** (I.E., WITH MISSING DATA) FOR ANY QUARTER IN 1970.  IF A MISSING   **
** OBSERVATION IS DETECTED FOR ANY QUARTER, THEN ALL OBSERVATIONS FOR **
** THAT BANK IN 1970 ARE DELETED (BECAUSE ALTON AND KEVIN ONLY WANT   **
** BANKS IN THEIR SAMPLE WITH CALL REPORT DATA FOR ALL FOUR QUARTERS  **
** IN 1970).  THE PROGRAM IS THEN USED TO FRACTIONALIZE ALL OF THE    **
** BALANCE SHEET VARIABLES (I.E., ESTIMATE ANNUAL AVERAGE OF EACH     **
** VARIABLE OVER THE YEAR).  NOTE:  INCOME AND EXPENSE DATA ARE NOT   **
** FRACTIONALIZED BECAUSE THEY ARE FLOW VARIABLES.  FINALLY, THE      **
** PROGRAM COMPUTES THE VARIOUS SUMMARY STATISTICS AND RATIOS         **
** OUTLINED IN THE 7/15/94 AND 7/21/94 MEMOS FROM ALTON.  NOTE:       **
** THIS PROGRAM IS DOCUMENTED BELOW FOR FUTURE USE.                   **
************************************************************************;

PROC FORMAT;
     VALUE ZEROFMT
     0 = '           .'; *BE CAREFUL WITH THIS FORMAT! TOO MANY;
                         *SPACES CAN CORRUPT THE DATA AND RATIOS;
RUN;

%INC IN1(AGBK70A);
%INC IN1(AGBK70C);
%INC IN1(AGBK70E);
%INC IN1(AGBK70F);
************************************************************************
** READING IN CALL REPORT DATA FOR 1969.4-1970.4                      **
** PRIOR TO 1984.1, RCON0081 IS KNOWN AS RCON0010.                    **
** NOTE:  RCON2122=(RCON1400-RCON3370) PRIOR TO 1976.1.>>>>>>>>>>>>>  **
************************************************************************;
DATA AGBK6912;
     SET IN.RCRI6912(DROP=RIAD4010 RIAD4020 RIAD4027 RIAD4050 RIAD4060
                          RIAD4170 RIAD4180 RIAD4190 RIAD4200);
     RCON2122=(RCON1400-RCON3370);  *NOTICE CHANGE IN TOTAL LOANS DEFINITION;
RUN;
DATA AGBK7006;
     SET IN.RCRI7006(DROP=RIAD4010 RIAD4020 RIAD4027 RIAD4050 RIAD4060
                          RIAD4170 RIAD4180 RIAD4190 RIAD4200);
     RCON2122=(RCON1400-RCON3370);  *NOTICE CHANGE IN TOTAL LOANS DEFINITION;
RUN;
DATA AGBK7012;
     SET IN.RCRI7012;         *DO NOT DROP RIAD VARIABLES IN 4TH QTR!;
                              *NEED FLOW VARIABLES IN 4TH QTR ONLY;
     RCON2122=(RCON1400-RCON3370);  *NOTICE CHANGE IN TOTAL LOANS DEFINITION;
RUN;
DATA MERGE1;
     SET AGBK6912 AGBK7006 AGBK7012;
************************************************************************
** THE VARIABLE, DATE, IS A NUMERIC VARIABLE WHEN IT IS PULLED OFF OF **
** THE TAPE AT THE BOARD.  THEREFORE, TO CREATE A SAS DATE VALUE, YOU **
** HAVE TO FIRST USE A PUT FUNCTION TO RETURN THE NUMERIC DATE VALUE  **
** AS A CHARACTER STRING.  THEN, YOU HAVE TO USE THE INPUT            **
** FUNCTION TO RETURN THE CHARACTER STRING AS A SAS DATE VALUE.       **
** AFTER YOU HAVE CREATED A SASDATE VARIABLE, YOU CAN THEN CREATE A   **
** YEAR AND QUARTER VARIABLE.                                         **
************************************************************************;
     NUMDATE=PUT(DATE,Z6.);
     SASDATE=INPUT(NUMDATE,YYMMDD6.);
     YEAR=YEAR(SASDATE);
     QTR=QTR(SASDATE);
************************************************************************
** NICK WALRAVEN AND MICHELE RICCI SELECTED ONLY BANKS WHERE          **
** BANK9420<=23 AND BANK9210<=56 FOR THE AGRICULTURAL FINANCE         **
** DATABOOK.  ALTON ELECTED TO SUBSET BY THE SAME VARIABLES TO BE     **
** CONSISTENT.                                                        **
************************************************************************;
     IF BANK9420<24 & BANK9210<57;
RUN;
     PROC SORT DATA=MERGE1;     *ENABLES USE OF LAST.BANK AND LAST.DATE;
          BY BANK9000 DESCENDING DATE;
RUN;

DATA MERGE2;
     SET MERGE1;
          BY BANK9000 DESCENDING DATE;
************************************************************************
** THE FOLLOWING STATEMENTS:                                          **
**         (1) CREATE A COUNTER, T, WITH POSSIBLE VALUES FROM 1-5     **
**         (2) DELETE BANKS WITH MISSING OBSERVATIONS IN A PARTICULAR **
**             QUARTER                                                **
**         (3) SUM THE COUNTER VARIABLE AND OUTPUT IT TO A NEW DATASET**
************************************************************************;
     RETAIN T 1;
     T+1;
     IF LAST.BANK9000 AND LAST.DATE THEN T=1;
     IF YEAR=1970 & (QTR=2³QTR=4) &
        (RCON0010=.³RCON1420=.³RCON1590=.³
        RCON2122=.³RCON2145=.³RCON2150=.³RCON2170=.³
        RCON3210=.) THEN DELETE;
RUN;
     PROC MEANS SUM NOPRINT DATA=MERGE2;
          VAR T;
          BY BANK9000;
          OUTPUT OUT=SUMOUT SUM=SUMT;
RUN;

DATA MERGE3;
     MERGE MERGE2 SUMOUT;
          BY BANK9000;
************************************************************************
** IF SUMT (CREATED ABOVE) <6 , THEN THIS IMPLIES THAT THE BANK HAD   **
** MISSING OBSERVATIONS IN AT LEAST ONE QUARTER IN 1970.  SINCE ONLY  **
** MISSING OBSERVATIONS WERE DELETED ABOVE, THERE MAY VERY WELL BE    **
** NON-MISSING OBSERVATIONS FOR OTHER QUARTERS (I.E., IT IS ENTIRELY  **
** POSSIBLE THAT A BANK ONLY HAD MISSING CALL REPORT DATA FOR SAY THE **
** 2ND QUARTER).  HOWEVER, ONLY BANKS WITH NON-MISSING OBSERVATIONS   **
** IN ALL FOUR QUARTERS CAN REMAIN IN THE SAMPLE.  THEREFORE, BANKS   **
** WHERE SUMT < 6  MUST BE DELETED BECAUSE ONLY BANKS WHERE SUMT=6    **
** HAVE NON-MISSING OBSERVATIONS FOR QTRS 2 AND 4 IN 1970.            **
************************************************************************;
      IF SUMT < 6 THEN DELETE;
RUN;

DATA USAGBANK;
     SET MERGE3(WHERE=(YEAR=1970 & QTR=2));BY BANK9000;
     RCON2122=LEFT(INPUT(PUT(RCON2122,ZEROFMT.),BEST12.));
     USRATIO= ((SUM(RCON1420,RCON1590))/RCON2122)*100;
RUN;
     PROC MEANS DATA=USAGBANK ;
          VAR USRATIO;
          OUTPUT OUT=USAGBK MEAN=USRATIO;
          TITLE1 'US RATIO IN 1970';
          LABEL USRATIO="RATIO OF AGLOANS TO TOTAL LOANS";
     RUN;
************************************************************************
** FRACTIONALIZATION BEGINS HERE!!                                    **
************************************************************************;
DATA AGBK6912;
     SET MERGE3(WHERE=(YEAR=1969 & QTR=4));
     AGBANK=1;
     %AGBK70A;
     RUN;

DATA AGBK7006;
     SET MERGE3(WHERE=(YEAR=1970 & QTR=2));
     AGBANK=3;
     %AGBK70C;
     RUN;

DATA AGBK7012;
     SET MERGE3(WHERE=(YEAR=1970 & QTR=4));
     AGBANK=5;
     %AGBK70E;
     RUN;
************************************************************************
** FRACTIONALIZATION ENDS HERE!!                                      **
************************************************************************;
DATA MERGAGBK; **CHECK THIS CAREFULLY!!;
     MERGE AGBK6912 AGBK7006 AGBK7012;
          BY BANK9000;
     RUN;

DATA AGBK70A;
************************************************************************
** THE FOLLOWING IF STATEMENT SETS IN THE US AGGREGATE RATIO OF       **
** (RCON1420+RCON1590)/RCON2122 AND MERGES IT TO ALL OBSERVATIONS IN  **
** THE DATA SET MERGAGBK (CREATED IN THE DATA STEP ABOVE).            **
************************************************************************;
     IF _N_=1 THEN SET USAGBK;
     DROP _TYPE_ _FREQ_;
     SET MERGAGBK;
     IF AGBANK=5;
     %MACRO QTRSUM;
          %LET VNAMES=
               R1420 R1590 R2122 R2170 R3210
               R2150 R0010 R2145;

          %DO AG=1 %TO 8;
               %LET VNAME= %SCAN(&VNAMES,&AG);
               &VNAME=SUM(&VNAME.A,&VNAME.C,&VNAME.E);
               DROP &VNAME.A &VNAME.C &VNAME.E;
          %END;
    %MEND QTRSUM;

   *INVOKE QTRSUM MACRO CREATED ABOVE;
    %QTRSUM;
************************************************************************
** IDENTIFY AGRICULTURAL BANKS USING DEFINITION SUPPLIED BY ALTON     **
** GILBERT                                                            **
************************************************************************;
    R2122=LEFT(INPUT(PUT(R2122,ZEROFMT.),BEST12.));
    R2170=LEFT(INPUT(PUT(R2170,ZEROFMT.),BEST12.));
    AGLOAN=SUM(R1420,R1590);
    AGBKRATO= (AGLOAN/R2122)*100;
    IF AGBKRATO > USRATIO THEN AGBNK=1;               *AN AG BANK*;
         ELSE IF AGBKRATO <= USRATIO THEN AGBNK=0;    *NOT AN AG BANK*;
************************************************************************
**  CREATING RANGES OF AGRICULTURAL LOANS (R1420+R1590) TO TOTAL      **
**  LOANS (R2122) (TO BE USED LATER IN %AGBK70F)                      **
************************************************************************;
    IF AGBKRATO >= 0 & AGBKRATO <= 10 THEN RANGE=1;
    ELSE IF 10 < AGBKRATO <= 15 THEN RANGE=2;
    ELSE IF 15 < AGBKRATO <= 20 THEN RANGE=3;
    ELSE IF 20 < AGBKRATO <= 25 THEN RANGE=4;
    ELSE IF 25 < AGBKRATO <= 30 THEN RANGE=5;
    ELSE IF 30 < AGBKRATO <= 40 THEN RANGE=6;
    ELSE IF 40 < AGBKRATO <= 50 THEN RANGE=7;
    ELSE IF 50 < AGBKRATO <= 60 THEN RANGE=8;
    ELSE IF 60 < AGBKRATO <= 70 THEN RANGE=9;
    ELSE IF AGBKRATO > 70 THEN RANGE=10;
    ELSE IF AGBKRATO = .  THEN RANGE=11;
************************************************************************
**  CALCULATING VARIOUS RATIOS OUTLINED IN ALTON'S 7/15/94 MEMO FOR 3 **
**  GROUPS OF BANKS:  (1) AGRICULTURAL BANKS, (2) COMMERCIAL BANKS    **
**  (NOT AG BANKS!) WITH TOTAL ASSETS <= 500 MILLION, AND (3)         **
**  COMMERICAL BANKS (NOT AG BANKS!) WITH TOTAL ASSETS <= INDEXED     **
**  AVERAGE TOTAL ASSETS OF THE BANKING INDUSTRY (IN THAT YEAR).      **
**  NOTE, HOWEVER, THAT GROUP (3) = GROUP (2) FOR 1994 BECAUSE 1994   **
**  IS THE BASE PERIOD FOR GROUP (3).  SEE ALTON'S MEMO FOR FURTHER   **
**  DETAILS.                                                          **
************************************************************************;
* RETURN ON ASSETS= (NET INCOME/TOTAL ASSETS);
     ROA= (RIAD4340/R2170);

* RETURN ON EQUITY= (EQUITY/TOTAL ASSETS);
     ROE= (R3210/R2170);

* NET INTEREST MARGIN= (NET INTEREST INCOME/EARNING ASSETS), WHERE;
* EARNINGS ASSETS= INTEREST BEARING BALANCES+SECURITIES+FEDERAL FUNDS;
* SOLD AND REPURCHASE AGREEMENTS+NET LOANS+TRADING ACCOUNT ASSETS;
     NUMER=(SUM(RIAD4010,RIAD4020,RIAD4027,RIAD4050,RIAD4060)
                -SUM(RIAD4170,RIAD4180,RIAD4190,RIAD4200));
     NIM= ((NUMER)/(R2170-(R0010+R2145+R2150)));     *CHANGED PER 7/21/94 MEMO;
RUN;

PROC MEANS DATA=AGBK70A N SUM;
     VAR R2170;
     TITLE "TOTAL US ASSETS IN 1970";
RUN;
DATA AGBK70B;                *COMPUTING SUMMARY STATISTICS FOR AGBANKS;
     SET AGBK70A;
     IF AGBNK=1;
RUN;

     PROC MEANS DATA=AGBK70B ;
          VAR AGBKRATO;
          TITLE 'AG BANKS IN 1970';
     %AGBK70F;
     RUN;

DATA AGBK70C;
     SET AGBK70A;
************************************************************************
**  THE FOLLOWING IF STATEMENT IDENTIFIES COMMERICAL BANKS (NOT AG    **
**  BANKS!-I.E., AGBNK=0) WITH TOTAL ASSETS < 500 MILLION             **
************************************************************************;
     IF AGBNK=0 AND R2170 <= 500000 THEN ASSETSZ=1;
           ELSE IF AGBNK=0 AND 1000000>=R2170 > 500000 THEN ASSETSZ=2;
           ELSE IF AGBNK=1 AND 500000 >= R2170 >= 0 THEN ASSETSZ=3;
     RUN;

DATA AGBK70D;
     SET AGBK70C;
     FORMAT ASSETSZ SIZEFMT.;
RUN;
************************************************************************
**  SUMMARY STATISTICS FOR COMMERICAL BANKS (NOT AG BANKS!-I.E.,      **
**  AGBNK=0) WITH TOTAL ASSETS <= 500 MILLION                         **
************************************************************************;
     PROC MEANS DATA=AGBK70D;
          VAR ROA ROE;
          CLASS ASSETSZ;
          TITLE1 ' RETURN ON ASSETS (ROA), RETURN ON';
          TITLE2 ' EQUITY (ROE), NET INTEREST MARGIN';
          TITLE3 " (NIM) FOR  BANKS ";
     RUN;

DATA INDEX;
     SET AGBK70A;
     INDASSL=(46457.45/334075.41)*500000;
     INDASSH=(46457.45/334075.41)*1000000;
 IF AGBNK=0 AND R2170 <= INDASSL THEN INDEX=1;
   ELSE IF AGBNK=0 AND INDASSH>=R2170>INDASSL THEN INDEX=2;
   ELSE IF AGBNK=1 AND INDASSL >= R2170 >= 0 THEN INDEX=3;
     RUN;

DATA INDEX1;
     SET INDEX; FORMAT INDEX SIZEFMT.;
RUN;
************************************************************************
**  SUMMARY STATISTICS FOR COMMERICAL BANKS (NOT AG BANKS!-I.E.,      **
**  AGBNK=0) WITH TOTAL ASSETS <= 70.4282 MILLION.   THIS IS THE      **
**  ALTERNATIVE GROUP OF BANKS THAT ALTON MENTIONED IN HIS MEMO ON    **
**  7/15/94.  TO CALCULATE THIS ALTERNATIVE GROUP FOR 1992, DIVIDE    **
**  AVERAGE TOTAL ASSETS OF THE BANKING INDUSTRY IN 1992 BY AVG. TOTAL**
**  ASSETS IN 1994 AND MULTIPLY BY 500 MILLION.  TO DETERMINE THE SIZE**
**  CUTOFF FOR EACH YEAR, APPLY THIS METHOD BACK THROUGH TIME.        **
************************************************************************;
     PROC MEANS DATA=INDEX1;
          VAR ROA ROE;
          CLASS INDEX;
          TITLE1 ' RETURN ON ASSETS (ROA), RETURN ON';
          TITLE2 ' EQUITY (ROE), NET INTEREST MARGIN';
          TITLE3 " (NIM) FOR COMMERICAL BANKS ";
          TITLE7 '   (THIS IS THE INDEXED GROUP)    ';
     RUN;

DATA AGBK70E; SET AGBK70D;
NEGINC=0;
  IF RIAD4340<0 THEN NEGINC=1;
RUN;
  PROC SORT; BY ASSETSZ NEGINC;
  RUN;
     PROC FREQ DATA=AGBK70E;
       TABLES NEGINC/LIST SPARSE;
       BY ASSETSZ;
       TITLE "PERCENTAGE WITH NEGATIVE NET INCOME";
       TITLE2 "1970 UNINDEXED";
     RUN;

DATA INDEX2; SET INDEX1;
NEGINC=0;
  IF RIAD4340<0 THEN NEGINC=1;
RUN;
  PROC SORT; BY INDEX NEGINC;
  RUN;
     PROC FREQ DATA=INDEX2;
       TABLES NEGINC/LIST SPARSE;
       BY INDEX;
       TITLE "PERCENTAGE WITH NEGATIVE NET INCOME";
       TITLE2 "1970 INDEXED";
     RUN;






************************************************************************
************************************************************************
************************************************************************
** THIS CREATES THE 1971 DATA IN TABLES 4.                            **
************************************************************************
************************************************************************
************************************************************************;

LIBNAME IN 'H1CAW01.AGBANK.DATA' DISP=SHR;
FILENAME IN1 'H1CAW01.RAGKLK.MACRO' DISP=SHR;
LIBNAME IN2 'H1HLB00.AGBANK.DATA';

PROC FORMAT;
  VALUE SIZEFMT
   1="SMALL NON-AG BANKS"
   2="OTHER NON-AG BANKS"
   3="AG BANKS";
RUN;
************************************************************************
** THIS PROGRAM IS USED TO COMPUTE VARIOUS FINANCIAL RATIOS AND       **
** SUMMARY STATISTICS FOR ALTON GILBERT'S AND KEVIN KLIESEN'S         **
** AGRICULTURAL BANKING PAPER (SUMMER/FALL 1994).  IT                 **
** IS USED FIRST TO IDENTIFY BANKS WITHOUT CALL REPORT DATA           **
** (I.E., WITH MISSING DATA) FOR ANY QUARTER IN 1971.  IF A MISSING   **
** OBSERVATION IS DETECTED FOR ANY QUARTER, THEN ALL OBSERVATIONS FOR **
** THAT BANK IN 1971 ARE DELETED (BECAUSE ALTON AND KEVIN ONLY WANT   **
** BANKS IN THEIR SAMPLE WITH CALL REPORT DATA FOR ALL FOUR QUARTERS  **
** IN 1971).  THE PROGRAM IS THEN USED TO FRACTIONALIZE ALL OF THE    **
** BALANCE SHEET VARIABLES (I.E., ESTIMATE ANNUAL AVERAGE OF EACH     **
** VARIABLE OVER THE YEAR).  NOTE:  INCOME AND EXPENSE DATA ARE NOT   **
** FRACTIONALIZED BECAUSE THEY ARE FLOW VARIABLES.  FINALLY, THE      **
** PROGRAM COMPUTES THE VARIOUS SUMMARY STATISTICS AND RATIOS         **
** OUTLINED IN THE 7/15/94 AND 7/21/94 MEMOS FROM ALTON.  NOTE:       **
** THIS PROGRAM IS DOCUMENTED BELOW FOR FUTURE USE.                   **
************************************************************************;
PROC FORMAT;
     VALUE ZEROFMT
     0 = '           .'; *BE CAREFUL WITH THIS FORMAT! TOO MANY;
                         *SPACES CAN CORRUPT THE DATA AND RATIOS;
RUN;

%INC IN1(AGBK71A);
%INC IN1(AGBK71C);
%INC IN1(AGBK71E);
%INC IN1(AGBK71F);
************************************************************************
** READING IN CALL REPORT DATA FOR 1970.4-1971.4                      **
** PRIOR TO 1984.1, RCON0081 IS KNOWN AS RCON0010.                    **
** NOTE:  RCON2122=(RCON1400-RCON3370) PRIOR TO 1976.1.>>>>>>>>>>>>>  **
************************************************************************;
DATA AGBK7012;
     SET IN.RCRI7012(DROP=RIAD4010 RIAD4020 RIAD4027 RIAD4050 RIAD4060
                          RIAD4170 RIAD4180 RIAD4190 RIAD4200);
     RCON2122=(RCON1400-RCON3370);  *NOTICE CHANGE IN TOTAL LOANS DEFINITION;
RUN;
DATA AGBK7106;
     SET IN.RCRI7106(DROP=RIAD4010 RIAD4020 RIAD4027 RIAD4050 RIAD4060
                          RIAD4170 RIAD4180 RIAD4190 RIAD4200);
     RCON2122=(RCON1400-RCON3370);  *NOTICE CHANGE IN TOTAL LOANS DEFINITION;
RUN;
DATA AGBK7112;
     SET IN.RCRI7112;         *DO NOT DROP RIAD VARIABLES IN 4TH QTR!;
                              *NEED FLOW VARIABLES IN 4TH QTR ONLY;
     RCON2122=(RCON1400-RCON3370);  *NOTICE CHANGE IN TOTAL LOANS DEFINITION;
RUN;
DATA MERGE1;
     SET AGBK7012 AGBK7106 AGBK7112;
************************************************************************
** THE VARIABLE, DATE, IS A NUMERIC VARIABLE WHEN IT IS PULLED OFF OF **
** THE TAPE AT THE BOARD.  THEREFORE, TO CREATE A SAS DATE VALUE, YOU **
** HAVE TO FIRST USE A PUT FUNCTION TO RETURN THE NUMERIC DATE VALUE  **
** AS A CHARACTER STRING.  THEN, YOU HAVE TO USE THE INPUT            **
** FUNCTION TO RETURN THE CHARACTER STRING AS A SAS DATE VALUE.       **
** AFTER YOU HAVE CREATED A SASDATE VARIABLE, YOU CAN THEN CREATE A   **
** YEAR AND QUARTER VARIABLE.                                         **
************************************************************************; 
     NUMDATE=PUT(DATE,Z6.);
     SASDATE=INPUT(NUMDATE,YYMMDD6.);
     YEAR=YEAR(SASDATE);
     QTR=QTR(SASDATE);
************************************************************************
** NICK WALRAVEN AND MICHELE RICCI SELECTED ONLY BANKS WHERE          **
** BANK9420<=23 AND BANK9210<=56 FOR THE AGRICULTURAL FINANCE         **
** DATABOOK.  ALTON ELECTED TO SUBSET BY THE SAME VARIABLES TO BE     **
** CONSISTENT.                                                        **
************************************************************************;
     IF BANK9420<24 & BANK9210<57;
RUN;
     PROC SORT DATA=MERGE1;     *ENABLES USE OF LAST.BANK AND LAST.DATE;
          BY BANK9000 DESCENDING DATE;
     RUN;

DATA MERGE2;
     SET MERGE1;
          BY BANK9000 DESCENDING DATE;
************************************************************************
** THE FOLLOWING STATEMENTS:                                          **
**         (1) CREATE A COUNTER, T, WITH POSSIBLE VALUES FROM 1-5     **
**         (2) DELETE BANKS WITH MISSING OBSERVATIONS IN A PARTICULAR **
**             QUARTER                                                **
**         (3) SUM THE COUNTER VARIABLE AND OUTPUT IT TO A NEW DATASET**
************************************************************************;
     RETAIN T 1;
     T+1;
     IF LAST.BANK9000 AND LAST.DATE THEN T=1;
     IF YEAR=1971 & (QTR=2³QTR=4) &
        (RCON0010=.³RCON1420=.³RCON1590=.³
        RCON2122=.³RCON2145=.³RCON2150=.³RCON2170=.³
        RCON3210=.) THEN DELETE;
RUN;
     PROC MEANS SUM NOPRINT DATA=MERGE2;
          VAR T;
          BY BANK9000;
          OUTPUT OUT=SUMOUT SUM=SUMT;
     RUN;

DATA MERGE3;
     MERGE MERGE2 SUMOUT;
          BY BANK9000;
************************************************************************
** IF SUMT (CREATED ABOVE) <6 , THEN THIS IMPLIES THAT THE BANK HAD   **
** MISSING OBSERVATIONS IN AT LEAST ONE QUARTER IN 1971.  SINCE ONLY  **
** MISSING OBSERVATIONS WERE DELETED ABOVE, THERE MAY VERY WELL BE    **
** NON-MISSING OBSERVATIONS FOR OTHER QUARTERS (I.E., IT IS ENTIRELY  **
** POSSIBLE THAT A BANK ONLY HAD MISSING CALL REPORT DATA FOR SAY THE **
** 2ND QUARTER).  HOWEVER, ONLY BANKS WITH NON-MISSING OBSERVATIONS   **
** IN ALL FOUR QUARTERS CAN REMAIN IN THE SAMPLE.  THEREFORE, BANKS   **
** WHERE SUMT < 6  MUST BE DELETED BECAUSE ONLY BANKS WHERE SUMT=6    **
** HAVE NON-MISSING OBSERVATIONS FOR QTRS 2 AND 4 IN 1971.            **
************************************************************************;
      IF SUMT < 6 THEN DELETE;
RUN;

DATA USAGBANK;
     SET MERGE3(WHERE=(YEAR=1971 & QTR=2));BY BANK9000;
     RCON2122=LEFT(INPUT(PUT(RCON2122,ZEROFMT.),BEST12.));
     USRATIO= ((SUM(RCON1420,RCON1590))/RCON2122)*100;
RUN;
     PROC MEANS DATA=USAGBANK ;
          VAR USRATIO;
          OUTPUT OUT=USAGBK MEAN=USRATIO;
          TITLE1 'US RATIO IN 1971';
          LABEL USRATIO="RATIO OF AGLOANS TO TOTAL LOANS";
     RUN;
************************************************************************
** FRACTIONALIZATION BEGINS HERE!!                                    **
************************************************************************;
DATA AGBK7012;
     SET MERGE3(WHERE=(YEAR=1970 & QTR=4));
     AGBANK=1;
     %AGBK71A;
     RUN;

DATA AGBK7106;
     SET MERGE3(WHERE=(YEAR=1971 & QTR=2));
     AGBANK=3;
     %AGBK71C;
     RUN;

DATA AGBK7112;
     SET MERGE3(WHERE=(YEAR=1971 & QTR=4));
     AGBANK=5;
     %AGBK71E;
     RUN;
************************************************************************
** FRACTIONALIZATION ENDS HERE!!                                      **
************************************************************************;

DATA MERGAGBK; **CHECK THIS CAREFULLY!!;
     MERGE AGBK7012 AGBK7106 AGBK7112;
          BY BANK9000;
     RUN;

DATA AGBK71A;
************************************************************************
** THE FOLLOWING IF STATEMENT SETS IN THE US AGGREGATE RATIO OF       **
** (RCON1420+RCON1590)/RCON2122 AND MERGES IT TO ALL OBSERVATIONS IN  **
** THE DATA SET MERGAGBK (CREATED IN THE DATA STEP ABOVE).            **
************************************************************************;
     IF _N_=1 THEN SET USAGBK;
     DROP _TYPE_ _FREQ_;
     SET MERGAGBK;
     IF AGBANK=5;
     %MACRO QTRSUM;
          %LET VNAMES=
               R1420 R1590 R2122 R2170 R3210
               R2150 R0010 R2145;

          %DO AG=1 %TO 8;
               %LET VNAME= %SCAN(&VNAMES,&AG);
               &VNAME=SUM(&VNAME.A,&VNAME.C,&VNAME.E);
               DROP &VNAME.A &VNAME.C &VNAME.E;
          %END;
    %MEND QTRSUM;

   *INVOKE QTRSUM MACRO CREATED ABOVE;
    %QTRSUM;
************************************************************************
** IDENTIFY AGRICULTURAL BANKS USING DEFINITION SUPPLIED BY ALTON     **
** GILBERT                                                            **
************************************************************************;
    R2122=LEFT(INPUT(PUT(R2122,ZEROFMT.),BEST12.));
    R2170=LEFT(INPUT(PUT(R2170,ZEROFMT.),BEST12.));
    AGLOAN=SUM(R1420,R1590);
    AGBKRATO= (AGLOAN/R2122)*100;
    IF AGBKRATO > USRATIO THEN AGBNK=1;               *AN AG BANK*;
         ELSE IF AGBKRATO <= USRATIO THEN AGBNK=0;    *NOT AN AG BANK*;
************************************************************************
**  CREATING RANGES OF AGRICULTURAL LOANS (R1420+R1590) TO TOTAL      **
**  LOANS (R2122) (TO BE USED LATER IN %AGBK71F)                      **
************************************************************************;
    IF AGBKRATO >= 0 & AGBKRATO <= 10 THEN RANGE=1;
    ELSE IF 10 < AGBKRATO <= 15 THEN RANGE=2;
    ELSE IF 15 < AGBKRATO <= 20 THEN RANGE=3;
    ELSE IF 20 < AGBKRATO <= 25 THEN RANGE=4;
    ELSE IF 25 < AGBKRATO <= 30 THEN RANGE=5;
    ELSE IF 30 < AGBKRATO <= 40 THEN RANGE=6;
    ELSE IF 40 < AGBKRATO <= 50 THEN RANGE=7;
    ELSE IF 50 < AGBKRATO <= 60 THEN RANGE=8;
    ELSE IF 60 < AGBKRATO <= 70 THEN RANGE=9;
    ELSE IF AGBKRATO > 70 THEN RANGE=10;
    ELSE IF AGBKRATO = .  THEN RANGE=11;
************************************************************************
**  CALCULATING VARIOUS RATIOS OUTLINED IN ALTON'S 7/15/94 MEMO FOR 3 **
**  GROUPS OF BANKS:  (1) AGRICULTURAL BANKS, (2) COMMERCIAL BANKS    **
**  (NOT AG BANKS!) WITH TOTAL ASSETS <= 500 MILLION, AND (3)         **
**  COMMERICAL BANKS (NOT AG BANKS!) WITH TOTAL ASSETS <= INDEXED     **
**  AVERAGE TOTAL ASSETS OF THE BANKING INDUSTRY (IN THAT YEAR).      **
**  NOTE, HOWEVER, THAT GROUP (3) = GROUP (2) FOR 1994 BECAUSE 1994   **
**  IS THE BASE PERIOD FOR GROUP (3).  SEE ALTON'S MEMO FOR FURTHER   **
**  DETAILS.                                                          **
************************************************************************;
* RETURN ON ASSETS= (NET INCOME/TOTAL ASSETS);
     ROA= (RIAD4340/R2170);

* RETURN ON EQUITY= (EQUITY/TOTAL ASSETS);
     ROE= (R3210/R2170);

* NET INTEREST MARGIN= (NET INTEREST INCOME/EARNING ASSETS), WHERE;
* EARNINGS ASSETS= INTEREST BEARING BALANCES+SECURITIES+FEDERAL FUNDS;
* SOLD AND REPURCHASE AGREEMENTS+NET LOANS+TRADING ACCOUNT ASSETS;
     NUMER=(SUM(RIAD4010,RIAD4020,RIAD4027,RIAD4050,RIAD4060)
                -SUM(RIAD4170,RIAD4180,RIAD4190,RIAD4200));
     NIM= ((NUMER)/(R2170-(R0010+R2145+R2150)));     *CHANGED PER 7/21/94 MEMO;
     RUN;

PROC MEANS DATA=AGBK71A N SUM;;
     VAR R2170;
     TITLE "TOTAL US ASSETS IN 1971";
RUN;
DATA AGBK71B;                *COMPUTING SUMMARY STATISTICS FOR AGBANKS;
     SET AGBK71A;
     IF AGBNK=1;
RUN;

     PROC MEANS DATA=AGBK71B ;
          VAR AGBKRATO;
          TITLE 'AG BANKS IN 1971';
     %AGBK71F;

     RUN;

DATA AGBK71C;
     SET AGBK71A;
************************************************************************
**  THE FOLLOWING IF STATEMENT IDENTIFIES COMMERICAL BANKS (NOT AG    **
**  BANKS!-I.E., AGBNK=0) WITH TOTAL ASSETS < 500 MILLION             **
************************************************************************;
     IF AGBNK=0 AND R2170 <= 500000 THEN ASSETSZ=1;
           ELSE IF AGBNK=0 AND R2170 > 500000 THEN ASSETSZ=2;
           ELSE IF AGBNK=1 AND 500000>= R2170 >= 0 THEN ASSETSZ=3;
     RUN;

DATA AGBK71D;
     SET AGBK71C;
     FORMAT ASSETSZ SIZEFMT.;
RUN;
************************************************************************
**  SUMMARY STATISTICS FOR COMMERICAL BANKS (NOT AG BANKS!-I.E.,      **
**  AGBNK=0) WITH TOTAL ASSETS <= 500 MILLION                         **
************************************************************************;
     PROC MEANS DATA=AGBK71D;
          VAR ROA ROE;
          CLASS ASSETSZ;
          TITLE1 ' RETURN ON ASSETS (ROA), RETURN ON';
          TITLE2 ' EQUITY (ROE), NET INTEREST MARGIN';
          TITLE3 ' (NIM) FOR BANKS ';
     RUN;

DATA INDEX;
     SET AGBK71A;
     INDASSL=(51386.63/334075.41)*500000;
     INDASSH=(51386.63/334075.41)*5000000;
 IF AGBNK=0 AND R2170 <= INDASSL THEN INDEX=1;
   ELSE IF AGBNK=0 AND INDASSH>=R2170>INDASSL THEN INDEX=2;
   ELSE IF AGBNK=1 AND INDASSL >= R2170 >= 0 THEN INDEX=3;
     RUN;

DATA INDEX1;
     SET INDEX;
     FORMAT INDEX SIZEFMT.;
RUN;
************************************************************************
**  SUMMARY STATISTICS FOR COMMERICAL BANKS (NOT AG BANKS!-I.E.,      **
**  AGBNK=0) WITH TOTAL ASSETS <= 77.9007 MILLION.   THIS IS THE      **
**  ALTERNATIVE GROUP OF BANKS THAT ALTON MENTIONED IN HIS MEMO ON    **
**  7/15/94.  TO CALCULATE THIS ALTERNATIVE GROUP FOR 1992, DIVIDE    **
**  AVERAGE TOTAL ASSETS OF THE BANKING INDUSTRY IN 1992 BY AVG. TOTAL**
**  ASSETS IN 1994 AND MULTIPLY BY 500 MILLION.  TO DETERMINE THE SIZE**
**  CUTOFF FOR EACH YEAR, APPLY THIS METHOD BACK THROUGH TIME.        **
************************************************************************;
     PROC MEANS DATA=INDEX1;
          VAR ROA ROE;
          CLASS INDEX;
          TITLE1 ' RETURN ON ASSETS (ROA), RETURN ON';
          TITLE2 ' EQUITY (ROE), NET INTEREST MARGIN';
          TITLE3 ' (NIM) FOR  BANKS ';
          TITLE4 '   (THIS IS THE INDEXED GROUP)    ';
     RUN;

DATA AGBK71E; SET AGBK71D;
NEGINC=0;
  IF RIAD4340<0 THEN NEGINC=1;
RUN;
  PROC SORT; BY ASSETSZ NEGINC;
  RUN;
     PROC FREQ DATA=AGBK71E;
       TABLES NEGINC/LIST SPARSE;
       BY ASSETSZ;
       TITLE "PERCENTAGE WITH NEGATIVE NET INCOME";
       TITLE2 "1971 UNINDEXED";
     RUN;

DATA INDEX2; SET INDEX1;
NEGINC=0;
  IF RIAD4340<0 THEN NEGINC=1;
RUN;
  PROC SORT; BY INDEX NEGINC;
  RUN;
     PROC FREQ DATA=INDEX2;
       TABLES NEGINC/LIST SPARSE;
       BY INDEX;
       TITLE "PERCENTAGE WITH NEGATIVE NET INCOME";
       TITLE2 "1971 INDEXED";
     RUN;






************************************************************************
************************************************************************
************************************************************************
** THIS CREATES THE 1972 DATA IN TABLES 4.                            **
************************************************************************
************************************************************************
************************************************************************;

LIBNAME IN 'H1CAW01.AGBANK.DATA' DISP=SHR;
FILENAME IN1 'H1CAW01.RAGKLK.MACRO' DISP=SHR;
LIBNAME IN2 'H1HLB00.AGBANK.DATA';

PROC FORMAT;
  VALUE SIZEFMT
   1="SMALL NON-AG BANKS"
   2="OTHER NON-AG BANKS"
   3="AG BANKS";
RUN;
************************************************************************
** THIS PROGRAM IS USED TO COMPUTE VARIOUS FINANCIAL RATIOS AND       **
** SUMMARY STATISTICS FOR ALTON GILBERT'S AND KEVIN KLIESEN'S         **
** AGRICULTURAL BANKING PAPER (SUMMER/FALL 1994).  IT                 **
** IS USED FIRST TO IDENTIFY BANKS WITHOUT CALL REPORT DATA           **
** (I.E., WITH MISSING DATA) FOR ANY QUARTER IN 1972.  IF A MISSING   **
** OBSERVATION IS DETECTED FOR ANY QUARTER, THEN ALL OBSERVATIONS FOR **
** THAT BANK IN 1972 ARE DELETED (BECAUSE ALTON AND KEVIN ONLY WANT   **
** BANKS IN THEIR SAMPLE WITH CALL REPORT DATA FOR ALL FOUR QUARTERS  **
** IN 1972).  THE PROGRAM IS THEN USED TO FRACTIONALIZE ALL OF THE    **
** BALANCE SHEET VARIABLES (I.E., ESTIMATE ANNUAL AVERAGE OF EACH     **
** VARIABLE OVER THE YEAR).  NOTE:  INCOME AND EXPENSE DATA ARE NOT   **
** FRACTIONALIZED BECAUSE THEY ARE FLOW VARIABLES.  FINALLY, THE      **
** PROGRAM COMPUTES THE VARIOUS SUMMARY STATISTICS AND RATIOS         **
** OUTLINED IN THE 7/15/94 AND 7/21/94 MEMOS FROM ALTON.  NOTE:       **
** THIS PROGRAM IS DOCUMENTED BELOW FOR FUTURE USE.                   **
************************************************************************;
PROC FORMAT;
     VALUE ZEROFMT
     0 = '           .'; *BE CAREFUL WITH THIS FORMAT! TOO MANY;
                         *SPACES CAN CORRUPT THE DATA AND RATIOS;
RUN;

%INC IN1(AGBK72A);
%INC IN1(AGBK72C);
%INC IN1(AGBK72E);
%INC IN1(AGBK72F);
************************************************************************
** READING IN CALL REPORT DATA FOR 1971.4-1972.4                      **
** PRIOR TO 1984.1, RCON0081 IS KNOWN AS RCON0010.                    **
** NOTE:  RCON2122=(RCON1400-RCON3370) PRIOR TO 1976.1.>>>>>>>>>>>>>  **
************************************************************************;
DATA AGBK7112;
     SET IN.RCRI7112(DROP=RIAD4010 RIAD4020 RIAD4027 RIAD4050 RIAD4060
                          RIAD4170 RIAD4180 RIAD4190 RIAD4200);
     RCON2122=(RCON1400-RCON3370);  *NOTICE CHANGE IN TOTAL LOANS DEFINITION;
RUN;
DATA AGBK7206;
     SET IN.RCRI7206(DROP=RIAD4010 RIAD4020 RIAD4027 RIAD4050 RIAD4060
                          RIAD4170 RIAD4180 RIAD4190 RIAD4200);
     RCON2122=(RCON1400-RCON3370);  *NOTICE CHANGE IN TOTAL LOANS DEFINITION;
RUN;
DATA AGBK7212;
     SET IN.RCRI7212;         *DO NOT DROP RIAD VARIABLES IN 4TH QTR!;
                              *NEED FLOW VARIABLES IN 4TH QTR ONLY;
     RCON2122=(RCON1400-RCON3370);  *NOTICE CHANGE IN TOTAL LOANS DEFINITION;
RUN;
DATA MERGE1;
     SET AGBK7112 AGBK7206 AGBK7212;
************************************************************************
** THE VARIABLE, DATE, IS A NUMERIC VARIABLE WHEN IT IS PULLED OFF OF **
** THE TAPE AT THE BOARD.  THEREFORE, TO CREATE A SAS DATE VALUE, YOU **
** HAVE TO FIRST USE A PUT FUNCTION TO RETURN THE NUMERIC DATE VALUE  **
** AS A CHARACTER STRING.  THEN, YOU HAVE TO USE THE INPUT            **
** FUNCTION TO RETURN THE CHARACTER STRING AS A SAS DATE VALUE.       **
** AFTER YOU HAVE CREATED A SASDATE VARIABLE, YOU CAN THEN CREATE A   **
** YEAR AND QUARTER VARIABLE.                                         **
************************************************************************;
     NUMDATE=PUT(DATE,Z6.);
     SASDATE=INPUT(NUMDATE,YYMMDD6.);
     YEAR=YEAR(SASDATE);
     QTR=QTR(SASDATE);
************************************************************************
** NICK WALRAVEN AND MICHELE RICCI SELECTED ONLY BANKS WHERE          **
** BANK9420<=23 AND BANK9210<=56 FOR THE AGRICULTURAL FINANCE         **
** DATABOOK.  ALTON ELECTED TO SUBSET BY THE SAME VARIABLES TO BE     **
** CONSISTENT.                                                        **
************************************************************************;
     IF BANK9420<24 & BANK9210<57;
RUN;
     PROC SORT DATA=MERGE1;     *ENABLES USE OF LAST.BANK AND LAST.DATE;
          BY BANK9000 DESCENDING DATE;
     RUN;

DATA MERGE2;
     SET MERGE1;
          BY BANK9000 DESCENDING DATE;
************************************************************************
** THE FOLLOWING STATEMENTS:                                          **
**         (1) CREATE A COUNTER, T, WITH POSSIBLE VALUES FROM 1-5     **
**         (2) DELETE BANKS WITH MISSING OBSERVATIONS IN A PARTICULAR **
**             QUARTER                                                **
**         (3) SUM THE COUNTER VARIABLE AND OUTPUT IT TO A NEW DATASET**
************************************************************************;
     RETAIN T 1;
     T+1;
     IF LAST.BANK9000 AND LAST.DATE THEN T=1;
     IF YEAR=1972 & (QTR=2³QTR=4) &
        (RCON0010=.³RCON1420=.³RCON1590=.³
        RCON2122=.³RCON2145=.³RCON2150=.³RCON2170=.³
        RCON3210=.) THEN DELETE;
RUN;
     PROC MEANS SUM NOPRINT DATA=MERGE2;
          VAR T;
          BY BANK9000;
          OUTPUT OUT=SUMOUT SUM=SUMT;
     RUN;

DATA MERGE3;
     MERGE MERGE2 SUMOUT;
          BY BANK9000;
************************************************************************
** IF SUMT (CREATED ABOVE) <6 , THEN THIS IMPLIES THAT THE BANK HAD   **
** MISSING OBSERVATIONS IN AT LEAST ONE QUARTER IN 1972.  SINCE ONLY  **
** MISSING OBSERVATIONS WERE DELETED ABOVE, THERE MAY VERY WELL BE    **
** NON-MISSING OBSERVATIONS FOR OTHER QUARTERS (I.E., IT IS ENTIRELY  **
** POSSIBLE THAT A BANK ONLY HAD MISSING CALL REPORT DATA FOR SAY THE **
** 2ND QUARTER).  HOWEVER, ONLY BANKS WITH NON-MISSING OBSERVATIONS   **
** IN ALL FOUR QUARTERS CAN REMAIN IN THE SAMPLE.  THEREFORE, BANKS   **
** WHERE SUMT < 6  MUST BE DELETED BECAUSE ONLY BANKS WHERE SUMT=6    **
** HAVE NON-MISSING OBSERVATIONS FOR QTRS 2 AND 4 IN 1972.            **
************************************************************************;
      IF SUMT < 6 THEN DELETE;
RUN;

DATA USAGBANK;
     SET MERGE3(WHERE=(YEAR=1972 & QTR=2));BY BANK9000;
     RCON2122=LEFT(INPUT(PUT(RCON2122,ZEROFMT.),BEST12.));
     USRATIO= ((SUM(RCON1420,RCON1590))/RCON2122)*100;
RUN;
     PROC MEANS DATA=USAGBANK;
          VAR USRATIO;
          OUTPUT OUT=USAGBK MEAN=USRATIO;
          TITLE1 'US RATIO IN 1972';
          LABEL USRATIO="RATIO OF AGLOANS TO TOTAL LOANS";
     RUN;
************************************************************************
** FRACTIONALIZATION BEGINS HERE!!                                    **
************************************************************************;
DATA AGBK7112;
     SET MERGE3(WHERE=(YEAR=1971 & QTR=4));
     AGBANK=1;
     %AGBK72A;
     RUN;

DATA AGBK7206;
     SET MERGE3(WHERE=(YEAR=1972 & QTR=2));
     AGBANK=3;
     %AGBK72C;
     RUN;

DATA AGBK7212;
     SET MERGE3(WHERE=(YEAR=1972 & QTR=4));
     AGBANK=5;
     %AGBK72E;
     RUN;
************************************************************************
** FRACTIONALIZATION ENDS HERE!!                                      **
************************************************************************;
DATA MERGAGBK; **CHECK THIS CAREFULLY!!;
     MERGE AGBK7112 AGBK7206 AGBK7212;
          BY BANK9000;
RUN;

DATA AGBK72A;
************************************************************************
** THE FOLLOWING IF STATEMENT SETS IN THE US AGGREGATE RATIO OF       **
** (RCON1420+RCON1590)/RCON2122 AND MERGES IT TO ALL OBSERVATIONS IN  **
** THE DATA SET MERGAGBK (CREATED IN THE DATA STEP ABOVE).            **
************************************************************************;
     IF _N_=1 THEN SET USAGBK;
     DROP _TYPE_ _FREQ_;
     SET MERGAGBK;
     IF AGBANK=5;
     %MACRO QTRSUM;
          %LET VNAMES=
               R1420 R1590 R2122 R2170 R3210
               R2150 R0010 R2145;

          %DO AG=1 %TO 8;
               %LET VNAME= %SCAN(&VNAMES,&AG);
               &VNAME=SUM(&VNAME.A,&VNAME.C,&VNAME.E);
               DROP &VNAME.A &VNAME.C &VNAME.E;
          %END;
    %MEND QTRSUM;

   *INVOKE QTRSUM MACRO CREATED ABOVE;
    %QTRSUM;
************************************************************************
** IDENTIFY AGRICULTURAL BANKS USING DEFINITION SUPPLIED BY ALTON     **
** GILBERT                                                            **
************************************************************************;
    R2122=LEFT(INPUT(PUT(R2122,ZEROFMT.),BEST12.));
    R2170=LEFT(INPUT(PUT(R2170,ZEROFMT.),BEST12.));
    AGLOAN=SUM(R1420,R1590);
    AGBKRATO= (AGLOAN/R2122)*100;
    IF AGBKRATO > USRATIO THEN AGBNK=1;               *AN AG BANK*;
         ELSE IF AGBKRATO <= USRATIO THEN AGBNK=0;    *NOT AN AG BANK*;
************************************************************************
**  CREATING RANGES OF AGRICULTURAL LOANS (R1420+R1590) TO TOTAL      **
**  LOANS (R2122) (TO BE USED LATER IN %AGBK72F)                      **
************************************************************************;
    IF AGBKRATO >= 0 & AGBKRATO <= 10 THEN RANGE=1;
    ELSE IF 10 < AGBKRATO <= 15 THEN RANGE=2;
    ELSE IF 15 < AGBKRATO <= 20 THEN RANGE=3;
    ELSE IF 20 < AGBKRATO <= 25 THEN RANGE=4;
    ELSE IF 25 < AGBKRATO <= 30 THEN RANGE=5;
    ELSE IF 30 < AGBKRATO <= 40 THEN RANGE=6;
    ELSE IF 40 < AGBKRATO <= 50 THEN RANGE=7;
    ELSE IF 50 < AGBKRATO <= 60 THEN RANGE=8;
    ELSE IF 60 < AGBKRATO <= 70 THEN RANGE=9;
    ELSE IF AGBKRATO > 70 THEN RANGE=10;
    ELSE IF AGBKRATO = .  THEN RANGE=11;
************************************************************************
**  CALCULATING VARIOUS RATIOS OUTLINED IN ALTON'S 7/15/94 MEMO FOR 3 **
**  GROUPS OF BANKS:  (1) AGRICULTURAL BANKS, (2) COMMERCIAL BANKS    **
**  (NOT AG BANKS!) WITH TOTAL ASSETS <= 500 MILLION, AND (3)         **
**  COMMERICAL BANKS (NOT AG BANKS!) WITH TOTAL ASSETS <= INDEXED     **
**  AVERAGE TOTAL ASSETS OF THE BANKING INDUSTRY (IN THAT YEAR).      **
**  NOTE, HOWEVER, THAT GROUP (3) = GROUP (2) FOR 1994 BECAUSE 1994   **
**  IS THE BASE PERIOD FOR GROUP (3).  SEE ALTON'S MEMO FOR FURTHER   **
**  DETAILS.                                                          **
************************************************************************;
* RETURN ON ASSETS= (NET INCOME/TOTAL ASSETS);
     ROA= (RIAD4340/R2170);

* RETURN ON EQUITY= (EQUITY/TOTAL ASSETS);
     ROE= (R3210/R2170);

* NET INTEREST MARGIN= (NET INTEREST INCOME/EARNING ASSETS), WHERE;
* EARNINGS ASSETS= INTEREST BEARING BALANCES+SECURITIES+FEDERAL FUNDS;
* SOLD AND REPURCHASE AGREEMENTS+NET LOANS+TRADING ACCOUNT ASSETS;
     NUMER=(SUM(RIAD4010,RIAD4020,RIAD4027,RIAD4050,RIAD4060)
                -SUM(RIAD4170,RIAD4180,RIAD4190,RIAD4200));
     NIM= ((NUMER)/(R2170-(R0010+R2145+R2150)));     *CHANGED PER 7/21/94 MEMO;

     RUN;
PROC MEANS DATA=AGBK72A N SUM;
     VAR R2170;
     TITLE "TOTAL US ASSETS IN 1972";
RUN;
DATA AGBK72B;                *COMPUTING SUMMARY STATISTICS FOR AGBANKS;
     SET AGBK72A;
     IF AGBNK=1;
RUN;

     PROC MEANS DATA=AGBK72B;
          VAR AGBKRATO;
          TITLE 'AG BANKS IN 1972';
     RUN;
     %AGBK72F;
     RUN;

DATA AGBK72C;
     SET AGBK72A;
************************************************************************
**  THE FOLLOWING IF STATEMENT IDENTIFIES COMMERICAL BANKS (NOT AG    **
**  BANKS!-I.E., AGBNK=0) WITH TOTAL ASSETS < 500 MILLION             **
************************************************************************;
     IF AGBNK=0 AND R2170 <= 500000 THEN ASSETSZ=1;
           ELSE IF AGBNK=0 AND R2170 > 500000 THEN ASSETSZ=2;
           ELSE IF AGBNK=1 AND 500000 >= R2170 >= 0 THEN ASSETSZ=3;
     RUN;

DATA AGBK72D;
     SET AGBK72C;
     FORMAT ASSETSZ SIZEFMT.;
RUN;
************************************************************************
**  SUMMARY STATISTICS FOR COMMERICAL BANKS (NOT AG BANKS!-I.E.,      **
**  AGBNK=0) WITH TOTAL ASSETS <= 500 MILLION                         **
************************************************************************;
     PROC MEANS DATA=AGBK72D;
          VAR ROA ROE;
          CLASS ASSETSZ;
          TITLE1 ' RETURN ON ASSETS (ROA), RETURN ON';
          TITLE2 ' EQUITY (ROE), NET INTEREST MARGIN';
          TITLE3 ' (NIM) FOR BANKS ';
     RUN;

DATA INDEX;
     SET AGBK72A;
     INDASSL=(58371.04/334075.41)*500000;
     INDASSH=(58371.04/334075.41)*1000000;
 IF AGBNK=0 AND R2170 <= INDASSL THEN INDEX=1;
   ELSE IF AGBNK=0 AND INDASSH>=R2170>INDASSL THEN INDEX=2;
   ELSE IF AGBNK=1 AND INDASSL >= R2170 >= 0 THEN INDEX=3;
     RUN;

DATA INDEX1;
     SET INDEX; FORMAT INDEX SIZEFMT.;
RUN;
************************************************************************
**  SUMMARY STATISTICS FOR COMMERICAL BANKS (NOT AG BANKS!-I.E.,      **
**  AGBNK=0) WITH TOTAL ASSETS <= 88.4888 MILLION.   THIS IS THE      **
**  ALTERNATIVE GROUP OF BANKS THAT ALTON MENTIONED IN HIS MEMO ON    **
**  7/15/94.  TO CALCULATE THIS ALTERNATIVE GROUP FOR 1992, DIVIDE    **
**  AVERAGE TOTAL ASSETS OF THE BANKING INDUSTRY IN 1992 BY AVG. TOTAL**
**  ASSETS IN 1994 AND MULTIPLY BY 500 MILLION.  TO DETERMINE THE SIZE**
**  CUTOFF FOR EACH YEAR, APPLY THIS METHOD BACK THROUGH TIME.        **
************************************************************************;
     PROC MEANS DATA=INDEX1;
          VAR ROA ROE;
          CLASS INDEX;
          TITLE1 ' RETURN ON ASSETS (ROA), RETURN ON';
          TITLE2 ' EQUITY (ROE), NET INTEREST MARGIN';
          TITLE3 ' (NIM) FOR  BANKS ';
          TITLE4 '   (THIS IS THE INDEXED GROUP)    ';
     RUN;

DATA AGBK72E; SET AGBK72D;
NEGINC=0;
  IF RIAD4340<0 THEN NEGINC=1;
RUN;
  PROC SORT; BY ASSETSZ NEGINC;
  RUN;
     PROC FREQ DATA=AGBK72E;
       TABLES NEGINC/LIST SPARSE;
       BY ASSETSZ;
       TITLE "PERCENTAGE WITH NEGATIVE NET INCOME";
       TITLE2 "1972 UNINDEXED";
RUN;

DATA INDEX2; SET INDEX1;
NEGINC=0;
  IF RIAD4340<0 THEN NEGINC=1;
RUN;
  PROC SORT; BY INDEX NEGINC;
  RUN;
     PROC FREQ DATA=INDEX2;
       TABLES NEGINC/LIST SPARSE;
       BY INDEX;
       TITLE "PERCENTAGE WITH NEGATIVE NET INCOME";
       TITLE2 "1972 INDEXED";
     RUN;









************************************************************************
************************************************************************
************************************************************************
** THIS CREATES THE 1973 DATA IN TABLES 4.                            **
************************************************************************
************************************************************************
************************************************************************;
LIBNAME IN 'H1CAW01.AGBANK.DATA' DISP=SHR;
FILENAME IN1 'H1CAW01.RAGKLK.MACRO' DISP=SHR;
LIBNAME IN2 'H1HLB00.AGBANK.DATA';

PROC FORMAT;
  VALUE SIZEFMT
   1="SMALL NON-AG BANKS"
   2="OTHER NON-AG BANKS"
   3="AG BANKS";
RUN;
************************************************************************
** THIS PROGRAM IS USED TO COMPUTE VARIOUS FINANCIAL RATIOS AND       **
** SUMMARY STATISTICS FOR ALTON GILBERT'S AND KEVIN KLIESEN'S         **
** AGRICULTURAL BANKING PAPER (SUMMER/FALL 1994).  IT                 **
** IS USED FIRST TO IDENTIFY BANKS WITHOUT CALL REPORT DATA           **
** (I.E., WITH MISSING DATA) FOR ANY QUARTER IN 1973.  IF A MISSING   **
** OBSERVATION IS DETECTED FOR ANY QUARTER, THEN ALL OBSERVATIONS FOR **
** THAT BANK IN 1973 ARE DELETED (BECAUSE ALTON AND KEVIN ONLY WANT   **
** BANKS IN THEIR SAMPLE WITH CALL REPORT DATA FOR ALL FOUR QUARTERS  **
** IN 1973).  THE PROGRAM IS THEN USED TO FRACTIONALIZE ALL OF THE    **
** BALANCE SHEET VARIABLES (I.E., ESTIMATE ANNUAL AVERAGE OF EACH     **
** VARIABLE OVER THE YEAR).  NOTE:  INCOME AND EXPENSE DATA ARE NOT   **
** FRACTIONALIZED BECAUSE THEY ARE FLOW VARIABLES.  FINALLY, THE      **
** PROGRAM COMPUTES THE VARIOUS SUMMARY STATISTICS AND RATIOS         **
** OUTLINED IN THE 7/15/94 AND 7/21/94 MEMOS FROM ALTON.  NOTE:       **
** THIS PROGRAM IS DOCUMENTED BELOW FOR FUTURE USE.                   **
************************************************************************;
PROC FORMAT;
     VALUE ZEROFMT
     0 = '           .'; *BE CAREFUL WITH THIS FORMAT! TOO MANY;
                         *SPACES CAN CORRUPT THE DATA AND RATIOS;
RUN;

%INC IN1(AGBK73A);
%INC IN1(AGBK73C);
%INC IN1(AGBK73E);
%INC IN1(AGBK73F);
************************************************************************
** READING IN CALL REPORT DATA FOR 1972.4-1973.4                      **
** PRIOR TO 1984.1, RCON0081 IS KNOWN AS RCON0010.                    **
** NOTE:  RCON2122=(RCON1400-RCON3370) PRIOR TO 1976.1.>>>>>>>>>>>>>  **
************************************************************************;
DATA AGBK7212;
     SET IN.RCRI7212(DROP=RIAD4010 RIAD4020 RIAD4027 RIAD4050 RIAD4060
                          RIAD4170 RIAD4180 RIAD4190 RIAD4200);
     RCON2122=(RCON1400-RCON3370);  *NOTICE CHANGE IN TOTAL LOANS DEFINITION;
RUN;
DATA AGBK7306;
     SET IN.RCRI7306(DROP=RIAD4010 RIAD4020 RIAD4027 RIAD4050 RIAD4060
                          RIAD4170 RIAD4180 RIAD4190 RIAD4200);
     RCON2122=(RCON1400-RCON3370);  *NOTICE CHANGE IN TOTAL LOANS DEFINITION;
RUN;
DATA AGBK7312;
     SET IN.RCRI7312;         *DO NOT DROP RIAD VARIABLES IN 4TH QTR!;
                              *NEED FLOW VARIABLES IN 4TH QTR ONLY;
     RCON2122=(RCON1400-RCON3370);  *NOTICE CHANGE IN TOTAL LOANS DEFINITION;
RUN;
DATA MERGE1;
     SET AGBK7212 AGBK7306 AGBK7312;
************************************************************************
** THE VARIABLE, DATE, IS A NUMERIC VARIABLE WHEN IT IS PULLED OFF OF **
** THE TAPE AT THE BOARD.  THEREFORE, TO CREATE A SAS DATE VALUE, YOU **
** HAVE TO FIRST USE A PUT FUNCTION TO RETURN THE NUMERIC DATE VALUE  **
** AS A CHARACTER STRING.  THEN, YOU HAVE TO USE THE INPUT            **
** FUNCTION TO RETURN THE CHARACTER STRING AS A SAS DATE VALUE.       **
** AFTER YOU HAVE CREATED A SASDATE VARIABLE, YOU CAN THEN CREATE A   **
** YEAR AND QUARTER VARIABLE.                                         **
************************************************************************;
     NUMDATE=PUT(DATE,Z6.);
     SASDATE=INPUT(NUMDATE,YYMMDD6.);
     YEAR=YEAR(SASDATE);
     QTR=QTR(SASDATE);
************************************************************************
** NICK WALRAVEN AND MICHELE RICCI SELECTED ONLY BANKS WHERE          **
** BANK9420<=23 AND BANK9210<=56 FOR THE AGRICULTURAL FINANCE         **
** DATABOOK.  ALTON ELECTED TO SUBSET BY THE SAME VARIABLES TO BE     **
** CONSISTENT.                                                        **
************************************************************************;
     IF BANK9420<24 & BANK9210<57;
     RUN;

     PROC SORT DATA=MERGE1;     *ENABLES USE OF LAST.BANK AND LAST.DATE;
          BY BANK9000 DESCENDING DATE;
     RUN;

DATA MERGE2;
     SET MERGE1;
          BY BANK9000 DESCENDING DATE;
************************************************************************
** THE FOLLOWING STATEMENTS:                                          **
**         (1) CREATE A COUNTER, T, WITH POSSIBLE VALUES FROM 1-5     **
**         (2) DELETE BANKS WITH MISSING OBSERVATIONS IN A PARTICULAR **
**             QUARTER                                                **
**         (3) SUM THE COUNTER VARIABLE AND OUTPUT IT TO A NEW DATASET**
************************************************************************;
     RETAIN T 1;
     T+1;
     IF LAST.BANK9000 AND LAST.DATE THEN T=1;
     IF YEAR=1973 & (QTR=2³QTR=4) &
        (RCON0010=.³RCON1420=.³RCON1590=.³
        RCON2122=.³RCON2145=.³RCON2150=.³RCON2170=.³
        RCON3210=.) THEN DELETE;
RUN;
     PROC MEANS SUM NOPRINT DATA=MERGE2;
          VAR T;
          BY BANK9000;
          OUTPUT OUT=SUMOUT SUM=SUMT;
     RUN;

DATA MERGE3;
     MERGE MERGE2 SUMOUT;
          BY BANK9000;
************************************************************************
** IF SUMT (CREATED ABOVE) <6 , THEN THIS IMPLIES THAT THE BANK HAD   **
** MISSING OBSERVATIONS IN AT LEAST ONE QUARTER IN 1973.  SINCE ONLY  **
** MISSING OBSERVATIONS WERE DELETED ABOVE, THERE MAY VERY WELL BE    **
** NON-MISSING OBSERVATIONS FOR OTHER QUARTERS (I.E., IT IS ENTIRELY  **
** POSSIBLE THAT A BANK ONLY HAD MISSING CALL REPORT DATA FOR SAY THE **
** 2ND QUARTER).  HOWEVER, ONLY BANKS WITH NON-MISSING OBSERVATIONS   **
** IN ALL FOUR QUARTERS CAN REMAIN IN THE SAMPLE.  THEREFORE, BANKS   **
** WHERE SUMT < 6  MUST BE DELETED BECAUSE ONLY BANKS WHERE SUMT=6    **
** HAVE NON-MISSING OBSERVATIONS FOR QTRS 2 AND 4 IN 1973.            **
************************************************************************;
      IF SUMT < 6 THEN DELETE;
RUN;

DATA USAGBANK;
     SET MERGE3(WHERE=(YEAR=1973 & QTR=2));BY BANK9000;
     RCON2122=LEFT(INPUT(PUT(RCON2122,ZEROFMT.),BEST12.));
     USRATIO= ((SUM(RCON1420,RCON1590))/RCON2122)*100;
RUN;
     PROC MEANS DATA=USAGBANK;
          VAR USRATIO;
          OUTPUT OUT=USAGBK MEAN=USRATIO;
          TITLE1 'US RATIO IN 1973';
          LABEL USRATIO="RATIO OF AGLOANS TO TOTAL LOANS";
     RUN;

************************************************************************
** FRACTIONALIZATION BEGINS HERE!!                                    **
************************************************************************;
DATA AGBK7212;
     SET MERGE3(WHERE=(YEAR=1972 & QTR=4));
     AGBANK=1;
     %AGBK73A;
     RUN;

DATA AGBK7306;
     SET MERGE3(WHERE=(YEAR=1973 & QTR=2));
     AGBANK=3;
     %AGBK73C;
     RUN;

DATA AGBK7312;
     SET MERGE3(WHERE=(YEAR=1973 & QTR=4));
     AGBANK=5;
     %AGBK73E;
     RUN;
************************************************************************
** FRACTIONALIZATION ENDS HERE!!                                      **
************************************************************************;

DATA MERGAGBK; **CHECK THIS CAREFULLY!!;
     MERGE AGBK7212 AGBK7306 AGBK7312;
          BY BANK9000;
     RUN;

DATA AGBK73A;
************************************************************************
** THE FOLLOWING IF STATEMENT SETS IN THE US AGGREGATE RATIO OF       **
** (RCON1420+RCON1590)/RCON2122 AND MERGES IT TO ALL OBSERVATIONS IN  **
** THE DATA SET MERGAGBK (CREATED IN THE DATA STEP ABOVE).            **
************************************************************************;
     IF _N_=1 THEN SET USAGBK;
     DROP _TYPE_ _FREQ_;
     SET MERGAGBK;
     IF AGBANK=5;
     %MACRO QTRSUM;
          %LET VNAMES=
               R1420 R1590 R2122 R2170 R3210
               R2150 R0010 R2145;

          %DO AG=1 %TO 8;
               %LET VNAME= %SCAN(&VNAMES,&AG);
               &VNAME=SUM(&VNAME.A,&VNAME.C,&VNAME.E);
               DROP &VNAME.A &VNAME.C &VNAME.E;
          %END;
    %MEND QTRSUM;

   *INVOKE QTRSUM MACRO CREATED ABOVE;
    %QTRSUM;
************************************************************************
** IDENTIFY AGRICULTURAL BANKS USING DEFINITION SUPPLIED BY ALTON     **
** GILBERT                                                            **
************************************************************************;
    R2122=LEFT(INPUT(PUT(R2122,ZEROFMT.),BEST12.));
    R2170=LEFT(INPUT(PUT(R2170,ZEROFMT.),BEST12.));
    AGLOAN=SUM(R1420,R1590);
    AGBKRATO= (AGLOAN/R2122)*100;
    IF AGBKRATO > USRATIO THEN AGBNK=1;               *AN AG BANK*;
         ELSE IF AGBKRATO <= USRATIO THEN AGBNK=0;    *NOT AN AG BANK*;
************************************************************************
**  CREATING RANGES OF AGRICULTURAL LOANS (R1420+R1590) TO TOTAL      **
**  LOANS (R2122) (TO BE USED LATER IN %AGBK73F)                      **
************************************************************************;
    IF AGBKRATO >= 0 & AGBKRATO <= 10 THEN RANGE=1;
    ELSE IF 10 < AGBKRATO <= 15 THEN RANGE=2;
    ELSE IF 15 < AGBKRATO <= 20 THEN RANGE=3;
    ELSE IF 20 < AGBKRATO <= 25 THEN RANGE=4;
    ELSE IF 25 < AGBKRATO <= 30 THEN RANGE=5;
    ELSE IF 30 < AGBKRATO <= 40 THEN RANGE=6;
    ELSE IF 40 < AGBKRATO <= 50 THEN RANGE=7;
    ELSE IF 50 < AGBKRATO <= 60 THEN RANGE=8;
    ELSE IF 60 < AGBKRATO <= 70 THEN RANGE=9;
    ELSE IF AGBKRATO > 70 THEN RANGE=10;
    ELSE IF AGBKRATO = .  THEN RANGE=11;
************************************************************************
**  CALCULATING VARIOUS RATIOS OUTLINED IN ALTON'S 7/15/94 MEMO FOR 3 **
**  GROUPS OF BANKS:  (1) AGRICULTURAL BANKS, (2) COMMERCIAL BANKS    **
**  (NOT AG BANKS!) WITH TOTAL ASSETS <= 500 MILLION, AND (3)         **
**  COMMERICAL BANKS (NOT AG BANKS!) WITH TOTAL ASSETS <= INDEXED     **
**  AVERAGE TOTAL ASSETS OF THE BANKING INDUSTRY (IN THAT YEAR).      **
**  NOTE, HOWEVER, THAT GROUP (3) = GROUP (2) FOR 1994 BECAUSE 1994   **
**  IS THE BASE PERIOD FOR GROUP (3).  SEE ALTON'S MEMO FOR FURTHER   **
**  DETAILS.                                                          **
************************************************************************;
* RETURN ON ASSETS= (NET INCOME/TOTAL ASSETS);
     ROA= (RIAD4340/R2170);

* RETURN ON EQUITY= (EQUITY/TOTAL ASSETS);
     ROE= (R3210/R2170);

* NET INTEREST MARGIN= (NET INTEREST INCOME/EARNING ASSETS), WHERE;
* EARNINGS ASSETS= INTEREST BEARING BALANCES+SECURITIES+FEDERAL FUNDS;
* SOLD AND REPURCHASE AGREEMENTS+NET LOANS+TRADING ACCOUNT ASSETS;
     NUMER=(SUM(RIAD4010,RIAD4020,RIAD4027,RIAD4050,RIAD4060)
                -SUM(RIAD4170,RIAD4180,RIAD4190,RIAD4200));
     NIM= ((NUMER)/(R2170-(R0010+R2145+R2150)));     *CHANGED PER 7/21/94 MEMO;
RUN;

PROC MEANS DATA=AGBK73A N SUM;
     VAR R2170;
     TITLE "TOTAL US ASSETS IN 1973";
RUN;
DATA AGBK73B;                *COMPUTING SUMMARY STATISTICS FOR AGBANKS;
     SET AGBK73A;
     IF AGBNK=1;
RUN;

     PROC MEANS DATA=AGBK73B;
          VAR AGBKRATO;
          TITLE 'AG BANKS IN 1973';
     RUN;
     %AGBK73F;
     RUN;

DATA AGBK73C;
     SET AGBK73A;
************************************************************************
**  THE FOLLOWING IF STATEMENT IDENTIFIES COMMERICAL BANKS (NOT AG    **
**  BANKS!-I.E., AGBNK=0) WITH TOTAL ASSETS < 500 MILLION             **
************************************************************************;
     IF AGBNK=0 AND R2170 <= 500000 THEN ASSETSZ=1;
           ELSE IF AGBNK=0 AND R2170 > 500000 THEN ASSETSZ=2;
           ELSE IF AGBNK=1 AND 500000 >= R2170 >= 0 THEN ASSETSZ=3;
     RUN;

DATA AGBK73D;
     SET AGBK73C;
     FORMAT ASSETSZ SIZEFMT.;
RUN;
************************************************************************
**  SUMMARY STATISTICS FOR COMMERICAL BANKS (NOT AG BANKS!-I.E.,      **
**  AGBNK=0) WITH TOTAL ASSETS <= 500 MILLION                         **
************************************************************************;
     PROC MEANS DATA=AGBK73D;
          VAR ROA ROE;
          CLASS ASSETSZ;
          TITLE1 ' RETURN ON ASSETS (ROA), RETURN ON';
          TITLE2 ' EQUITY (ROE), NET INTEREST MARGIN';
          TITLE3 ' (NIM) FOR BANKS ';
     RUN;

DATA INDEX;
     SET AGBK73A;
     INDASSL=(64370.21/334075.41)*500000;
     INDASSH=(64370.21/334075.41)*1000000;
 IF AGBNK=0 AND R2170 <= INDASSL THEN INDEX=1;
   ELSE IF AGBNK=0 AND INDASSH>=R2170>INDASSL THEN INDEX=2;
   ELSE IF AGBNK=1 AND INDASSL >= R2170 >= 0 THEN INDEX=3;
     RUN;

DATA INDEX1;
     SET INDEX; FORMAT INDEX SIZEFMT.;
RUN;
************************************************************************
**  SUMMARY STATISTICS FOR COMMERICAL BANKS (NOT AG BANKS!-I.E.,      **
**  AGBNK=0) WITH TOTAL ASSETS <= 97.5834 MILLION.   THIS IS THE      **
**  ALTERNATIVE GROUP OF BANKS THAT ALTON MENTIONED IN HIS MEMO ON    **
**  7/15/94.  TO CALCULATE THIS ALTERNATIVE GROUP FOR 1992, DIVIDE    **
**  AVERAGE TOTAL ASSETS OF THE BANKING INDUSTRY IN 1992 BY AVG. TOTAL**
**  ASSETS IN 1994 AND MULTIPLY BY 100 MILLION.  TO DETERMINE THE SIZE**
**  CUTOFF FOR EACH YEAR, APPLY THIS METHOD BACK THROUGH TIME.        **
************************************************************************;
     PROC MEANS DATA=INDEX1;
          VAR ROA ROE;
          CLASS INDEX;
          TITLE1 ' RETURN ON ASSETS (ROA), RETURN ON';
          TITLE2 ' EQUITY (ROE), NET INTEREST MARGIN';
          TITLE3 ' (NIM) FOR BANKS ';
          TITLE4 '   (THIS IS THE INDEXED GROUP)    ';
     RUN;

DATA AGBK73E; SET AGBK73D;
NEGINC=0;
  IF RIAD4340<0 THEN NEGINC=1;
RUN;
  PROC SORT; BY ASSETSZ NEGINC;
  RUN;
     PROC FREQ DATA=AGBK73E;
       TABLES NEGINC/LIST SPARSE;
       BY ASSETSZ;
       TITLE "PERCENTAGE WITH NEGATIVE NET INCOME";
       TITLE2 "1973 UNINDEXED";
     RUN;

DATA INDEX2; SET INDEX1;
NEGINC=0;
  IF RIAD4340<0 THEN NEGINC=1;
RUN;
  PROC SORT; BY INDEX NEGINC;
  RUN;
     PROC FREQ DATA=INDEX2;
       TABLES NEGINC/LIST SPARSE;
       BY INDEX;
       TITLE "PERCENTAGE WITH NEGATIVE NET INCOME";
       TITLE2 "1973 INDEXED";
RUN;







************************************************************************
************************************************************************
************************************************************************
** THIS CREATES THE 1974 DATA IN TABLES 4.                            **
************************************************************************
************************************************************************
************************************************************************;

LIBNAME IN 'H1CAW01.AGBANK.DATA' DISP=SHR;
FILENAME IN1 'H1CAW01.RAGKLK.MACRO' DISP=SHR;
LIBNAME IN2 'H1HLB00.AGBANK.DATA';

PROC FORMAT;
  VALUE SIZEFMT
   1="SMALL NON-AG BANKS"
   2="OTHER NON-AG BANKS"
   3="AG BANKS";
RUN;
************************************************************************
** THIS PROGRAM IS USED TO COMPUTE VARIOUS FINANCIAL RATIOS AND       **
** SUMMARY STATISTICS FOR ALTON GILBERT'S AND KEVIN KLIESEN'S         **
** AGRICULTURAL BANKING PAPER (SUMMER/FALL 1994).  IT                 **
** IS USED FIRST TO IDENTIFY BANKS WITHOUT CALL REPORT DATA           **
** (I.E., WITH MISSING DATA) FOR ANY QUARTER IN 1974.  IF A MISSING   **
** OBSERVATION IS DETECTED FOR ANY QUARTER, THEN ALL OBSERVATIONS FOR **
** THAT BANK IN 1974 ARE DELETED (BECAUSE ALTON AND KEVIN ONLY WANT   **
** BANKS IN THEIR SAMPLE WITH CALL REPORT DATA FOR ALL FOUR QUARTERS  **
** IN 1974).  THE PROGRAM IS THEN USED TO FRACTIONALIZE ALL OF THE    **
** BALANCE SHEET VARIABLES (I.E., ESTIMATE ANNUAL AVERAGE OF EACH     **
** VARIABLE OVER THE YEAR).  NOTE:  INCOME AND EXPENSE DATA ARE NOT   **
** FRACTIONALIZED BECAUSE THEY ARE FLOW VARIABLES.  FINALLY, THE      **
** PROGRAM COMPUTES THE VARIOUS SUMMARY STATISTICS AND RATIOS         **
** OUTLINED IN THE 7/15/94 AND 7/21/94 MEMOS FROM ALTON.  NOTE:       **
** THIS PROGRAM IS DOCUMENTED BELOW FOR FUTURE USE.  THIS IS          **
************************************************************************;
PROC FORMAT;
     VALUE ZEROFMT
     0 = '           .'; *BE CAREFUL WITH THIS FORMAT! TOO MANY;
                         *SPACES CAN CORRUPT THE DATA AND RATIOS;
RUN;
%INC IN1(AGBK74A);
%INC IN1(AGBK74C);
%INC IN1(AGBK74E);
%INC IN1(AGBK74F);
************************************************************************
** READING IN CALL REPORT DATA FOR 1973.4-1974.4                      **
** PRIOR TO 1984.1, RCON0081 IS KNOWN AS RCON0010.                    **
** NOTE:  RCON2122=(RCON1400-RCON3370) PRIOR TO 1976.1.>>>>>>>>>>>>>  **
************************************************************************;
DATA AGBK7312;
     SET IN.RCRI7312(DROP=RIAD4010 RIAD4020 RIAD4027 RIAD4050 RIAD4060
                          RIAD4170 RIAD4180 RIAD4190 RIAD4200);
     RCON2122=(RCON1400-RCON3370);  *NOTICE CHANGE IN TOTAL LOANS DEFINITION;
RUN;
DATA AGBK7406;
     SET IN.RCRI7406(DROP=RIAD4010 RIAD4020 RIAD4027 RIAD4050 RIAD4060
                          RIAD4170 RIAD4180 RIAD4190 RIAD4200);
     RCON2122=(RCON1400-RCON3370);  *NOTICE CHANGE IN TOTAL LOANS DEFINITION;
RUN;
DATA AGBK7412;
     SET IN.RCRI7412;         *DO NOT DROP RIAD VARIABLES IN 4TH QTR!;
                              *NEED FLOW VARIABLES IN 4TH QTR ONLY;
     RCON2122=(RCON1400-RCON3370);  *NOTICE CHANGE IN TOTAL LOANS DEFINITION;
RUN;
DATA MERGE1;
     SET AGBK7312 AGBK7406 AGBK7412;
************************************************************************
** THE VARIABLE, DATE, IS A NUMERIC VARIABLE WHEN IT IS PULLED OFF OF **
** THE TAPE AT THE BOARD.  THEREFORE, TO CREATE A SAS DATE VALUE, YOU **
** HAVE TO FIRST USE A PUT FUNCTION TO RETURN THE NUMERIC DATE VALUE  **
** AS A CHARACTER STRING.  THEN, YOU HAVE TO USE THE INPUT            **
** FUNCTION TO RETURN THE CHARACTER STRING AS A SAS DATE VALUE.       **
** AFTER YOU HAVE CREATED A SASDATE VARIABLE, YOU CAN THEN CREATE A   **
** YEAR AND QUARTER VARIABLE.                                         **
************************************************************************;
     NUMDATE=PUT(DATE,Z6.);
     SASDATE=INPUT(NUMDATE,YYMMDD6.);
     YEAR=YEAR(SASDATE);
     QTR=QTR(SASDATE);
************************************************************************
** NICK WALRAVEN AND MICHELE RICCI SELECTED ONLY BANKS WHERE          **
** BANK9420<=23 AND BANK9210<=56 FOR THE AGRICULTURAL FINANCE         **
** DATABOOK.  ALTON ELECTED TO SUBSET BY THE SAME VARIABLES TO BE     **
** CONSISTENT.                                                        **
************************************************************************;
     IF BANK9420<24 & BANK9210<57;
     PROC SORT DATA=MERGE1;     *ENABLES USE OF LAST.BANK AND LAST.DATE;
          BY BANK9000 DESCENDING DATE;
RUN;

DATA MERGE2;
     SET MERGE1;
          BY BANK9000 DESCENDING DATE;
************************************************************************
** THE FOLLOWING STATEMENTS:                                          **
**         (1) CREATE A COUNTER, T, WITH POSSIBLE VALUES FROM 1-5     **
**         (2) DELETE BANKS WITH MISSING OBSERVATIONS IN A PARTICULAR **
**             QUARTER                                                **
**         (3) SUM THE COUNTER VARIABLE AND OUTPUT IT TO A NEW DATASET**
************************************************************************;
     RETAIN T 1;
     T+1;
     IF LAST.BANK9000 AND LAST.DATE THEN T=1;
     IF YEAR=1974 & (QTR=2³QTR=4) &
        (RCON0010=.³RCON1420=.³RCON1590=.³
        RCON2122=.³RCON2145=.³RCON2150=.³RCON2170=.³
        RCON3210=.) THEN DELETE;
RUN;
    PROC MEANS SUM NOPRINT DATA=MERGE2;
          VAR T;
          BY BANK9000;
          OUTPUT OUT=SUMOUT SUM=SUMT;
    RUN;

DATA MERGE3;
     MERGE MERGE2 SUMOUT;
          BY BANK9000;
************************************************************************
** IF SUMT (CREATED ABOVE) <6 , THEN THIS IMPLIES THAT THE BANK HAD   **
** MISSING OBSERVATIONS IN AT LEAST ONE QUARTER IN 1974.  SINCE ONLY  **
** MISSING OBSERVATIONS WERE DELETED ABOVE, THERE MAY VERY WELL BE    **
** NON-MISSING OBSERVATIONS FOR OTHER QUARTERS (I.E., IT IS ENTIRELY  **
** POSSIBLE THAT A BANK ONLY HAD MISSING CALL REPORT DATA FOR SAY THE **
** 2ND QUARTER).  HOWEVER, ONLY BANKS WITH NON-MISSING OBSERVATIONS   **
** IN ALL FOUR QUARTERS CAN REMAIN IN THE SAMPLE.  THEREFORE, BANKS   **
** WHERE SUMT < 6  MUST BE DELETED BECAUSE ONLY BANKS WHERE SUMT=6    **
** HAVE NON-MISSING OBSERVATIONS FOR QTRS 2 AND 4 IN 1974.            **
************************************************************************;
     IF SUMT < 6 THEN DELETE;
RUN;

DATA USAGBANK;
     SET MERGE3(WHERE=(YEAR=1974 & QTR=2));BY BANK9000;
     RCON2122=LEFT(INPUT(PUT(RCON2122,ZEROFMT.),BEST12.));
     USRATIO= ((SUM(RCON1420,RCON1590))/RCON2122)*100;
RUN;
     PROC MEANS DATA=USAGBANK;
          VAR USRATIO;
          OUTPUT OUT=USAGBK MEAN=USRATIO;
          TITLE1 'US RATIO IN 1974';
          LABEL USRATIO="RATIO OF AGLOANS TO TOTAL LOANS";
     RUN;

************************************************************************
** FRACTIONALIZATION BEGINS HERE!!                                    **
************************************************************************;
DATA AGBK7312;
     SET MERGE3(WHERE=(YEAR=1973 & QTR=4));
     AGBANK=1;
     %AGBK74A;
     RUN;

DATA AGBK7406;
     SET MERGE3(WHERE=(YEAR=1974 & QTR=2));
     AGBANK=3;
     %AGBK74C;
     RUN;

DATA AGBK7412;
     SET MERGE3(WHERE=(YEAR=1974 & QTR=4));
     AGBANK=5;
     %AGBK74E;
     RUN;
************************************************************************
** FRACTIONALIZATION ENDS HERE!!                                      **
************************************************************************;

DATA MERGAGBK; **CHECK THIS CAREFULLY!!;
     MERGE AGBK7312 AGBK7406 AGBK7412;
          BY BANK9000;
RUN;

DATA AGBK74A;
************************************************************************
** THE FOLLOWING IF STATEMENT SETS IN THE US AGGREGATE RATIO OF       **
** (RCON1420+RCON1590)/RCON2122 AND MERGES IT TO ALL OBSERVATIONS IN  **
** THE DATA SET MERGAGBK (CREATED IN THE DATA STEP ABOVE).            **
************************************************************************;
     IF _N_=1 THEN SET USAGBK;
     DROP _TYPE_ _FREQ_;
     SET MERGAGBK;
     IF AGBANK=5;
     %MACRO QTRSUM;
          %LET VNAMES=
               R1420 R1590 R2122 R2170 R3210
               R2150 R0010 R2145;

          %DO AG=1 %TO 8;
               %LET VNAME= %SCAN(&VNAMES,&AG);
               &VNAME=SUM(&VNAME.A,&VNAME.C,&VNAME.E);
               DROP &VNAME.A &VNAME.C &VNAME.E;
          %END;
    %MEND QTRSUM;

   *INVOKE QTRSUM MACRO CREATED ABOVE;
    %QTRSUM;
************************************************************************
** IDENTIFY AGRICULTURAL BANKS USING DEFINITION SUPPLIED BY ALTON     **
** GILBERT                                                            **
************************************************************************;
    R2122=LEFT(INPUT(PUT(R2122,ZEROFMT.),BEST12.));
    R2170=LEFT(INPUT(PUT(R2170,ZEROFMT.),BEST12.));
    AGLOAN=SUM(R1420,R1590);
    AGBKRATO= (AGLOAN/R2122)*100;
    IF AGBKRATO > USRATIO THEN AGBNK=1;               *AN AG BANK*;
         ELSE IF AGBKRATO <= USRATIO THEN AGBNK=0;    *NOT AN AG BANK*;
************************************************************************
**  CREATING RANGES OF AGRICULTURAL LOANS (R1420+R1590) TO TOTAL      **
**  LOANS (R2122) (TO BE USED LATER IN %AGBK74F)                      **
************************************************************************;
    IF AGBKRATO >= 0 & AGBKRATO <= 10 THEN RANGE=1;
    ELSE IF 10 < AGBKRATO <= 15 THEN RANGE=2;
    ELSE IF 15 < AGBKRATO <= 20 THEN RANGE=3;
    ELSE IF 20 < AGBKRATO <= 25 THEN RANGE=4;
    ELSE IF 25 < AGBKRATO <= 30 THEN RANGE=5;
    ELSE IF 30 < AGBKRATO <= 40 THEN RANGE=6;
    ELSE IF 40 < AGBKRATO <= 50 THEN RANGE=7;
    ELSE IF 50 < AGBKRATO <= 60 THEN RANGE=8;
    ELSE IF 60 < AGBKRATO <= 70 THEN RANGE=9;
    ELSE IF AGBKRATO > 70 THEN RANGE=10;
    ELSE IF AGBKRATO = .  THEN RANGE=11;
************************************************************************
**  CALCULATING VARIOUS RATIOS OUTLINED IN ALTON'S 7/15/94 MEMO FOR 3 **
**  GROUPS OF BANKS:  (1) AGRICULTURAL BANKS, (2) COMMERCIAL BANKS    **
**  (NOT AG BANKS!) WITH TOTAL ASSETS <= 500 MILLION, AND (3)         **
**  COMMERICAL BANKS (NOT AG BANKS!) WITH TOTAL ASSETS <= INDEXED     **
**  AVERAGE TOTAL ASSETS OF THE BANKING INDUSTRY (IN THAT YEAR).      **
**  NOTE, HOWEVER, THAT GROUP (3) = GROUP (2) FOR 1994 BECAUSE 1994   **
**  IS THE BASE PERIOD FOR GROUP (3).  SEE ALTON'S MEMO FOR FURTHER   **
**  DETAILS.                                                          **
************************************************************************;

* RETURN ON ASSETS= (NET INCOME/TOTAL ASSETS);
     ROA= (RIAD4340/R2170);

* RETURN ON EQUITY= (EQUITY/TOTAL ASSETS);
     ROE= (R3210/R2170);

* NET INTEREST MARGIN= (NET INTEREST INCOME/EARNING ASSETS), WHERE;
* EARNINGS ASSETS= INTEREST BEARING BALANCES+SECURITIES+FEDERAL FUNDS;
* SOLD AND REPURCHASE AGREEMENTS+NET LOANS+TRADING ACCOUNT ASSETS;
     NUMER=(SUM(RIAD4010,RIAD4020,RIAD4027,RIAD4050,RIAD4060)
                -SUM(RIAD4170,RIAD4180,RIAD4190,RIAD4200));
     NIM= ((NUMER)/(R2170-(R0010+R2145+R2150)));     *CHANGED PER 7/21/94 MEMO;
     RUN;

PROC MEANS DATA=AGBK74A N SUM;
     VAR R2170;
     TITLE "TOTAL US ASSETS IN 1974";
RUN;
DATA AGBK74B;                *COMPUTING SUMMARY STATISTICS FOR AGBANKS;
     SET AGBK74A;
     IF AGBNK=1;
RUN;

     PROC MEANS DATA=AGBK74B;
          VAR AGBKRATO;
          TITLE 'AG BANKS IN 1974';
     RUN;
     %AGBK74F;
     RUN;

DATA AGBK74C;
     SET AGBK74A;
************************************************************************
**  THE FOLLOWING IF STATEMENT IDENTIFIES COMMERICAL BANKS (NOT AG    **
**  BANKS!-I.E., AGBNK=0) WITH TOTAL ASSETS < 500 MILLION             **
************************************************************************;
     IF AGBNK=0 AND R2170 <= 500000 THEN ASSETSZ=1;
           ELSE IF AGBNK=0 AND R2170 > 500000 THEN ASSETSZ=2;
           ELSE IF AGBNK=1 AND 500000 >= R2170 >= 0 THEN ASSETSZ=3;
     RUN;

DATA AGBK74D;
     SET AGBK74C;
     FORMAT ASSETSZ SIZEFMT.;
RUN;
************************************************************************
**  SUMMARY STATISTICS FOR COMMERICAL BANKS (NOT AG BANKS!-I.E.,      **
**  AGBNK=0) WITH TOTAL ASSETS <= 500 MILLION                         **
************************************************************************;
     PROC MEANS DATA=AGBK74D;
          VAR ROA ROE;
          CLASS ASSETSZ;
          TITLE1 ' RETURN ON ASSETS (ROA), RETURN ON';
          TITLE2 ' EQUITY (ROE), NET INTEREST MARGIN';
          TITLE3 ' (NIM) FOR  BANKS ';
     RUN;

DATA INDEX;
     SET AGBK74A;
     INDASSL=(68855.94/334075.41)*500000;
     INDASSH=(68855.94/334075.41)*1000000;
 IF AGBNK=0 AND R2170 <= INDASSL THEN INDEX=1;
   ELSE IF AGBNK=0 AND INDASSH>=R2170>INDASSL THEN INDEX=2;
   ELSE IF AGBNK=1 AND INDASSL >= R2170 >= 0 THEN INDEX=3;
     RUN;

DATA INDEX1;
     SET INDEX; FORMAT INDEX SIZEFMT.;
RUN;
************************************************************************
**  SUMMARY STATISTICS FOR COMMERICAL BANKS (NOT AG BANKS!-I.E.,      **
**  AGBNK=0) WITH TOTAL ASSETS <= 104.384 MILLION.   THIS IS THE      **
**  ALTERNATIVE GROUP OF BANKS THAT ALTON MENTIONED IN HIS MEMO ON    **
**  7/15/94.  TO CALCULATE THIS ALTERNATIVE GROUP FOR 1992, DIVIDE    **
**  AVERAGE TOTAL ASSETS OF THE BANKING INDUSTRY IN 1992 BY AVG. TOTAL**
**  ASSETS IN 1994 AND MULTIPLY BY 500 MILLION.  TO DETERMINE THE SIZE**
**  CUTOFF FOR EACH YEAR, APPLY THIS METHOD BACK THROUGH TIME.        **
************************************************************************;
     PROC MEANS DATA=INDEX1;
          VAR ROA ROE;
          CLASS INDEX;
          TITLE1 ' RETURN ON ASSETS (ROA), RETURN ON';
          TITLE2 ' EQUITY (ROE), NET INTEREST MARGIN';
          TITLE3 ' (NIM) FOR  BANKS ';
          TITLE4 '   (THIS IS THE INDEXED GROUP)    ';
     RUN;

DATA AGBK74E; SET AGBK74D;
NEGINC=0;
  IF RIAD4340<0 THEN NEGINC=1;
RUN;
  PROC SORT; BY ASSETSZ NEGINC;
  RUN;
     PROC FREQ DATA=AGBK74E;
       TABLES NEGINC/LIST SPARSE;
       BY ASSETSZ;
       TITLE "PERCENTAGE WITH NEGATIVE NET INCOME";
       TITLE2 "1974 UNINDEXED";
RUN;

DATA INDEX2; SET INDEX1;
NEGINC=0;
  IF RIAD4340<0 THEN NEGINC=1;
RUN;
  PROC SORT; BY INDEX NEGINC;
  RUN;
     PROC FREQ DATA=INDEX2;
       TABLES NEGINC/LIST SPARSE;
       BY INDEX;
       TITLE "PERCENTAGE WITH NEGATIVE NET INCOME";
       TITLE2 "1974 INDEXED";
      RUN;







************************************************************************
************************************************************************
************************************************************************
** THIS CREATES THE 1975 DATA IN TABLES 4 AND 5.                      **
************************************************************************
************************************************************************
************************************************************************;

LIBNAME IN 'H1CAW01.AGBANK.DATA' DISP=SHR;
FILENAME IN1 'H1CAW01.RAGKLK.MACRO' DISP=SHR;
LIBNAME IN2 'H1HLB00.AGBANK.DATA';

PROC FORMAT;
  VALUE SIZEFMT
   1="SMALL NON-AG BANKS"
   2="OTHER NON-AG BANKS"
   3="AG BANKS";
RUN;
************************************************************************
** THIS PROGRAM IS USED TO COMPUTE VARIOUS FINANCIAL RATIOS AND       **
** SUMMARY STATISTICS FOR ALTON GILBERT'S AND KEVIN KLIESEN'S         **
** AGRICULTURAL BANKING PAPER (SUMMER/FALL 1994).  IT                 **
** IS USED FIRST TO IDENTIFY BANKS WITHOUT CALL REPORT DATA           **
** (I.E., WITH MISSING DATA) FOR ANY QUARTER IN 1975.  IF A MISSING   **
** OBSERVATION IS DETECTED FOR ANY QUARTER, THEN ALL OBSERVATIONS FOR **
** THAT BANK IN 1975 ARE DELETED (BECAUSE ALTON AND KEVIN ONLY WANT   **
** BANKS IN THEIR SAMPLE WITH CALL REPORT DATA FOR ALL FOUR QUARTERS  **
** IN 1975).  THE PROGRAM IS THEN USED TO FRACTIONALIZE ALL OF THE    **
** BALANCE SHEET VARIABLES (I.E., ESTIMATE ANNUAL AVERAGE OF EACH     **
** VARIABLE OVER THE YEAR).  NOTE:  INCOME AND EXPENSE DATA ARE NOT   **
** FRACTIONALIZED BECAUSE THEY ARE FLOW VARIABLES.  FINALLY, THE      **
** PROGRAM COMPUTES THE VARIOUS SUMMARY STATISTICS AND RATIOS         **
** OUTLINED IN THE 7/15/94 AND 7/21/94 MEMOS FROM ALTON.  NOTE:       **
** THIS PROGRAM IS DOCUMENTED BELOW FOR FUTURE USE.  THIS IS          **
************************************************************************;

PROC FORMAT;
     VALUE ZEROFMT
     0 = '           .'; *BE CAREFUL WITH THIS FORMAT! TOO MANY;
                         *SPACES CAN CORRUPT THE DATA AND RATIOS;
RUN;
%INC IN1(AGBK75A);
%INC IN1(AGBK75C);
%INC IN1(AGBK75E);
%INC IN1(AGBK75F);
************************************************************************
** READING IN CALL REPORT DATA FOR 1974.4-1975.4                      **
** PRIOR TO 1984.1, RCON0081 IS KNOWN AS RCON0010.                    **
** NOTE:  RCON2122=(RCON1400-RCON3370) PRIOR TO 1976.1.>>>>>>>>>>>>>  **
************************************************************************;
DATA AGBK7412;
     SET IN.RCRI7412(DROP=RIAD4010 RIAD4020 RIAD4027 RIAD4050 RIAD4060
                          RIAD4170 RIAD4180 RIAD4190 RIAD4200);
     RCON2122=(RCON1400-RCON3370);  *NOTICE CHANGE IN TOTAL LOANS DEFINITION;
RUN;
DATA AGBK7506;
     SET IN.RCRI7506(DROP=RIAD4010 RIAD4020 RIAD4027 RIAD4050 RIAD4060
                          RIAD4170 RIAD4180 RIAD4190 RIAD4200);
     RCON2122=(RCON1400-RCON3370);  *NOTICE CHANGE IN TOTAL LOANS DEFINITION;
RUN;
DATA AGBK7512;
     SET IN.RCRI7512;         *DO NOT DROP RIAD VARIABLES IN 4TH QTR!;
                              *NEED FLOW VARIABLES IN 4TH QTR ONLY;
     RCON2122=(RCON1400-RCON3370);  *NOTICE CHANGE IN TOTAL LOANS DEFINITION;
RUN;
DATA MERGE1;
     SET AGBK7412 AGBK7506 AGBK7512;
************************************************************************
** THE VARIABLE, DATE, IS A NUMERIC VARIABLE WHEN IT IS PULLED OFF OF **
** THE TAPE AT THE BOARD.  THEREFORE, TO CREATE A SAS DATE VALUE, YOU **
** HAVE TO FIRST USE A PUT FUNCTION TO RETURN THE NUMERIC DATE VALUE  **
** AS A CHARACTER STRING.  THEN, YOU HAVE TO USE THE INPUT            **
** FUNCTION TO RETURN THE CHARACTER STRING AS A SAS DATE VALUE.       **
** AFTER YOU HAVE CREATED A SASDATE VARIABLE, YOU CAN THEN CREATE A   **
** YEAR AND QUARTER VARIABLE.                                         **
************************************************************************;
     NUMDATE=PUT(DATE,Z6.);
     SASDATE=INPUT(NUMDATE,YYMMDD6.);
     YEAR=YEAR(SASDATE);
     QTR=QTR(SASDATE);
************************************************************************
** NICK WALRAVEN AND MICHELE RICCI SELECTED ONLY BANKS WHERE          **
** BANK9420<=23 AND BANK9210<=56 FOR THE AGRICULTURAL FINANCE         **
** DATABOOK.  ALTON ELECTED TO SUBSET BY THE SAME VARIABLES TO BE     **
** CONSISTENT.                                                        **
************************************************************************;
     IF BANK9420<24 & BANK9210<57;
RUN;
     PROC SORT DATA=MERGE1;     *ENABLES USE OF LAST.BANK AND LAST.DATE;
          BY BANK9000 DESCENDING DATE;
     RUN;

DATA MERGE2;
     SET MERGE1;
          BY BANK9000 DESCENDING DATE;
************************************************************************
** THE FOLLOWING STATEMENTS:                                          **
**         (1) CREATE A COUNTER, T, WITH POSSIBLE VALUES FROM 1-5     **
**         (2) DELETE BANKS WITH MISSING OBSERVATIONS IN A PARTICULAR **
**             QUARTER                                                **
**         (3) SUM THE COUNTER VARIABLE AND OUTPUT IT TO A NEW DATASET**
************************************************************************;
     RETAIN T 1;
     T+1;
     IF LAST.BANK9000 AND LAST.DATE THEN T=1;
     IF YEAR=1975 & (QTR=2³QTR=4) &
        (RCON0010=.³RCON1420=.³RCON1590=.³
        RCON2122=.³RCON2145=.³RCON2150=.³RCON2170=.³
        RCON3210=.) THEN DELETE;
RUN;
     PROC MEANS SUM NOPRINT DATA=MERGE2;
          VAR T;
          BY BANK9000;
          OUTPUT OUT=SUMOUT SUM=SUMT;
     RUN;

DATA MERGE3;
     MERGE MERGE2 SUMOUT;
          BY BANK9000;
************************************************************************
** IF SUMT (CREATED ABOVE) <6 , THEN THIS IMPLIES THAT THE BANK HAD   **
** MISSING OBSERVATIONS IN AT LEAST ONE QUARTER IN 1975.  SINCE ONLY  **
** MISSING OBSERVATIONS WERE DELETED ABOVE, THERE MAY VERY WELL BE    **
** NON-MISSING OBSERVATIONS FOR OTHER QUARTERS (I.E., IT IS ENTIRELY  **
** POSSIBLE THAT A BANK ONLY HAD MISSING CALL REPORT DATA FOR SAY THE **
** 2ND QUARTER).  HOWEVER, ONLY BANKS WITH NON-MISSING OBSERVATIONS   **
** IN ALL FOUR QUARTERS CAN REMAIN IN THE SAMPLE.  THEREFORE, BANKS   **
** WHERE SUMT < 6  MUST BE DELETED BECAUSE ONLY BANKS WHERE SUMT=6    **
** HAVE NON-MISSING OBSERVATIONS FOR QTRS 2 AND 4 IN 1975.            **
************************************************************************;
      IF SUMT < 6 THEN DELETE;
RUN;

DATA USAGBANK;
     SET MERGE3(WHERE=(YEAR=1975 & QTR=2));BY BANK9000;
     RCON2122=LEFT(INPUT(PUT(RCON2122,ZEROFMT.),BEST12.));
     USRATIO= ((SUM(RCON1420,RCON1590))/RCON2122)*100;
RUN;
     PROC MEANS DATA=USAGBANK;
          VAR USRATIO;
          OUTPUT OUT=USAGBK MEAN=USRATIO;
          TITLE1 'US RATIO IN 1975';
          LABEL USRATIO="RATIO OF AGLOANS TO TOTAL LOANS";
     RUN;
************************************************************************
** FRACTIONALIZATION BEGINS HERE!!                                    **
************************************************************************;
DATA AGBK7412;
     SET MERGE3(WHERE=(YEAR=1974 & QTR=4));
     AGBANK=1;
     %AGBK75A;
     RUN;

DATA AGBK7506;
     SET MERGE3(WHERE=(YEAR=1975 & QTR=2));
     AGBANK=3;
     %AGBK75C;
     RUN;

DATA AGBK7512;
     SET MERGE3(WHERE=(YEAR=1975 & QTR=4));
     AGBANK=5;
     %AGBK75E;
     RUN;
************************************************************************
** FRACTIONALIZATION ENDS HERE!!                                      **
************************************************************************;

DATA MERGAGBK; **CHECK THIS CAREFULLY!!;
     MERGE AGBK7412 AGBK7506 AGBK7512;
          BY BANK9000;
     RUN;

DATA AGBK75A;
************************************************************************
** THE FOLLOWING IF STATEMENT SETS IN THE US AGGREGATE RATIO OF       **
** (RCON1420+RCON1590)/RCON2122 AND MERGES IT TO ALL OBSERVATIONS IN  **
** THE DATA SET MERGAGBK (CREATED IN THE DATA STEP ABOVE).            **
************************************************************************;
     IF _N_=1 THEN SET USAGBK;
     DROP _TYPE_ _FREQ_;
     SET MERGAGBK;
     IF AGBANK=5;
     %MACRO QTRSUM;
          %LET VNAMES=
               R1420 R1590 R2122 R2170 R3210
               R2150 R0010 R2145;

          %DO AG=1 %TO 8;
               %LET VNAME= %SCAN(&VNAMES,&AG);
               &VNAME=SUM(&VNAME.A,&VNAME.C,&VNAME.E);
               DROP &VNAME.A &VNAME.C &VNAME.E;
          %END;
    %MEND QTRSUM;

   *INVOKE QTRSUM MACRO CREATED ABOVE;
    %QTRSUM;
************************************************************************
** IDENTIFY AGRICULTURAL BANKS USING DEFINITION SUPPLIED BY ALTON     **
** GILBERT                                                            **
************************************************************************;
    R2122=LEFT(INPUT(PUT(R2122,ZEROFMT.),BEST12.));
    R2170=LEFT(INPUT(PUT(R2170,ZEROFMT.),BEST12.));
    AGLOAN=SUM(R1420,R1590);
    AGBKRATO= (AGLOAN/R2122)*100;
    IF AGBKRATO > USRATIO THEN AGBNK=1;               *AN AG BANK*;
         ELSE IF AGBKRATO <= USRATIO THEN AGBNK=0;    *NOT AN AG BANK*;
************************************************************************
**  CREATING RANGES OF AGRICULTURAL LOANS (R1420+R1590) TO TOTAL      **
**  LOANS (R2122) (TO BE USED LATER IN %AGBK75F)                      **
************************************************************************;
    IF AGBKRATO >= 0 & AGBKRATO <= 10 THEN RANGE=1;
    ELSE IF 10 < AGBKRATO <= 15 THEN RANGE=2;
    ELSE IF 15 < AGBKRATO <= 20 THEN RANGE=3;
    ELSE IF 20 < AGBKRATO <= 25 THEN RANGE=4;
    ELSE IF 25 < AGBKRATO <= 30 THEN RANGE=5;
    ELSE IF 30 < AGBKRATO <= 40 THEN RANGE=6;
    ELSE IF 40 < AGBKRATO <= 50 THEN RANGE=7;
    ELSE IF 50 < AGBKRATO <= 60 THEN RANGE=8;
    ELSE IF 60 < AGBKRATO <= 70 THEN RANGE=9;
    ELSE IF AGBKRATO > 70 THEN RANGE=10;
    ELSE IF AGBKRATO = .  THEN RANGE=11;
************************************************************************
**  CALCULATING VARIOUS RATIOS OUTLINED IN ALTON'S 7/15/94 MEMO FOR 3 **
**  GROUPS OF BANKS:  (1) AGRICULTURAL BANKS, (2) COMMERCIAL BANKS    **
**  (NOT AG BANKS!) WITH TOTAL ASSETS <= 500 MILLION, AND (3)         **
**  COMMERICAL BANKS (NOT AG BANKS!) WITH TOTAL ASSETS <= INDEXED     **
**  AVERAGE TOTAL ASSETS OF THE BANKING INDUSTRY (IN THAT YEAR).      **
**  NOTE, HOWEVER, THAT GROUP (3) = GROUP (2) FOR 1994 BECAUSE 1994   **
**  IS THE BASE PERIOD FOR GROUP (3).  SEE ALTON'S MEMO FOR FURTHER   **
**  DETAILS.                                                          **
************************************************************************;

* RETURN ON ASSETS= (NET INCOME/TOTAL ASSETS);
     ROA= (RIAD4340/R2170);

* RETURN ON EQUITY= (EQUITY/TOTAL ASSETS);
     ROE= (R3210/R2170);

* NET INTEREST MARGIN= (NET INTEREST INCOME/EARNING ASSETS), WHERE;
* EARNINGS ASSETS= INTEREST BEARING BALANCES+SECURITIES+FEDERAL FUNDS;
* SOLD AND REPURCHASE AGREEMENTS+NET LOANS+TRADING ACCOUNT ASSETS;
     NUMER=(SUM(RIAD4010,RIAD4020,RIAD4027,RIAD4050,RIAD4060)
                -SUM(RIAD4170,RIAD4180,RIAD4190,RIAD4200));
     NIM= ((NUMER)/(R2170-(R0010+R2145+R2150)));     *CHANGED PER 7/21/94 MEMO;
     RUN;

PROC MEANS DATA=AGBK75A N SUM;
     VAR R2170;
     TITLE "TOTAL US ASSETS IN 1975";
DATA AGBK75B;                *COMPUTING SUMMARY STATISTICS FOR AGBANKS;
     SET AGBK75A;
     IF AGBNK=1;
RUN;

     PROC MEANS DATA=AGBK75B;
          VAR AGBKRATO;
          TITLE 'AG BANKS IN 1975';
     %AGBK75F;

     RUN;

DATA AGBK75C;
     SET AGBK75A;
************************************************************************
**  THE FOLLOWING IF STATEMENT IDENTIFIES COMMERICAL BANKS (NOT AG    **
**  BANKS!-I.E., AGBNK=0) WITH TOTAL ASSETS < 500 MILLION             **
************************************************************************;
     IF AGBNK=0 AND R2170 <= 500000 THEN ASSETSZ=1;
           ELSE IF AGBNK=0 AND R2170 > 500000 THEN ASSETSZ=2;
           ELSE IF AGBNK=1 AND 500000 >= R2170 >= 0 THEN ASSETSZ=3;
     RUN;

DATA AGBK75D;
     SET AGBK75C;
     FORMAT ASSETSZ SIZEFMT.;
RUN;
************************************************************************
**  SUMMARY STATISTICS FOR COMMERICAL BANKS (NOT AG BANKS!-I.E.,      **
**  AGBNK=0) WITH TOTAL ASSETS <= 500 MILLION                         **
************************************************************************;
     PROC MEANS DATA=AGBK75D;
          VAR ROA ROE;
          CLASS ASSETSZ;
          TITLE1 ' RETURN ON ASSETS (ROA), RETURN ON';
          TITLE2 ' EQUITY (ROE), NET INTEREST MARGIN';
          TITLE3 ' (NIM) FOR  BANKS ';
     RUN;

DATA INDEX;
     SET AGBK75A;
     INDASSL=(71953.59/334075.41)*500000;
     INDASSH=(71953.59/334075.41)*1000000;
 IF AGBNK=0 AND R2170 <= INDASSL THEN INDEX=1;
   ELSE IF AGBNK=0 AND INDASSH>=R2170>INDASSL THEN INDEX=2;
   ELSE IF AGBNK=1 AND INDASSL >= R2170 >= 0 THEN INDEX=3;
     RUN;

DATA INDEX1;
     SET INDEX; FORMAT INDEX SIZEFMT.;
RUN;
************************************************************************
**  SUMMARY STATISTICS FOR COMMERICAL BANKS (NOT AG BANKS!-I.E.,      **
**  AGBNK=0) WITH TOTAL ASSETS <= 109.080 MILLION.   THIS IS THE      **
**  ALTERNATIVE GROUP OF BANKS THAT ALTON MENTIONED IN HIS MEMO ON    **
**  7/15/94.  TO CALCULATE THIS ALTERNATIVE GROUP FOR 1992, DIVIDE    **
**  AVERAGE TOTAL ASSETS OF THE BANKING INDUSTRY IN 1992 BY AVG. TOTAL**
**  ASSETS IN 1994 AND MULTIPLY BY 500 MILLION.  TO DETERMINE THE SIZE**
**  CUTOFF FOR EACH YEAR, APPLY THIS METHOD BACK THROUGH TIME.        **
************************************************************************;
     PROC MEANS DATA=INDEX1;
          VAR ROA ROE;
          CLASS INDEX;
          TITLE1 ' RETURN ON ASSETS (ROA), RETURN ON';
          TITLE2 ' EQUITY (ROE), NET INTEREST MARGIN';
          TITLE3 ' (NIM) FOR BANKS ';
          TITLE7 '   (THIS IS THE INDEXED GROUP)    ';
     RUN;

DATA AGBK75E; SET AGBK75D;
NEGINC=0;
  IF RIAD4340<0 THEN NEGINC=1;
RUN;
  PROC SORT; BY ASSETSZ NEGINC;
  RUN;
     PROC FREQ DATA=AGBK75E;
       TABLES NEGINC/LIST SPARSE;
       BY ASSETSZ;
       TITLE "PERCENTAGE WITH NEGATIVE NET INCOME";
       TITLE2 "1975 UNINDEXED";
     RUN;

DATA INDEX2; SET INDEX1;
NEGINC=0;
  IF RIAD4340<0 THEN NEGINC=1;
RUN;
  PROC SORT; BY INDEX NEGINC;
  RUN;
     PROC FREQ DATA=INDEX2;
       TABLES NEGINC/LIST SPARSE;
       BY INDEX;
       TITLE "PERCENTAGE WITH NEGATIVE NET INCOME";
       TITLE2 "1975 INDEXED";
     RUN;








************************************************************************
************************************************************************
************************************************************************
** THIS CREATES THE 1976 THROUGH 1993 DATA IN TABLES 4 AND 5.         **
************************************************************************
************************************************************************
************************************************************************;

LIBNAME IN 'H1CAW01.AGBANK.DATA' ;
FILENAME IN1 'H1CAW01.RAGKLK.MACRO' ;
LIBNAME IN2 'H1HLB00.AGBANK.DATA';

PROC FORMAT;
  VALUE SIZEFMT
   1="SMALL NON-AG BANKS"
   2="OTHER NON-AG BANKS"
   3="AG BANKS";
RUN;

PROC FORMAT;
     VALUE ZEROFMT
     0 = '           .'; *BE CAREFUL WITH THIS FORMAT! TOO MANY;
                         *SPACES CAN CORRUPT THE DATA AND RATIOS;
RUN;
************************************************************************
** THIS PROGRAM IS USED TO COMPUTE VARIOUS FINANCIAL RATIOS AND       **
** SUMMARY STATISTICS FOR ALTON GILBERT S AND KEVIN KLIESEN S         **
** AGRICULTURAL BANKING PAPER (SUMMER/FALL 1994).  IT                 **
** IS USED FIRST TO IDENTIFY BANKS WITHOUT CALL REPORT DATA           **
** (I.E., WITH MISSING DATA) FOR ANY QUARTER IN 1976.  IF A MISSING   **
** OBSERVATION IS DETECTED FOR ANY QUARTER, THEN ALL OBSERVATIONS FOR **
** THAT BANK IN 1976 ARE DELETED (BECAUSE ALTON AND KEVIN ONLY WANT   **
** BANKS IN THEIR SAMPLE WITH CALL REPORT DATA FOR ALL FOUR QUARTERS  **
** IN 1976).  THE PROGRAM IS THEN USED TO FRACTIONALIZE ALL OF THE    **
** BALANCE SHEET VARIABLES (I.E., ESTIMATE ANNUAL AVERAGE OF EACH     **
** VARIABLE OVER THE YEAR).  NOTE:  INCOME AND EXPENSE DATA ARE NOT   **
** FRACTIONALIZED BECAUSE THEY ARE FLOW VARIABLES.  FINALLY, THE      **
** PROGRAM COMPUTES THE VARIOUS SUMMARY STATISTICS AND RATIOS         **
** OUTLINED IN THE 7/15/94 AND 7/21/94 MEMOS FROM ALTON.  NOTE:       **
** THIS PROGRAM IS DOCUMENTED BELOW FOR FUTURE USE.                   **
************************************************************************;

%MACRO LOOP(YR,LYR,AVG);

%INC IN1(AGBK&YR.A);
%INC IN1(AGBK&YR.B);
%INC IN1(AGBK&YR.C);
%INC IN1(AGBK&YR.D);
%INC IN1(AGBK&YR.E);
%INC IN1(AGBK&YR.F);

************************************************************************
** READING IN CALL REPORT DATA FOR 1975.4-1976.4                      **
** PRIOR TO 1984.1, RCON0081 IS KNOWN AS RCON0010.                    **
** NOTE:  RCON2122=(RCON1400-RCON3370) PRIOR TO 1976.1.>>>>>>>>>>>>>  **
************************************************************************;
DATA AGBK&LYR.12;
%IF &YR<84 %THEN %DO;
  SET IN.RCRI&LYR.12(DROP=RIAD4010 RIAD4020 RIAD4027 RIAD4050 RIAD4060
                         RIAD4170 RIAD4180 RIAD4200);
%END;
%IF &YR>84 %THEN %DO;
  SET IN.RCRI&LYR.12(DROP=RIAD4074 RIAD4340);
%END;
%IF &YR=84 %THEN %DO;
  SET IN.RCRI&LYR.12(RENAME=(RCON0010=RCON0081));
%END;

  %IF 84>&YR>76 %THEN %DO;
      DROP RIAD4115;
  %END;
  %IF 84>&YR>78 %THEN %DO;
      DROP RIAD4185; %END;
      %ELSE %IF &YR<78 %THEN %DO; DROP RIAD4190;
  %END;
RUN;

DATA AGBK&YR.03;
%IF &YR<84 %THEN %DO;
  SET IN.RCRI&YR.03(DROP=RIAD4010 RIAD4115 RIAD4020 RIAD4027 RIAD4050
                          RIAD4060 RIAD4065 RIAD4170 RIAD4180
                          RIAD4200);
%END;
%IF &YR>=84 %THEN %DO;
  SET IN.RCRI&YR.03(DROP=RIAD4074 RIAD4340);
%END;

  %IF 84>&YR>76 %THEN %DO;
      DROP RIAD4115;
  %END;
  %IF 84>&YR>78 %THEN %DO;
      DROP RIAD4185; %END;
      %ELSE %IF &YR<78 %THEN %DO; DROP RIAD4190;
  %END;
RUN;

DATA AGBK&YR.06;
%IF &YR<84 %THEN %DO;
  SET IN.RCRI&YR.06(DROP=RIAD4010 RIAD4115 RIAD4020 RIAD4027 RIAD4050
                          RIAD4060 RIAD4065 RIAD4170 RIAD4180
                          RIAD4200);
%END;
%IF &YR>=84 %THEN %DO;
  SET IN.RCRI&YR.06(DROP=RIAD4074 RIAD4340);
%END;

  %IF 84>&YR>76 %THEN %DO;
      DROP RIAD4115;
  %END;
  %IF 84>&YR>78 %THEN %DO;
      DROP RIAD4185; %END;
      %ELSE %IF &YR<78 %THEN %DO; DROP RIAD4190;
  %END;
RUN;

DATA AGBK&YR.09;
%IF &YR<84 %THEN %DO;
  SET IN.RCRI&YR.09(DROP=RIAD4010 RIAD4115 RIAD4020 RIAD4027 RIAD4050
                          RIAD4060 RIAD4065 RIAD4170 RIAD4180
                          RIAD4200);
%END;
%IF &YR>=84 %THEN %DO;
  SET IN.RCRI&YR.09(DROP=RIAD4074 RIAD4340);
%END;

  %IF 84>&YR>76 %THEN %DO;
      DROP RIAD4115;
  %END;
  %IF 84>&YR>78 %THEN %DO;
      DROP RIAD4185; %END;
      %ELSE %IF &YR<78 %THEN %DO; DROP RIAD4190;
  %END;
RUN;
DATA AGBK&YR.12;
  SET IN.RCRI&YR.12;         *DO NOT DROP RIAD VARIABLES IN 4TH QTR!;
                              *NEED FLOW VARIABLES IN 4TH QTR ONLY;
RUN;

DATA MERGE1;
     SET AGBK&LYR.12 AGBK&YR.03 AGBK&YR.06 AGBK&YR.09 AGBK&YR.12;
     %IF &YR<84 %THEN %DO;  RCON0081=RCON0010; %END;
************************************************************************
** THE VARIABLE, DATE, IS A NUMERIC VARIABLE WHEN IT IS PULLED OFF OF **
** THE TAPE AT THE BOARD.  THEREFORE, TO CREATE A SAS DATE VALUE, YOU **
** HAVE TO FIRST USE A PUT FUNCTION TO RETURN THE NUMERIC DATE VALUE  **
** AS A CHARACTER STRING.  THEN, YOU HAVE TO USE THE INPUT            **
** FUNCTION TO RETURN THE CHARACTER STRING AS A SAS DATE VALUE.       **
** AFTER YOU HAVE CREATED A SASDATE VARIABLE, YOU CAN THEN CREATE A   **
** YEAR AND QUARTER VARIABLE.                                         **
************************************************************************;
     NUMDATE=PUT(DATE,Z6.);
     SASDATE=INPUT(NUMDATE,YYMMDD6.);
     YEAR=YEAR(SASDATE);
     QTR=QTR(SASDATE);
************************************************************************
** NICK WALRAVEN AND MICHELE RICCI SELECTED ONLY BANKS WHERE          **
** BANK9420<=23 AND BANK9210<=56 FOR THE AGRICULTURAL FINANCE         **
** DATABOOK.  ALTON ELECTED TO SUBSET BY THE SAME VARIABLES TO BE     **
** CONSISTENT.  AS OF 7/31/94, BOTH NICK WALRAVEN AND MICHELE RICCI   **
** WORKED AT THE BOARD.  MICHELE RICCI CAN BE REACHED AT              **
** (202)-452-3080.  KEVIN HAS NICK WALRAVEN S PHONE NUMBER.           **
************************************************************************;
     IF BANK9420<24 & BANK9210<57;
RUN;
     PROC SORT DATA=MERGE1;     *ENABLES USE OF LAST.BANK AND LAST.DATE;
          BY BANK9000 DESCENDING DATE;
     RUN;

DATA MERGE2;
     SET MERGE1;
          BY BANK9000 DESCENDING DATE;
************************************************************************
** THE FOLLOWING STATEMENTS:                                          **
**         (1) CREATE A COUNTER, T, WITH POSSIBLE VALUES FROM 1-5     **
**         (2) DELETE BANKS WITH MISSING OBSERVATIONS IN A PARTICULAR **
**             QUARTER                                                **
**         (3) SUM THE COUNTER VARIABLE AND OUTPUT IT TO A NEW DATASET**
************************************************************************;
     RETAIN T 1;
     T+1;
     IF LAST.BANK9000 AND LAST.DATE THEN T=1;
 %IF &YR<=82 %THEN %DO;
     IF YEAR=19&YR & (QTR=1³QTR=2³QTR=3³QTR=4) &
        (RCON0010=.³RCON1420=.³RCON1590=.³
        RCON2122=.³RCON2145=.³RCON2150=.³RCON2170=.³
        RCON3210=.) THEN DELETE;
 %END;
 %IF &YR>82 %THEN %DO;
     IF YEAR=19&YR & (QTR=1³QTR=2³QTR=3³QTR=4) &
        (RCON0081=.³RCON1403=.³RCON1407=.³RCON1420=.³RCON1590=.³
        RCON2122=.³RCON2145=.³RCON2150=.³RCON2170=.³RCON3123=.³
        RCON3210=.) THEN DELETE;
 %END;
RUN;

     PROC MEANS SUM NOPRINT DATA=MERGE2;
          VAR T;
          BY BANK9000;
          OUTPUT OUT=SUMOUT SUM=SUMT;
     RUN;

DATA MERGE3;
     MERGE MERGE2 SUMOUT;
          BY BANK9000;
************************************************************************
** IF SUMT (CREATED ABOVE) <15, THEN THIS IMPLIES THAT THE BANK HAD   **
** MISSING OBSERVATIONS IN AT LEAST ONE QUARTER IN 1976.  SINCE ONLY  **
** MISSING OBSERVATIONS WERE DELETED ABOVE, THERE MAY VERY WELL BE    **
** NON-MISSING OBSERVATIONS FOR OTHER QUARTERS (I.E., IT IS ENTIRELY  **
** POSSIBLE THAT A BANK ONLY HAD MISSING CALL REPORT DATA FOR SAY THE **
** 2ND QUARTER).  HOWEVER, ONLY BANKS WITH NON-MISSING OBSERVATIONS   **
** IN ALL FOUR QUARTERS CAN REMAIN IN THE SAMPLE.  THEREFORE, BANKS   **
** WHERE SUMT < 15 MUST BE DELETED BECAUSE ONLY BANKS WHERE SUMT=15   **
** HAVE NON-MISSING OBSERVATIONS FOR ALL 4 QUARTERS IN THE YEAR.      **
************************************************************************;
      IF SUMT < 15 THEN DELETE;
RUN;

DATA USAGBANK;
     SET MERGE3(WHERE=(YEAR=19&YR AND QTR=2));BY BANK9000;
     RCON2122=LEFT(INPUT(PUT(RCON2122,ZEROFMT.),BEST12.));
     USRATIO= ((SUM(RCON1420,RCON1590))/RCON2122)*100;
     PROC MEANS DATA=USAGBANK;
          VAR USRATIO;
          OUTPUT OUT=USAGBK MEAN=USRATIO;
          TITLE1 "US RATIO IN 19&YR";
          LABEL USRATIO="RATIO OF AGLOANS TO TOTAL LOANS";
RUN;
************************************************************************
** FRACTIONALIZATION BEGINS HERE!!                                    **
************************************************************************;
DATA AGBK&LYR.12;
     SET MERGE3(WHERE=(YEAR=19&LYR & QTR=4));
     AGBANK=1;
%IF &YR=76 %THEN %DO;
     RCON2122=(RCON1400-RCON3370);  *NOTICE CHANGE IN TOTAL LOANS DEFINITION;
%END;
     %AGBK&YR.A;
RUN;

DATA AGBK&YR.03;
     SET MERGE3(WHERE=(YEAR=19&YR & QTR=1));
     AGBANK=2;
     %AGBK&YR.B;
RUN;

DATA AGBK&YR.06;
     SET MERGE3(WHERE=(YEAR=19&YR & QTR=2));
     AGBANK=3;
     %AGBK&YR.C;
RUN;

DATA AGBK&YR.09;
     SET MERGE3(WHERE=(YEAR=19&YR & QTR=3));
     AGBANK=4;
     %AGBK&YR.D;
RUN;

DATA AGBK&YR.12;
     SET MERGE3(WHERE=(YEAR=19&YR & QTR=4));
     AGBANK=5;
     %AGBK&YR.E;
RUN;
************************************************************************
** FRACTIONALIZATION ENDS HERE!!                                      **
************************************************************************;

DATA MERGAGBK; **CHECK THIS CAREFULLY!!;
     MERGE AGBK&LYR.12 AGBK&YR.03 AGBK&YR.06 AGBK&YR.09 AGBK&YR.12;
          BY BANK9000;
RUN;

DATA AGBK&YR.A;
************************************************************************
** THE FOLLOWING IF STATEMENT SETS IN THE US AGGREGATE RATIO OF       **
** (RCON1420+RCON1590)/RCON2122 AND MERGES IT TO ALL OBSERVATIONS IN  **
** THE DATA SET MERGAGBK (CREATED IN THE DATA STEP ABOVE).            **
************************************************************************;
     IF _N_=1 THEN SET USAGBK;
     DROP _TYPE_ _FREQ_;
     SET MERGAGBK;
     IF AGBANK=5;
     %MACRO QTRSUM1;
          %LET VNAMES=
               R1420 R1590 R2122 R2170 R3210
               R2150 R0010 R2145;

          %DO AG=1 %TO 8;
               %LET VNAME= %SCAN(&VNAMES,&AG);
               &VNAME=SUM(&VNAME.A,&VNAME.B,&VNAME.C,&VNAME.D,
                          &VNAME.E);
               DROP &VNAME.A &VNAME.B &VNAME.C &VNAME.D &VNAME.E;
          %END;
    %MEND QTRSUM1;
     %MACRO QTRSUM2;
          %LET VNAMES=
               R1420 R1590 R2122 R2170 R3210 R1407 R1403
               R2150 R3123 R0081 R2145;

          %DO AG=1 %TO 11;
               %LET VNAME= %SCAN(&VNAMES,&AG);
               &VNAME=SUM(&VNAME.A,&VNAME.B,&VNAME.C,&VNAME.D,
                          &VNAME.E);
               DROP &VNAME.A &VNAME.B &VNAME.C &VNAME.D &VNAME.E;
          %END;
    %MEND QTRSUM2;

   *INVOKE QTRSUM MACRO CREATED ABOVE;
 %IF &YR<=82 %THEN %DO;
    %QTRSUM1;
 %END;
 %IF &YR>82 %THEN %DO;
    %QTRSUM2;
 %END;
************************************************************************
** IDENTIFY AGRICULTURAL BANKS USING DEFINITION SUPPLIED BY ALTON     **
** GILBERT                                                            **
************************************************************************;
    R2122=LEFT(INPUT(PUT(R2122,ZEROFMT.),BEST12.));
    R2170=LEFT(INPUT(PUT(R2170,ZEROFMT.),BEST12.));
    AGLOAN=SUM(R1420,R1590);
    AGBKRATO= (AGLOAN/R2122)*100;
    IF AGBKRATO > USRATIO THEN AGBNK=1;               *AN AG BANK*;
         ELSE IF AGBKRATO <= USRATIO THEN AGBNK=0;    *NOT AN AG BANK*;
************************************************************************
**  CREATING RANGES OF AGRICULTURAL LOANS (R1420+R1590) TO TOTAL      **
**  LOANS (R2122) (TO BE USED LATER IN %AGBK76F)                      **
************************************************************************;
    IF AGBKRATO >= 0 & AGBKRATO <= 10 THEN RANGE=1;
    ELSE IF 10 < AGBKRATO <= 15 THEN RANGE=2;
    ELSE IF 15 < AGBKRATO <= 20 THEN RANGE=3;
    ELSE IF 20 < AGBKRATO <= 25 THEN RANGE=4;
    ELSE IF 25 < AGBKRATO <= 30 THEN RANGE=5;
    ELSE IF 30 < AGBKRATO <= 40 THEN RANGE=6;
    ELSE IF 40 < AGBKRATO <= 50 THEN RANGE=7;
    ELSE IF 50 < AGBKRATO <= 60 THEN RANGE=8;
    ELSE IF 60 < AGBKRATO <= 70 THEN RANGE=9;
    ELSE IF AGBKRATO > 70 THEN RANGE=10;
    ELSE IF AGBKRATO = .  THEN RANGE=11;
************************************************************************
**  CALCULATING VARIOUS RATIOS OUTLINED IN ALTON S 7/15/94 MEMO FOR 3 **
**  GROUPS OF BANKS:  (1) AGRICULTURAL BANKS, (2) COMMERCIAL BANKS    **
**  (NOT AG BANKS!) WITH TOTAL ASSETS <= 500 MILLION, AND (3)         **
**  COMMERICAL BANKS (NOT AG BANKS!) WITH TOTAL ASSETS <= INDEXED     **
**  AVERAGE TOTAL ASSETS OF THE BANKING INDUSTRY (IN THAT YEAR).      **
**  NOTE, HOWEVER, THAT GROUP (3) = GROUP (2) FOR 1994 BECAUSE 1994   **
**  IS THE BASE PERIOD FOR GROUP (3).  SEE ALTON S MEMO FOR FURTHER   **
**  DETAILS.                                                          **
************************************************************************;
***NET INTEREST MARGIN NOT USED IN THE PAPER ANYMORE;
*** BUT IT IS STILL USED IN MACROS;
NIM=0;

* RETURN ON ASSETS= (NET INCOME/TOTAL ASSETS);
     ROA= (RIAD4340/R2170);

* RETURN ON EQUITY= (EQUITY/TOTAL ASSETS);
     ROE= (R3210/R2170);

* SUM OF NONPERFORMING LOANS PLUS OTHER REALESTATE OWNED MINUS;
* ALLOWANCE FOR LOAN AND LEASE LOSSES, ALL DIVIDED BY TOTAL ASSETS;
%IF &YR>=83 %THEN %DO;
    SUMNPL=SUM(R1407,R1403);
    NPLRATIO=((SUMNPL+R2150-R3123)/R2170);
    NEWNPL=((SUMNPL+R2150)/R2170);
%END;
RUN;


DATA AGBK&YR.B;              *COMPUTING SUMMARY STATISTICS FOR AGBANKS;
     SET AGBK&YR.A;
     IF AGBNK=1;
RUN;

     PROC MEANS DATA=AGBK&YR.B;
          VAR AGBKRATO;
          TITLE "AG BANKS IN 19&YR";
     RUN;
*******************************************************************;
***THIS CREATES THE VALUES FOR TABLE 5 FOR 1980, 1985, 1990-1993***;
*******************************************************************;   
       %DO I= 1 %TO 6;
         %LET RUNS=80 85 90 91 92 93;
         %LET R=%SCAN(&RUNS,&I);
             %IF &YR=&R %THEN %DO;
             %AGBK&YR.F;
       %END;
       %END;
     RUN;

***************************************************************;
***THIS CREATES THE VALUES FOR TABLE 4 FOR 1976 THROUGH 1993***;
***************************************************************;     
DATA INDEX;
     SET AGBK&YR.A;
     INDASSL=(&AVG/334075.41)*500000;
     INDASSH=(&AVG/334075.41)*1000000;
 IF AGBNK=0 AND R2170 <= INDASSL THEN INDEX=1;
   ELSE IF AGBNK=0 AND INDASSH>=R2170>INDASSL THEN INDEX=2;
   ELSE IF AGBNK=1 AND INDASSL >= R2170 >= 0 THEN INDEX=3;
RUN;

DATA INDEX1;
     SET INDEX; FORMAT INDEX SIZEFMT.;
RUN;
************************************************************************
**  SUMMARY STATISTICS FOR COMMERICAL BANKS (NOT AG BANKS!-I.E.,      **
**  AGBNK=0) WITH TOTAL ASSETS <= 116.655 MILLION.   THIS IS THE      **
**  ALTERNATIVE GROUP OF BANKS THAT ALTON MENTIONED IN HIS MEMO ON    **
**  7/15/94.  TO CALCULATE THIS ALTERNATIVE GROUP FOR 1992, DIVIDE    **
**  AVERAGE TOTAL ASSETS OF THE BANKING INDUSTRY IN 1992 BY AVG. TOTAL**
**  ASSETS IN 1994 AND MULTIPLY BY 500 MILLION.  TO DETERMINE THE SIZE**
**  CUTOFF FOR EACH YEAR, APPLY THIS METHOD BACK THROUGH TIME.        **
************************************************************************;
%IF &YR>=83 %THEN %DO;
     PROC MEANS DATA=INDEX1;
          VAR ROA ROE NPLRATIO NEWNPL;
          CLASS INDEX;
          TITLE1 ' RETURN ON ASSETS (ROA), RETURN ON';
          TITLE2 ' EQUITY (ROE), NONPERFORMING LOANS';
          TITLE4 '   (THIS IS THE INDEXED GROUP)    ';
%END;
     RUN;
%IF &YR<83 %THEN %DO;
     PROC MEANS DATA=INDEX1;
          VAR ROA ROE;
          CLASS INDEX;
          TITLE1 ' RETURN ON ASSETS (ROA), RETURN ON';
          TITLE2 ' EQUITY (ROE) ';
          TITLE4 '   (THIS IS THE INDEXED GROUP)    ';
%END;
     RUN;

DATA INDEX2; SET INDEX1;
NEGINC=0;
  IF RIAD4340<0 THEN NEGINC=1;
RUN;
  PROC SORT; BY INDEX NEGINC;
  RUN;
     PROC FREQ DATA=INDEX2;
       TABLES NEGINC/LIST SPARSE;
       BY INDEX;
       TITLE "PERCENTAGE WITH NEGATIVE NET INCOME";
       TITLE2 "19&YR INDEXED";
     RUN;

%MEND;
%LOOP(76,75,76950.72);
%LOOP(77,76,86543.18);
%LOOP(78,77,97686.50);
%LOOP(79,78,106330.49);
%LOOP(80,79,120854.84);
%LOOP(81,80,131934.65);
%LOOP(82,81,146092.48);
%LOOP(83,82,157941.01);
%LOOP(84,83,166577.41);
%LOOP(85,84,174913.22);
%LOOP(86,85,195381.39);
%LOOP(87,86,207966.98);
%LOOP(88,87,231602.16);
%LOOP(89,88,253317.44);
%LOOP(90,89,270765.55);
%LOOP(91,90,288202.19);
%LOOP(92,91,306594.25);
%LOOP(93,92,334075.40);








************************************************************************
************************************************************************
************************************************************************
** THIS CREATES THE 1994 DATA IN TABLES 4,5, AND 6.                   **
************************************************************************
************************************************************************
************************************************************************;

LIBNAME IN 'H1CAW01.AGBANK.DATA' DISP=SHR;
FILENAME IN1 'H1HLB00.AGBANK.MACRO';
FILENAME OUT 'H1HLB00.AGBANK.CNTL';
LIBNAME IN2 'H1HLB00.AGBANK.DATA';

PROC FORMAT;
  VALUE SIZEFMT
  3="AG BANKS"
  1="SMALL NON-AG BANKS"
  2="OTHER NON-AG BANKS";
RUN;

************************************************************************
** THIS PROGRAM CREATES THE 1994 LINE IN TABLE 4 AND IT CREATES       **
**      THE 1994 LINE IN TABLE 5 AND ALL OF TABLE 6.                  **
** THIS PROGRAM IS USED TO COMPUTE VARIOUS FINANCIAL RATIOS AND       **
** SUMMARY STATISTICS FOR ALTON GILBERT S AND KEVIN KLIESEN S         **
** AGRICULTURAL BANKING PAPER (SUMMER/FALL 1994).  IT                 **
** IS USED FIRST TO IDENTIFY BANKS WITHOUT CALL REPORT DATA           **
** (I.E., WITH MISSING DATA) FOR ANY QUARTER IN 1994.  IF A MISSING   **
** OBSERVATION IS DETECTED FOR ANY QUARTER, THEN ALL OBSERVATIONS FOR **
** THAT BANK IN 1994 ARE DELETED (BECAUSE ALTON AND KEVIN ONLY WANT   **
** BANKS IN THEIR SAMPLE WITH CALL REPORT DATA FOR ALL FOUR QUARTERS  **
** IN 1994).  THE PROGRAM IS THEN USED TO FRACTIONALIZE ALL OF THE    **
** BALANCE SHEET VARIABLES (I.E., ESTIMATE ANNUAL AVERAGE OF EACH     **
** VARIABLE OVER THE YEAR).  NOTE:  INCOME AND EXPENSE DATA ARE NOT   **
** FRACTIONALIZED BECAUSE THEY ARE FLOW VARIABLES.  FINALLY, THE      **
** PROGRAM COMPUTES THE VARIOUS SUMMARY STATISTICS AND RATIOS         **
** OUTLINED IN THE 7/15/94 AND 7/21/94 MEMOS FROM ALTON.  NOTE:       **
** THIS PROGRAM IS DOCUMENTED BELOW FOR FUTURE USE.                   **
************************************************************************;

PROC FORMAT;
     VALUE ZEROFMT
     0 = "           ."; *BE CAREFUL WITH THIS FORMAT! TOO MANY;
                         *SPACES CAN CORRUPT THE DATA AND RATIOS;
RUN;

%INC IN1(AGBK94A);
%INC IN1(AGBK94B);
%INC IN1(AGBK94C);
%INC IN1(AGBK94D);
%INC IN1(AGBK94E);
%INC IN1(AGBK94F);
************************************************************************
** READING IN CALL REPORT DATA FOR 1993.4-1994.4                      **
************************************************************************;
DATA AGBK9312;
     SET IN.RCRI9312 (RENAME=(BANK9000=BANK));
RUN;
DATA AGBK9403;
     SET IN2.RCRI9403;
RUN;
DATA AGBK9406;
     SET IN2.RCRI9406;
RUN;
DATA AGBK9409;
     SET IN2.RCRI9409;
RUN;
DATA AGBK9412;
     SET IN2.RCRI9412;        *DO NOT DROP RIAD4074³RIAD4340 IN 4 QTR!;
                              *NEED FLOW VARIABLES IN 4TH QTR ONLY;
RUN;
DATA MERGE1;
     SET AGBK9312 AGBK9403 AGBK9406 AGBK9409 AGBK9412;
************************************************************************
** THE VARIABLE, DATE, IS A NUMERIC VARIABLE WHEN IT IS PULLED OFF OF **
** THE TAPE AT THE BOARD.  THEREFORE, TO CREATE A SAS DATE VALUE, YOU **
** HAVE TO FIRST USE A PUT FUNCTION TO RETURN THE NUMERIC DATE VALUE  **
** AS A CHARACTER STRING.  THEN, YOU HAVE TO USE THE INPUT            **
** FUNCTION TO RETURN THE CHARACTER STRING AS A SAS DATE VALUE.       **
** AFTER YOU HAVE CREATED A SASDATE VARIABLE, YOU CAN THEN CREATE A   **
** YEAR AND QUARTER VARIABLE.                                         **
************************************************************************;
     NUMDATE=PUT(DATE,Z6.);
     SASDATE=INPUT(NUMDATE,YYMMDD6.);
     YEAR=YEAR(SASDATE);
     QTR=QTR(SASDATE);
************************************************************************
** NICK WALRAVEN AND MICHELE RICCI SELECTED ONLY BANKS WHERE          **
** BANK9420<=23 AND BANK9210<=56 FOR THE AGRICULTURAL FINANCE         **
** DATABOOK.  ALTON ELECTED TO SUBSET BY THE SAME VARIABLES TO BE     **
** CONSISTENT.                                                        **
************************************************************************;
     IF BANK9420<24 & BANK9210<57;
RUN;
     PROC SORT DATA=MERGE1;     *ENABLES USE OF LAST.BANK AND LAST.DATE;
          BY BANK DESCENDING DATE;
     RUN;

DATA MERGE2;
     SET MERGE1;
          BY BANK DESCENDING DATE;
************************************************************************
** THE FOLLOWING STATEMENTS:                                          **
**         (1) CREATE A COUNTER, T, WITH POSSIBLE VALUES FROM 1-5     **
**         (2) DELETE BANKS WITH MISSING OBSERVATIONS IN A PARTICULAR **
**             QUARTER                                                **
**         (3) SUM THE COUNTER VARIABLE AND OUTPUT IT TO A NEW DATASET**
************************************************************************;
     RETAIN T 1;
     T+1;
     IF LAST.BANK AND LAST.DATE THEN T=1;
      IF YEAR=1994 & (QTR=1³QTR=2³QTR=3³QTR=4) &
        (RCON1420=.³
        RCON2122=.³RCON2145=.³RCON2150=.³RCON2170=.
        ³RCON0081=.³RCON1403=.³RCON1407=.³RCON1590=.³RCON3123=.³
         RCON3210=.)
        THEN DELETE;
RUN;
     PROC MEANS SUM NOPRINT DATA=MERGE2;
          VAR T;
          BY BANK;
          OUTPUT OUT=SUMOUT SUM=SUMT;
     RUN;

DATA MERGE3;
     MERGE MERGE2 SUMOUT;
          BY BANK;
************************************************************************
** IF SUMT (CREATED ABOVE) <15, THEN THIS IMPLIES THAT THE BANK HAD   **
** MISSING OBSERVATIONS IN AT LEAST ONE QUARTER IN 1994.  SINCE ONLY  **
** MISSING OBSERVATIONS WERE DELETED ABOVE, THERE MAY VERY WELL BE    **
** NON-MISSING OBSERVATIONS FOR OTHER QUARTERS (I.E., IT IS ENTIRELY  **
** POSSIBLE THAT A BANK ONLY HAD MISSING CALL REPORT DATA FOR SAY THE **
** 2ND QUARTER).  HOWEVER, ONLY BANKS WITH NON-MISSING OBSERVATIONS   **
** IN ALL FOUR QUARTERS CAN REMAIN IN THE SAMPLE.  THEREFORE, BANKS   **
** WHERE SUMT < 15 MUST BE DELETED BECAUSE ONLY BANKS WHERE SUMT=15   **
** HAVE NON-MISSING OBSERVATIONS FOR ALL 4 QUARTERS IN THE YEAR.      **
************************************************************************;
      IF SUMT < 15 THEN DELETE;
RUN;

DATA USAGBANK;
     SET MERGE3(WHERE=(YEAR=1994 & QTR=2));BY BANK;
     IF RCON2122=0 THEN RCON2122=".";
     USRATIO= ((SUM(RCON1420,RCON1590))/RCON2122)*100;
RUN;
     PROC MEANS DATA=USAGBANK;
          VAR USRATIO;
          OUTPUT OUT=USAGBK MEAN=USRATIO;
          TITLE1 "US RATIO IN 1994";
          LABEL USRATIO="RATIO OF AGLOANS TO TOTAL LOANS";
     RUN;

************************************************************************
** FRACTIONALIZATION BEGINS HERE!!                                    **
************************************************************************;
DATA AGBK9312;
     SET MERGE3(WHERE=(YEAR=1993 & QTR=4));
     AGBANK=1;
     %AGBK94A;
     RUN;

DATA AGBK9403;
     SET MERGE3(WHERE=(YEAR=1994 & QTR=1));
     AGBANK=2;
     %AGBK94B;
     RUN;

DATA AGBK9406;
     SET MERGE3(WHERE=(YEAR=1994 & QTR=2));
     AGBANK=3;
     %AGBK94C;
     RUN;

DATA AGBK9409;
     SET MERGE3(WHERE=(YEAR=1994 & QTR=3));
     AGBANK=4;
     %AGBK94D;
     RUN;

DATA AGBK9412;
     SET MERGE3(WHERE=(YEAR=1994 & QTR=4));
     AGBANK=5;
     %AGBK94E;
     RUN;
************************************************************************
** FRACTIONALIZATION ENDS HERE!!                                      **
************************************************************************;

DATA MERGAGBK; **CHECK THIS CAREFULLY!!;
     MERGE AGBK9312 AGBK9403 AGBK9406 AGBK9409 AGBK9412;
          BY BANK;
RUN;

DATA AGBK94A;
************************************************************************
** THE FOLLOWING IF STATEMENT SETS IN THE US AGGREGATE RATIO OF       **
** (RCON1420+RCON1590)/RCON2122 AND MERGES IT TO ALL OBSERVATIONS IN  **
** THE DATA SET MERGAGBK (CREATED IN THE DATA STEP ABOVE).            **
************************************************************************;
     IF _N_=1 THEN SET USAGBK;
     DROP _TYPE_ _FREQ_;
     SET MERGAGBK;
     IF AGBANK=5;

     %MACRO QTRSUM;
          %LET VNAMES=
               R1420 R1590 R2122 R2170 R3210 R1407 R1403
               R2150 R3123 R0081 R2145;

          %DO AG=1 %TO 11;
               %LET VNAME= %SCAN(&VNAMES,&AG);
               &VNAME=SUM(&VNAME.A,&VNAME.B,&VNAME.C,&VNAME.D,
                          &VNAME.E);
               DROP &VNAME.A &VNAME.B &VNAME.C &VNAME.D &VNAME.E;
          %END;
    %MEND QTRSUM;

   *INVOKE QTRSUM MACRO CREATED ABOVE;
    %QTRSUM;
************************************************************************
** IDENTIFY AGRICULTURAL BANKS USING DEFINITION SUPPLIED BY ALTON     **
** GILBERT                                                            **
************************************************************************;
    IF R2122=0 THEN R2122=".";
    IF R2170=0 THEN R2170=".";
    AGLOAN=SUM(R1420,R1590);
    AGBKRATO= (AGLOAN/R2122)*100;
    IF AGBKRATO > USRATIO THEN AGBNK=1;               *AN AG BANK*;
         ELSE IF AGBKRATO <= USRATIO THEN AGBNK=0;    *NOT AN AG BANK*;
************************************************************************
**  CREATING RANGES OF AGRICULTURAL LOANS (R1420+R1590) TO TOTAL      **
**  LOANS (R2122) (TO BE USED LATER IN %AGBK94F)                      **
************************************************************************;
    IF AGBKRATO >= 0 & AGBKRATO <= 10 THEN RANGE=1;
    ELSE IF 10 < AGBKRATO <= 15 THEN RANGE=2;
    ELSE IF 15 < AGBKRATO <= 20 THEN RANGE=3;
    ELSE IF 20 < AGBKRATO <= 25 THEN RANGE=4;
    ELSE IF 25 < AGBKRATO <= 30 THEN RANGE=5;
    ELSE IF 30 < AGBKRATO <= 40 THEN RANGE=6;
    ELSE IF 40 < AGBKRATO <= 50 THEN RANGE=7;
    ELSE IF 50 < AGBKRATO <= 60 THEN RANGE=8;
    ELSE IF 60 < AGBKRATO <= 70 THEN RANGE=9;
    ELSE IF AGBKRATO > 70 THEN RANGE=10;
    ELSE IF AGBKRATO = .  THEN RANGE=11;
************************************************************************
**  CALCULATING VARIOUS RATIOS OUTLINED IN ALTON S 7/15/94 MEMO FOR 3 **
**  GROUPS OF BANKS:  (1) AGRICULTURAL BANKS, (2) COMMERCIAL BANKS    **
**  (NOT AG BANKS!) WITH TOTAL ASSETS <= 500 MILLION, AND (3)         **
**  COMMERICAL BANKS (NOT AG BANKS!) WITH TOTAL ASSETS <= INDEXED     **
**  AVERAGE TOTAL ASSETS OF THE BANKING INDUSTRY (IN THAT YEAR).      **
**  NOTE, HOWEVER, THAT GROUP (3) = GROUP (2) FOR 1994 BECAUSE 1994   **
**  IS THE BASE PERIOD FOR GROUP (3).  SEE ALTON   MEMO FOR FURTHER   **
**  DETAILS.                                                          **
************************************************************************ ;
* RETURN ON ASSETS= (NET INCOME/TOTAL ASSETS);
     ROA= (RIAD4340/R2170);

* RETURN ON EQUITY= (EQUITY/TOTAL ASSETS);
     ROE= (R3210/R2170);

* SUM OF NONPERFORMING LOANS PLUS OTHER REAL ESTATE OWNED MINUS;
* ALLOWANCE FOR LOAN AND LEASE LOSSES, ALL DIVIDED BY TOTAL ASSETS;
     SUMNPL= SUM(R1407,R1403);
     NPLRATIO= ((SUMNPL+R2150-R3123)/R2170);
     NEWNPL= ((SUMNPL+R2150)/R2170);

* NET INTEREST MARGIN= (NET INTEREST INCOME/EARNING ASSETS), WHERE;
* EARNINGS ASSETS= INTEREST BEARING BALANCES+SECURITIES+FEDERAL FUNDS;
* SOLD AND REPURCHASE AGREEMENTS+NET LOANS+TRADING ACCOUNT ASSETS;
     NIM= (RIAD4074)/(R2170-(R0081+R2145+R2150));*CHANGED PER 7/21/94 MEMO;
RUN;

PROC MEANS DATA=AGBK94A N SUM;
     VAR R2170;
     TITLE "TOTAL US ASSETS IN 1994";
RUN;

DATA AGBK94B;                *COMPUTING SUMMARY STATISTICS FOR AGBANKS;
     SET AGBK94A;
     IF AGBNK=1;

 /*
 THIS OUTPUTS THE BANK IDS FOR AG BANKS IN 1994 USED IN FIGURE 1.
  FILE OUT(AGBNK94);
  PUT BANK ;
 */
RUN;
     PROC MEANS DATA=AGBK94B;
          VAR AGBKRATO;
          TITLE "AG BANKS IN 1994";
     %AGBK94F;
     RUN;


DATA AGBK94C;
     SET AGBK94A;
************************************************************************
**  THE FOLLOWING IF STATEMENT IDENTIFIES COMMERICAL BANKS (NOT AG    **
**  BANKS!-I.E., AGBNK=0) WITH TOTAL ASSETS < 500 MILLION             **
************************************************************************;
     IF AGBNK=0 AND R2170 <= 500000 THEN ASSETSZ=1;
           ELSE IF AGBNK=0 AND 1000000>=R2170 > 500000 THEN ASSETSZ=2;
           ELSE IF AGBNK=1 AND 500000 >= R2170 >= 0 THEN ASSETSZ=3;
RUN;

 /*
 THIS OUTPUTS THE BANK IDS FOR SMALL NONAG BANKS IN 1994 FOR FIGURE 1.
   data _null_; set agbk94c(where=(assetsz=1));
   file in3(snabnk94);
   put bank;
   run;
 */

DATA AGBK94D;
     SET AGBK94C;
     FORMAT ASSETSZ SIZEFMT.;
RUN;
************************************************************************
**  SUMMARY STATISTICS FOR COMMERICAL BANKS (NOT AG BANKS!-I.E.,      **
**  AGBNK=0) WITH TOTAL ASSETS <= 500 MILLION                         **
************************************************************************;
DATA AGBK94E; SET AGBK94D;
NEGINC=0;
  IF RIAD4340<0 THEN NEGINC=1;
RUN;
  PROC SORT; BY ASSETSZ NEGINC;
  RUN;
     PROC FREQ DATA=AGBK94E;
       TABLES NEGINC/LIST SPARSE;
       BY ASSETSZ;
       TITLE "PERCENTAGE WITH NEGATIVE NET INCOME";
     RUN;

     PROC MEANS DATA=AGBK94D;
          VAR ROA ROE NPLRATIO NEWNPL;
          CLASS ASSETSZ;
          TITLE1 " RETURN ON ASSETS (ROA), RETURN ON";
          TITLE2 " EQUITY (ROE), NET INTEREST MARGIN";
          TITLE3 " (NIM), AND NONPERFORMING LOANS TO";
          TITLE4 " TOTAL ASSETS RATIOS";
     RUN;


DATA TOPHOLD;
     MERGE AGBK9412(KEEP=BANK DATE BANK9347 RCON2170)
           AGBK94A(KEEP=BANK DATE AGLOAN AGBNK);
           BY BANK;
     IF AGBNK=1;  *KEEP ONLY AGBANKS!!!!!USE WHERE STATEMENT FOR EFFICIENCY.;
     RUN;
     PROC SORT DATA=TOPHOLD;
          BY BANK9347;
     RUN;
     PROC MEANS N SUM NOPRINT DATA=TOPHOLD;
          VAR RCON2170;
          ID BANK;
          BY BANK9347;
          OUTPUT OUT=TOPASSET SUM=TPHASSET;
     RUN;
     PROC SORT DATA=TOPASSET;
          BY BANK9347;
     RUN;

DATA MERGE4;
     MERGE TOPASSET(KEEP=BANK BANK9347 TPHASSET) TOPHOLD;
          BY BANK9347;
     LABEL RCON2170="SUM OF ASSETS";
RUN;
     PROC SORT DATA=MERGE4;
          BY BANK;
     RUN;

     PROC MEANS N SUM DATA=MERGE4(WHERE=(BANK9347=0));
          VAR BANK RCON2170 AGLOAN;
          LABEL RCON2170="SUM OF ASSETS";
          TITLE 'NUMBER OF BANKS NOT IN A BHC';
     RUN;
     PROC MEANS N SUM DATA=MERGE4(WHERE=(TPHASSET<100000 & BANK9347Ş=0));
          VAR BANK RCON2170 AGLOAN;
          LABEL RCON2170="SUM OF ASSETS";
          TITLE  'NO. OF BANKS IN A BHC WITH ';
          TITLE2 'ASSETS < 100 MILLION       ';
     RUN;
     PROC MEANS N SUM DATA=MERGE4(WHERE=(100000<=TPHASSET<1000000 &
                                         BANK9347Ş=0));
          VAR BANK RCON2170 AGLOAN;
          LABEL RCON2170="SUM OF ASSETS";
          TITLE  'NO. OF BANKS IN A BHC WITH ';
          TITLE2 'ASSETS BETWEEN 100M AND 1B ';
     RUN;
     PROC MEANS N SUM DATA=MERGE4(WHERE=(1000000<=TPHASSET<10000000 &
                                         BANK9347Ş=0));
          VAR BANK RCON2170  AGLOAN;
          LABEL RCON2170="SUM OF ASSETS";
          TITLE  'NO. OF BANKS IN A BHC WITH ';
          TITLE2 'ASSETS BETWEEN 1B AND 10B ';
     RUN;
     PROC MEANS N SUM DATA=MERGE4(WHERE=(TPHASSET>=10000000 & BANK9347Ş=0));
          VAR BANK RCON2170 AGLOAN;
          LABEL RCON2170="SUM OF ASSETS";
          TITLE  'NO. OF BANKS IN A BHC WITH ';
          TITLE2 'ASSETS >= 10 BILLION       ';
     RUN;