****** UPDATE MACRO DATES FOR LAST 12 MONTHS ****************;

%let Mth4 = 01;
%let Yr4 = 2017;
%macro dates(date,mth,yr);
proc sql;
/* connect to oracle as db(user=&usr. password=&pass. path=f3rdprod buffsize=4000)*/;
create table get_data as /*select * from connection to db*/(
select 
acct_num,
acct_key,
app_num,			
bank_num,
dealr_loc,
amt_orig_loan,
amt_principal_bal,
dt_loan_entry

from emrsstap.dr_plr_&date /*plr_accnt_dtl*/
where proc_type = '05' and acct_purge_ind = 'N' and collat_cd = 'AUT' and mth_key = &date.);
quit;

data get_losses;
set get_data;
where collat_purp_cd ^= 'BUS' and (amt_coff_mtd ^= 0 or amt_recover_mtd ^= 0);
amt_ncoff_mtd = amt_coff_mtd - amt_recover_mtd;
run;

data get_EPD;
set get_data;
where collat_purp_cd ^= 'BUS' and datepart(dt_loan_entry) >= mdy(&mth12,01,&yr12) 
	and (amt_coff_mtd > 0 or (amt_coff + amt_recover_itd) > 0 or num_days_pdue >= 60);
bad = 1;
run;

data get_Early_Payoff;
set get_data;
where collat_purp_cd ^= 'BUS' and datepart(dt_loan_entry) >= mdy (&mth12,01,&yr12)
	and amt_principal_bal = 0 and amt_coff = 0 and amt_coff_mtd = 0;
if datepart(dt_loan_entry) < mdy(&mth4,01,&yr4) then early_pay_12 = 1;
else early_pay_12 = 0;
if datepart(dt_loan_entry) >= mdy(&mth4,01,&yr4) then early_pay_4 = 1;
else early_pay_4 = 0;
run;

data get_orig;
set get_data;
where collat_purp_cd ^= 'BUS' and datepart(dt_loan_entry) >= mdy(&mth,01,&yr);
accountnumber = acct_num * 1;
run;

proc summary data=get_data;
where collat_purp_cd ^= 'BUS' and acct_stat ^= 'CL' and proc_stat ^= 'CF';
class bank_num dealr_loc;
var amt_principal_bal;
output out=get_balances sum=;
run;

data get_balances;
set get_balances;
mth_bal = &date;
run;

proc append data=get_losses base=Losses force; run;
proc append data=get_EPD base=EPD3 force; run;
proc append data=get_orig base=Orig force; run;
proc append data=get_balances base=running_Balances force; where _TYPE_ = 3; run;
proc append data=get_early_payoff base=Early_Payoff force; run;
%mend;
%dates(201505,05,2015);
%dates(201506,06,2015);
%dates(201507,07,2015);
%dates(201508,08,2015);
%dates(201509,09,2015);
%dates(201510,10,2015);
%dates(201511,11,2015);
%dates(201512,12,2015);
%dates(201601,01,2016);
%dates(201602,02,2016);
%dates(201603,03,2016);
%dates(201604,04,2016);

data exceptions;
set consind.exceptions_2016;
where mdy(&mth12,01,&yr12) <= pbookingdate < mdy(&mth12,01,&yr);
excep = 1;
run;