%let lib = /home/institution/user/output; /* define a save directory for the file */

options linesize=250;
libname out "&lib.";

%macro loop(start_month=, stop_month=);
    %local month;
    %do month=&start_month %to &stop_month %by 3;
        %put Month: &month;

        data call&month (keep=date2 rssdid name cert rcon8725 rcon8729 rconA589 rcon3450 rconA126 rssd9048 rcfd0010 rcfd2170 rcfd1400 rcfd2165 rcfd1410 rcfd1590 rcfd1600 rcfd1766 rcfd2122 rcfd3123 rcfd1975 rcfd1350 rcfdB989 rcfdB987 rcfd3545 rcfd2146 rcfd1000 rcfd0400 rcfd0600 rcfd0900 rcfd0380 rcfd0390 rcfd0950 rcfd1754 rcfd1771 rcfd1772 rcfd1773 rcfd2948 rcfd2950 rcfd3200 rcfd3210 rcon2200 rcfn2200 rcfn6636 rcon6631 rcon6636 rcfd2800 rcfdB993 rcfdB995 rconB993 rconB995 rcfd3548 rcfd3190 rcfd1935 rcfd2861 rcfd2869 rcfd2850 rcon2215 rcon2210 rcon2350 rcon2385 rcon2365 rcon6810 rcon0352 rcon6648 rcon2604 rconJ473 rconJ474 rcon2389 riad4000 riad4107 riad4079 riad4010 riad4020 riad4130 riad4073 riad4093 riad4230 riad4135 riad4170 riad4180 riad4217 riad4340 riad4024 riad4012 riad4065 riad4069 riad4218 riad4508 riad0093 riadA517 riadA518 riad4185 riad4200 riad4074 riad4080 riadA220 riad4150 riad4460 riad4174 riad4172 riad4509 riad4511 rcfdB558 rcfdB559 rcfdB560 rcfd3365 riad4115 riadB488 riadB489 riad4060 rcfd3401 rcfd3381 rcon3360 rcon3465 rcon3466 riad4435 riad4436 rcon3386 rcon3387 rconB561 riadB485 rconB562 riadB486 rcfn3360 riad4059 rcfd3484 rcfd3368 rcon3485 rconB563 rconA514 rconA529 rcfn3404 rcfd3353 rcfd3355 rcon3388 rcon3385 rcfd3382 rcfd3383 rcfd3384 rcon3345 riad4011 rcfdA549 rcfdA550 rcfdA551 rcfdA552 rcfdA553 rcfdA554 rcfdA555 rcfdA556 rcfdA557 rcfdA558 rcfdA559 rcfdA560 rcfdA561 rcfdA562 rcfdA247 rcfdA248 rconA564 rconA565 rconA566 rconA567 rconA568 rconA569 rcfdA570 rcfdA571 rcfdA572 rcfdA573 rcfdA574 rcfdA575 rconA579 rconA580 rconA581 rconA582 rconA241 rconA584 rconA585 rconA586 rconA587 riad4512 rcfd2930);
		set bank.call&month;
		rename rssd9001=rssdid rssd9010=name rssd9999=date2 rssd9050=cert;
	run;

        %if %substr(&month,5,2)=12 %then %let month=%eval(&month+88);
    %end;
%mend loop;
%loop(start_month=197603,stop_month=201512)

data temp;
     set call:;
     rename date2=date;
	 rename rssd9048=chartertype;
run;

data out.callreports (drop=rcfd: rcon: rcfn: riad:);
	set temp;
	
		assets=rcfd2170; /* 197603-     */
		reloans=rcfd1410; /* 197603- */
		cash=rcfd0010; /* 197603- */
		persloans=rcfd1975; /* 197603- */
		agloans=rcfd1590; /* 197603- */
		subordinateddebt=rcfd3200; /* 197603- */
		equity=rcfd3210; /* 197603- */
		demanddep=rcon2210; /* 197603- */
		transdep=rcon2215; /* 198403- */
		brokereddep=rcon2365; /* 198309- */
		timedepge100k=rcon2604; /* 197603- */
		timedeple100k=rcon6648; /* 198403- */

	    /* There are two C&I loan series: rcfd1600 up to 20001231 and rcfd1766 from 19840331. The difference between them is rcfd1755 "Acceptances of other banks",
			which is excluded from rcfd1600. Since rcfd1755 is only available from 19840331 to 20001231, there is no way to form a consistent time series for the
			entire sample. Yet since there is some overlap between rcfd1600 and rcfd1766, it is possible to construct a consistent growth rate series. */
	    ciloans=rcfd1766; /* 198403- */
		if ciloans=. then ciloans=rcfd1600; /* 197603-200012 */

	    /* Total loans and leases is rcfd1400. Prior to 198312, it does not include lease financing receivables, rcfd2165 */
	    if date<=19831231 then loans=sum(rcfd1400,rcfd2165); 
	    else loans=rcfd1400; /* 197603- */

	    /* Loans and leases net of unearned income and allowance for loan and lease losses. This is the variable that makes assets add up on the balance sheet. */
	    if rcfd3123 ne . then loansnet=rcfd2122-rcfd3123;
	    else loansnet=rcfd2122; /* 197603- */
	
		/* Fed funds sold and securities purchased under agreements to resell is rcfd1350 through 20011231, after which it is broken up into Fed funds sold, rcfdB987
		   and securities purchased under agreements to resell, rcfd989. Note that the balance sheet includes only Fed funds sold in domestic offices, rconB987.
		   However, using this series would not form a consistent time series with rcfd1350. */
		fedfundsrepoasset=rcfd1350; /* 197603-200112 */
	    if fedfundsrepoasset=. then fedfundsrepoasset=rcfdB987+rcfdB989; /* 200203- */
	
		/* Trading assets, rcfd3545, starts in 19940331. From 19840331 to 19931231 it is reported under rcfd2146, which goes back to 19840331. Trading assets up to
		   19831231 are reported at book value as rcfd1000. This creates a possible discontinuity at 19831231 hence we do not use it. */
		tradingassets=rcfd3545; /* 199403- */
		if tradingassets=. then tradingassets=rcfd2146; /* 198403-199312 */
	
		/* Up to 19831231, securities are reported separately as Treasury securities, rcfd0400, agency and corporate securities, rcfd0600, obligations of states and political subdivisions, rcfd0900, and other seucrities, rcfd0950. After that, until 19931231 they are available as rcfd0390. After 19940331, they are broken down into securities held to maturity, rcfd1754, and securities available for sale, rcfd1773. */
		if date<=19831231 then securities=sum(rcfd0400,rcfd0600,rcfd0900,rcfd0950); /* 197603-198312 */
		else if date<=19931231 then securities=rcfd0390; /* 198403-199312 */
		else securities=sum(rcfd1754,rcfd1773); /* 199403- */
		/* Securities available for sale are reported at fair value by default but are also available at historical cost under rcfd1772. */
		securities_ammcost = sum(rcfd1754,rcfd1772); /* 199403- */
	
		/* Securities held to maturity are reported as rcfd1754 since 19940331. Securities available for sale are reported as rcfd1773 since 19940331. Securities held to maturity are reported at amortized cost. Securities available for sale are reported at fair value. There appears to be some reclassification between securities held to maturity and securities available for sale between 19950930 and 19951231. */
		securitiesheldtomaturity=rcfd1754;
		securitiesavailableforsale=rcfd1773;
	
		/* Total liabilities is rcfd2948. It can also be computed as the sum of rcfd2950, liabilities excluding subordinated debt, and rcfd3200, subordinated debt.  The latter measure is also occasionally present when rcfd2948 is missing in later years. */
		liabilities=rcfd2948; /* 197603- */
		if liabilities=. then liabilities=sum(rcfd2950,rcfd3200); /* 197603- */
	
		/* Total deposits, rcon2200, is defined excluding foreign deposits, rcfn2200. */
		deposits=rcon2200; /* 197603- */
		foreigndep=rcfn2200; /* 197603- */
	
		/* Noninterest-bearing, rcon6631, and interest-bearing, rcfd6636, start in 19840331. Foreign interest-bearing deposits, rcfn6636 starts in 197812. */
		nonintbeardep=rcon6631; /* 198403- */
		intbeardep=rcon6636; /* 198403- */
		intbearfordep=rcfn6636; /* 197812- */
	
		/* Fed funds purchased and securities sold under agreements to repurchase is rcfd1280 through 20011231, after which it is broken up into Fed funds purchased,
		   rcfdB993 and securities sold under agreements to repurchase, rcfdB995. Note that the balance sheet includes only Fed funds ourchased in domestic offices,
		   rconB993. However, using this series would not form a consistent time series with rcfd1280. */
		fedfundsrepoliab=rcfd2800; /* 197603-200112 */
	    if fedfundsrepoliab=. then fedfundsrepoliab=sum(rcfdB993,rcfdB995); /* 200203- */
	
		/* Trading liabilities, rcfd3548, starts in 19940331. There does not appear to be a corresponding series prior to that date. */
		tradingliabilities=rcfd3548; /* 199403- */
		
		/* Other borrowed money is reported as rcfd2850 until 19931231 then rcfd3190 after that. It can also be reconstructed as the sum of rcfd1935, total other
		   borrowed money owed to nonrelated banks in foreign countries, rcfd2861, other borrowed money owed to nonrelated banks in the U.S. (including their IBFs),
		   and rcfd2869, total other borrowed money owed to others. */
		otherborrowedmoney=rcfd3190; /* 199403- */
		if otherborrowedmoney=. then otherborrowedmoney=rcfd2850; /* 197612-199312 */
		if otherborrowedmoney=. then otherborrowedmoney=sum(rcfd1935,rcfd2861,rcfd2869); /* 198006- */
		
		/* Time and savings deposits are reported as rcon2350 until 19951231 and then again from 20040930 on. They can also be calculated as the sum of transaction
		   and non-transaction accounts, minus demand deposits. Therefore some of these are counted as transaction deposits but not demand deposits. */
		timesavdep=rcon2350; /* 197603-199512, 200409- */
		if timesavdep=. then timesavdep=rcon2215+rcon2385-rcon2210; /* 198406- */
		
		/* Non-transaction accounts are reported as rcon2385 after 19840630. Prior to that they are just savings and time deposits, rcon2350 */
		nontransdep=rcon2385; /* 198406- */
		if nontransdep=. then nontransdep=rcon2350; /* 197603-199512, 200409- */
		
		/* Time deposits is the sum of time deposits less than $100k and more than $100k. */
		timedep=rcon6648+rcon2604; /* 198403- */
		
		/* Uninsured time deposits is time deposits greater than $100k until 20091231 and greater than $250k after that. */
		if date<=20091231 then timedepuninsured=rcon2604; /* 197603- */
		else timedepuninsured=rconJ474; /* 201003- */
		
		/* Nontransaction savings deposits are reported as other nontransaction accounts, rcon0352, plus MMDA accounts, rcon6810. Their sum is also reported as rcon2389. This is the series for which interest expense is reported as riad0093.*/
		savdep=rcon2389; /* 198403- */
		if savdep=. then savdep=rcon0352+rcon6810; /* 198703- */
		/* Total savings deposits, which includes transaction savings deposits can be calculated as time and savings deposits minus time deposits. */
		totsavdep=timesavdep-timedep; /* 198403- */
		
		/* The following income and expense measures are well populated since at least 19840331. */
		operinc=riad4000; /* 198303- */
		intexp=riad4073; /* 198403- */
		intincnet=riad4074; /* 198403- */
		nonintinc=riad4079; /* 198403- */
		domdepservicecharges=riad4080; /* 198303- */
		nonintexp=riad4093; /* 198303- */
		intandnonintexp=riad4130; /* 198303- */
		salaries=riad4135; /* 198303- */
		numemployees=riad4150; /* 198403- */
		intexpdep=riad4170; /* 198303- */
		intexpsubordinated=riad4200; /* 198303- */
		exponpremises=riad4217; /* 198303- */
		intanddivincsecurities=riad4218; /* 198403- */
		loanleaselossprovision=riad4230; /* 198303- */
		netinc=riad4340; /* 198303- */
		
		/* Interest on large time deposits is available as riad4174 from 19870331 to 19961231. It comes back as riadA517 starting in 19970930. */
		intexptimedepge100k=riad4174; /* 198303-199612 */
		if intexptimedepge100k=. then intexptimedepge100k=riadA517; /* 199703- */
		/* Interest on small time deposits is available as riadA518 starting in 19970930 and riad4512 from 19870331 to 19961231. */
		intexptimedeple100k=riadA518; /* 199709- */
		if intexptimedeple100k=. then intexptimedeple100k=riad4512;
		/* Trading revenue is available as riadA220 since 19970930 for FFIEC 031 filers and 20010331 for FFIEC 041 filers. */
		tradingrevenue=riadA220; /* 199709- */
		/* Cash dividend on common stock is available as riad4460. Most banks seem to report this variable semi-annually from 19760331 to 19821231,
		   annually from 19830331 to 20001231, and quarterly after 20010331. Therefore for any analysis this variable should be summed over each
		   year to remove seasonality. */
		dividendoncommonstock=riad4460; /* 198303- */
				
		/* Quarterly average balance sheet items and corresponding income items used to compute rates. */
		qavgbaldue=rcfd3381; /* 198403- */
		intincbaldue=riad4115; /* 198303- */
		qavgtreasuriesagencydebt=rcfdB558; /* 200103- */
		intinctreasuriesagencydebt=riadB488; /* 200103- */
		qavgmbs=rcfdB559; /* 200103- */
		intincmbs=riadB489; /* 200103- */
		qavgothersecurities=rcfdB560; /* 200103- */
		intincothersecurities=riad4060; /* 200103- */
		qavgtradingassets=rcfd3401; /* 198403- */
		qavgfedfundsrepoasset=rcfd3365; /* 197603- */
		intincfedfundsrepoasset=riad4020; /* 198303- */
		qavgloans=rcon3360; /* 197603- */
		intincloans=riad4010; /* 198303- */
		qavgreloans1to4fam=rcon3465; /* 200803- */
		intincreloans1to4fam=riad4435; /* 200803- */
		qavgreloansother=rcon3466; /* 200803- */
		intincreloansother=riad4436; /* 200803- */
		qavgagloans=rcon3386; /* 200103- */
		intincagloans=riad4024; /* 200103- */
		qavgciloans=rcon3387; /* 200103- */
		intincciloans=riad4012; /* 200103- */
		qavgpersccards=rconB561; /* 200103- */
		intincpersccards=riadB485; /* 200103- */
		qavgpersother=rconB562; /* 200103- */
		intincpersother=riadB486; /* 200103- */
		qavgforloans=rcfn3360; /* 197812- */
		intincforloans=riad4059; /* 198403- */
		qavgleases=rcfd3484; /* 198703- */
		intincleases=riad4065; /* 198303- */
		qavgassets=rcfd3368; /* 197812- */
		intincassets=riad4107; /* 198403- */
		qavgtransdep=rcon3485; /* 198703- */
		intexptransdep=riad4508; /* 198703- */
		qavgsavdep=rconB563; /* 200103- */
		intexpsavdep=riad0093; /* 198703- */
		if intexpsavdep=. then intexpsavdep=riad4509+riad4511;
		qavgtimedepge100k=rconA514; /* 197812- */
		if qavgtimedepge100k=. then qavgtimedepge100k=rcon3345;
		intexptimedepge100k=riad4174; /* 198303- */
		if intexptimedepge100k=. then intexptimedepge100k=riadA517;
		qavgtimedeple100k=rconA529; /* 199703-*/
		qavgfordep=rcfn3404; /* 198403- */
		intexpfordep=riad4172; /* 198303- */
		qavgfedfundsrepoliab=rcfd3353; /* 197603- */
		intexpfedfundsrepoliab=riad4180; /* 198303- */
		qavgtradingandotherborrowed=rcfd3355; /* 197603- */
		intexptradingandotherborrowed=riad4185; /* 198303- */
		/* There is an earlier series for quarterly average of personal loans, rcon3388, but coverage is sparse. */
		qavgpersloans=rconB561+rconB562; /* 200103- */
		intincpersloans=riadB485+riadB486; /* 200103- */
		qavgreloans=rcon3385; /* 200103- */
		if qavgreloans=. then qavgreloans=rcon3465+rcon3466; /* 200803- */
		intincreloans=riad4011; /* 200103- */
		if intincreloans=. then intincreloans=riad4435+riad4436; /* 200803- */
		qavgsecurities=rcfdB558+rcfdB559+rcfdB560; /* 200103- */
		
		/* Maturity and repricing data available starting in 19970630 */
		securities_less_3m=rcfdA549+rcfdA555;
		securities_3m_1y=rcfdA550+rcfdA556;
		securities_1y_3y=rcfdA551+rcfdA557;
		securities_3y_5y=rcfdA552+rcfdA558;
		securities_5y_15y=rcfdA553+rcfdA559;
		securities_over_15y=rcfdA554+rcfdA560;
		securitiestreasury_less_3m=rcfdA549;
		securitiestreasury_3m_1y=rcfdA550;
		securitiestreasury_1y_3y=rcfdA551;
		securitiestreasury_3y_5y=rcfdA552;
		securitiestreasury_5y_15y=rcfdA553;
		securitiestreasury_over_15y=rcfdA554;
		securitiesrmbs_less_3m=rcfdA555;
		securitiesrmbs_3m_1y=rcfdA556;
		securitiesrmbs_1y_3y=rcfdA557;
		securitiesrmbs_3y_5y=rcfdA558;
		securitiesrmbs_5y_15y=rcfdA559;
		securitiesrmbs_over_15y=rcfdA560;
		securitiesothermbs_less_3y=rcfdA561;
		securitiesothermbs_over_3y=rcfdA562;
		loansleases_mat_less_1y=rcfdA247;
		securities_mat_less_1y=rcfdA248;
		resloans_less_3m=rconA564;
		resloans_3m_1y=rconA565;
		resloans_1y_3y=rconA566;
		resloans_3y_5y=rconA567;
		resloans_5y_15y=rconA568;
		resloans_over_15y=rconA569;
		loansleases_less_3m=rcfdA570+rconA564;
		loansleases_3m_1y=rcfdA571+rconA565;
		loansleases_1y_3y=rcfdA572+rconA566;
		loansleases_3y_5y=rcfdA573+rconA567;
		loansleases_5y_15y=rcfdA574+rconA568;
		loansleases_over_15y=rcfdA575+rconA569;
		timedeple100k_less_3m=rconA579;
		timedeple100k_3m_1y=rconA580;
		timedeple100k_1y_3y=rconA581;
		timedeple100k_over_3y=rconA582;
		timedeple100k_less_1y=rconA241;
		timedepge100k_less_3m=rconA584;
		timedepge100k_3m_1y=rconA585;
		timedepge100k_1y_3y=rconA586;
		timedepge100k_over_3y=rconA587;
		
		/*Derivatives data*/
		interestratederivatives = rcon8725; /*199503- *Total gross notional amount of IR derivativs for purposes other than trading*/
		interestratederivatives_par = rcon8729; /*199503- 200012 *Total gross notional amount of IR derivativs for purposes other than trading - not marked to market*/
		grosshedging= sum(rcon8725,rcon8729);
		
		fixedrateswaps= rconA589; /*199706- * Interest rate swaps where bank has agreed to pay a fixed rate*/
		totalswaps = rcon3450; /*198503- * Notional value of all outstanding interest rate swaps*/
		floatingrateswaps= rcon3450 - rconA589; /*199706 - * Total swaps minus pay-fixed swaps*/
		nethedging=fixedrateswaps-floatingrateswaps; 
		
		grosstrading = rconA126; /*199503 - *Total gross notional amount of interest rate derivate contracts held for trading*/
		
run;

proc export data=out.callreports outfile="&lib./callreports.dta" replace;

data temp;
     set out.callreports;
     year=int(date/10000);
     if year>2013 then delete;
run;

proc means nolabels data=temp n nmiss mean std min;
     var _numeric_;
run;

proc means nolabels data=temp n nmiss mean std min;
     var _numeric_;
	 by date;
run;