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 ... View more

Hello experts,   The code given is running too long and I couldn't find a way how to optimize it so could you please help me about it? I attached also sample dataset and ALLPRICE table has 161.403.360 records. There are 3 scenarios and and these scenarios could run parallel the first thing i came up but any other ideas would be great.   Thanks in advance, Kindly regards   %let scenario_name=base adverse favorable; %let scenario_short=BAS ADV FAV; %PUT &scenario_name.; %PUT &scenario_short.; %include "/sasdata/sasinput/%left(&sst_current_project_id.)/ifrs9_00_init.sas"; %MACRO SCENARIO_FORMAT(SCENARIO,SHORT); PROC SQL; CREATE TABLE SENARYO_FILTRE AS SELECT DISTINCT t1.InstID, t1.SimulationTime, t1._CORRECTED_PD_, t1._EIR_ADJUSTED_, t1._PD_RATE_, t1._PDM_, t1.EL_LIFETIME_PARTIAL, t1.EL_LIFETIME_WITH_REDISC, t1.EL_LIFETIME_WITHOUT_REDISC, T1.EL_BDDK FROM SAS_RSLT.ALLPRICE t1 WHERE t1.AnalysisName = "&SCENARIO."; QUIT; PROC SQL; CREATE TABLE SENARYO_FILTRE_2 AS SELECT DISTINCT t1.InstID, t1.SimulationTime, t1._CORRECTED_PD_, t1._EIR_ADJUSTED_, t1._PD_RATE_, t1._PDM_, t1.EL_LIFETIME_PARTIAL, t1.EL_LIFETIME_WITH_REDISC, t1.EL_LIFETIME_WITHOUT_REDISC, t2.EL_1Y_TOTAL, t2.IFRS_RATING, T1.EL_BDDK, t2.InstID AS key FROM WORK.SENARYO_FILTRE t1 LEFT JOIN SAS_RSLT.ECL_ALLOCATION_DETAIL t2 ON (t1.InstID = t2.InstID) ORDER BY t1.InstID, t1.SimulationTime; QUIT; data SENARYO_FILTRE_3; set SENARYO_FILTRE_2; array PDM[10]; array CORRECTEDPD[10]; array DISCOUNT[10]; array PDRATE[10]; array EL[10]; array CUM_R_EL[10]; array CUM_EL[10]; if instid = key and SimulationTime= 1 then do; CUM_R_EL[SimulationTime]= EL_LIFETIME_WITH_REDISC; PDRATE[SimulationTime]= _PD_RATE_; CUM_EL[SimulationTime]= EL_LIFETIME_WITHOUT_REDISC; EL[SimulationTime]= EL_LIFETIME_PARTIAL; DISCOUNT[SimulationTime]= _EIR_ADJUSTED_; PDM[SimulationTime]= _PDM_; CORRECTEDPD[SimulationTime]= _CORRECTED_PD_; end; else if instid = key and SimulationTime= 2 then do; CUM_R_EL[SimulationTime]= EL_LIFETIME_WITH_REDISC; PDRATE[SimulationTime]= _PD_RATE_; CUM_EL[SimulationTime]= EL_LIFETIME_WITHOUT_REDISC; EL[SimulationTime]= EL_LIFETIME_PARTIAL; DISCOUNT[SimulationTime]= _EIR_ADJUSTED_; PDM[SimulationTime]= _PDM_; CORRECTEDPD[SimulationTime]= _CORRECTED_PD_; end; else if instid = key and SimulationTime= 3 then do; CUM_R_EL[SimulationTime]= EL_LIFETIME_WITH_REDISC; PDRATE[SimulationTime]= _PD_RATE_; CUM_EL[SimulationTime]= EL_LIFETIME_WITHOUT_REDISC; EL[SimulationTime]= EL_LIFETIME_PARTIAL; DISCOUNT[SimulationTime]= _EIR_ADJUSTED_; PDM[SimulationTime]= _PDM_; CORRECTEDPD[SimulationTime]= _CORRECTED_PD_; end; else if instid = key and SimulationTime= 4 then do; CUM_R_EL[SimulationTime]= EL_LIFETIME_WITH_REDISC; PDRATE[SimulationTime]= _PD_RATE_; CUM_EL[SimulationTime]= EL_LIFETIME_WITHOUT_REDISC; EL[SimulationTime]= EL_LIFETIME_PARTIAL; DISCOUNT[SimulationTime]= _EIR_ADJUSTED_; PDM[SimulationTime]= _PDM_; CORRECTEDPD[SimulationTime]= _CORRECTED_PD_; end; else if instid = key and SimulationTime= 5 then do; CUM_R_EL[SimulationTime]= EL_LIFETIME_WITH_REDISC; PDRATE[SimulationTime]= _PD_RATE_; CUM_EL[SimulationTime]= EL_LIFETIME_WITHOUT_REDISC; EL[SimulationTime]= EL_LIFETIME_PARTIAL; DISCOUNT[SimulationTime]= _EIR_ADJUSTED_; PDM[SimulationTime]= _PDM_; CORRECTEDPD[SimulationTime]= _CORRECTED_PD_; end; else if instid = key and SimulationTime= 6 then do; CUM_R_EL[SimulationTime]= EL_LIFETIME_WITH_REDISC; PDRATE[SimulationTime]= _PD_RATE_; CUM_EL[SimulationTime]= EL_LIFETIME_WITHOUT_REDISC; EL[SimulationTime]= EL_LIFETIME_PARTIAL; DISCOUNT[SimulationTime]= _EIR_ADJUSTED_; PDM[SimulationTime]= _PDM_; CORRECTEDPD[SimulationTime]= _CORRECTED_PD_; end; else if instid = key and SimulationTime= 7 then do; CUM_R_EL[SimulationTime]= EL_LIFETIME_WITH_REDISC; PDRATE[SimulationTime]= _PD_RATE_; CUM_EL[SimulationTime]= EL_LIFETIME_WITHOUT_REDISC; EL[SimulationTime]= EL_LIFETIME_PARTIAL; DISCOUNT[SimulationTime]= _EIR_ADJUSTED_; PDM[SimulationTime]= _PDM_; CORRECTEDPD[SimulationTime]= _CORRECTED_PD_; end; else if instid = key and SimulationTime= 8 then do; CUM_R_EL[SimulationTime]= EL_LIFETIME_WITH_REDISC; PDRATE[SimulationTime]= _PD_RATE_; CUM_EL[SimulationTime]= EL_LIFETIME_WITHOUT_REDISC; EL[SimulationTime]= EL_LIFETIME_PARTIAL; DISCOUNT[SimulationTime]= _EIR_ADJUSTED_; PDM[SimulationTime]= _PDM_; CORRECTEDPD[SimulationTime]= _CORRECTED_PD_; end; else if instid = key and SimulationTime= 9 then do; CUM_R_EL[SimulationTime]= EL_LIFETIME_WITH_REDISC; PDRATE[SimulationTime]= _PD_RATE_; CUM_EL[SimulationTime]= EL_LIFETIME_WITHOUT_REDISC; EL[SimulationTime]= EL_LIFETIME_PARTIAL; DISCOUNT[SimulationTime]= _EIR_ADJUSTED_; PDM[SimulationTime]= _PDM_; CORRECTEDPD[SimulationTime]= _CORRECTED_PD_; end; else if instid = key and SimulationTime= 10 then do; CUM_R_EL[SimulationTime]= EL_LIFETIME_WITH_REDISC; PDRATE[SimulationTime]= _PD_RATE_; CUM_EL[SimulationTime]= EL_LIFETIME_WITHOUT_REDISC; EL[SimulationTime]= EL_LIFETIME_PARTIAL; DISCOUNT[SimulationTime]= _EIR_ADJUSTED_; PDM[SimulationTime]= _PDM_; CORRECTEDPD[SimulationTime]= _CORRECTED_PD_; end; run; PROC SQL; CREATE TABLE SENARYO_FILTRE_4 AS SELECT DISTINCT t1.InstID, /* PDM1 */ (SUM(t1.PDM1)) AS PDM1, /* PDM2 */ (SUM(t1.PDM2)) AS PDM2, /* PDM3 */ (SUM(t1.PDM3)) AS PDM3, /* PDM4 */ (SUM(t1.PDM4)) AS PDM4, /* PDM5 */ (SUM(t1.PDM5)) AS PDM5, /* PDM6 */ (SUM(t1.PDM6)) AS PDM6, /* PDM7 */ (SUM(t1.PDM7)) AS PDM7, /* PDM8 */ (SUM(t1.PDM8)) AS PDM8, /* PDM9 */ (SUM(t1.PDM9)) AS PDM9, /* PDM10 */ (SUM(t1.PDM10)) AS PDM10, /* CORRECTEDPD1 */ (SUM(t1.CORRECTEDPD1)) AS CORRECTEDPD1, /* CORRECTEDPD2 */ (SUM(t1.CORRECTEDPD2)) AS CORRECTEDPD2, /* CORRECTEDPD3 */ (SUM(t1.CORRECTEDPD3)) AS CORRECTEDPD3, /* CORRECTEDPD4 */ (SUM(t1.CORRECTEDPD4)) AS CORRECTEDPD4, /* CORRECTEDPD5 */ (SUM(t1.CORRECTEDPD5)) AS CORRECTEDPD5, /* CORRECTEDPD6 */ (SUM(t1.CORRECTEDPD6)) AS CORRECTEDPD6, /* CORRECTEDPD7 */ (SUM(t1.CORRECTEDPD7)) AS CORRECTEDPD7, /* CORRECTEDPD8 */ (SUM(t1.CORRECTEDPD8)) AS CORRECTEDPD8, /* CORRECTEDPD9 */ (SUM(t1.CORRECTEDPD9)) AS CORRECTEDPD9, /* CORRECTEDPD10 */ (SUM(t1.CORRECTEDPD10)) AS CORRECTEDPD10, /* DISCOUNT1 */ (SUM(t1.DISCOUNT1)) AS DISCOUNT1, /* DISCOUNT2 */ (SUM(t1.DISCOUNT2)) AS DISCOUNT2, /* DISCOUNT3 */ (SUM(t1.DISCOUNT3)) AS DISCOUNT3, /* DISCOUNT4 */ (SUM(t1.DISCOUNT4)) AS DISCOUNT4, /* DISCOUNT5 */ (SUM(t1.DISCOUNT5)) AS DISCOUNT5, /* DISCOUNT6 */ (SUM(t1.DISCOUNT6)) AS DISCOUNT6, /* DISCOUNT7 */ (SUM(t1.DISCOUNT7)) AS DISCOUNT7, /* DISCOUNT8 */ (SUM(t1.DISCOUNT8)) AS DISCOUNT8, /* DISCOUNT9 */ (SUM(t1.DISCOUNT9)) AS DISCOUNT9, /* DISCOUNT10 */ (SUM(t1.DISCOUNT10)) AS DISCOUNT10, /* PDRATE1 */ (SUM(t1.PDRATE1)) AS PDRATE1, /* PDRATE2 */ (SUM(t1.PDRATE2)) AS PDRATE2, /* PDRATE3 */ (SUM(t1.PDRATE3)) AS PDRATE3, /* PDRATE4 */ (SUM(t1.PDRATE4)) AS PDRATE4, /* PDRATE5 */ (SUM(t1.PDRATE5)) AS PDRATE5, /* PDRATE6 */ (SUM(t1.PDRATE6)) AS PDRATE6, /* PDRATE7 */ (SUM(t1.PDRATE7)) AS PDRATE7, /* PDRATE8 */ (SUM(t1.PDRATE8)) AS PDRATE8, /* PDRATE9 */ (SUM(t1.PDRATE9)) AS PDRATE9, /* PDRATE10 */ (SUM(t1.PDRATE10)) AS PDRATE10, /* EL1 */ (SUM(t1.EL1)) AS EL1, /* EL2 */ (SUM(t1.EL2)) AS EL2, /* EL3 */ (SUM(t1.EL3)) AS EL3, /* EL4 */ (SUM(t1.EL4)) AS EL4, /* EL5 */ (SUM(t1.EL5)) AS EL5, /* EL6 */ (SUM(t1.EL6)) AS EL6, /* EL7 */ (SUM(t1.EL7)) AS EL7, /* EL8 */ (SUM(t1.EL8)) AS EL8, /* EL9 */ (SUM(t1.EL9)) AS EL9, /* EL10 */ (SUM(t1.EL10)) AS EL10, /* CUM_R_EL1 */ (SUM(t1.CUM_R_EL1)) AS CUM_R_EL1, /* CUM_R_EL2 */ (SUM(t1.CUM_R_EL2)) AS CUM_R_EL2, /* CUM_R_EL3 */ (SUM(t1.CUM_R_EL3)) AS CUM_R_EL3, /* CUM_R_EL4 */ (SUM(t1.CUM_R_EL4)) AS CUM_R_EL4, /* CUM_R_EL5 */ (SUM(t1.CUM_R_EL5)) AS CUM_R_EL5, /* CUM_R_EL6 */ (SUM(t1.CUM_R_EL6)) AS CUM_R_EL6, /* CUM_R_EL7 */ (SUM(t1.CUM_R_EL7)) AS CUM_R_EL7, /* CUM_R_EL8 */ (SUM(t1.CUM_R_EL8)) AS CUM_R_EL8, /* CUM_R_EL9 */ (SUM(t1.CUM_R_EL9)) AS CUM_R_EL9, /* CUM_R_EL10 */ (SUM(t1.CUM_R_EL10)) AS CUM_R_EL10, /* CUM_EL1 */ (SUM(t1.CUM_EL1)) AS CUM_EL1, /* CUM_EL2 */ (SUM(t1.CUM_EL2)) AS CUM_EL2, /* CUM_EL3 */ (SUM(t1.CUM_EL3)) AS CUM_EL3, /* CUM_EL4 */ (SUM(t1.CUM_EL4)) AS CUM_EL4, /* CUM_EL5 */ (SUM(t1.CUM_EL5)) AS CUM_EL5, /* CUM_EL6 */ (SUM(t1.CUM_EL6)) AS CUM_EL6, /* CUM_EL7 */ (SUM(t1.CUM_EL7)) AS CUM_EL7, /* CUM_EL8 */ (SUM(t1.CUM_EL8)) AS CUM_EL8, /* CUM_EL9 */ (SUM(t1.CUM_EL9)) AS CUM_EL9, /* CUM_EL10 */ (SUM(t1.CUM_EL10)) AS CUM_EL10, EL_BDDK /* EL_LIFETIME_PARTIAL*/ FROM SENARYO_FILTRE_3 t1 GROUP BY t1.InstID; QUIT; PROC SQL; CREATE TABLE SAS_PORT.SENARYOFLTRFEB_&SCENARIO. AS SELECT t2.INSTID, "&SHORT" AS PD_F, t2.INSTTYPE, t2.DATA_DATE, t2.MASTID, t2.MATURITY_DATE, t2.BALANCE_TL, t2.BALANCE_ORG, t2.CURRENCY_CODE, t2.RECONSTRUCTION_FLAG, t2.SHORT_PORTFOLIO, t2.PORTFOLIO_ADJUSTED, t2.BNPP_PORTFOLIO, t2.RETAIL_TYPE, t2.RETAIL_FLAG, t2.COMMITMENT_TYPE, t2.CREDIT_TYPE, t2.ACCOUNT_NUMBER, t2.BANK_GROUP_CODE, t2.BANK_GROUP_NAME, t2.BANK_NAME, t2.BANKNO, t2.BLOCKING_DEGREE, t2.BRANCH_ID, t2.COUNTERPARTY_NAME, t2.COUNTERPARTY_TYPE, t2.CREDIT_TYPE_NAME, t2.ACCRUED_INTEREST_AMT_TL, t2.AVG_BNP_RATING, t2.BUSINESS_LINE_CODE, t2.CURRENT_IG_NG, t2.COUNTERPARTY_ID, t2.COUNTERPARTY_VKN, t2.DEK_ADJ, t2.DEK_BAKIYE, t2.DEK_CURRENCY_CODE, t2.CRITEMNO, t2.CURR_INTEREST_RATE, t2.FAAL_ORAN, t2.CURRENT_BNP_RATING, t2.CURRENT_BUCKET, t2.CURRENT_EXCHANGE_RATE, t2.CURRENT_RATING, t2.CURRENT_RATING_POINT, t2.CURRENT_SCORE, t2.DELAY_DATE, t2.EFFECTIVE_INTEREST_RATE, t2.GENERAL_LOAN_LIMIT, t2.GENERAL_LOAN_MATURITY, t2.GL_ACCOUNT_NUM, t2.GROUP_CODE, t2.GROUP_GENERAL_LOAN_LIMIT, t2.GROUP_DESC, t2.IFRS_RATING, t2.INCEPTION_DATE, t2.INCEPTION_EXCHANGE_RATE, t2.INITIAL_BUCKET, t2.INITIAL_IG_NG, t2.INITIAL_RATING, t2.INITIAL_RATING_DATE, t2.INITIAL_RATING_POINT, t2.INTEREST_CALC_TYP_ORJ, t2.KTRNO, t2.INITIAL_SCORE, t2.LOAN_CUSTOMER_STATUS, t2.LOAN_TP_ID, t2.LGD_RATE, t2.MAIN_BRANCH_ID, t2.MIS_PRODUCTMAPNO, t2.NACE_CODE, t2.NACE_NAME, t2.OPEN_EXCH_RATE, t2.PROV_DELAY, t2.RATING_ANALYSIS_NO, t2.RATING_CLASS, t2.RATING_DNM_GRP, t2.RATING_DATE, t2.RATING_GRP, t2.REDISCOUNT_TL, t2.RMPM_ID, t2.RR_DISCOUNTED, t2.SCORE_CARD_ID, t2.STANDART_CCF, t2.TREEFAMILYNO, t2.TREELINENO, t2.TREESINGLENO, t3.EAD_STAGE1_TOTAL, t3.EL_1Y_BALANCE, t3.EL_1Y_REDISC, t3.EL_1Y_TOTAL, t3.EL_LIFETIME_REDISC, t3.EL_LIFETIME_WITH_REDISC, t3.EL_LIFETIME_WITHOUT_REDISC, t3.REMAINING_YEAR, t2.IFRS_RATING, t2.AVG_RATING, t2.SCORE_PROV_DELAY, t3.BASE_1Y_BALANCE, t3.FAV_1Y_BALANCE, t3.ADV_1Y_BALANCE, t3.BASE_1Y_REDISC, t3.FAV_1Y_REDISC , t3.ADV_1Y_REDISC, t3.BASE_1Y_TOTAL, t3.FAV_1Y_TOTAL, t3.ADV_1Y_TOTAL, t2.POINT_DIFF, t2.FORBORNE_FLAG, t2.RR_ASSESSMENT_TYPE, t2.STAGING, t2.STAGING_DETAIL, t2.STAGING_REASON, t1.CORRECTEDPD1, t1.CORRECTEDPD2, t1.CORRECTEDPD3, t1.CORRECTEDPD4, t1.CORRECTEDPD5, t1.CORRECTEDPD6, t1.CORRECTEDPD7, t1.CORRECTEDPD8, t1.CORRECTEDPD9, t1.CORRECTEDPD10, t1.DISCOUNT1, t1.DISCOUNT2, t1.DISCOUNT3, t1.DISCOUNT4, t1.DISCOUNT5, t1.DISCOUNT6, t1.DISCOUNT7, t1.DISCOUNT8, t1.DISCOUNT9, t1.DISCOUNT10, t1.PDRATE1, t1.PDRATE2, t1.PDRATE3, t1.PDRATE4, t1.PDRATE5, t1.PDRATE6, t1.PDRATE7, t1.PDRATE8, t1.PDRATE9, t1.PDRATE10, t1.PDM1, t1.PDM2, t1.PDM3, t1.PDM4, t1.PDM5, t1.PDM6, t1.PDM7, t1.PDM8, t1.PDM9, t1.PDM10, t1.EL1, t1.EL2, t1.EL3, t1.EL4, t1.EL5, t1.EL6, t1.EL7, t1.EL8, t1.EL9, t1.EL10, t1.CUM_R_EL1, t1.CUM_R_EL2, t1.CUM_R_EL3, t1.CUM_R_EL4, t1.CUM_R_EL5, t1.CUM_R_EL6, t1.CUM_R_EL7, t1.CUM_R_EL8, t1.CUM_R_EL9, t1.CUM_R_EL10, t1.CUM_EL1, t1.CUM_EL2, t1.CUM_EL3, t1.CUM_EL4, t1.CUM_EL5, t1.CUM_EL6, t1.CUM_EL7, t1.CUM_EL8, t1.CUM_EL9, t1.CUM_EL10, T1.EL_BDDK /*,*/ /* t1.EL_LIFETIME_PARTIAL*/ /* t1. IFRS9_FINAL_STAGE ,*/ /* t1.IFRS9_FINAL_EL*/ FROM SENARYO_FILTRE_4 t1 LEFT JOIN sas_port.Portfolio_%left(&mcr_part_id._v2) t2 ON (t1.InstID = t2.INSTID) /* LEFT JOIN SAS_PORT.PORTFOLIO_201905_V2 t2 ON (t1.InstID = t2.INSTID)*/ LEFT JOIN SAS_RSLT.ECL_ALLOCATION_DETAIL t3 ON (t1.InstID = t3.InstID); QUIT; %MEND SCENARIO_FORMAT; %MACRO LOOP_SCENARIO_FORMAT; %LET I=1; %DO %WHILE(%SCAN(&scenario_name.,&I.,%STR( ))~=); %SCENARIO_FORMAT(%SCAN(&scenario_name.,&I.,%STR( )),%SCAN(&scenario_short.,&I.,%STR( ))); %LET I=&I.+1; %END; %MEND LOOP_SCENARIO_FORMAT; ; %LOOP_SCENARIO_FORMAT;   ... View more