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;