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
Maxim 34: Work in Steps.
This also means that you must start by checking the log to determine which of the steps take so long, and then optimize those.
What immediately jumps to my attention is this:
SELECT DISTINCT t1.DATA_DATE,
t1.COUNTERPARTY_ID,
t1.CREDIT_TYPE,
1 AS FLAG,
(SUM(t2.VALAMOUNT)) FORMAT=22.6 AS SUM_of_EAD
/* ... */
GROUP BY t1.DATA_DATE,
t1.COUNTERPARTY_ID,
t1.CREDIT_TYPE;
by definition, the DISTINCT is not needed here, and might force the SQL procedure to do an unnecessary extra sort.
You should also be able to do these two quite similar SQL steps in one, and set the FLAG variable in a CASE clause.
Maxim 34: Work in Steps.
This also means that you must start by checking the log to determine which of the steps take so long, and then optimize those.
What immediately jumps to my attention is this:
SELECT DISTINCT t1.DATA_DATE,
t1.COUNTERPARTY_ID,
t1.CREDIT_TYPE,
1 AS FLAG,
(SUM(t2.VALAMOUNT)) FORMAT=22.6 AS SUM_of_EAD
/* ... */
GROUP BY t1.DATA_DATE,
t1.COUNTERPARTY_ID,
t1.CREDIT_TYPE;
by definition, the DISTINCT is not needed here, and might force the SQL procedure to do an unnecessary extra sort.
You should also be able to do these two quite similar SQL steps in one, and set the FLAG variable in a CASE clause.
Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.