IFRS9 Calculation Code Optimization

FerhatD — Wed, 19 Aug 2020 10:30:23 GMT

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

Re: IFRS9 Calculation Code Optimization

Kurt_Bremser — Wed, 19 Aug 2020 11:15:35 GMT

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.

Re: IFRS9 Calculation Code Optimization

Ksharp — Wed, 19 Aug 2020 11:33:22 GMT

WOW, Almost written by PROC SQL .
SQL is not efficient due to Cartesian Product .
Try original tradition SAS Data Step and Hash Table .

Re: IFRS9 Calculation Code Optimization

Ksharp — Wed, 19 Aug 2020 11:39:46 GMT

If could try SPDE engine.

libname x spde 'c:\temp';

topic Re: IFRS9 Calculation Code Optimization in SAS Programming

IFRS9 Calculation Code Optimization

Re: IFRS9 Calculation Code Optimization

Re: IFRS9 Calculation Code Optimization

Re: IFRS9 Calculation Code Optimization