LIBNAME PROJECT '/home/mlglazewska0/BIBL';
data PROJECT.tmp;
set PROJECT.Loan;
if status='A' or status='C' then loan_status=0;
else loan_status=1;
run;
proc sort data=PROJECT.tmp;
by status;
run;
data PROJECT.client_disp;
merge PROJECT.client(in=a) PROJECT.disp(in=b);
by client_id;
if a=b;
run;
proc sort data=PROJECT.tmp;
by account_id;
run;
proc sort data=PROJECT.account;
by account_id;
run;
data PROJECT.client_account;
merge PROJECT.client_disp(in=a) PROJECT.account(in=b);
by account_id;
if a=b;
run;
proc sql;
create table PROJECT.owner_account as select * from PROJECT.client_account where type='OWNER';
quit;
data PROJECT.result;
merge PROJECT.tmp(in=b rename=(date=credit_date)) PROJECT.owner_account(in=c);
by account_id;
if b;
run;
data PROJECT.result;
set PROJECT.result;
if(birth_number-(round(birth_number/10000))*10000)<0 then sex=1;
else sex=0;
age=year(credit_date)-(1900+round(birth_number/10000));
seniority=round((credit_date-date)/12);
run;
proc sort data=PROJECT.trans;
by account_id;
run;
proc sort data=PROJECT.loan;
by account_id;
run;
data PROJECT.amount;
merge PROJECT.loan(in=a rename=(date=credit_date)) PROJECT.trans(in=b);
by account_id;
if a=b;
run;
data PROJECT.amount;
set PROJECT.amount;
where date < credit_date;
run;

data PROJECT.amount_30;
set PROJECT.amount;
where date < credit_date and date > (credit_date -30);
run;
data PROJECT.amount_60;
set PROJECT.amount;
where date < credit_date and date > (credit_date -60);
run;
data PROJECT.amount_90;
set PROJECT.amount;
where date < credit_date and date > (credit_date -90);
run;

data PROJECT.balance;
set PROJECT.amount;
by account_id;
if last.account_id then output;
keep account_id balance;
run;
data PROJECT.payments_30;
set PROJECT.amount_30(where=(type='PRIJEM'));
by account_id;
if first.account_id then payment_30=0;
payment_30+amount;
if last.account_id then output;
keep account_id payment_30;
run;
data PROJECT.payments_60;
set PROJECT.amount_60(where=(type='PRIJEM'));
by account_id;
if first.account_id then payment_60=0;
payment_60+amount;
if last.account_id then output;
keep account_id payment_60;
run;
data PROJECT.payments_90;
set PROJECT.amount_90(where=(type='PRIJEM'));
by account_id;
if first.account_id then payment_90=0;
payment_90+amount;
if last.account_id then output;
keep account_id payment_90;
run;
data PROJECT.withdrawns_30;
set PROJECT.amount_30(where=(type='VYDAJ'));
by account_id;
if first.account_id then withdrawn_30=0;
withdrawn_30+amount;
if last.account_id then output;
keep account_id withdrawn_30;
run;
data PROJECT.withdrawns_60;
set PROJECT.amount_60(where=(type='VYDAJ'));
by account_id;
if first.account_id then withdrawn_60=0;
withdrawn_60+amount;
if last.account_id then output;
keep account_id withdrawn_60;
run;
data PROJECT.withdrawns_90;
set PROJECT.amount_90(where=(type='VYDAJ'));
by account_id;
if first.account_id then withdrawn_90=0;
withdrawn_90+amount;
if last.account_id then output;
keep account_id withdrawn_90;
run;
data PROJECT.result;
merge PROJECT.result(in=a) PROJECT.balance(in=b);
by account_id;
if a=b;
run;
data PROJECT.result;
merge PROJECT.result(in=a) PROJECT.payments_30(in=b);
by account_id;
if a;
run;
data PROJECT.result;
merge PROJECT.result(in=a) PROJECT.payments_60(in=b);
by account_id;
if a;
run;
data PROJECT.result;
merge PROJECT.result(in=a) PROJECT.payments_90(in=b);
by account_id;
if a;
run;
data PROJECT.result;
merge PROJECT.result(in=a) PROJECT.withdrawns_30(in=b);
by account_id;
if a;
run;
data PROJECT.result;
merge PROJECT.result(in=a) PROJECT.withdrawns_60(in=b);
by account_id;
if a;
run;
data PROJECT.result;
merge PROJECT.result(in=a) PROJECT.withdrawns_90(in=b);
by account_id;
if a;
run;
data PROJECT.result;
set PROJECT.result;
if frequency='POPLATEK MESICNE' then transaction_frequency=1;
else if frequency='POPLATEK TYDNE' then transaction_frequency=2;
else transaction_frequency=0;
run;
proc sql;
create table PROJECT.owner_district as select * from PROJECT.owner_account order by district_id;
quit;
data PROJECT.client_d;
merge PROJECT.owner_district(in=a) PROJECT.district(in=b rename=(A1=district_id));
by district_id;
if a=b;
run;
data PROJECT.client_district_s;
set PROJECT.client_d(rename=(A10=urban_level A11=salary_avg A12=unemployment_95 A13=unemployment_96 A14=enterprises));
keep account_id urban_level salary_avg unemployment_95 unemployment_96 enterprises;
run;
proc sql;
create table PROJECT.client_district as select * from PROJECT.client_district_s order by account_id;
quit;
data PROJECT.result;
merge PROJECT.result(in=a) PROJECT.client_district(in=b);
by account_id;
if a=b;
run;
data PROJECT.result;
set PROJECT.result;
if (year(credit_date)<1996) then unemployment=unemployment_95;
else unemployment=unemployment_96;
drop unemployment_95 unemployment_96;
run;
proc sql;
create table PROJECT.cards as select * from PROJECT.card order by disp_id;
quit;
data PROJECT.account_card;
merge PROJECT.owner_account(in=a) PROJECT.cards(in=b rename=(type=card_type));
by disp_id;
if a=b;
keep disp_id card_type issued;
run;
data PROJECT.result;
merge PROJECT.result(in=a) PROJECT.account_card(in=b);
by disp_id;
if a;
run;
data PROJECT.result;
set PROJECT.result;
if card_type='golden' and issued<credit_date then type_of_card=3;
else if card_type='classic' and issued<credit_date  then type_of_card=2;
else if card_type='junior' and issued<credit_date then type_of_card=1;
else type_of_card=0;
drop card_type issued;
run;
data PROJECT.insurrance_30;
set PROJECT.amount_30(where=(k_symbol='POJISTNE'));
by account_id;
if first.account_id then insurrance_30=0;
insurrance_30+amount;
if last.account_id then output;
keep account_id insurrance_30;
run;
data PROJECT.insurrance_90;
set PROJECT.amount_90(where=(k_symbol='POJISTNE'));
by account_id;
if first.account_id then insurrance_90=0;
insurrance_90+amount;
if last.account_id then output;
keep account_id insurrance_90;
run;
data PROJECT.result;
merge PROJECT.result(in=a) PROJECT.insurrance_30(in=b);
by account_id;
if a;
run;
data PROJECT.result;
merge PROJECT.result(in=a) PROJECT.insurrance_90(in=b);
by account_id;
if a;
run;
data PROJECT.result;
set PROJECT.result;
if insurrance_30='.' then insurrance_30=0;
if insurrance_90='.' then insurrance_90=0;
run;
data PROJECT.costs;
set PROJECT.order;
by account_id;
if first.account_id then household=0;
if first.account_id then all_costs=0;
if k_symbol='SIPO' then household+amount;
all_costs+amount;
if last.account_id then output;
keep account_id household all_costs;
run;

data PROJECT.household_avg;
set PROJECT.costs;
household_avg = round(household/all_costs,0.01);
keep account_id all_costs household_avg;
run;

data PROJECT.result;
merge PROJECT.result(in=a) PROJECT.household_avg(in=b);
by account_id;
if a=b;
run;
data PROJECT.client_crime;
set PROJECT.client_district;
crimes_rate_95=round(A15/10000,0.01);
crimes_rate_96=round(A16/10000,0.01);
keep account_id crimes_rate_95 crimes_rate_96;
run;
proc sql;
create table PROJECT.district_crime as select * from PROJECT.client_crime order by account_id;
quit;
data PROJECT.result;
merge PROJECT.result(in=a) PROJECT.district_crime(in=b);
by account_id;
if a=b;
run;
data PROJECT.result;
set PROJECT.result;
if (year(credit_date)<1996) then crimes_rate=crimes_rate_95;
else crimes_rate=crimes_rate_96;
drop crimes_rate_95 crimes_rate_96;
run;
data PROJECT.municipal;
set PROJECT.district;
if round(A8/(SUM(A5,A6,A7)),0.01)='.' then municipal=0;
else municipal = round(A8/(SUM(A5,A6,A7)),0.01);
KEEP A1 municipal;
run;
PROC SORT data=PROJECT.result;
by district_id;
run;
data PROJECT.result;
merge PROJECT.result(in=a) PROJECT.municipal(in=b rename=(A1=district_id));
by district_id;
if a=b;
run;
PROC SORT data=PROJECT.result;
by account_id;
run;
data PROJECT.sanctions;
set PROJECT.trans;
by account_id;
if first.account_id then sanction=0;
if k_symbol in ('UROK', 'SANKC') then sanction=1;
else sanction=0;
if last.account_id then output;
keep account_id sanction;
run;
data PROJECT.other_loan;
set PROJECT.trans;
by account_id;
if first.account_id then other_loan=0;
if k_symbol ='UVER' then other_loan=1;
else other_loan=0;
if last.account_id then output;
keep account_id other_loan;
run;
data PROJECT.result;
merge PROJECT.result(in=a) PROJECT.sanctions(in=b);
by account_id;
if a=b;
run;

data PROJECT.result;
merge PROJECT.result(in=a) PROJECT.other_loan(in=b);
by account_id;
if a=b;
run;