DM "CLEAR LOG; CLEAR OUTPUT"; DATA _NULL_; CALL SYMPUT('BDAY', TRIM(LEFT(PUT(DATE(), DOWNAME.)))); CALL SYMPUT('BDATE', PUT(DATE(), DATE.)); CALL SYMPUT('BWDATE', TRIM(LEFT(PUT(DATE(), WORDDATE.)))); CALL SYMPUT('BTIME', TRIM(LEFT(PUT(TIME(), TIME.)))); RUN; %PUT NOTE: THIS PROGRAM WAS RUN ON &BDAY, &BWDATE, AT &BTIME.; OPTIONS OBS = MAX NODATE PAGENO = 1 LINESIZE = 124 PAGESIZE = 39 SYMBOLGEN MPRINT; **************************************************************************************; ** **; ** EXCHANGE RATE CREATION PROGRAM **; ** **; ** LAST UPDATED 04/20/12 **; ** **; ** THIS PROGRAM CREATES THE REVIEW AND INVESTIGATION EXCHANGE RATES FROM RAW RATES **; ** EXTRACTED FROM EITHER THE FEDERAL RESERVE OR FACTIVA. THE RAW RATES ARE STORED **; ** IN TWO EXCEL SPREADSHEETS. THE FEDERAL RESERVE SPREADSHEET CONTAINS EXCHANGE **; ** RATES FOR THE LAST SEVERAL YEARS. THE FACTIVA SPEADSHEET CONTAINS EXCHANGE **; ** RATES FOR THE MOST RECENT QUARTER. **; ** **; ** THE FOLLOWING IS THE OUTPUT OF THIS PROGRAM: **; ** **; ** DATA FILES OF RAW RATES FOR ALL COUNTRIES FOR ITA CENTRAL: **; ** EX: ...\ALL RAWRATES\BANGLADESH.DAT **; ** SAS DATASETS OF RAW RATES, BY SOURCE TYPE: **; ** EX: ...\ALL RAWRATES\FACTIVA\BANGLADESH.sas7bdat **; ** EX: ...\ALL RAWRATES\FED_RESERVE\BRAZIL.sas7bdat **; ** EX: ...\ALL RAWRATES\IN_DOLLARS\AUSTRALIA.sas7bdat **; ** TEXT FILES OF EXCHANGE RATES FOR ALL COUNTRIES FOR THE INTERNET: **; ** EX: ...\INTERNET_RATES\BANGLADESH.TXT **; ** SAS DATASETS OF FINALIZED EXCHANGE RATES FOR ALL COUNTRIES, MODULATED **; ** FOR AD CALCULATIONS: **; ** EX: ...\FINAL_ALL_RATES\BANGLADESH.sas7bdat **; **************************************************************************************; **************************************************************************************; ** DEFINE MACRO VARIABLES REQUIRED FOR THIS PROGRAM. **; ** **; ** NOTE: THIS IS THE ONLY SECTION THAT REQUIRES EDITING. **; **************************************************************************************; %LET STARTDAY = < >; ** FIRST DAY OF NEW PERIOD, IN DATE9. FORMAT **; %LET ENDDAY = < >; ** LAST DAY OF NEW PERIOD, IN DATE9. FORMAT **; %LET CURRENT_EXRATE_PATH = ; ** DIRECTORY WHERE RAW SPREADSHEET DATA FOR NEWEST PERIOD RESIDE **; %LET PAST_EXRATE_PATH = ; ** DIRECTORY WHERE PREVIOUSLY GENERATED .SAS7BDAT EXCHANGE RATE DATABASES RESIDE **; /******************************************/ /* No edits required beyound this point. */ /******************************************/ **************************************************************************************; ** GENERATE REQUIRED SUBDIRECTORIES, LIBNAMES, TITLE AND MACRO VARIABLES **; **************************************************************************************; **********************************************************************************; ** SET THE BENCHMARCH PERIOD AND CREATE NEEDED DIRECTORIES/LIBRARIES. **; ** **; ** CALCULATE THE BEGINNING OF THE BENCHMARK PERIOD, I.E., THREE MONTHS PRIOR **; ** TO THE START OF NEW DATA, AND CREATE A MACRO VARIABLE FOR THE FIRST DAY OF **; ** THE BENCHMARK PERIOD CALLED, 'START_BENCHMARK.' **; ** **; ** CREATE SUBDIRECTORIES, WHEN NEEDED, AND THEN LIBRARIES FOR: **; ** - PAST FINALIZED EXCHANGE RATES AS SAS DATASETS **; ** - CURRENT EXCHANGE RATE DATA IN SPREADSHEETS FROM FACTIVA & FED RESERVE **; ** - ALL RAW RATES IN .dat FORMAT FOR THE SAS PAGE IN ITA CENTRAL **; ** - RAW FACTIVA RATES **; ** - RAW FEDERAL RESERVE RATES, FOREIGN CURRENCY/USD **; ** - RAW FEDERAL RESERVE RATES, USD/FOREIGN CURRENCY **; ** - FINAL RATES IN .txt FORMAT FOR THE ITA WEBPAGE AVAILABLE TO PUBLIC **; ** - FINAL RATES AS SAS DATASETS FOR THE SAS PAGE IN ITA CENTRAL **; **********************************************************************************; DATA _NULL_; FORMAT START_DAY DATE9. START_BENCHMARK $9. ; STARTDAY = "&STARTDAY"; START_DAY = INPUT(STARTDAY,ANYDTDTE.); START_BENCHMARK = PUT(INTNX('MONTH',START_DAY,-3),DATE9.); %GLOBAL START_BENCHMARK; CALL SYMPUT ('START_BENCHMARK',START_BENCHMARK); CURRENT_PATH = "&CURRENT_EXRATE_PATH"; RAW_DIR = "ALL RAWRATES"; RAW_DIRECTORY=DCREATE(RAW_DIR,CURRENT_PATH); FACTIVA_DIR = "FACTIVA"; FACTIVA_DIRECTORY=DCREATE(FACTIVA_DIR,RAW_DIRECTORY); FED_RES_DIR = "FED_RESERVE"; FED_RES_DIRECTORY=DCREATE(FED_RES_DIR,RAW_DIRECTORY); DOLLAR_DIR = "IN_DOLLARS"; DOLLAR_DIRECTORY=DCREATE(DOLLAR_DIR,RAW_DIRECTORY); INTERNET_DIR = "INTERNET_RATES"; INTERNET_DIRECTORY=DCREATE(INTERNET_DIR,CURRENT_PATH); FINAL_DIR = "FINAL_ALL_RATES"; FINAL_DIRECTORY=DCREATE(FINAL_DIR,CURRENT_PATH); RUN; LIBNAME PASTLIB "&PAST_EXRATE_PATH"; LIBNAME FACLIB "&CURRENT_EXRATE_PATH\ALL RAWRATES\FACTIVA"; LIBNAME FEDLIB "&CURRENT_EXRATE_PATH\ALL RAWRATES\FED_RESERVE"; LIBNAME USLIB "&CURRENT_EXRATE_PATH\ALL RAWRATES\IN_DOLLARS"; LIBNAME FINLIB "&CURRENT_EXRATE_PATH\FINAL_ALL_RATES"; **********************************************************************************; ** CREATE MACRO VARIABLE PREFIXES REPRESENTING BOTH THE SOURCE OF THE DATA **; ** AND THE COUNTRYNAME **; ** **; ** FAC - COUNTRIES DOWNLOADED FROM FACTIVA WITH EXCHANGE RATES IN **; ** FOREIGN CURRENCY/USD **; ** FED - COUNTRIES DOWNLOADED FROM THE FEDERAL RESERVE WITH EXCHANGE **; ** RATES IN FOREIGN CURRENCY/USD **; ** US - COUNTRIES DOWNLOADED FROM THE FEDERAL RESERVE WITH EXCHANGE **; ** RATES IN USD/FOREIGN CURRENCY **; **********************************************************************************; %LET FAC1 = ARGENTINA; %LET FED1 = BRAZIL; %LET FAC2 = BANGLADESH; %LET FED2 = CANADA; %LET FAC3 = CHILE; %LET FED3 = CHINA; %LET FAC4 = COLOMBIA; %LET FED4 = DENMARK; %LET FAC5 = CZECHREPUBLIC; %LET FED5 = HONGKONG; %LET FAC6 = EGYPT; %LET FED6 = INDIA; %LET FAC7 = HUNGARY; %LET FED7 = JAPAN; %LET FAC8 = INDONESIA; %LET FED8 = MALAYSIA; %LET FAC9 = IRAN; %LET FED9 = MEXICO; %LET FAC10 = ISRAEL; %LET FED10 = NORWAY; %LET FAC11 = KENYA; %LET FED11 = SAFRICA; %LET FAC12 = LATVIA; %LET FED12 = SINGAPORE; %LET FAC13 = MOROCCO; %LET FED13 = SKOREA; %LET FAC14 = OMAN; %LET FED14 = SRILANKA; %LET FAC15 = PAKISTAN; %LET FED15 = SWEDEN; %LET FAC16 = PHILIPPINES; %LET FED16 = SWITZERLAND; %LET FAC17 = POLAND; %LET FED17 = TAIWAN; %LET FAC18 = RUSSIA; %LET FED18 = THAILAND; %LET FAC19 = TRINIDAD; %LET FED19 = VENEZUELA; %LET FAC20 = TURKEY; %LET FAC21 = UKRAINE; %LET US1 = AUSTRALIA; %LET FAC22 = VIETNAM; %LET US2 = EURO; %LET FAC23 = ROMANIA; %LET US3 = NEWZEALAND; %LET FAC24 = UAE; %LET US4 = UK; **********************************************************************************; ** CREATE THE TITLE FOR PRINT STATEMENTS **; **********************************************************************************; TITLE "EXCHANGE RATE PROCESSING FOR THE PERIOD &STARTDAY THROUGH &ENDDAY"; ******************************************************************************************; ** CALCULATE THE EXCHANGE RATES FOR EACH COUNTRY **; ******************************************************************************************; **************************************************************************************; ** CREATE THE RATES MACRO WHICH WILL BE EXECUTED FOR EACH SOURCE TYPE, AND THEN **; ** WITHIN THE SOURCE TYPE BY COUNTRY, USING THE FOLLOWING PARAMETERS: **; ** **; ** SOURCE: MACRO VARIABLE ABOVE (E.G., "FAC1" FOR ARGENTINA) REPRESENTING BOTH **; ** THE COUNTRY AND THE SOURCE OF THE DATA (FACTIVA FOR ARGENTINA) **; ** NUMBER_OF_COUNTRIES: THE NUMBER OF COUNTRIES TO PROCESS. **; ** SOURCE_SHEET: THE SPREADSHEET CONTAINING RAW RATES. **; ** DIVIDE_FOREIGN_CURRENCY: EQUATION TO BE MULTIPLIED BY THE EXCHANGE RATE **; ** - IT IS SET TO "1/" FOR RATES EXPRESSED IN FOREIGN CURRENCY/USD, **; ** MAKING THE {NEW}RATE=1/{ORIGNAL}RATE AND DENOMINATED AS **; ** USD/FOREIGN CURRENCY. **; ** - FOR RATES ALREADY EXPRESSED IN USD/FOREIGN CURRENCY, THE EQUATION IS **; ** SET TO BLANK SPACE, LEAVING THE {ORIGINAL}RATE UNCHANGED. **; **************************************************************************************; %MACRO RATES(SOURCE = , NUMBER_OF_COUNTRIES = , SOURCE_SHEET =, DIVIDE_FOREIGN_CURRENCY = ); %DO I = 1 %TO &NUMBER_OF_COUNTRIES; %LET CTRYCODE = &&&SOURCE.&I; PROC IMPORT OUT = &SOURCE.LIB.&CTRYCODE DATAFILE = "&CURRENT_EXRATE_PATH\&SOURCE_SHEET..xls" DBMS = EXCEL2000 REPLACE; SHEET = "&CTRYCODE$"; GETNAMES = YES; RUN; DATA &SOURCE.LIB.&CTRYCODE; SET &SOURCE.LIB.&CTRYCODE; IF RATE = . THEN DELETE; RUN; DATA _NULL_; FILE "&CURRENT_EXRATE_PATH.\ALL RAWRATES\&CTRYCODE..DAT" NOPRINT; DO COLUMN = 3; PUT @COLUMN "DATE " +(-1) @; PUT "RATE " +(-1) @; DO LINE = 2 TO 10000; SET &SOURCE.LIB.&CTRYCODE END = ENDFILE; PUT @COLUMN DATE +(-1) +1 RATE +(-1) +1; END; END; RUN; %LET RATECODE = %SUBSTR(&CTRYCODE, 1); %PUT CTRYCODE = &CTRYCODE RATECODE = &RATECODE; DATA &CTRYCODE; SET &SOURCE.LIB.&CTRYCODE; WHERE RATE GT 0 AND DATE > "&START_BENCHMARK"D; EXRATE = &DIVIDE_FOREIGN_CURRENCY RATE; BENCHMRK=(LAG1(EXRATE)+LAG2(EXRATE)+LAG3(EXRATE)+LAG4(EXRATE)+LAG5(EXRATE)+ LAG6(EXRATE)+LAG7(EXRATE)+LAG8(EXRATE)+LAG9(EXRATE)+LAG10(EXRATE)+ LAG11(EXRATE)+LAG12(EXRATE)+LAG13(EXRATE)+LAG14(EXRATE)+LAG15(EXRATE)+ LAG16(EXRATE)+LAG17(EXRATE)+LAG18(EXRATE)+LAG19(EXRATE)+LAG20(EXRATE)+ LAG21(EXRATE)+LAG22(EXRATE)+LAG23(EXRATE)+LAG24(EXRATE)+LAG25(EXRATE)+ LAG26(EXRATE)+LAG27(EXRATE)+LAG28(EXRATE)+LAG29(EXRATE)+LAG30(EXRATE)+ LAG31(EXRATE)+LAG32(EXRATE)+LAG33(EXRATE)+LAG34(EXRATE)+LAG35(EXRATE)+ LAG36(EXRATE)+LAG37(EXRATE)+LAG38(EXRATE)+LAG39(EXRATE)+LAG40(EXRATE))/40; BENCHPCT=((EXRATE-BENCHMRK)/BENCHMRK)*100; AVGRATE1=(LAG1(EXRATE)+LAG2(EXRATE)+LAG3(EXRATE)+LAG4(EXRATE)+ LAG5(EXRATE))/5; AVGBMRK1=(LAG1(BENCHMRK)+LAG2(BENCHMRK)+LAG3(BENCHMRK)+LAG4(BENCHMRK)+ LAG5(BENCHMRK))/5; PCTWK1=((AVGRATE1-AVGBMRK1)/AVGBMRK1)*100; AVGRATE2=(LAG6(EXRATE)+LAG7(EXRATE)+LAG8(EXRATE)+LAG9(EXRATE)+ LAG10(EXRATE))/5; AVGBMRK2=(LAG6(BENCHMRK)+LAG7(BENCHMRK)+LAG8(BENCHMRK)+LAG9(BENCHMRK)+ LAG10(BENCHMRK))/5; PCTWK2=((AVGRATE2-AVGBMRK2)/AVGBMRK2)*100; AVGRATE3=(LAG11(EXRATE)+LAG12(EXRATE)+LAG13(EXRATE)+LAG14(EXRATE)+ LAG15(EXRATE))/5; AVGBMRK3=(LAG11(BENCHMRK)+LAG12(BENCHMRK)+LAG13(BENCHMRK)+LAG14(BENCHMRK)+ LAG15(BENCHMRK))/5; PCTWK3=((AVGRATE3-AVGBMRK3)/AVGBMRK3)*100; AVGRATE4=(LAG16(EXRATE)+LAG17(EXRATE)+LAG18(EXRATE)+LAG19(EXRATE)+ LAG20(EXRATE))/5; AVGBMRK4=(LAG16(BENCHMRK)+LAG17(BENCHMRK)+LAG18(BENCHMRK)+LAG19(BENCHMRK)+ LAG20(BENCHMRK))/5; PCTWK4=((AVGRATE4-AVGBMRK4)/AVGBMRK4)*100; AVGRATE5=(LAG21(EXRATE)+LAG22(EXRATE)+LAG23(EXRATE)+LAG24(EXRATE)+ LAG25(EXRATE))/5; AVGBMRK5=(LAG21(BENCHMRK)+LAG22(BENCHMRK)+LAG23(BENCHMRK)+LAG24(BENCHMRK)+ LAG25(BENCHMRK))/5; PCTWK5=((AVGRATE5-AVGBMRK5)/AVGBMRK5)*100; AVGRATE6=(LAG26(EXRATE)+LAG27(EXRATE)+LAG28(EXRATE)+LAG29(EXRATE)+ LAG30(EXRATE))/5; AVGBMRK6=(LAG26(BENCHMRK)+LAG27(BENCHMRK)+LAG28(BENCHMRK)+LAG29(BENCHMRK)+ LAG30(BENCHMRK))/5; PCTWK6=((AVGRATE6-AVGBMRK6)/AVGBMRK6)*100; AVGRATE7=(LAG31(EXRATE)+LAG32(EXRATE)+LAG33(EXRATE)+LAG34(EXRATE)+ LAG35(EXRATE))/5; AVGBMRK7=(LAG31(BENCHMRK)+LAG32(BENCHMRK)+LAG33(BENCHMRK)+LAG34(BENCHMRK)+ LAG35(BENCHMRK))/5; PCTWK7=((AVGRATE7-AVGBMRK7)/AVGBMRK7)*100; AVGRATE8=(LAG36(EXRATE)+LAG37(EXRATE)+LAG38(EXRATE)+LAG39(EXRATE)+ LAG40(EXRATE))/5; AVGBMRK8=(LAG36(BENCHMRK)+LAG37(BENCHMRK)+LAG38(BENCHMRK)+LAG39(BENCHMRK)+ LAG40(BENCHMRK))/5; PCTWK8=((AVGRATE8-AVGBMRK8)/AVGBMRK8)*100; FORMAT PCTWK1 PCTWK2 PCTWK3 PCTWK4 PCTWK5 PCTWK6 PCTWK7 PCTWK8 7.3; SUSTMOVE='NO '; SUSTRATE=0; SUSTDAYS=0; &RATECODE.I=0; &RATECODE.R=0; IF -2.25 LE BENCHPCT LE 2.25 THEN DO; RATETYPE='DAILY'; &RATECODE.I=EXRATE; &RATECODE.R=EXRATE; END; ELSE IF (BENCHPCT LT -2.25) OR (BENCHPCT GT 2.25) THEN DO; RATETYPE='BMARK'; &RATECODE.I=BENCHMRK; &RATECODE.R=BENCHMRK; END; IF PCTWK1 GT 5 & PCTWK2 GT 5 & PCTWK3 GT 5 & PCTWK4 GT 5 & PCTWK5 GT 5 & PCTWK6 GT 5 & PCTWK7 GT 5 & PCTWK8 GT 5 THEN DO; SUSTMOVE='YES'; END; IF SUSTMOVE='YES' & ((LAG1(SUSTMOVE) NE 'YES') & (LAG2(SUSTMOVE) NE 'YES') & (LAG3(SUSTMOVE) NE 'YES') & (LAG4(SUSTMOVE) NE 'YES') & (LAG5(SUSTMOVE) NE 'YES') & (LAG6(SUSTMOVE) NE 'YES') & (LAG7(SUSTMOVE) NE 'YES') & (LAG8(SUSTMOVE) NE 'YES') & (LAG9(SUSTMOVE) NE 'YES') & (LAG10(SUSTMOVE) NE 'YES') & (LAG11(SUSTMOVE) NE 'YES') & (LAG12(SUSTMOVE) NE 'YES') & (LAG13(SUSTMOVE) NE 'YES') & (LAG14(SUSTMOVE) NE 'YES') & (LAG15(SUSTMOVE) NE 'YES') & (LAG16(SUSTMOVE) NE 'YES') & (LAG17(SUSTMOVE) NE 'YES') & (LAG18(SUSTMOVE) NE 'YES') & (LAG19(SUSTMOVE) NE 'YES') & (LAG20(SUSTMOVE) NE 'YES') & (LAG21(SUSTMOVE) NE 'YES') & (LAG22(SUSTMOVE) NE 'YES') & (LAG23(SUSTMOVE) NE 'YES') & (LAG24(SUSTMOVE) NE 'YES') & (LAG25(SUSTMOVE) NE 'YES') & (LAG26(SUSTMOVE) NE 'YES') & (LAG27(SUSTMOVE) NE 'YES') & (LAG28(SUSTMOVE) NE 'YES') & (LAG29(SUSTMOVE) NE 'YES') & (LAG30(SUSTMOVE) NE 'YES') & (LAG31(SUSTMOVE) NE 'YES') & (LAG32(SUSTMOVE) NE 'YES') & (LAG33(SUSTMOVE) NE 'YES') & (LAG34(SUSTMOVE) NE 'YES') & (LAG35(SUSTMOVE) NE 'YES') & (LAG36(SUSTMOVE) NE 'YES') & (LAG37(SUSTMOVE) NE 'YES') & (LAG38(SUSTMOVE) NE 'YES') & (LAG39(SUSTMOVE) NE 'YES') & (LAG40(SUSTMOVE) NE 'YES') & (LAG41(SUSTMOVE) NE 'YES') & (LAG42(SUSTMOVE) NE 'YES')) THEN DO; SUSTDAYS=1; IF RATETYPE='DAILY' THEN SUSTRATE=EXRATE; ELSE SUSTRATE=BENCHMRK; END; IF (LAG1(SUSTDAYS)=1) OR (LAG2(SUSTDAYS)=1) OR (LAG3(SUSTDAYS)=1) OR (LAG4(SUSTDAYS)=1) OR (LAG5(SUSTDAYS)=1) OR (LAG6(SUSTDAYS)=1) OR (LAG7(SUSTDAYS)=1) OR (LAG8(SUSTDAYS)=1) OR (LAG9(SUSTDAYS)=1) OR (LAG10(SUSTDAYS)=1) OR (LAG11(SUSTDAYS)=1) OR (LAG12(SUSTDAYS)=1) OR (LAG13(SUSTDAYS)=1) OR (LAG14(SUSTDAYS)=1) OR (LAG15(SUSTDAYS)=1) OR (LAG16(SUSTDAYS)=1) OR (LAG17(SUSTDAYS)=1) OR (LAG18(SUSTDAYS)=1) OR (LAG19(SUSTDAYS)=1) OR (LAG20(SUSTDAYS)=1) OR (LAG21(SUSTDAYS)=1) OR (LAG22(SUSTDAYS)=1) OR (LAG23(SUSTDAYS)=1) OR (LAG24(SUSTDAYS)=1) OR (LAG25(SUSTDAYS)=1) OR (LAG26(SUSTDAYS)=1) OR (LAG27(SUSTDAYS)=1) OR (LAG28(SUSTDAYS)=1) OR (LAG29(SUSTDAYS)=1) OR (LAG30(SUSTDAYS)=1) OR (LAG31(SUSTDAYS)=1) OR (LAG32(SUSTDAYS)=1) OR (LAG33(SUSTDAYS)=1) OR (LAG34(SUSTDAYS)=1) OR (LAG35(SUSTDAYS)=1) OR (LAG36(SUSTDAYS)=1) OR (LAG37(SUSTDAYS)=1) OR (LAG38(SUSTDAYS)=1) OR (LAG39(SUSTDAYS)=1) OR (LAG40(SUSTDAYS)=1) OR (LAG41(SUSTDAYS)=1) OR (LAG42(SUSTDAYS)=1) THEN DO; SUSTMOVE='YES'; END; RUN; DATA SUSTAIN OTHRATES; SET &CTRYCODE; IF SUSTMOVE='YES' THEN OUTPUT SUSTAIN; ELSE OUTPUT OTHRATES; RUN; DATA SUST1(DROP=SUSTRATE &RATECODE.I) SUSRATE1(KEEP=SUSTRATE) SUST2(DROP=SUSTRATE &RATECODE.I) SUSRATE2(KEEP=SUSTRATE) SUST3(DROP=SUSTRATE &RATECODE.I) SUSRATE3(KEEP=SUSTRATE); SET SUSTAIN; SDAYS=_N_; IF SDAYS LE 43 THEN DO; SUSTDAYS=44-SDAYS; IF SUSTDAYS=43 THEN OUTPUT SUSRATE1; OUTPUT SUST1; END; ELSE IF 44 LE SDAYS LE 86 THEN DO; SUSTDAYS=87-SDAYS; IF SUSTDAYS=43 THEN OUTPUT SUSRATE2; OUTPUT SUST2; END; ELSE IF 87 LE SDAYS LE 129 THEN DO; SUSTDAYS=130-SDAYS; IF SUSTDAYS=43 THEN OUTPUT SUSRATE3; OUTPUT SUST3; END; RUN; DATA SUST1; IF _N_=1 THEN SET SUSRATE1; SET SUST1; &RATECODE.I=SUSTRATE; RUN; DATA SUST2; IF _N_=1 THEN SET SUSRATE2; SET SUST2; &RATECODE.I=SUSTRATE; RUN; DATA SUST3; IF _N_=1 THEN SET SUSRATE3; SET SUST3; &RATECODE.i=SUSTRATE; RUN; DATA SUSTALL; SET SUST1 SUST2 SUST3; RUN; PROC SORT DATA=SUSTALL; BY DATE; RUN; DATA ALLRATES; SET OTHRATES SUSTALL(DROP=SDAYS); RUN; PROC SORT DATA=ALLRATES; BY DATE; RUN; DATA ALLDATES (DROP=I); DO I=0 TO 10000; DATE = "&START_BENCHMARK"D + I; IF ("&STARTDAY"D - 7) LE DATE LE "&ENDDAY"D THEN OUTPUT ALLDATES; END; RUN; DATA ALLDATES (DROP=LAG1INV LAG2INV LAG3INV LAG4INV LAG1COM LAG2COM LAG3COM LAG4COM LAG5INV LAG6INV LAG7INV LAG8INV LAG5COM LAG6COM LAG7COM LAG8COM) BADRATES (DROP=LAG1INV LAG2INV LAG3INV LAG4INV LAG1COM LAG2COM LAG3COM LAG4COM LAG5INV LAG6INV LAG7INV LAG8INV LAG5COM LAG6COM LAG7COM LAG8COM); MERGE ALLDATES (IN=A) ALLRATES (IN=B); BY DATE; IF A THEN DO; IF &RATECODE.I=. THEN &RATECODE.I=0; IF &RATECODE.R=. THEN &RATECODE.R=0; LAG1INV=LAG1(&RATECODE.I); LAG2INV=LAG2(&RATECODE.I); LAG3INV=LAG3(&RATECODE.I); LAG4INV=LAG4(&RATECODE.I); LAG5INV=LAG5(&RATECODE.I); LAG6INV=LAG6(&RATECODE.I); LAG7INV=LAG7(&RATECODE.I); LAG8INV=LAG8(&RATECODE.I); IF &RATECODE.I=0 THEN DO; SELECT; WHEN ((LAG1INV) GT 0) &RATECODE.I=LAG1INV; WHEN ((LAG2INV) GT 0) &RATECODE.I=LAG2INV; WHEN ((LAG3INV) GT 0) &RATECODE.I=LAG3INV; WHEN ((LAG4INV) GT 0) &RATECODE.I=LAG4INV; WHEN ((LAG5INV) GT 0) &RATECODE.I=LAG5INV; WHEN ((LAG6INV) GT 0) &RATECODE.I=LAG6INV; WHEN ((LAG7INV) GT 0) &RATECODE.I=LAG7INV; WHEN ((LAG8INV) GT 0) &RATECODE.I=LAG8INV; OTHERWISE &RATECODE.I=0; END; END; LAG1COM=LAG1(&RATECODE.R); LAG2COM=LAG2(&RATECODE.R); LAG3COM=LAG3(&RATECODE.R); LAG4COM=LAG4(&RATECODE.R); LAG5COM=LAG5(&RATECODE.R); LAG6COM=LAG6(&RATECODE.R); LAG7COM=LAG7(&RATECODE.R); LAG8COM=LAG8(&RATECODE.R); IF &RATECODE.R=0 THEN DO; SELECT; WHEN ((LAG1COM) GT 0) &RATECODE.R=LAG1COM; WHEN ((LAG2COM) GT 0) &RATECODE.R=LAG2COM; WHEN ((LAG3COM) GT 0) &RATECODE.R=LAG3COM; WHEN ((LAG4COM) GT 0) &RATECODE.R=LAG4COM; WHEN ((LAG5COM) GT 0) &RATECODE.R=LAG5COM; WHEN ((LAG6COM) GT 0) &RATECODE.R=LAG6COM; WHEN ((LAG7COM) GT 0) &RATECODE.R=LAG7COM; WHEN ((LAG8COM) GT 0) &RATECODE.R=LAG8COM; OTHERWISE &RATECODE.R=0; END; END; IF &RATECODE.I=0 OR &RATECODE.R=0 THEN OUTPUT BADRATES; ELSE IF "&STARTDAY"D LE DATE LE "&ENDDAY"D THEN OUTPUT ALLDATES; END; RUN; DATA CHECK; SET ALLDATES (KEEP=DATE &RATECODE.R &RATECODE.I); YR = YEAR(DATE); MON = MONTH(DATE); IRATE=1/&RATECODE.I; RRATE=1/&RATECODE.R; RUN; PROC SORT DATA=CHECK; BY YR MON; RUN; PROC MEANS NOPRINT DATA=CHECK; BY YR MON; VAR IRATE RRATE; OUTPUT OUT = CHECK1 (DROP=_TYPE_ _FREQ_) MIN = MININV MINREV MAX = MAXINV MAXREV MEAN= AVGINV AVGREV; RUN; DATA &CTRYCODE; SET ALLDATES (KEEP=DATE &RATECODE.R &RATECODE.I) PASTLIB.&CTRYCODE; RUN; PROC SORT DATA = &CTRYCODE NODUPKEY NODUPREC OUT = &CTRYCODE; FORMAT DATE DATE9. &RATECODE.R 9.6 &RATECODE.I 9.6; BY DATE; RUN; DATA DATES; SET &CTRYCODE END=EOF; BY DATE; IF _N_ = 1 THEN FIRST_SALE_DATE=DATE; RETAIN FIRST_SALE_DATE; IF EOF THEN DO; LAST_SALE_DATE=DATE; OUTPUT DATES; END; RUN; PROC PRINT DATA = DATES NOOBS; VAR FIRST_SALE_DATE LAST_SALE_DATE; FORMAT FIRST_SALE_DATE LAST_SALE_DATE DATE9.; TITLE2 "&STARTDAY - &ENDDAY DATABASE FOR ANALYSTS AND INTERNET (&CTRYCODE)"; TITLE3 "FIRST & LAST DATES OF RATES "; RUN; DATA _NULL_; FILE "&CURRENT_EXRATE_PATH\INTERNET_RATES\&CTRYCODE..TXT" NOPRINT; DO COLUMN = 3; PUT @COLUMN "DATE " +(-1) ";" @; PUT "&RATECODE.I " +(-1) ";" @; PUT "&RATECODE.R " +(-1) ";" ; DO LINE = 2 TO 10000; SET &CTRYCODE END = ENDFILE; PUT @COLUMN DATE +(-1) ";" +1 &RATECODE.I +(-1) ";" +1 &RATECODE.R +(-1) ";" ; END; END; RUN; PROC PRINTTO; RUN; PROC DATASETS NOLIST; COPY IN = WORK OUT = FINLIB; SELECT &CTRYCODE; QUIT; %END; %MEND RATES; **************************************************************************************; ** EXECUTE THE RATES MACRO FOR EACH TYPE OF SOURCE DATA **; **************************************************************************************; **********************************************************************************; ** For Countries Whose Data is Sourced from Factiva **; **********************************************************************************; %RATES(SOURCE = FAC, NUMBER_OF_COUNTRIES = 24, SOURCE_SHEET = FACTIVA_RATES, DIVIDE_FOREIGN_CURRENCY = 1 %STR(/)) **********************************************************************************; ** For Countries Whose Data is Sourced from the Federal Reserve and are **; ** Denominated in Foreign Currency/USD **; **********************************************************************************; %RATES(SOURCE = FED, NUMBER_OF_COUNTRIES = 19, SOURCE_SHEET = FEDERAL_RESERVE_FILE, DIVIDE_FOREIGN_CURRENCY = 1 %STR(/)) **********************************************************************************; ** For Countries Whose Data is Sourced from the Federal Reserve and are **; ** Denominated in USD/Foreign Currency **; **********************************************************************************; %RATES(SOURCE = US, NUMBER_OF_COUNTRIES = 4, SOURCE_SHEET = FEDERAL_RESERVE_FILE, DIVIDE_FOREIGN_CURRENCY = ) ******************************************************************************************; ** CALCULATE PROGRAM RUN TIME **; ******************************************************************************************; DATA _NULL_; CALL SYMPUT('EDAY', PUT(DATE(), DOWNAME.)); CALL SYMPUT('EDATE', PUT(DATE(), DATE.)); CALL SYMPUT('EWDATE', PUT(DATE(), WORDDATE.)); CALL SYMPUT('ETIME', PUT(TIME(), TIME.)); RUN; DATA _NULL_; B_DATE = INPUT("&BDATE", DATE.); B_TIME = INPUT("&BTIME", TIME.); E_DATE = INPUT("&EDATE", DATE.); E_TIME = INPUT("&ETIME", TIME.); IF B_DATE = E_DATE THEN TOT_TIME = E_TIME - B_TIME; ELSE TOT_TIME = (86400 - B_TIME) + E_TIME; CALL SYMPUT('TTIME', PUT(TOT_TIME, TIME.)); RUN; %PUT NOTE: This program finished running on &EDAY, &EWDATE, at &ETIME.; %PUT NOTE: This program took &TTIME hour(s) to run.; ******************************************************************************************; ** CHECK FOR ERRORS AND SAVE THE LOG OF THE CHECK AS A FILE CALLED, "ERRORLOG," **; ** UNDER THE DIRECTORY INDICATED BY &CURRENT_EXRATE_PATH **; **; **; ******************************************************************************************; DM "LOG; FILE '&CURRENT_EXRATE_PATH.\ERRORLOG.TXT'"; DATA REVIEWLOG; LENGTH LINE $200; INFILE "&CURRENT_EXRATE_PATH.\ERRORLOG.TXT" END = DONE TRUNCOVER; DO WHILE (NOT DONE); INPUT LINE $ 1-200; IF LINE IN ("NOTE: Missing values were generated as a result of performing an operation on missing values." "SYMBOLGEN: Macro variable _IMEXSERROR_ resolves to SERROR") THEN DELETE; IF (INDEX(LINE, 'converted') NE 0) OR (INDEX(LINE, 'ERROR') NE 0) OR (INDEX(LINE, 'missing') NE 0) OR (INDEX(LINE, 'repeats') NE 0) OR (INDEX(LINE, 'uninitialized') NE 0) OR (INDEX(LINE, 'WARNING') NE 0) THEN OUTPUT; END; STOP; RUN; PROC PRINT DATA = REVIEWLOG DOUBLE; TITLE2 "RESULTS OF CHECKING THE LOG FOR THE FOLLOWING WORDS INDICATIVE OF POTENTIAL PROBLEMS:"; TITLE3 "CONVERTED, ERROR, MISSING, REPEATS, UNINITIALIZED, OR WARNING"; RUN; DATA REVIEWLOG (DROP = _ALL_); SET REVIEWLOG NOBS = NOBS END = EOF; RUN; DATA REVIEWLOG; SET REVIEWLOG POINT = I NOBS = NOBS END = END; IF _N_ = 1 AND NOBS = 0 THEN DO; ALL_MATCH = "NO INSTANCES OF CONVERTED, ERROR, MISSING, REPEATS, UNINITIALIZED, OR WARNING FOUND"; OUTPUT; STOP; END; ELSE STOP; RUN; PROC PRINT DATA = REVIEWLOG SPLIT = '*' NOOBS; LABEL ALL_MATCH = '00'x; RUN; DM "DELETE '&CURRENT_EXRATE_PATH.\ERRORLOG.TXT'";