Hello experts,
I am working for SAS IFRS 9 for my company and there is some bottleneck about the code given, it runs too slow and I am trying to reduce the run time. It would be great if you have any ideas.
OPTIONS COMPRESS=YES mprint mlogic;
%include "/sasdata/sasinput/%left(&sst_current_project_id.)/ifrs9_00_init.sas";
%MACRO RECOVERY_RATE(PORTFOLIO);
DATA _NULL_ ;
SET SAS_COLL.RR_PARAMETER;
IF RUN_DATE="&sst_as_of_dt"D AND PORTFOLIO_FLAG="&PORTFOLIO.";
CALL SYMPUT ("mcr_mortgage_constant",mortgage_constant);
CALL SYMPUT ("mcr_car_constant",car_constant);
CALL SYMPUT ("mcr_unsecured",unsecured);
CALL SYMPUT ("mcr_prev_unsecured",prev_unsecured);
CALL SYMPUT ("mcr_filter",FILTER);
CALL SYMPUT ("mcr_prev_data_date",PUT(prev_data_date,DATETIME20.));
RUN;
%PUT MORTGAGE= &mcr_mortgage_constant.;
%PUT CAR= &mcr_car_constant.;
%PUT UNSECURED= &mcr_unsecured;
%PUT prev_unsecured= &mcr_prev_unsecured;
%PUT mcr_prev_data_date= &mcr_prev_data_date;
Data CASHFLOW_%left(&mcr_part_id.);
Set SAS_CASH.CASHFLOW_%left(&mcr_part_id.);
BY INSTID;
IF FIRST.INSTID THEN OUTPUT;
RUN;
PROC SQL;
CREATE TABLE WORK.OZET AS
SELECT DISTINCT t1.DATA_DATE,
t1.COUNTERPARTY_ID,
t1.CREDIT_TYPE,
1 AS FLAG,
(SUM(t2.VALAMOUNT)) FORMAT=22.6 AS SUM_of_EAD
FROM SAS_PORT.Portfolio_%left(&mcr_part_id.) t1
LEFT JOIN CASHFLOW_%left(&mcr_part_id.) T2
ON T1.INSTID=T2.INSTID
WHERE t1.RETAIL_FLAG = 0 AND t1.CREDIT_TYPE IN
(
'Cash',
'Non Cash'
'Commitment'
) AND t1.PORTFOLIO_ADJUSTED IN
(
&mcr_filter.
)
AND T2.VALAMOUNT NOT IS MISSING AND t1.GL_ACCOUNT_NUM NOT LIKE '17%'
GROUP BY t1.DATA_DATE,
t1.COUNTERPARTY_ID,
t1.CREDIT_TYPE;
QUIT;
PROC SQL;
CREATE VIEW SORTED_OZET AS
SELECT DATA_DATE, COUNTERPARTY_ID, SUM_of_EAD, /*CREDIT_TYPE,*/ FLAG,
CASE WHEN CREDIT_TYPE = "Non Cash" THEN "Non_Cash"
ELSE CREDIT_TYPE
END AS CREDIT_TYPE
FROM WORK.OZET ;
QUIT;
PROC TRANSPOSE DATA=SORTED_OZET
OUT=TRANSPOZED_BALANCE (DROP=SOURCE)
NAME=SOURCE ;
BY DATA_DATE COUNTERPARTY_ID FLAG ;
ID CREDIT_TYPE ;
VAR SUM_of_EAD ;
RUN; QUIT;
/*KK CIKAR*/
PROC SQL;
CREATE TABLE OZET_KKSIZ AS
SELECT DISTINCT T1.DATA_DATE,
T1.COUNTERPARTY_ID,
T1.CREDIT_TYPE,
3 AS FLAG,
/* SUM_of_EAD */
(SUM(t2.VALAMOUNT)) FORMAT=22.6 AS SUM_of_EAD
FROM SAS_PORT.Portfolio_%left(&mcr_part_id.) t1
LEFT JOIN CASHFLOW_%left(&mcr_part_id.) T2
ON T1.INSTID=T2.INSTID
WHERE t1.RETAIL_FLAG = 0 AND t1.CREDIT_TYPE IN
(
'Cash',
'Non Cash'
'Commitment'
) AND t1.PORTFOLIO_ADJUSTED IN
(
&mcr_filter.
)
AND T2.VALAMOUNT NOT IS MISSING AND t1.GL_ACCOUNT_NUM NOT LIKE '17%' AND RETAIL_TYPE NOT IN ('Kurumsal KK')
GROUP BY t1.DATA_DATE,
t1.COUNTERPARTY_ID,
t1.CREDIT_TYPE;
QUIT;
PROC SQL;
CREATE VIEW SORTED_OZET_KKSIZ AS
SELECT DATA_DATE, COUNTERPARTY_ID, SUM_of_EAD, /*CREDIT_TYPE,*/ FLAG,
CASE WHEN CREDIT_TYPE = "Non Cash" THEN "Non_Cash"
ELSE CREDIT_TYPE
END AS CREDIT_TYPE
FROM OZET_KKSIZ ;
QUIT;
PROC TRANSPOSE DATA=SORTED_OZET_KKSIZ
OUT=TRANSPOZED_BALANCE_KKSIZ (DROP=SOURCE)
NAME=SOURCE;
BY DATA_DATE COUNTERPARTY_ID FLAG ;
ID CREDIT_TYPE ;
VAR SUM_of_EAD ;
RUN; QUIT;
/**/
/*append datası*/
PROC SQL;
CREATE TABLE WORK.APPEND AS
SELECT T1.DATA_DATE,
T1.COUNTERPARTY_ID,
T1.CASH,
T1.NON_CASH
FROM WORK.TRANSPOZED_BALANCE T1
FULL JOIN WORK.TRANSPOZED_BALANCE_KKSIZ T2 ON (T1.COUNTERPARTY_ID = T2.COUNTERPARTY_ID)
WHERE T1.FLAG = 1 AND T2.FLAG NOT = 3;
QUIT;
/**/
PROC APPEND DATA=APPEND BASE=TRANSPOZED_BALANCE_KKSIZ;
RUN;
PROC SQL;
CREATE TABLE COLLATERAL_IPOTEK AS
SELECT * FROM SAS_COLL.Collateral_%left(&mcr_part_id.) t1
WHERE t1.RISK_MITIGANT_TYPE = 'IPOTEK';
QUIT;
PROC SQL;
CREATE TABLE COLLATERALINTERMEDIATESTEP AS
SELECT DISTINCT
t1.COUNTERPARTY_ID,
t1.RISK_MITIGANT_TYPE,
(SUM(t1.CGF_EXP_SHARED_AMT)) FORMAT=19.2 AS SUM_of_CGF_EXP_SHARED_AMT1
,CASE
WHEN CALCULATED SUM_OF_CGF_EXP_SHARED_AMT1=. THEN 0
ELSE CALCULATED SUM_OF_CGF_EXP_SHARED_AMT1
END AS SUM_OF_CGF_EXP_SHARED_AMT
FROM COLLATERAL_IPOTEK t1
GROUP BY t1.COUNTERPARTY_ID,
t1.RISK_MITIGANT_TYPE;
QUIT;
PROC SQL;
CREATE TABLE COLLATERAL_IPOTEK2 AS
SELECT T1.COUNTERPARTY_ID AS MOBIS,
T1.DATA_DATE AS TARIH,
T1.RISK_MITIGANT_TYPE AS GUV_TUR_KODU,
/* Teminat_TL_Tut */
(CASE
WHEN T1.COLLATERAL_AMT=. THEN 0
ELSE T1.COLLATERAL_AMT
END)
FORMAT=NEGPAREN18. AS TEMINAT_TL_PRE, T1.CURRENCY_CD AS PARA_KODU, T1.COLLATERAL_ID AS TEMINAT_NO,
T1.INSURANCE_MATURITY_DATE AS SIGORTA_POLICE_VADE, /* Toplam_Ekspertiz_Tut */
(CASE
WHEN t1.MORTGAGE_TOTAL_EXPERT_AMNT_TL=. THEN 0
ELSE t1.MORTGAGE_TOTAL_EXPERT_AMNT_TL
END)
FORMAT=NEGPAREN18. AS Toplam_Ekspertiz_Tut, /* Gayrimenkul_Tür */
(CASE
WHEN t1.REAL_ESTATE_MORG_TYPE=1 THEN 'Ev'
WHEN t1.REAL_ESTATE_MORG_TYPE=2 THEN 'Arsa'
WHEN t1.REAL_ESTATE_MORG_TYPE=3 THEN 'İşyeri'
ELSE ''
END)
AS GAYRIMENKUL_TUR, t1.MORTGAGE_EXPERT_DATE AS EKSPERTIZ_TARIHI, T1.REIDEN_YN AS REIDEN_E_H,
t1.CAR_YN AS SYR_E_H, t1.MORTGAGE_RANK AS IPOTEK_DERECESI,
t1.RELATED_COLLATERAL_ID AS ILISKILI_TEMINAT_NO, /* Kullanılan_Tut */
(CASE
WHEN t1.USED_AMOUNT=. THEN 0
ELSE t1.USED_AMOUNT
END)
FORMAT=NEGPAREN18. AS KULLANILAN_TUT, t1.CAR_INSURANCE_FLG AS GECERSIZ_SIG_POL_VD,
t1.CAR_EXPER_FLG AS GECERSIZ_EKS_VD, /* Pol_Vade */
(CASE
WHEN t1.REAL_ESTATE_MORG_TYPE=2 and (t1.INSURANCE_MATURITY_DATE IS MISSING OR T1.INSURANCE_MATURITY_DATE>=t1.DATA_DATE) THEN "OK"
WHEN t1.REAL_ESTATE_MORG_TYPE IN (1,3) and t1.INSURANCE_MATURITY_DATE=. THEN "Not OK"
WHEN t1.REAL_ESTATE_MORG_TYPE IN (1,3) and t1.INSURANCE_MATURITY_DATE>=t1.DATA_DATE THEN "OK"
ELSE "Not OK"
END )
AS Pol_Vade, /* Eks_Vade */
(CASE
WHEN t1.REAL_ESTATE_MORG_TYPE=1 and (((t1.DATA_DATE- t1.MORTGAGE_EXPERT_DATE)/86400)/365)<=3 THEN "OK"
WHEN t1.REAL_ESTATE_MORG_TYPE IN (2,3) and (((t1.DATA_DATE- t1.MORTGAGE_EXPERT_DATE)/86400)/365)<=1 THEN "OK"
ELSE "Not OK"
END)
AS Eks_Vade , (CASE
WHEN REIDEN_YN='Y' THEN 'OK'
WHEN REIDEN_YN='N' and CALCULATED Eks_Vade='OK' THEN 'OK'
WHEN REIDEN_YN='N' and CALCULATED Eks_Vade='Not OK' THEN 'Not OK'
ELSE 'Not OK'
END )
AS Reiden_Eks_Tr,
(CASE
WHEN CALCULATED Reiden_Eks_Tr='Not OK' THEN CALCULATED Teminat_TL_Pre*0.85 /*Paraemetize edilecek - Tablodan deger alma*/
ELSE CALCULATED Teminat_TL_Pre
END)
AS Teminat_TL_Tut
FROM COLLATERAL_IPOTEK t1
WHERE CALCULATED POL_VADE = 'OK';
QUIT;
PROC SQL;
CREATE TABLE COLLATERAL_IPOTEK3 AS
SELECT DISTINCT t1.Mobis,
t1.ILISKILI_TEMINAT_NO,
/* SUM_of_Teminat_TL_Tut */
(SUM(t1.Teminat_TL_Tut)) FORMAT=NEGPAREN18. AS SUM_of_Teminat_TL_Tut, /* AVG_of_Toplam_Ekspertiz_Tut */
(MIN(t1.Toplam_Ekspertiz_Tut)) FORMAT=NEGPAREN18. AS AVG_of_Toplam_Ekspertiz_Tut, /* SUM_of_Kullanılan_Tut */
(SUM(t1.KULLANILAN_TUT)) FORMAT=NEGPAREN18. AS SUM_OF_KULLANILAN_TUT
FROM COLLATERAL_IPOTEK2 t1 WHERE t1.ILISKILI_TEMINAT_NO NOT = . GROUP BY t1.Mobis, t1.ILISKILI_TEMINAT_NO;
QUIT;
PROC SQL;
CREATE TABLE SONUC1 AS
SELECT T1.MOBIS,
/* Key */
('Mortgage') AS KEY, /* TL_TEMINAT_TUT */
(MIN(T1.SUM_OF_TEMINAT_TL_TUT, T1.AVG_OF_TOPLAM_EKSPERTIZ_TUT)) FORMAT=NEGPAREN18. AS TL_Teminat_Tut
FROM COLLATERAL_IPOTEK3 t1;
QUIT;
PROC SQL;
CREATE TABLE COLLATERAL_IPOTEK4 AS
SELECT DISTINCT t1.Mobis,
t1.Teminat_No,
t1.IPOTEK_DERECESI,
/* SUM_of_Teminat_TL_Tut */
(SUM(t1.Teminat_TL_Tut)) FORMAT=NEGPAREN18. AS SUM_of_Teminat_TL_Tut, /* AVG_of_Toplam_Ekspertiz_Tut */
(AVG(t1.Toplam_Ekspertiz_Tut)) FORMAT=NEGPAREN18. AS AVG_of_Toplam_Ekspertiz_Tut, /* SUM_of_Kullanılan_Tut */
(SUM(t1.KULLANILAN_TUT)) FORMAT=NEGPAREN18. AS SUM_OF_KULLANILAN_TUT,
(CASE
WHEN CALCULATED SUM_of_Teminat_TL_Tut-CALCULATED SUM_OF_KULLANILAN_TUT <0 THEN 0
ELSE CALCULATED SUM_of_Teminat_TL_Tut-CALCULATED SUM_OF_KULLANILAN_TUT
END )
FORMAT=NEGPAREN18. AS Kalan_Teminat_TL_Tut, /* Teminat_TL_Tut */
(MIN((CASE
WHEN CALCULATED SUM_of_Teminat_TL_Tut-CALCULATED SUM_OF_KULLANILAN_TUT <0 THEN 0
ELSE CALCULATED SUM_of_Teminat_TL_Tut-CALCULATED SUM_OF_KULLANILAN_TUT END ),
CALCULATED AVG_of_Toplam_Ekspertiz_Tut)) FORMAT=NEGPAREN18. AS Teminat_TL_Tut
FROM COLLATERAL_IPOTEK2 t1
WHERE t1.ILISKILI_TEMINAT_NO = . AND t1.IPOTEK_DERECESI IN ( 1, 2, 3 ) GROUP BY t1.Mobis, t1.Teminat_No, t1.IPOTEK_DERECESI;
QUIT;
PROC SQL;
CREATE TABLE SONUC2 AS
SELECT T1.MOBIS,
/* Key */
('Mortgage') AS KEY, T1.TEMINAT_TL_TUT AS TL_TEMINAT_TUT FROM COLLATERAL_IPOTEK4 T1;
QUIT;
PROC SQL;
CREATE TABLE WORK.APPEND_TABLE AS
SELECT * FROM WORK.SONUC1
OUTER UNION CORR
SELECT * FROM WORK.SONUC2;
QUIT;
PROC SQL;
CREATE TABLE WORK.IPOTEK_SONUC AS
SELECT DISTINCT t1.Mobis,
t1.Key,
/* TL_Teminat_Tut */
(SUM(t1.TL_Teminat_Tut)) FORMAT=NEGPAREN18. AS TL_Teminat_Tut2
FROM WORK.APPEND_TABLE t1 GROUP BY t1.Mobis, t1.Key;
QUIT;
PROC SQL;
CREATE TABLE IPOTEK_SONUC2(DROP=TL_TEMINAT_TUT2 TL_TEMINAT_TUT3) AS
SELECT T1.*,
(TL_TEMINAT_TUT2-SUM_OF_CGF_EXP_SHARED_AMT) AS TL_TEMINAT_TUT3,
CASE
WHEN CALCULATED TL_TEMINAT_TUT3 <0 THEN 0
ELSE CALCULATED TL_TEMINAT_TUT3
END
AS TL_TEMINAT_TUT
FROM IPOTEK_SONUC T1
LEFT JOIN COLLATERALINTERMEDIATESTEP T2
ON T1.MOBIS=T2.COUNTERPARTY_ID;
QUIT;
PROC SQL;
CREATE TABLE COLLATERAL_IPOTEK_HARIC AS
SELECT * FROM SAS_COLL.COLLATERAL_%left(&mcr_part_id.) t1
WHERE t1.RISK_MITIGANT_TYPE NOT = 'IPOTEK';
QUIT;
PROC SQL;
CREATE TABLE IPOTEK_HARIC AS
SELECT DISTINCT t1.COUNTERPARTY_ID AS Mobis,
t2.Key,
/* TL_Teminat_Tut */
(SUM(t1.COLLATERAL_AMT)) FORMAT=NEGPAREN18. AS TL_Teminat_Tut
FROM COLLATERAL_IPOTEK_HARIC t1
LEFT JOIN SAS_COLL.TEMINAT_DONUSUM_TABLOSU_%left(&mcr_part_id.)/*KGF TEMINAT TABLOSU - IS BIRIMI YENI BIR BILGI GELDIGI ZAMAN EKLEME YA DA DEGISTIRME YAPABILECEK*/
t2 ON (t1.RISK_MITIGANT_TYPE = t2.Guv_Tur_Kodu)
GROUP BY t1.COUNTERPARTY_ID,
t2.Key;
QUIT;
PROC SQL;
CREATE TABLE APPEND_TOTAL AS
SELECT * FROM WORK.IPOTEK_HARIC
OUTER UNION CORR
SELECT * FROM WORK.IPOTEK_SONUC2;
Quit;
PROC SQL;
CREATE TABLE APPEND_TOTAL2 AS
SELECT DISTINCT T1.MOBIS,
/* T1.KEY, */
CASE
WHEN KEY = 'Bank Guarantee' THEN 'Bank_Guarantee'
WHEN KEY = 'Cash and Cash Equivalents' THEN 'Cash_and_Cash_Equivalents'
WHEN KEY = 'Car Pledge' THEN 'Car_Pledge'
ELSE KEY
END AS KEY,
/* TL_TEMINAT_TUT */
(SUM(T1.TL_TEMINAT_TUT)) FORMAT=NEGPAREN18. AS TL_TEMINAT_TUT FROM APPEND_TOTAL T1
WHERE T1.KEY IN ( 'Bank Guarantee',
'Car Pledge',
'Cash and Cash Equivalents',
'KGF_Specific',
'Other',
'Mortgage',
'Surety' ) GROUP BY t1.Mobis, t1.Key;
QUIT;
PROC TRANSPOSE DATA=APPEND_TOTAL2
OUT=TRNSTRANSPOSED(LABEL="Transposed WORK.QUERY_FOR_APPEND_TABLE_0000")
NAME=SOURCE
LABEL=LABEL;
BY MOBIS;
ID KEY;
VAR TL_TEMINAT_TUT;
RUN;
QUIT;
PROC SQL;
CREATE TABLE MOBIS_ANAHTAR AS /*excel ila almayıp, hesaplama süresinde alabiliriz*/
SELECT DISTINCT t1.MOBIS AS COUNTERPARTY_ID
FROM SAS_COLL.MOBIS_ANAHTAR_%left(&mcr_part_id.) t1;
QUIT;
PROC SQL;
CREATE TABLE KGF_MIN_CHECK AS
SELECT A.COUNTERPARTY_ID,
SUM(A.EAD) FORMAT=22.6 AS SUM_OF_EAD
FROM (SELECT T2.COUNTERPARTY_ID,
T1.MOBISNO AS TEBCOUNTERPARTY_ID,
T2.ACCOUNT_NUMBER,
T1.HESAPNO AS TEBACCOUNT_NUMBER,
T3.VALAMOUNT AS EAD
FROM SAS_COLL.KGF_202006 T1
LEFT JOIN SAS_PORT.Portfolio_%left(&mcr_part_id.) T2
ON T1.HESAPNO=T2.ACCOUNT_NUMBER AND T1.MOBISNO=T2.COUNTERPARTY_ID
LEFT JOIN CASHFLOW_%left(&mcr_part_id.) T3
ON T3.INSTID=T2.INSTID) A
GROUP BY A.COUNTERPARTY_ID;
QUIT;
PROC SQL;
CREATE TABLE MOBIS_INTERMEDIATE AS
SELECT t1.COUNTERPARTY_ID,
/* Bank Guarantee */
(CASE
WHEN t2.Bank_Guarantee=. THEN 0
ELSE t2.Bank_Guarantee
END)
FORMAT=NEGPAREN18. AS Bank_Guarantee, /* Car Pledge */
(CASE
WHEN t2.Car_Pledge=. THEN 0
ELSE t2.Car_Pledge
END)
FORMAT=NEGPAREN18. AS Car_Pledge,/* Cash and Cash Equivalents */
(CASE
WHEN t2.Cash_and_Cash_Equivalents=. THEN 0
ELSE t2.Cash_and_Cash_Equivalents
END)
FORMAT=NEGPAREN18. AS Cash_and_Cash_Equivalents, /* Kefalet */
(CASE
WHEN t2.Surety=. THEN 0
ELSE t2.Surety
END)
FORMAT=NEGPAREN18. AS Kefalet, /* Mortgage */
(CASE
WHEN t2.Mortgage=. THEN 0
ELSE t2.Mortgage
END)
FORMAT=NEGPAREN18. AS Mortgage, /* Other */
(CASE
WHEN t2.Other=. THEN 0
ELSE t2.Other
END)
FORMAT=NEGPAREN18. AS Other, /* KGF */
(CASE
WHEN t2.KGF_Specific=. THEN 0
ELSE t2.KGF_Specific
END)
FORMAT=NEGPAREN18. AS KGF FROM MOBIS_ANAHTAR t1 LEFT JOIN TRNSTRANSPOSED t2 ON (t1.COUNTERPARTY_ID = t2.Mobis);
QUIT;
PROC SQL;
CREATE TABLE MOBIS_FINAL AS
SELECT T1.*,
COALESCE(T2.SUM_OF_EAD,0) AS SUM_OF_EAD,
CASE
WHEN T1.KGF>0 AND T2.SUM_of_EAD=. THEN 0
ELSE MIN(T2.SUM_of_EAD,T1.KGF)
END AS KGFFINAL
FROM MOBIS_INTERMEDIATE T1
LEFT JOIN KGF_MIN_CHECK T2
ON T1.COUNTERPARTY_ID=T2.COUNTERPARTY_ID;
QUIT;
PROC SORT DATA=SAS_COLL.GUARANTEE_RR_%LEFT(&mcr_part_id.)
(WHERE=(DATA_DATE="&mcr_prev_data_date."DT))
OUT=CURRENT_GUARANTEE_RR(RENAME=(MUSNO=COUNTERPARTY_ID)) ; BY MUSNO;
RUN;
PROC SORT DATA=MOBIS_FINAL; BY COUNTERPARTY_ID;
RUN;
PROC SORT DATA=TRANSPOZED_BALANCE; BY COUNTERPARTY_ID;
RUN;
DATA SAS_COLL.RR_ADJUSTMENT_%LEFT(&mcr_part_id.);
MERGE MOBIS_FINAL (IN=cr)
CURRENT_GUARANTEE_RR (IN=rr)
TRANSPOZED_BALANCE(IN=bl);
BY COUNTERPARTY_ID;
LENGTH TOPLAM_BALANCE 8.;
IF CASH=. THEN CASH=0;
else if Non_cash=. then Non_cash=0;
else if Commitment=. then Commitment=0;
TOPLAM_BALANCE=SUM(CASH,Non_cash,Commitment);
CASH_MIN=MIN(COALESCE(TOPLAM_BALANCE,0),COALESCE(Cash_and_Cash_Equivalents,0)); /*l*/
BANK_MIN=MIN((TOPLAM_BALANCE-CASH_MIN),COALESCE(Bank_Guarantee,0));/*m*/
KGF_MIN=MIN((TOPLAM_BALANCE-CASH_MIN),COALESCE(KGFFINAL,0));/*n*/
IF COALESCE(TOPLAM_BALANCE,0)-CASH_MIN-BANK_MIN-KGF_MIN= 0 THEN IPOTEK_CHECK=0 ;
ELSE IPOTEK_CHECK=COALESCE(MORTGAGE,0)/(COALESCE(TOPLAM_BALANCE,0)-CASH_MIN-BANK_MIN-KGF_MIN); /*N*/
IF COALESCE(TOPLAM_BALANCE,0)-CASH_MIN-BANK_MIN-KGF_MIN= 0 THEN CAR_CHECK=0 ;
ELSE CAR_CHECK=COALESCE(Car_Pledge,0)/(COALESCE(TOPLAM_BALANCE,0)-CASH_MIN-BANK_MIN-KGF_MIN); /*O*/
IF COALESCE(TOPLAM_BALANCE,0)-CASH_MIN-BANK_MIN-KGF_MIN= 0 THEN IPOTEK_CAR_CHECK=0 ;
ELSE IPOTEK_CAR_CHECK=(COALESCE(Car_Pledge,0)+COALESCE(MORTGAGE,0))/(COALESCE(TOPLAM_BALANCE,0)-CASH_MIN-BANK_MIN-KGF_MIN); /*R*/
IF IPOTEK_CHECK>=0.99 THEN IPOTEK_FLAG="Yes"; ELSE IPOTEK_FLAG="No"; /*p*/
IF CAR_CHECK>=0.99 THEN CAR_FLAG="Yes"; else car_flag="No"; /*q*/
IF IPOTEK_FLAG="Yes" then S=MIN(COALESCE(MORTGAGE,0),(COALESCE(TOPLAM_BALANCE,0)-CASH_MIN-BANK_MIN-KGF_MIN)) ;ELSE S=0; /*S*/
IF CAR_FLAG="Yes" then T=MIN(COALESCE(Car_Pledge,0),TOPLAM_BALANCE-CASH_MIN-BANK_MIN-KGF_MIN) ;ELSE T=0; /*t*/
IF IPOTEK_FLAG="No" AND CAR_FLAG="No" and ABS(IPOTEK_CAR_CHECK)>=0.99 THEN
U=MIN((Car_Pledge+MORTGAGE),(TOPLAM_BALANCE-CASH_MIN-BANK_MIN-KGF_MIN)) ; ELSE U=0 ;
V=COALESCE(TOPLAM_BALANCE,0)-CASH_MIN-BANK_MIN-KGF_MIN-S-T-U ; /*V*/
IF TOPLAM_BALANCE NOT IN (0) THEN
RR_DISCOUNTED=((CASH_MIN*0.99)+(KGF_MIN*0.98) + (BANK_MIN*COALESCE(GUARANTEE_RR,0))
+(S*&mcr_mortgage_constant.)+(t*&mcr_car_constant.)+(u*&mcr_car_constant.)+(v*&mcr_unsecured.))/TOPLAM_BALANCE ;
LGD_RATE=1-RR_DISCOUNTED ;
IF (RR_DISCOUNTED =. OR RR_DISCOUNTED =0) THEN DO ;
RR_DISCOUNTED=&mcr_unsecured.;
LGD_RATE=1-&mcr_unsecured.;
END;
IF BL THEN OUTPUT;
RUN;
PROC SORT DATA=SAS_COLL.RR_ADJUSTMENT_%LEFT(&MCR_PART_ID.) OUT=RR_ADJUSTMENT_&PORTFOLIO.;
BY COUNTERPARTY_ID ;
RUN;
%MEND RECOVERY_RATE;
PROC SQL NOPRINT;
SELECT DISTINCT PORTFOLIO_FLAG INTO :PORTFOLIO_FLAG SEPARATED BY " " FROM SAS_COLL.RR_PARAMETER;
QUIT;
%PUT PORTFOLIO_FLAG= &PORTFOLIO_FLAG.;
%MACRO LOOP_RECOVERY_RATE;
%LET I=1;
%DO %WHILE(%SCAN(&PORTFOLIO_FLAG.,&I.,%STR( ))~=);
%RECOVERY_RATE(%SCAN(&PORTFOLIO_FLAG.,&I.,%STR( )));
%LET I=&I.+1;
%END;
%MEND LOOP_RECOVERY_RATE; ;
%LOOP_RECOVERY_RATE;
PROC SQL;
CREATE TABLE WORK.RR_ADJUSTMENT AS
SELECT * FROM WORK.RR_ADJUSTMENT_SME
OUTER UNION CORR
SELECT * FROM WORK.RR_ADJUSTMENT_AGRICULTURE
OUTER UNION CORR
SELECT * FROM WORK.RR_ADJUSTMENT_MICROSME
OUTER UNION CORR
SELECT * FROM WORK.RR_ADJUSTMENT_CORPORATE
;
Quit;
data sas_port.Portfolio_%left(&mcr_part_id._v2) ;
set sas_port.Portfolio_%left(&mcr_part_id._v2) ;
if _n_ eq 1 then do ;
declare hash h1 (dataset: "rr_adjustment");
h1.definekey('counterparty_id');
h1.definedata('rr_discounted');
h1.definedata('lgd_rate');
h1.definedone();
end;
if rr_assessment_type not in ('MNL') and retail_flag=0 and short_portfolio in ('Corporate','SME','Other and Banks')
then do;
h1_control=h1.find();
rr_assessment_type='SYS' ;
end;
else if PORTFOLIO_ADJUSTED IN ('Tarım' 'Tar?m' 'MicroSME') then do;
h1_control=h1.find();
rr_assessment_type='SYS' ;
end;
if (RR_DISCOUNTED =. OR RR_DISCOUNTED =0) And short_portfolio in ('Other and Banks') THEN DO ;
RR_DISCOUNTED=0.177915356362287;
LGD_RATE=1-0.177915356362287;
END;
RUN;
Thanks in advance,
Ferhat
Click image to register for webinar
