/*The following program uses a few assumptions that you will need to correct for if your environment differs
	from the default:
	1) That the bulk data is downloaded to the user's desktop, and extracted to a folder with the same name as 
		the zip file.
	2) That the Windows root directory is "C:\" and that the SAS root directory is "C:\Documents and Settings\User".
	3) Optional: That a copy of the corresponding structure file has been downloaded from the chicagofed.org site
		to merge with the CDR dataset and opened into the work directory as work.CALLYYMM.
	4) Optional: That a folder named "call" has been created on the desktop to store the final merged dataset.*/

/*Select the date of the Call Report (MMDDYYYY) and the current Windows user name. If you are working in the 
	default environment, these are the only changes that need to be made.*/
%let date=date;
%let user=user;

/*Using the date above, create a reference that will be used to name the SAS dataset.*/
%let name = CALL%substr(&date,7,2)%substr(&date,1,2);

/*Create a file path reference to the extracted zip file folder.*/
%let filepath = %bquote("c:\documents and settings\&user\desktop\ffiec_cdr_call_bulk_all_schedules_&date");

/*Create a pipe to the folder, which is used in the data step below to read in the text file names.*/
filename CDR_Bulk pipe "dir &filepath /a:-d-h-s /b /s";

data tempschedules;
infile CDR_Bulk;
input ;
schedule=_infile_;
if scan(schedule,-1,'\') = "Readme.txt" then delete;
if scan(schedule,-1,'\') = "FFIEC CDR Call Bulk POR &date..txt" then delete;
run;

proc sql noprint;
select schedule into: schedulelist separated by ',' from tempschedules;
quit;

/*Optional: Assign an arbitrary libname and location for the merged dataset - to include all schedules, and
	structure data. In this case, a location named Call must be created as a new folder on the desktop.*/
libname call ".\desktop\call";

/*A precautionary step in case data exists for the current period in the Call library*/
proc datasets lib=call;
delete &name;
quit;

/*Import data from each text file into the Call library, and then merge the data using the 'CALLYYMM'
	naming convention used for the XPT files currently hosted on chicagofed.org.*/

%macro cdrextract;

%let i=1; /*Can be changed to six if you do not want to add in blank schedules, transmission data and geographic
			information from CDR. A value of one is used as the default in case the order of the files changes.*/

%do %while (%qscan(%quote(&schedulelist),&i,',') ne );

%let textfile=%qscan(%quote(&schedulelist),&i,',');
%put &textfile;

/*Create a value for the macro to use when guessing rows. This slows down the import procedure, but it is needed
	or some financial and text items will be truncated.*/

data _null_;
infile "&textfile";
input ;
call symput("nvar",_n_);
run;
%put &nvar;

PROC IMPORT OUT= work.tempdata 
            DATAFILE= "&textfile"
            DBMS=dlm REPLACE;
     DELIMITER='09'x;
	 GETNAMES=YES;
	 GUESSINGROWS=&nvar;
	 DATAROW=3; /*This setting omits data from the "POR" file for RSSD_ID 37 as the file is not consistent.*/ 
RUN;
quit;

/*This data step will remove any text remarks that the delimited file pushed to a new line. This can truncate
	certain text remarks.*/

data tempdata;
set tempdata;
if idrssd=. then delete;
RSSD9001=input(idrssd,best12.);
drop idrssd var:;
run;

/*Once data from each schedule are imported, a merge statement can be used to create the master file 
	for the quarter.*/

%if (&i=1) %then %do;

data call.&name;
set work.tempdata;
run;

%end;
%else %do;

data call.&name;
merge call.&name work.tempdata;
by RSSD9001;
run;

%end;
%let i = %eval(&i+1);
%end;
%mend cdrextract;

%cdrextract;

 /*Three variables related to fiduciary activities are changed from character to numeric to match the current
	and historical datasets. The items are reported as "Yes" or "No" and translated to 0 or 1, yet appear in the
	CDR dataset as "true" or "false" statements. On the FFIEC 002 these are annual items.*/

data call.&name;
set call.&name;
if RCFDA345 = "true" then tempRCFDA345 = 1;
else if RCFDA345 = "false" then tempRCFDA345 = 0;
else tempRCFDA345 = .;
if RCFDA346 = "true" then tempRCFDA346 = 1;
else if RCFDA346 = "false" then tempRCFDA346 = 0;
else tempRCFDA346 = .;
if RCFDB867 = "true" then tempRCFDB867 = 1;
else if RCFDB867 = "false" then tempRCFDB867 = 0;
else tempRCFDB867 = .;
drop RCFDA345 RCFDA346 RCFDB867;
rename tempRCFDA345=RCFDA345 tempRCFDA346=RCFDA346 tempRCFDB867=RCFDB867;
run;


/*Final step to merge the CDR call report data with the chicaofed.org structure dataset--dataset must 
	already be present in user's Work library as mentioned in assumptions above.*/

/*The order of the merge statement determines what will be present in the final dataset. By placing the CDR
	dataset second, the program ensures that CDR data for items with the same name in each dataset will 
	overwrite data already present for the same ID_RSSD in the structure dataset. The retain statement is 
	also used to ensure that the first two items of the resultant dataset are the as-of date and ID.*/

/*Other possible solutions to this problem would be a proc sql insert or alter statement or an update data step
	instead of the merge statement.*/

data call.&name;
retain RSSD9001 RSSD9999;
merge work.&name call.&name (rename=(RCON9999=RSSD9999));
by RSSD9001;
run;

/*Optional: If data is needed in XPT format rather than in a SAS library, this libname statement and data step 
	could be used. Variable names would need to be changed to match the version 6 length limit.*/
*libname callxpt xport ".\desktop\call\&name..xpt";
*data callxpt.&name;
*set call.&name;
*run;

/*This is just one of many possible ways of combining the data into a dataset similar to those currently available
	on chicagofed.org.*/