BookmarkSubscribeRSS Feed
FerhatD
Calcite | Level 5

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;

 

1 REPLY 1
Kurt_Bremser
Super User

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?

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 1 reply
  • 339 views
  • 0 likes
  • 2 in conversation