libname PROJEKT "C:\Users\student\Desktop\BI_projekt\dane\SAS version";

proc import datafile="C:\Users\student\Desktop\BI_projekt\dane\asc version\loan.asc"
out=PROJEKT.loan dbms=dlm replace;
delimiter=";";
getnames=yes;
run;

proc import datafile="C:\Users\student\Desktop\BI_projekt\dane\asc version\account.asc"
out=PROJEKT.account dbms=dlm replace;
delimiter=";";
getnames=yes;
run;

proc import datafile="C:\Users\student\Desktop\BI_projekt\dane\asc version\card.asc"
out=PROJEKT.card dbms=dlm replace;
delimiter=";";
getnames=yes;
run;

proc import datafile="C:\Users\student\Desktop\BI_projekt\dane\asc version\client.asc"
out=PROJEKT.client dbms=dlm replace;
delimiter=";";
getnames=yes;
run;

proc import datafile="C:\Users\student\Desktop\BI_projekt\dane\asc version\disp.asc"
out=PROJEKT.disp dbms=dlm replace;
delimiter=";";
getnames=yes;
run;

proc import datafile="C:\Users\student\Desktop\BI_projekt\dane\asc version\district.asc"
out=PROJEKT.district dbms=dlm replace;
delimiter=";";
getnames=yes;
run;

proc import datafile="C:\Users\student\Desktop\BI_projekt\dane\asc version\order.asc"
out=PROJEKT.order dbms=dlm replace;
delimiter=";";
getnames=yes;
run;

proc import datafile="C:\Users\student\Desktop\BI_projekt\dane\asc version\trans.asc"
out=PROJEKT.trans dbms=dlm replace;
delimiter=";";
getnames=yes;
run;


data PROJEKT.tabela (keep= account_id loan_id loan_status date data_loan data_kredyt);
set PROJEKT.loan;
if status in ('A', 'C') then loan_status=0;
if status in ('B', 'D') then loan_status=1;
data_loan=put(date,6.);
rok=substr(data_loan,1,2)+1900;
miesiac=substr(data_loan,3,2)*1;
dzien=substr(data_loan,5,2)*1;
data_kredyt=mdy(miesiac,dzien,rok);
format data_kredyt ddmmyy10.;
run;

proc sort data= PROJEKT.Account;
by account_id;
run;

proc sort data= PROJEKT.Tabela;
by account_id;
run;

data PROJEKT.tabela2;
merge PROJEKT.tabela (in=a)
PROJEKT.Account (in=b);
by account_id;
if a=1 and b=1;
run;

proc sort data= PROJEKT.Disp;
by account_id;
run;

proc sort data= PROJEKT.tabela2;
by account_id;
run;

data PROJEKT.tabela3;
merge PROJEKT.tabela2 (in=a)
PROJEKT.Disp (in=b);
by account_id;
if a=1 and b=1;
if type="OWNER" then output;
run;

proc sort data= PROJEKT.Client;
by client_id;
run;

proc sort data= PROJEKT.tabela3;
by client_id;
run;

data PROJEKT.tabela4;
merge PROJEKT.tabela3 (in=a)
PROJEKT.Client (in=b);
by client_id;
if a=1 and b=1;
run;

data PROJEKT.tabela4;
set PROJEKT.tabela4;
plec1 = substr(birth_number,3,2);
if plec1 >=50 then plec = "K";
else plec = "M";
rok=substr(birth_number,1,2);
rok=rok*1+1900;
if plec1 > 50 then miesiac = (plec1-50);
else miesiac = plec1;
dzien = substr(birth_number,5,2);
data_urodzenia = mdy(miesiac,dzien,rok);
wiek = 1999 - year(data_urodzenia);
format data_urodzenia DDMMYY10.;
run;

proc sort data= PROJEKT.Trans;
by account_id;
run;

proc sort data= PROJEKT.tabela4;
by account_id;
run;

data PROJEKT.tabela5;
merge PROJEKT.tabela4 (in=a)
PROJEKT.Trans (in=b);
by account_id;
if a=1 and b=1;
run;

proc sort data= PROJEKT.tabela5;
by descending loan_id descending date;
run;

data PROJEKT.tabela5;
set PROJEKT.tabela5;
if data_loan > date then output;
run;

data PROJEKT.tabela5;
set PROJEKT.tabela5;
by descending loan_id descending date;
if first.loan_id then output;
run;

data PROJEKT.Konto;
set PROJEKT.Account;
data_account=put(date,6.);
rok1=substr(data_account,1,2)+1900;
miesiac1=substr(data_account,3,2)*1;
dzien1=substr(data_account,5,2)*1;
data_zal_konta=mdy(miesiac1,dzien1,rok1);
format data_zal_konta ddmmyy10.;

proc sort data= PROJEKT.Konto;
by account_id;
run;

proc sort data= PROJEKT.tabela5;
by account_id;
run;

data PROJEKT.tabela6;
merge PROJEKT.tabela5 (in=a)
PROJEKT.Konto (in=b);
by account_id;
if a=1 and b=1;
run;

data PROJEKT.tabela7;
set PROJEKT.tabela6;
staz = (data_kredyt - data_zal_konta)/30;
run;

 proc sort data= PROJEKT.Card;
by disp_id;
run;

proc sort data= PROJEKT.Disp;
by disp_id;
run;

data PROJEKT.disp_card;
merge PROJEKT.Disp(in=a)
PROJEKT.Card (in=b);
by disp_id;
if a=1 and b=1;
run;

proc sort data= PROJEKT.Disp_card;
by account_id;
run;

proc sort data= PROJEKT.Account;
by account_id;
run;

data PROJEKT.Disp_card_account;
merge PROJEKT.Disp_card(in=a)
PROJEKT.Account (in=b);
by account_id;
if a=1 and b=1;
run;

proc sort data= PROJEKT.Disp_card_account;
by account_id;
run;

proc sort data= PROJEKT.Tabela7;
by account_id;
run;

data PROJEKT.tabela8;
merge PROJEKT.tabela7(in=a)
PROJEKT.Disp_card_account (in=b);
by account_id;
if a=1 and b=0;
run;

proc sort data= PROJEKT.District;
by A1;
run;

proc sort data= PROJEKT.Tabela7;
by district_id;
run;

data PROJEKT.tabela8 (keep= frequency plec A11 A16 A13 balance wiek staz loan_status);
merge PROJEKT.tabela7 (in=a)
PROJEKT.District (rename= (A1=district_id) in=b);
by district_id;
if a=1 and b=1;
run;