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. I attached the sample data and it would be great if you have any idea about it.
Thanks in advance,
Ferhat
PROC SORT DATA= MIPRSLT.ALLPRICE;
BY INSTNUMBER ANALYSISNAME;
QUIT;
DATA MIPRSLT.ECL_ALLOCATION_DETAIL;
/*LENGTH INSTID $256. INSTTYPE $32. */
SET MIPRSLT.ALLPRICE(RENAME=(EL_LIFETIME_WITHOUT_REDISC = TEMP_EL_LIFETIME_WITHOUT_REDISC));
RETAIN BASE_1Y_BALANCE FAV_1Y_BALANCE ADV_1Y_BALANCE BASE_1Y_REDISC FAV_1Y_REDISC ADV_1Y_REDISC BASE_1Y_TOTAL
FAV_1Y_TOTAL ADV_1Y_TOTAL EL_LIFETIME_WITHOUT_REDISC 0;
BY INSTNUMBER ANALYSISNAME;
IF SCENARIO_WEIGHT IN (0,.) THEN
SCENARIO_WEIGHT = 1/3;
IF ANALYSISNAME = 'base' AND SIMULATIONTIME = 1 THEN
DO;
BASE_1Y_BALANCE = EL_1Y_BALANCE * SCENARIO_WEIGHT;
BASE_1Y_TOTAL = EL_1Y_TOTAL * SCENARIO_WEIGHT;
BASE_1Y_REDISC = EL_1Y_REDISC * SCENARIO_WEIGHT;
END;
ELSE IF ANALYSISNAME = 'favorable' AND SIMULATIONTIME = 1 THEN
DO;
FAV_1Y_BALANCE = EL_1Y_BALANCE * SCENARIO_WEIGHT;
FAV_1Y_TOTAL = EL_1Y_TOTAL * SCENARIO_WEIGHT;
FAV_1Y_REDISC = EL_1Y_REDISC * SCENARIO_WEIGHT;
END;
ELSE IF ANALYSISNAME = 'adverse' AND SIMULATIONTIME = 1 THEN
DO;
ADV_1Y_BALANCE = EL_1Y_BALANCE * SCENARIO_WEIGHT;
ADV_1Y_TOTAL = EL_1Y_TOTAL * SCENARIO_WEIGHT;
ADV_1Y_REDISC = EL_1Y_REDISC * SCENARIO_WEIGHT;
END;
IF LAST.ANALYSISNAME AND STATENUMBER NE 0 THEN
DO;
EL_LIFETIME_WITHOUT_REDISC = SUM(EL_LIFETIME_WITHOUT_REDISC, TEMP_EL_LIFETIME_WITHOUT_REDISC * SCENARIO_WEIGHT);
END;
IF LAST.INSTNUMBER THEN
DO;
EL_1Y_TOTAL = SUM(ADV_1Y_TOTAL, FAV_1Y_TOTAL, BASE_1Y_TOTAL);
EL_1Y_BALANCE = SUM(ADV_1Y_BALANCE, FAV_1Y_BALANCE, BASE_1Y_BALANCE);
EL_1Y_REDISC = SUM(ADV_1Y_REDISC, FAV_1Y_REDISC, BASE_1Y_REDISC);
EL_LIFETIME_WITH_REDISC = SUM(EL_LIFETIME_WITHOUT_REDISC, EL_1Y_REDISC);
OUTPUT;
EL_LIFETIME_WITH_REDISC=0;
EL_LIFETIME_WITHOUT_REDISC=0;
END;
KEEP
INSTNUMBER
INSTID
STATENUMBER
ANALYSISNAME
SIMULATIONTIME
_DATE_
EAD_STAGE1_TOTAL
EAD_STAGE2_TOTAL
EL_1Y_BALANCE
EL_1Y_REDISC
EL_1Y_TOTAL
BASE_1Y_BALANCE
BASE_1Y_TOTAL
BASE_1Y_REDISC
FAV_1Y_BALANCE
FAV_1Y_TOTAL
FAV_1Y_REDISC
ADV_1Y_BALANCE
ADV_1Y_TOTAL
ADV_1Y_REDISC
EL_LIFETIME_PARTIAL
EL_LIFETIME_REDISC
EL_LIFETIME_WITHOUT_REDISC
EL_LIFETIME_WITH_REDISC
REMAINING_YEAR
MATURITY_DATE
SCENARIO_WEIGHT
BALANCE_TL
_CORRECTED_PD_
_EIR_ADJUSTED_
_PDM_
_PD_RATE_
INSTTYPE
CURRENT_RATING
IFRS_RATING
EFFECTIVE_INTEREST_RATE
SCORE_PROV_DELAY;
RUN;
You can slightly optimize the code by doing the check for SIMULATIONTIME only once:
data MIPRSLT.ECL_ALLOCATION_DETAIL;
/*LENGTH INSTID $256. INSTTYPE $32. */
set MIPRSLT.ALLPRICE (
rename=(EL_LIFETIME_WITHOUT_REDISC = TEMP_EL_LIFETIME_WITHOUT_REDISC)
);
retain
BASE_1Y_BALANCE FAV_1Y_BALANCE ADV_1Y_BALANCE BASE_1Y_REDISC
FAV_1Y_REDISC ADV_1Y_REDISC BASE_1Y_TOTAL
FAV_1Y_TOTAL ADV_1Y_TOTAL EL_LIFETIME_WITHOUT_REDISC
0
;
by INSTNUMBER ANALYSISNAME;
if SCENARIO_WEIGHT IN (0,.) then SCENARIO_WEIGHT = 1/3;
if SIMULATIONTIME = 1
then do;
select (analysisname);
when ('base') do;
BASE_1Y_BALANCE = EL_1Y_BALANCE * SCENARIO_WEIGHT;
BASE_1Y_TOTAL = EL_1Y_TOTAL * SCENARIO_WEIGHT;
BASE_1Y_REDISC = EL_1Y_REDISC * SCENARIO_WEIGHT;
end;
when ('favorable') do;
FAV_1Y_BALANCE = EL_1Y_BALANCE * SCENARIO_WEIGHT;
FAV_1Y_TOTAL = EL_1Y_TOTAL * SCENARIO_WEIGHT;
FAV_1Y_REDISC = EL_1Y_REDISC * SCENARIO_WEIGHT;
end;
when ('adverse') do;
ADV_1Y_BALANCE = EL_1Y_BALANCE * SCENARIO_WEIGHT;
ADV_1Y_TOTAL = EL_1Y_TOTAL * SCENARIO_WEIGHT;
ADV_1Y_REDISC = EL_1Y_REDISC * SCENARIO_WEIGHT;
end;
end;
end;
if last.ANALYSISNAME and STATENUMBER NE 0
then EL_LIFETIME_WITHOUT_REDISC = sum(
EL_LIFETIME_WITHOUT_REDISC,
TEMP_EL_LIFETIME_WITHOUT_REDISC * SCENARIO_WEIGHT
);
if last.INSTNUMBER
then do;
EL_1Y_TOTAL = sum(ADV_1Y_TOTAL, FAV_1Y_TOTAL, BASE_1Y_TOTAL);
EL_1Y_BALANCE = sum(ADV_1Y_BALANCE, FAV_1Y_BALANCE, BASE_1Y_BALANCE);
EL_1Y_REDISC = sum(ADV_1Y_REDISC, FAV_1Y_REDISC, BASE_1Y_REDISC);
EL_LIFETIME_WITH_REDISC = sum(EL_LIFETIME_WITHOUT_REDISC, EL_1Y_REDISC);
output;
EL_LIFETIME_WITH_REDISC = 0;
EL_LIFETIME_WITHOUT_REDISC = 0;
end;
keep
INSTNUMBER INSTID STATENUMBER ANALYSISNAME SIMULATIONTIME
_DATE_ EAD_STAGE1_TOTAL EAD_STAGE2_TOTAL EL_1Y_BALANCE
EL_1Y_REDISC EL_1Y_TOTAL BASE_1Y_BALANCE BASE_1Y_TOTAL
BASE_1Y_REDISC FAV_1Y_BALANCE FAV_1Y_TOTAL FAV_1Y_REDISC
ADV_1Y_BALANCE ADV_1Y_TOTAL ADV_1Y_REDISC EL_LIFETIME_PARTIAL
EL_LIFETIME_REDISC EL_LIFETIME_WITHOUT_REDISC EL_LIFETIME_WITH_REDISC
REMAINING_YEAR MATURITY_DATE SCENARIO_WEIGHT BALANCE_TL
_CORRECTED_PD_ _EIR_ADJUSTED_ _PDM_ _PD_RATE_ INSTTYPE
CURRENT_RATING IFRS_RATING EFFECTIVE_INTEREST_RATE SCORE_PROV_DELAY
;
run;
How large is your real-life dataset (number of obs and file size)? How much time do those steps consume?
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.