Below is my script, is a long one... %macro consolidate(DATE); %include "/sasdata2/ifrs9_validation/macro/00_Date_format.sas"; /* PAST 6 MTHS DATA */ %MACRO PDATE(DATE); PROC SQL; CREATE TABLE P_MORT AS SELECT month ,AR_ID ,UNQ_ID_SRC_STM ,UNQ_ID_SRC_STM2 ,MIA ,F_DEFAULT ,F_RR ,MOB /*,ADVANCE_INSTALLMENT*/ ,OVERDUE ,F_LM_AMT ,ORGL_AMT ,f_os_bal ,SHD_AMT ,DISB_PERC FROM mortdata.mortgage_perfmc_&DATE ;QUIT; PROC APPEND BASE=P6MTH_BASE DATA=P_MORT ;RUN; %MEND PDATE; %PDATE(&P_Month_1); %PDATE(&P_Month_2); %PDATE(&P_Month_3); %PDATE(&P_Month_4); %PDATE(&P_Month_5); %PDATE(&P_Month_6); %PDATE(&P_Month_7); PROC SQL; CREATE TABLE past_6mths AS SELECT DISTINCT A.* ,MAX((CASE WHEN B.MONTH = &P_Month_1 AND A.AR_ID = B.AR_ID THEN B.MIA END)) AS F_MIA12 ,MAX((CASE WHEN B.MONTH = &P_Month_2 AND A.AR_ID = B.AR_ID THEN B.MIA END)) AS F_MIA11 ,MAX((CASE WHEN B.MONTH = &P_Month_3 AND A.AR_ID = B.AR_ID THEN B.MIA END)) AS F_MIA10 ,MAX((CASE WHEN B.MONTH = &P_Month_4 AND A.AR_ID = B.AR_ID THEN B.MIA END)) AS F_MIA9 ,MAX((CASE WHEN B.MONTH = &P_Month_5 AND A.AR_ID = B.AR_ID THEN B.MIA END)) AS F_MIA8 ,MAX((CASE WHEN B.MONTH = &P_Month_6 AND A.AR_ID = B.AR_ID THEN B.MIA END)) AS F_MIA7 ,MAX((CASE WHEN B.MONTH = &P_Month_1 AND A.AR_ID = B.AR_ID THEN B.OVERDUE END)) AS F_OVERDUE12 ,MAX((CASE WHEN B.MONTH = &P_Month_2 AND A.AR_ID = B.AR_ID THEN B.OVERDUE END)) AS F_OVERDUE11 ,MAX((CASE WHEN B.MONTH = &P_Month_3 AND A.AR_ID = B.AR_ID THEN B.OVERDUE END)) AS F_OVERDUE10 ,MAX((CASE WHEN B.MONTH = &P_Month_4 AND A.AR_ID = B.AR_ID THEN B.OVERDUE END)) AS F_OVERDUE9 ,MAX((CASE WHEN B.MONTH = &P_Month_5 AND A.AR_ID = B.AR_ID THEN B.OVERDUE END)) AS F_OVERDUE8 ,MAX((CASE WHEN B.MONTH = &P_Month_6 AND A.AR_ID = B.AR_ID THEN B.OVERDUE END)) AS F_OVERDUE7 ,MAX((CASE WHEN B.MONTH = &P_Month_1 AND A.AR_ID = B.AR_ID AND B.DISB_PERC >= 70 THEN 1 ELSE 0 END)) AS F_DISB_PERC12 ,MAX((CASE WHEN B.MONTH = &P_Month_2 AND A.AR_ID = B.AR_ID AND B.DISB_PERC >= 70 THEN 1 ELSE 0 END)) AS F_DISB_PERC11 ,MAX((CASE WHEN B.MONTH = &P_Month_3 AND A.AR_ID = B.AR_ID AND B.DISB_PERC >= 70 THEN 1 ELSE 0 END)) AS F_DISB_PERC10 ,MAX((CASE WHEN B.MONTH = &P_Month_4 AND A.AR_ID = B.AR_ID AND B.DISB_PERC >= 70 THEN 1 ELSE 0 END)) AS F_DISB_PERC9 ,MAX((CASE WHEN B.MONTH = &P_Month_5 AND A.AR_ID = B.AR_ID AND B.DISB_PERC >= 70 THEN 1 ELSE 0 END)) AS F_DISB_PERC8 ,MAX((CASE WHEN B.MONTH = &P_Month_6 AND A.AR_ID = B.AR_ID AND B.DISB_PERC >= 70 THEN 1 ELSE 0 END)) AS F_DISB_PERC7 ,MAX((CASE WHEN B.MONTH = &P_Month_7 AND A.AR_ID = B.AR_ID AND B.DISB_PERC >= 70 THEN 1 ELSE 0 END)) AS F_DISB_PERC6 FROM mortdata.mortgage_perfmc_&yyyymm AS A LEFT JOIN P6MTH_BASE AS B ON A.AR_ID = B.AR_ID GROUP BY A.AR_ID ;QUIT; proc sql; create table past_6mths_2 as select *, sum(F_DISB_PERC12,F_DISB_PERC11,F_DISB_PERC10,F_DISB_PERC9,F_DISB_PERC8,F_DISB_PERC7,F_DISB_PERC6) as F_DISB_PERC_P7MTH from past_6mths group by AR_ID; quit; data mortdata.mortgage_perfmc_&yyyymm (Drop = i); set past_6mths_2; format GRP $50.; format SEGMENT $8.; format curr_overdue_install 8.; format entity $20.; if F_DEFAULT=1 or F_RRP6MTH=1 then GRP='Default'; else do; if F_ABANDONED = 1 or F_RRP12MTH = 1 or MIA>=2 then GRP='MIA>=2'; else if MIA=1 then GRP='MIA=1'; else if MIA<1 then do; if MOB=. then GRP='Amodel'; else if disb_perc < 70 and MOB <= 36 then GRP='Amodel'; else if F_DISB_PERC_P7MTH<7 and MOB <=36 then GRP='Amodel'; else GRP='Bmodel'; end; end; /*Define PD*/ if GRP='Default' then PD=100; else if GRP='MIA>=2' then PD=50.15; else if GRP='MIA=1' then PD=22.69; else PD=0;/*To be define later*/ IF GRP = 'Amodel' then do; if c_firstccris_2 = -9999999 then s_length_cch = 48; else if saa = 1 or Number_of_adverse_Credit > 0 then s_length_cch = 15; else if c_firstccris_2 = -888888 then s_length_cch = 15; else if c_firstccris_2 <= 24 then s_length_cch = 15; else if c_firstccris_2 <= 60 then s_length_cch = 47; else if c_firstccris_2 <= 78 then s_length_cch = 55; else s_length_cch = 72; if max_cnt1abv_6m_all = -9999999 then s_cnt_mia = 48; else if saa = 1 or Number_of_adverse_Credit > 0 then s_cnt_mia = 18; else if max_cnt1abv_6m_all = -888888 then s_cnt_mia = 18; else if max_cnt1abv_6m_all = 0 then s_cnt_mia = 63; else if max_cnt1abv_6m_all <= 0.5 then s_cnt_mia = 40; else s_cnt_mia = 18; if recent_p12m_NR = -9999999 then s_borr = 48; else if saa = 1 or Number_of_adverse_Credit > 0 then s_borr = 28; else if recent_p12m_NR = -888888 then s_borr = 28; else if recent_p12m_NR <= 0 then s_borr = 60; else if recent_p12m_NR <= 1 then s_borr = 38; else s_borr = 28; if utilization_ratio_rev = -9999999 then s_uti = 48; else if saa = 1 or Number_of_adverse_Credit > 0 then s_uti = 31; else if utilization_ratio_rev = -888888 then s_uti = 31; else if utilization_ratio_rev in (.,-999999) then s_uti = 31; else if utilization_ratio_rev <= 0.3 then s_uti = 69; else if utilization_ratio_rev <= 0.6 then s_uti = 48; else if utilization_ratio_rev <= 0.9 then s_uti = 38; else s_uti = 31; if undrawn_ratio_nonrev = -9999999 then s_undrawn = 48; else if saa = 1 or Number_of_adverse_Credit > 0 then s_undrawn = 35; else if undrawn_ratio_nonrev = -888888 then s_undrawn = 35; else if undrawn_ratio_nonrev = . then s_undrawn = 55; else if undrawn_ratio_nonrev = -999999 then s_undrawn = 35; else if undrawn_ratio_nonrev <= 0.05 then s_undrawn = 35; else if undrawn_ratio_nonrev <= 0.25 then s_undrawn = 47; else if undrawn_ratio_nonrev <= 0.40 then s_undrawn = 53; else s_undrawn = 55; if max_limit_rev_income = -9999999 then s_max_limit = 48; else if saa = 1 or Number_of_adverse_Credit > 0 then s_max_limit = 40; else if max_limit_rev_income = -888888 then s_max_limit = 40; else if max_limit_rev_income <= 0.5 then s_max_limit = 40; else if max_limit_rev_income <= 1.5 then s_max_limit = 46; else if max_limit_rev_income <= 2.5 then s_max_limit = 52; else s_max_limit = 59; if ccris_ind_new = 1 then s_ccris_ind = 54; else s_ccris_ind = 20; if ltv_re_pr = . then s_ltv = 29; else if ltv_re_pr <= 75 then s_ltv = 61; else if ltv_re_pr <= 95 then s_ltv = 48; else s_ltv = 29; if pr_state_grouped in ('East Malaysia','Wilayah Persekutuan') then s_state = 61; else if pr_state_grouped in ('Penang','Selangor','Selangor Darul Ehsan','Negeri Selangor') then s_state = 51; else if pr_state_grouped in ('Melaka / Negeri Sembilan','Pahang/Perak') then s_state = 48; else if pr_state_grouped in ('Johor','','Kedah/Perlis','East Coast','A15','999','Foreign') then s_state = 29; else s_state = 29; if employer_typ in ('BCB Group','ABC Group','Financial Institution' ,'Govt Agencies','KLSE Listed','MNC - Non FI','MNC (Non FI)','Public Limited Company - Not Listed' ,'Public Limited Company (Not Listed)') then s_employer = 71; else if employer_typ in ('Private Limited Companies','Private Limited Compa') then s_employer = 43; else if employer_typ in ('None (Unemployed)','None / Unemployed' ,'Others','Sole Proprietorship / Partnersh','Sole Proprietorship / Partnership','Sole Proprietorship/Partnership' ,'Sole Proprietorship /','') then s_employer = 36; else s_employer = 36; if gender in ('Female','F') then s_gender = 66; else s_gender = 42; if joint_app = 'Y' then s_joint = 80; else s_joint = 37; if marital_dep in ('Widowed','Single','Separated','Divorced') then s_marital= 34; else if marital_dep in ('Married1','Married0','Married.') then s_marital= 53; else if marital_dep in ('Married2','Married3','Married4','Married5') then s_marital= 58; else s_marital= 34 ; if occupation_grouped_1 in ('Professional') then s_occu = 69; else if occupation_grouped_1 in ('Management','Executive') then s_occu = 54; else if occupation_grouped_1 in ('Armed forces','Clerical','Housewife','No Investigatio' ,'Self Employed','Skilled','Student','Unemployed','Unskilled','') then s_occu = 40; else s_occu = 40; Ascore = sum(s_length_cch,s_cnt_mia,s_borr,s_uti,s_undrawn,s_max_limit,s_ccris_ind,s_ltv,s_state,s_employer,s_gender,s_joint,s_marital,s_occu); if Ascore<=630 then PD=0.93; else if 630<Ascore<=665 then PD=0.61; else if 665<Ascore<=700 then PD=0.27; else if 700<Ascore<=745 then PD=0.14; else if Ascore>745 then PD=0.05; if Ascore<=630 then segment= "A5"; else if 630<Ascore<=665 then segment= "A4"; else if 665<Ascore<=700 then segment= "A3"; else if 700<Ascore<=745 then segment= "A2"; else if Ascore>745 then segment= "A1"; END; if GRP = 'Bmodel' then do; array cnt6{6} F_MIA7-F_MIA12; array f_overdue{6} F_OVERDUE7-F_OVERDUE12; array dueAMTFIN{6} dueamtfin7-dueamtfin12; do i = 1 to 6; /* number of times >0 */ /* if SHD_AMT^=0 then dueINSTAL{i} = overdue{i}/SHD_AMT;*/ /* else dueINSTAL{i}=0;*/ if F_LM_AMT^=0 then dueAMTFIN{i} = f_overdue{i}/F_LM_AMT; else dueAMTFIN{i}=0; /* if cls_cr_lmt^=0 then dueAMTFINANCE{i} = f_overdue{i}/cls_cr_lmt;*/ /* else dueAMTFINANCE{i}=0;*/ end; /* Maximum Total amount Overdue over limit in past 6 mths */ Max_odue_limit = max(dueamtfin7,dueamtfin8,dueamtfin9,dueamtfin10,dueamtfin11,dueamtfin12); /* Maximum Total amount Overdue over limit in past 6 mths */ /*Max_odue_limit1 = max(dueAMTFINANCE1,dueAMTFINANCE2,dueAMTFINANCE3,dueAMTFINANCE4,dueAMTFINANCE5,dueAMTFINANCE6);*/ if Max_odue_limit = 0 then s_Max_odue_limit=155; else if Max_odue_limit <= 0.005 then s_Max_odue_limit=118; else if Max_odue_limit <= 0.01 then s_Max_odue_limit=89; else if Max_odue_limit > 0.01 then s_Max_odue_limit=41; /*if Max_odue_limit1 = 0 then s_Max_odue_limit1=155;*/ /*else if Max_odue_limit1 <= 0.005 then s_Max_odue_limit1=118;*/ /*else if Max_odue_limit1 <= 0.01 then s_Max_odue_limit1=89;*/ /*else if Max_odue_limit1 > 0.01 then s_Max_odue_limit1=41;*/ IF MAX(F_MIA7,F_MIA8,F_MIA9,F_MIA10,F_MIA11,F_MIA12) > 0 then s_MIA_P6MTH = 80; ELSE IF MAX(F_MIA7,F_MIA8,F_MIA9,F_MIA10,F_MIA11,F_MIA12) = 0 then s_MIA_P6MTH = 131; ELSE s_MIA_P6MTH = 80; if SHD_AMT^=0 then curr_overdue_install = overdue/SHD_AMT; else curr_overdue_install=0; if curr_overdue_install = 0 then s_curr_overdue_install=135; else if curr_overdue_install <= 0.5 then s_curr_overdue_install=84; else if curr_overdue_install > 0.5 then s_curr_overdue_install=67; if sum_apportioned_collateral_value <= 75000 then s_coll=103; else if sum_apportioned_collateral_value <= 125000 then s_coll=115; else if sum_apportioned_collateral_value <= 225000 then s_coll=125; else if sum_apportioned_collateral_value <= 375000 then s_coll=135; else s_coll=151; if F_1_DIS_DT le '30JUN2006'd then entity='BCB/SBB'; else entity='ABC'; if entity = "ABC" then s_entity=130; else s_entity = 100; Bscore = sum(s_Max_odue_limit,s_MIA_P6MTH,s_curr_overdue_install,s_coll,s_entity); if Bscore<=530 then PD=11.57; else if 530<Bscore<=630 then PD=2.10; else if 630<Bscore<=660 then PD=0.57; else if 660<Bscore<=680 then PD=0.27; else if Bscore>680 then PD=0.16; if Bscore<=530 then segment= "B5"; else if 530<Bscore<=630 then segment= "B4"; else if 630<Bscore<=660 then segment= "B3"; else if 660<Bscore<=680 then segment= "B2"; else if Bscore>680 then segment= "B1"; END; keep month AR_ID UNQ_ID_SRC_STM UNQ_ID_SRC_STM2 F_ACNO AR_TP_ID AR_FRQ_TP_ID AR_PPS_TP_ID AR_CR_RSK_RTG_ID AR_FNC_ST_TP_ID STATUS OPEN_DT MATURE_DT CLOSED_DT (and many other columns) ; run; %mend consolidate; %consolidate(31Mar2016); %consolidate(30Apr2016); %consolidate(31May2016); %consolidate(30Jun2016); %consolidate(31Jul2016); %consolidate(31Aug2016); %consolidate(30Sep2016); %consolidate(31Oct2016); %consolidate(30Nov2016); %consolidate(31Dec2016); My problem is, the first few output would be generated within 5 - 10 minutes, but the subsequent output would take longer time gradually... 20 minutes, 30 minutes, an hour... is there any amendment i can make to improve the speed to generate the output?
... View more
