%let user=****;
%let pw=***;
libname Toronto '*******';


%let Date_DD = %sysfunc(date(),Day2.);   	
%put &Date_DD;

%MACRO decode;
	data GPI&i;
	   infile iGPI;
	   input
			@1 CRDT_SRCE_CD 		$EBCDIC3.
			@4 CRDT_REF_NUM 		$EBCDIC21.
			@25 CRDT_TYPE 			$EBCDIC3.
			@28 SALES_BRANCH_NUM 	S370FPD3.
			@31 CREDIT_NUM			S370FPD10.
			@41 CRDT_STATUS 		$EBCDIC4.
			@45 CRDT_STAT_DATE 		$EBCDIC10.
			@55 BRANCH_PROV 		$EBCDIC2.
			@57 SALES_ID 			$EBCDIC8.
			@65 BILLING_DAY 		S370FPD2.
			@67 BILL_MODE 			$EBCDIC1.
			@68 CHRG_BANK 			$EBCDIC3.
			@71 CHRG_BRCH_NUM 		$EBCDIC5.
			@76 CHRG_ACCT_NUM 		$EBCDIC20.
			@96 APPL_SIGN_DATE 		$EBCDIC10.
			@106 CRDT_PLAN 			$EBCDIC3.
			@109 EXIST_INS_FLAG 	$EBCDIC1.
			@110 LOAN_CRDT_TERM		S370FPD2.
			@112 LOAN_CRDT_BAL 		S370FPD6.
			@118 MTG_CLOSE_DATE 	$EBCDIC10.
			@128 CRDT_ISSUE_DATE 	$EBCDIC10.
			@138 MSF_BRANCH 		S370FPD3.
			@141 PROC_DATE 			$EBCDIC10.
			@151 LOC_INSURANCE_TYPE $EBCDIC1.
			@152 CUST_INS_AMOUNT	 	S370FF13.2
			@165 CUST_FIRST_NAME 	$EBCDIC40.
			@205 CUST_LAST_NAME 	$EBCDIC40.
			@245 CUST_MIDDLE_INTL 	$EBCDIC1.
			@246 CUST_DOB 			$EBCDIC10.
			@256 CUST_SMOKER_FLAG 	$EBCDIC1.
			@257 CUST_GENDER 		$EBCDIC1.
			@258 CUST_LOC_OWNRSHP 	$EBCDIC4.
			@262 CUST_PREF_LANG 	$EBCDIC1.
			@263 CUST_ADDR_LINE_1 	$EBCDIC40.
			@303 CUST_ADDR_LINE_2 	$EBCDIC40.
			@343 CUST_ADDR_LINE_3 	$EBCDIC40.
			@383 CUST_CITY 			$EBCDIC40.
			@423 CUST_PROV 			$EBCDIC2.
			@425 CUST_CNTRY 		$EBCDIC3.
			@428 CUST_POSTAL_CODE 	$EBCDIC6.
			@434 CUST_RESDNCE_CODE 	$EBCDIC2.
			@436 COVG_PLAN_TYP 		$EBCDIC1.
			@437 COVG_PLAN_CODE 	$EBCDIC2.
			@439 COVG_STATUS 		$EBCDIC1.
			@440 COVG_MAX_BENFT	 	S370FF13.2
			@453 EXIST_MTG_NUM 			S370FF18.
			@471 CRDR_LOAN_TYPE 		S370FF2.
			@473 CRDR_LOAN_SUBTYPE 		S370FF2.
			@475 CUST_NEW_ADDR_1 	$EBCDIC40.
			@515 CUST_NEW_ADDR_2 	$EBCDIC40.
			@555 CUST_NEW_ADDR_3 	$EBCDIC40.
			@595 CUST_NEW_CITY 		$EBCDIC40.
			@635 CUST_NEW_PROV 		$EBCDIC2.
			@637 CUST_NEW_CNTRY 	$EBCDIC3.
			@640 CUST_NEW_PSTL_CD 	$EBCDIC6.
			@646 CUST_HOME_PHNUM 	$EBCDIC13.
			@659 CUST_WORK_PHNUM 	$EBCDIC13.
			@672 CRDT_LOC_SEC_IND 	$EBCDIC1.
			@673 CSTR_LINK_NUM			S370FPD6.
			@679 CRDR_ORIG_BRANCH 		$EBCDIC5.   
			@684 SERVICING_BRANCH 		S370FF4.
			@688 EXISTING_LOC_NUM 		S370FF18.
			@706 APP_PRINT_TIMESTAMP $EBCDIC26.
			@732 APP_PRINT_INDICATOR $EBCDIC6.
			@738 CUST_SUFFIX 		$EBCDIC40.
			@778 CUST_APPENDAGE 	$EBCDIC25.
			@803 APPL_CREDIT_LIMIT 	S370FF11.2
			@814 COVG_AMOUNT	 		S370FF13.2
			@827 LOC_EXIST_COVG 	$EBCDIC1.
			@439 ORIG_COVG_STATUS 	$EBCDIC1.
			@;
	run;
%MEND decode;

%MACRO get_27_months;
    %DO i = 0 %to 26;						
       	filename iGPI ftp "'GPIP.C.M.ZGPIXTRG.MONTHLY(-&i.)'" host='*******' user="&user" pass="&pw" recfm=f lrecl=840 rcmd='site rdw';
		%decode;							
    %END;
%MEND get_27_months;

%MACRO stack_27_months;
	%DO i = 0 %to 26;						
		PROC APPEND BASE=mastergpi DATA=gpi&i;
		RUN;
	%END;
%MEND stack_27_months;

%MACRO cleanGPI;

	data &gpi_data (DROP = 
		CRDT_REF_NUM
		CREDIT_NUM 
		BILLING_DAY 
		LOAN_CRDT_BAL 
		EXIST_MTG_NUM 
		CRDR_LOAN_TYPE 
		CRDR_LOAN_SUBTYPE
		CSTR_LINK_NUM
		EXISTING_LOC_NUM
		);

	set &gpi_data;

		CRDT_REF_NUM1 = SUBSTR(PUT(INPUT(CRDT_REF_NUM,best21.),Z21.),9,13); 		
		CREDIT_NUM1 = PUT(CREDIT_NUM, Z19.);				
		BILLING_DAY1	= PUT(BILLING_DAY, Z2.);			
		CHRG_ACCT_NUM = STRIP(CHRG_ACCT_NUM);				
		CRDT_PLAN = STRIP(CRDT_PLAN);						
		LOAN_CRDT_BAL1 =	PUT(LOAN_CRDT_BAL/100, 11.2);	
		CUST_ADDR_LINE_1 = STRIP(CUST_ADDR_LINE_1);			
		CUST_ADDR_LINE_2 = STRIP(CUST_ADDR_LINE_2);
		CUST_ADDR_LINE_3 = STRIP(CUST_ADDR_LINE_3);
		CUST_POSTAL_CODE = STRIP(CUST_POSTAL_CODE);
		EXIST_MTG_NUM1 = PUT(EXIST_MTG_NUM, Z18.);			
		CRDR_LOAN_TYPE1 = PUT(CRDR_LOAN_TYPE, Z2.);			
		CRDR_LOAN_SUBTYPE1 = PUT(CRDR_LOAN_SUBTYPE, Z2.);	
		CUST_NEW_ADDR_1 = STRIP(CUST_NEW_ADDR_1);			
		CUST_NEW_ADDR_2 = STRIP(CUST_NEW_ADDR_2);
		CUST_NEW_ADDR_3 = STRIP(CUST_NEW_ADDR_3);
		CUST_HOME_PHNUM = STRIP(CUST_HOME_PHNUM);
		CUST_WORK_PHNUM = STRIP(CUST_WORK_PHNUM);
		CSTR_LINK_NUM1 = PUT(CSTR_LINK_NUM, Z11.);			
		EXISTING_LOC_NUM1 = PUT(EXISTING_LOC_NUM, Z18.);	
		CUST_SUFFIX = STRIP(CUST_SUFFIX);
		CUST_APPENDAGE = STRIP(CUST_APPENDAGE);

		RENAME
		CRDT_REF_NUM1 = CRDT_REF_NUM
		CREDIT_NUM1 = CREDIT_NUM
		BILLING_DAY1 = BILLING_DAY 
		LOAN_CRDT_BAL1 = LOAN_CRDT_BAL
		EXIST_MTG_NUM1 = EXIST_MTG_NUM 
		CRDR_LOAN_TYPE1 = CRDR_LOAN_TYPE 
		CRDR_LOAN_SUBTYPE1 = CRDR_LOAN_SUBTYPE
		CSTR_LINK_NUM1 = CSTR_LINK_NUM
		EXISTING_LOC_NUM1 = EXISTING_LOC_NUM
		;													

	run;

/*data step to re-order columns using retain statment and save to both the work and permanent (Toronto) libraries*/
	data &gpi_data Toronto.&gpi_data;
		RETAIN
			CRDT_SRCE_CD
			CRDT_REF_NUM
			CRDT_TYPE
			SALES_BRANCH_NUM
			CREDIT_NUM
			CRDT_STATUS
			CRDT_STAT_DATE
			BRANCH_PROV
			SALES_ID
			BILLING_DAY
			BILL_MODE
			CHRG_BANK
			CHRG_BRCH_NUM
			CHRG_ACCT_NUM
			APPL_SIGN_DATE
			CRDT_PLAN
			EXIST_INS_FLAG
			LOAN_CRDT_TERM
			LOAN_CRDT_BAL
			MTG_CLOSE_DATE
			CRDT_ISSUE_DATE
			MSF_BRANCH
			PROC_DATE
			LOC_INSURANCE_TYPE
			CUST_INS_AMOUNT
			CUST_FIRST_NAME
			CUST_LAST_NAME
			CUST_MIDDLE_INTL
			CUST_DOB
			CUST_SMOKER_FLAG
			CUST_GENDER
			CUST_LOC_OWNRSHP
			CUST_PREF_LANG
			CUST_ADDR_LINE_1
			CUST_ADDR_LINE_2
			CUST_ADDR_LINE_3
			CUST_CITY
			CUST_PROV
			CUST_CNTRY
			CUST_POSTAL_CODE
			CUST_RESDNCE_CODE
			COVG_PLAN_TYP
			COVG_PLAN_CODE
			COVG_STATUS
			COVG_MAX_BENFT
			EXIST_MTG_NUM
			CRDR_LOAN_TYPE
			CRDR_LOAN_SUBTYPE
			CUST_NEW_ADDR_1
			CUST_NEW_ADDR_2
			CUST_NEW_ADDR_3
			CUST_NEW_CITY
			CUST_NEW_PROV
			CUST_NEW_CNTRY
			CUST_NEW_PSTL_CD
			CUST_HOME_PHNUM
			CUST_WORK_PHNUM
			CRDT_LOC_SEC_IND
			CSTR_LINK_NUM
			CRDR_ORIG_BRANCH
			SERVICING_BRANCH
			EXISTING_LOC_NUM
			APP_PRINT_TIMESTAMP
			APP_PRINT_INDICATOR
			CUST_SUFFIX
			CUST_APPENDAGE
			APPL_CREDIT_LIMIT
			COVG_AMOUNT
			LOC_EXIST_COVG
			ORIG_COVG_STATUS
			;
	set &gpi_data;

	run;

%MEND cleanGPI;

%MACRO newmonth;				*if code is executed in the first week of the month, a new master data set of the rolling 27 months must be compiled;
	%if &Date_DD <= 7 %then 
		%do;				
			%put "Updating master data set...";
			PROC DATASETS;
				DELETE mastergpi;
			QUIT;
			RUN;
			%get_27_months;

			%if &today < %sysfunc(putn('01DEC2015'd,5.))%then 
			%do;
				data MASTERGPI;
				set Toronto.GPIG0133V00; 
				run;
				PROC APPEND BASE=mastergpi DATA=Toronto.GPIG0134V00; 
				RUN;
			%end;
			%else %if &today < %sysfunc(putn('01JAN2016'd,5.))%then 
			%do;
				data MASTERGPI;
					set Toronto.GPIG0134V00;
				run;
			%end;

			%stack_27_months;
			%let gpi_data = MASTERGPI;
			%cleanGPI;
			%put "Update master data set complete";
		%end; 
	%else 
		%do;
			%put "Master data set does not need to be updated... using old master data table";
			data mastergpi;
			set toronto.mastergpi;
			run;
		%end;
%mend newmonth;

%newmonth;
filename iGPI ftp "'GPIP.C.M.ZGPIXTRG.MONACCUM(0)'" host='******' user="&user" pass="&pw" recfm=f lrecl=840 rcmd='site rdw';
%let i= monaccum;
%decode;

%let gpi_data = GPI&i;
%cleanGPI;

PROC APPEND BASE=mastergpi DATA=GPI&i;
RUN;