/*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.*/