LIBNAME PRO '/folders/myshortcuts/SAS_myfolder/tabele';

data PRO.Tabela;
set Pro.Loan;
if status='A' or status='C' then loan_status=0;
else loan_status=1;
run;
proc sort data=Pro.Tabela;
by status;
run;
data pro.client_disp;
merge pro.client(in=a) pro.disp(in=b);
by client_id;
if a=b;
run;
proc sort data=pro.tabela;
by account_id;
run;
proc sort data=pro.account;
by account_id;
run;
data pro.client_account;
merge pro.client_disp(in=a) pro.account(in=b);
by account_id;
if a=b;
run;
proc sql;
create table pro.account_client as select * from pro.client_account where type='OWNER';
quit;
data pro.tabela_a; /*tabela na razie wystarczy */
merge pro.tabela(in=b rename=(date=credit_date)) pro.account_client(in=c);
by account_id;
if b;
run;
data pro.tabela_a;
set pro.tabela_a;
if(birth_number-(round(birth_number/10000))*10000)<0 then sex=1; /*kobieta*/
else sex=0;
age=year(credit_date)-(1900+round(birth_number/10000));
client_since=round((credit_date-date)/12);
run;
proc sort data=pro.trans;
by account_id;
run;
proc sort data=pro.loan;
by account_id;
run;
data pro.trans_cred;
merge pro.loan(in=a rename=(date=credit_date)) pro.trans(in=b);
by account_id;
if a=b;
run;
data pro.trans_cred;
set pro.trans_cred;
where date < credit_date;
run;
data pro.trans_cred_30;
set pro.trans_cred;
where date < credit_date and date > (credit_date -30);
run;
data pro.trans_cred_60;
set pro.trans_cred;
where date < credit_date and date > (credit_date -60);
run;
data pro.trans_cred_90;
set pro.trans_cred;
where date < credit_date and date > (credit_date -90);
run;
data pro.balance;
set pro.trans_cred;
by account_id;
if last.account_id then output;
keep account_id balance;
run;
data pro.credits_30;
set pro.trans_cred_30(where=(type='PRIJEM'));
by account_id;
if first.account_id then sum_credit_30=0;
sum_credit_30+amount;
if last.account_id then output;
keep account_id sum_credit_30;
run;
data pro.credits_60;
set pro.trans_cred_60(where=(type='PRIJEM'));
by account_id;
if first.account_id then sum_credit_60=0;
sum_credit_60+amount;
if last.account_id then output;
keep account_id sum_credit_60;
run;
data pro.credits_90;
set pro.trans_cred_90(where=(type='PRIJEM'));
by account_id;
if first.account_id then sum_credit_90=0;
sum_credit_90+amount;
if last.account_id then output;
keep account_id sum_credit_90;
run;
data pro.withdrawn_30;
set pro.trans_cred_30(where=(type='VYDAJ'));
by account_id;
if first.account_id then sum_withdrawn_30=0;
sum_withdrawn_30+amount;
if last.account_id then output;
keep account_id sum_withdrawn_30;
run;
data pro.withdrawn_60;
set pro.trans_cred_60(where=(type='VYDAJ'));
by account_id;
if first.account_id then sum_withdrawn_60=0;
sum_withdrawn_60+amount;
if last.account_id then output;
keep account_id sum_withdrawn_60;
run;
data pro.withdrawn_90;
set pro.trans_cred_90(where=(type='VYDAJ'));
by account_id;
if first.account_id then sum_withdrawn_90=0;
sum_withdrawn_90+amount;
if last.account_id then output;
keep account_id sum_withdrawn_90;
run;
data pro.tabela_a;
merge pro.tabela_a(in=a) pro.balance(in=b);
by account_id;
if a=b;
run;
data pro.tabela_a;
merge pro.tabela_a(in=a) pro.credits_30(in=b);
by account_id;
if a;
run;
data pro.tabela_a;
merge pro.tabela_a(in=a) pro.credits_60(in=b);
by account_id;
if a;
run;
data pro.tabela_a;
merge pro.tabela_a(in=a) pro.credits_90(in=b);
by account_id;
if a;
run;
data pro.tabela_a;
merge pro.tabela_a(in=a) pro.withdrawn_30(in=b);
by account_id;
if a;
run;
data pro.tabela_a;
merge pro.tabela_a(in=a) pro.withdrawn_60(in=b);
by account_id;
if a;
run;
data pro.tabela_a;
merge pro.tabela_a(in=a) pro.withdrawn_90(in=b);
by account_id;
if a;
run;
data pro.tabela_a;
set pro.tabela_a;
if frequency='POPLATEK MESICNE' then freq_issuance=1;
else if frequency='POPLATEK TYDNE' then freq_issuance=2;
else freq_issuance=0;
run;
proc sql;
create table pro.account_client_d as select * from pro.account_client order by district_id;
quit;
data pro.client_district;
merge pro.account_client_d(in=a) pro.district(in=b rename=(A1=district_id));
by district_id;
if a=b;
run;
data pro.client_district;
set pro.client_district(rename=(A11=avg_salary A12=unempl_rate_95 A13=unempl_rate_96 A14=enterpr_rate));
keep account_id avg_salary unempl_rate_95 unempl_rate_96 entepr_rate;
run;
proc sql;
create table pro.client_distr as select * from pro.client_district order by account_id;
quit;
data pro.tabela_a;
merge pro.tabela_a(in=a) pro.client_distr(in=b);
by account_id;
if a=b;
run;
data pro.tabela_a;
set pro.tabela_a;
if (year(credit_date)<1996) then unempl_rate=unempl_rate_95;
else unempl_rate=unempl_rate_96;
drop unempl_rate_95 unempl_rate_96;
run;
proc sql;
create table pro.cards as select * from pro.card order by disp_id;
quit;
data pro.account_card;
merge pro.account_client(in=a) pro.cards(in=b rename=(type=card_type));
by disp_id;
if a=b;
keep disp_id card_type issued;
run;
data pro.tabela_a;
merge pro.tabela_a(in=a) pro.account_card(in=b);
by disp_id;
if a;
run;
data pro.tabela_a;
set pro.tabela_a;
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 pro.insurrance_30;
set pro.trans_cred_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 pro.insurrance_90;
set pro.trans_cred_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 pro.tabela_a;
merge pro.tabela_a(in=a) pro.insurrance_30(in=b);
by account_id;
if a;
run;
data pro.tabela_a;
merge pro.tabela_a(in=a) pro.insurrance_90(in=b);
by account_id;
if a;
run;
data pro.tabela_a;
set pro.tabela_a;
if insurrance_30='.' then insurrance_30=0;
if insurrance_90='.' then insurrance_90=0;
run;

data pro.costs;
set pro.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 pro.household_avg;
set pro.costs;
household_avg = household/all_costs;
keep account_id all_costs household_avg;
run;

data pro.tabela_a;
merge pro.tabela_a(in=a) pro.household_avg(in=b);
by account_id;
if a=b;