proc sql;
create table projekt.trantypes as
select loan.loan_id
,SIPO as Oplaty_mieszkanie
,POJISTNE as Oplaty_ubezpieczenie
,SLUZBY as Oplaty_wyciag
,UROK as Odsetki
,SANKC_UROK as Odsetki_karne
,DUCHOD as Emerytury
,UVER as Kredyty
from dane.loan
left join (select loan_id, count(k_symbol) as SIPO
    from dane.loan
    left join dane.trans
    on loan.account_id=trans.account_id
    where trans.date<=loan.date
    group by loan_id, trans.k_symbol
    having k_symbol like 'SIPO') as SIPO
on sipo.loan_id=loan.loan_id
left join (select loan_id, count(k_symbol) as POJISTNE
    from dane.loan
    left join dane.trans
    on loan.account_id=trans.account_id
    where trans.date<=loan.date
    group by loan_id, trans.k_symbol
    having k_symbol like 'POJISTNE') as POJISTNE
on POJISTNE.loan_id=loan.loan_id
left join (select loan_id, count(k_symbol) as SLUZBY
    from dane.loan
    left join dane.trans
    on loan.account_id=trans.account_id
    where trans.date<=loan.date
    group by loan_id, trans.k_symbol
    having k_symbol like 'SLUZBY') as SLUZBY
on SLUZBY.loan_id=loan.loan_id
left join (select loan_id, count(k_symbol) as UROK
    from dane.loan
    left join dane.trans
    on loan.account_id=trans.account_id
    where trans.date<=loan.date
    group by loan_id, trans.k_symbol
    having k_symbol like 'UROK') as UROK
on UROK.loan_id=loan.loan_id
left join (select loan_id, count(k_symbol) as SANKC_UROK
    from dane.loan
    left join dane.trans
    on loan.account_id=trans.account_id
    where trans.date<=loan.date
    group by loan_id, trans.k_symbol
    having k_symbol like 'SANKC. UROK') as SANKC_UROK
on SANKC_UROK.loan_id=loan.loan_id
left join (select loan_id, count(k_symbol) as DUCHOD
    from dane.loan
    left join dane.trans
    on loan.account_id=trans.account_id
    where trans.date<=loan.date
    group by loan_id, trans.k_symbol
    having k_symbol like 'DUCHOD') as DUCHOD
on DUCHOD.loan_id=loan.loan_id
left join (select loan_id, count(k_symbol) as UVER
    from dane.loan
    left join dane.trans
    on loan.account_id=trans.account_id
    where trans.date<=loan.date
    group by loan_id, trans.k_symbol
    having k_symbol like 'UVER') as UVER
on UVER.loan_id=loan.loan_id;
quit;