SAS Documentation
SASĀ® Solution for Stress Testing
Reference manual - version 08.2021
Loading...
Searching...
No Matches
irmst_model_postcode_ccar_measures.sas
1/* ************************************************** *
2 * Model Post-Code Template *
3 * ************************************************** *
4
5Purpose:
6 Injection point for executing custom code after the MIP model execution
7 This code is required in post processing of the Credit Risk Measures run
8
9Details:
10 The following SAS macro variables are available to the model at run time:
11 - ticket: SAS authentication service ticket (useful for performing operations that require authentication, i.e. interacting with REST services).
12 - rgf_protocol: Communication protocol (http/https) of the SAS Risk and Governance Framework web application
13 - rgf_host: Hostname of the SAS Risk and Governance Framework web application
14 - rgf_port: Port where the SAS Risk and Governance Framework web application is listening
15 - ds_in_model_result: Name of the input table containing the result of the MIP model execution
16 - ds_out_model_result: Name of the exposure-level output table to be created by this code
17 - ds_out_migration_detail: Name of the sub-exposure-level output table to be created by this code
18 - analysis_run_name: Name of the analysis run
19 - analysis_run_type: Type of the analysis run
20 - cycle_name: Name of the cycle
21
22 * ************************************************** */
23
24/*------------------------------------------------------------------------------------
25 * NAME: irmst_model_postcode_ccar_provision.sas
26 *
27 * Copyright (c) 2020 by SAS Institute Inc., Cary, NC, USA.
28 *------------------------------------------------------------------------------------*/
29
30%macro run_postcode_ccar_provision;
31
32 %local
33 scen_cnt
34 i
35 filter
36 crossclassvars
37 hResult_ds_dma
38 instid_rename_stmt
39 httpSuccess
40 responseStatus
41 ;
42
43 /*------------------------------------------------------------
44 The following code is used to overwrite the default
45 results from a RMC model run. These steps are
46 required to run the Credit Risk Measures model as shipped.
47 --------------------------------------------------------------*/
48
49 /* Defines the migration detail dataset */
50 %let ds_out_migration_detail = ST_STG.MIGRATION_DETAIL;
51
52 /* Overwrite the queryIndex if query all horizons is set to Y, if not keep the run model value */
53 %if (%upcase(&query_all_hrz_flg.) eq Y) %then %do;
54 %let queryIndex = %str( );
55 %end;
56 %else %if (%upcase(&keep_cube_flg.) eq Y) %then %do;
57 %let queryIndex = BASECASE &curr_mipHorizon.;
58 %end;
59 %else %do;
60 %let queryIndex = BASECASE 1;
61 %end;
62
63 /* ------------------------------------------------------------
64 Required macro variable definition prior to MIP queries
65 ---------------------------------------------------------------*/
66 %let bep_keep = bepName;
67 %let bep_lookup = , "&bep_keep.";
68 %let bep_csv = , &bep_keep.;
69 %let query_weight_vars_dma=;
70
71 /* Check if we need to perform scenario weighting */
72 %if(&scen_weight_flg. = Y) %then %do;
73 %let weight_keep = defaultWeight;
74 %let weight_lookup = , "&weight_keep.";
75 %let weight_csv = , &weight_keep.;
76
77 %let query_weight_vars=&query_weight_vars. el_amt;
78 %let query_weight_vars_dma=&query_weight_vars. initial_risk_rating;
79 %end;
80
81 /* Set custom code for the query post-process */
82 %let mip_query_custom_code = %str(
83
84 length
85 forecast_period $32.
86 scenario_name $512.
87 ;
88
89 rename
90 scenarioId = scenario_id
91 forecastTime = forecast_time
92 ;
93
94 if _N_ = 0 then
95 set &ds_in_scen_info.(keep = forecastTime &bep_keep mrsName &weight_keep.);
96
97 /* Define the lookup */
98 if _N_ = 1 then do;
99 declare hash hScen(dataset: "&ds_in_scen_info.");
100 hScen.defineKey("scenarioId");
101 hScen.defineData("forecastTime" &bep_lookup., "mrsName" &weight_lookup.);
102 hScen.defineDone();
103 end;
104
105 /* Perform lookup */
106 drop __rc__;
107 call missing(forecastTime &bep_csv., mrsName &weight_csv.);
108 __rc__ = hScen.find();
109
110 scenario_name = catx(" - " &bep_csv., mrsName);
111 forecastTime = sum(forecastTime, 0);
112 forecast_period = catx(" ", "&mipinterval.", put(forecastTime, 8.));
113 );
114
115 /* Set variables and get original portfolio when running with DMA expansion */
116 %if &dma_expansion_flg eq Y %then %do;
117 %let dma_expanded_portfolio = &ds_in_portfolio;
118 %let crossclassvars = original_instid;
119 %let instid_rename_stmt = (rename = (original_instid = instid));
120 /* Retreive original non-expanded portfolio */
121 %let ds_in_portfolio = work.portfolio_orig_&base_dt_ymdn._&model_run_key.;
122 %irm_rgf_retrieve_analysis_data(key = &data_prep_out_portfolio_key.
123 , libref = &dr_libref.
124 , dr_library_name = &dr_library_name.
125 , outds = &ds_in_portfolio
126 , outds_partition_list = ptf_partition_list
127 , out_type = data
128 , outds_dataInfo = portfolio_dataInfo
129 , outds_dataDef = portfolio_dataDef
130 , host = &rgf_protocol.://&rgf_host.
131 , server = &rgf_service.
132 , solution = &rgf_solution.
133 , port = &rgf_port.
134 , tgt_ticket = &tgt_ticket.
135 , outVarTicket = ticket
136 , outSuccess = httpSuccess
137 , outResponseStatus = responseStatus
138 , restartLUA = Y
139 , clearCache = Y
140 );
141
142 %if %sysevalf(%superq(cpty_rename_stmt) ne, boolean) %then
143 %do;
144 proc datasets library=work;
145 modify portfolio_orig_&base_dt_ymdn._&model_run_key.;
146 rename &cpty_rename_stmt.;
147 run;
148 %end;
149 %end;
150 %else %do;
151 /* Revert options to RMC default if portfolio not expanded */
152 %let crossclassvars = instid;
153 %let instid_rename_stmt = %str();
154 %end;
155
156 /* Query MIP results */
157 %irm_query_mip_results(conf_lib = mipconf
158 , expprojlib = mipexp
159 , out_ds = tmp_model_result_&run_idx.&instid_rename_stmt / view = tmp_model_result_&run_idx.
160 , out_ds_type = &mip_query_output_type.
161 , run_instance = &mip_runInstance_name.
162 , workgroup = &mip_ms_workgroup.
163 , where_clause = &mipQueryFilter.
164 , keep_vars = &query_weight_vars.
165 , horizon_index = &queryIndex.
166 , crossclassvars = &crossclassvars
167 , mip_root = &mip_root.
168 , enrich_output_flg = Y
169 , out_scenario_var = scenarioId
170 , scenario_id_value = &scenario_id_value.
171 , out_asofdate_var = base_dt
172 , custom_code = %superq(mip_query_custom_code)
173 , tmp_ds_suffix = &run_idx.
174 );
175
176 %let hResult_ds = tmp_model_result_&run_idx.;
177 %if(&scen_weight_flg. = Y) %then %do;
178
179 %let hResult_ds = tmp_model_result_all_&run_idx.;
180
181 /* Perform scenario weigthing */
182 proc summary data = tmp_model_result_&run_idx. missing nway;
183 class base_dt horizon forecast_time forecast_period &bep_keep instid;
184 var &query_weight_vars.;
185 weight &weight_keep.;
186 output
187 out = tmp_model_result_weighted_&run_idx. (drop = _type_ _freq_)
188 mean =
189 ;
190 run;
191
192 data tmp_model_result_all_&run_idx. / view = tmp_model_result_all_&run_idx.;
193 set
194 tmp_model_result_&run_idx.
195 tmp_model_result_weighted_&run_idx. (in = __weighted__)
196 ;
197
198 if __weighted__ then do;
199 mrsName = "Weighted";
200 scenario_name = catx(" - " &bep_csv., mrsName);
201 scenario_id = catx("_" &bep_csv., mrsName, cats("FT", sum(forecastTime, 0)));
202 if horizon ne 0 then output;
203 end;
204 else output;
205 run;
206 %end;
207
208 /* Get the list of variables from the model result table (remove INSTID) */
209 %let result_var_list = %sysfunc(prxchange(s/\bINSTID\b//i, -1, %rsk_getvarlist(&hResult_ds.)));
210 %let result_var_list_csv = %sysfunc(prxchange(s/\s+/%str(, )/i, -1, &result_var_list.));
211
212 /* Join model results with the portfolio table */
213 data &ds_in_model_result. / view = &ds_in_model_result.;
214 set &ds_in_portfolio.
215 %if(%sysevalf(%superq(cpty_reverse_rename_stmt) ne, boolean)) %then %do;
216 (rename = (&cpty_reverse_rename_stmt.))
217 %end;
218 ;
219
220 /* Set internal variables for tracking movement changes */
221 length MOVEMENT_ID 8. MOVEMENT_DESC $100.;
222 retain
223 MOVEMENT_ID 1
224 MOVEMENT_DESC "01. Model Output"
225 ;
226
227 rename
228 activationScenario = activation_scenario
229 activationHorizon = activation_horizon
230 ;
231
232 if _N_ = 0 then
233 set &hResult_ds.;
234
235 if _N_ = 1 then do;
236 declare hash hResult(dataset: "&hResult_ds.", multidata: "yes");
237 hResult.defineKey("instid");
238 hResult.defineData(all: "yes");
239 hResult.defineDone();
240 end;
241
242 drop __rc__;
243 call missing(&result_var_list_csv.);
244 __rc__ = hResult.find();
245 /* Check if we found a match */
246 if (__rc__ = 0) then do;
247 /* Loop through all matching records */
248 do while(__rc__ = 0);
249
250 %if &dma_expansion_flg eq Y %then
251 synthetic_instrument_flg = 'N';;
252
253 %if(&scen_weight_flg. = Y) %then
254 if mrsName="Weighted" then evaldate=intnx("&mipInterval.", reporting_dt, horizon, "SAME");;
255
256 /* Write only original portfolio position or synthetic positions where the activationScenario matches the scenario_id */
257 if(synthetic_instrument_flg = "N" or activationScenario = scenario_id) then
258 output;
259
260 /* Lookup next result */
261 call missing(&result_var_list_csv.);
262 __rc__ = hResult.find_next();
263 end;
264 end;
265 else
266 /* No match was found, write the record anyway (it could happen if there are issues with the MIP model. These positions will have missing scenario name so they can spotted in the reports) */
267 output;
268 run;
269
270 /*---------------------------------------------------
271 * Prepare reportmart structure for migration_detail
272 * Its content will be populated in fpost
273 * in the case of dma and left blank when not.
274 *---------------------------------------------------*/
275 %let libref = %scan(&ds_out_migration_detail., 1, .);
276 proc sql;
277 %include "&fa_path./source/sas/misc/ddl/reportmart/%lowcase(%scan(&ds_out_migration_detail., 2, .)).sas" / source2 lrecl = 32000;
278 quit;
279
280 /*---------------------------------
281 End RMC model overwirte section
282 -----------------------------------*/
283
284 /*----------------------------------------------------------------------------------
285 * Create exposure-level output dataset
286 *----------------------------------------------------------------------------------*/
287 data work.tmp_model_result_1;
288 set &ds_in_model_result
289 %if %rsk_varexist(&ds_in_model_result, initial_risk_rating) %then
290 %do;
291 (drop = initial_risk_rating)
292 %end;;
293 /*-----------------------------
294 * Treatment for scenario_name
295 *-----------------------------*/
296 /* Removing BEP name (i.e., The value before the first hyphen) */
297 if not missing(scenario_name) then
298 scenario_name = strip(substr(scenario_name, find(scenario_name, "-")+1));
299 /* Identify basecase scenario and assign a scenario name */
300 else if horizon eq 0 then
301 scenario_name = "Actual";
302 run;
303
304 /*----------------------------------------------
305 * Create hash table that retrieves basecase_el
306 *----------------------------------------------*/
307 data work.htbl_basecase_el (rename = (el_amt = basecase_el));
308 set work.tmp_model_result_1 (keep = instid
309 scenario_name
310 horizon
311 el_amt);
312 where horizon eq 0;
313 run;
314
315 /*--------------------------------------------------
316 * Set up & use hash table to lookup basecase_el
317 *--------------------------------------------------*/
318 data work.tmp_model_result_2 (drop = rc);
319 set work.tmp_model_result_1;
320 call missing(basecase_el);
321
322 if _n_ eq 1 then
323 do;
324 %irm_build_hash_lookup(
325 hash_name = h_basecase_el,
326 hash_table = work.htbl_basecase_el,
327 key_vars = instid,
328 data_vars = basecase_el
329 );
330 end;
331 rc = h_basecase_el.find();
332 run;
333
334 /*----------------------------------------------------------------
335 * Obtain the list of unique & processed scenario names
336 * - Except scenario name at horizon = 0 (i.e., actuals/basecase)
337 *----------------------------------------------------------------*/
338 proc sql noprint;
339 select distinct scenario_id,
340 scenario_name
341 into :scen_id_1-,
342 :scen_nm_1-
343 from work.tmp_model_result_1
344 where horizon ne 0;
345 quit;
346 %let scen_cnt = &sqlobs;
347
348 /*------------------------------------------------
349 * Copy the actuals record to all other scenarios
350 *------------------------------------------------*/
351 data &ds_out_model_result;
352 set work.tmp_model_result_2;
353 if horizon eq 0 then
354 do;
355 %do i=1 %to &scen_cnt;
356 scenario_id = "&&scen_id_&i";
357 scenario_name = "&&scen_nm_&i";
358 output;
359 %end;
360 end;
361 else if horizon gt 0 then
362 output;
363 run;
364
365 /*------------------------------------------------------
366 * Set MIP query filter using DMA expansion flag
367 * - MIGRATION_DETAIL will be blank if not using DMA
368 *------------------------------------------------------*/
369 %global drop_statement;
370 %if %qupcase(&dma_expansion_flg) ne Y %then
371 %do;
372 %let filter = 0;
373 %let crossclassvars = instid;
374 %end;
375 %else
376 %do;
377 %let filter = (&mipQueryFilter.) and dma_expansion_flag eq 'Y';
378 %let crossclassvars = instid dma_expansion_flag;
379 %let drop_statement = (drop = dma_expansion_flag);
380 %end;
381
382 /*------------------------------------------------
383 * Query MIP cube if running with DMA expansion
384 *------------------------------------------------*/
385 %irm_query_mip_results(
386 conf_lib = mipconf,
387 expprojlib = mipexp,
388 out_ds = tmp_model_result_dma_&run_idx.&drop_statement / view = tmp_model_result_dma_&run_idx.,
389 out_ds_type = &mip_query_output_type.,
390 run_instance = &mip_runInstance_name.,
391 workgroup = &mip_ms_workgroup.,
392 where_clause = &filter,
393 keep_vars = &query_weight_vars_dma.,
394 horizon_index = &queryIndex.,
395 crossclassvars = &crossclassvars,
396 mip_root = &mip_root.,
397 enrich_output_flg = Y,
398 out_scenario_var = scenarioId,
399 scenario_id_value = &scenario_id_value.,
400 out_asofdate_var = base_dt,
401 custom_code = %superq(mip_query_custom_code),
402 tmp_ds_suffix = dma_&run_idx.
403 )
404
405 %let hResult_ds_dma = tmp_model_result_dma_&run_idx.;
406
407 %if(&scen_weight_flg. eq Y) %then
408 %do;
409 %let hResult_ds_dma = tmp_model_result_dma_all_&run_idx;
410 %irmst_weight_mip_results(
411 outds = tmp_model_result_dma_all_&run_idx. / view = tmp_model_result_dma_all_&run_idx.,
412 in_mip_result_ds = tmp_model_result_dma_&run_idx.,
413 class_vars = &bep_keep,
414 output_vars = &query_weight_vars_dma.,
415 weight_vars = &weight_keep.,
416 scenario_vars = %quote(&bep_csv)
417 )
418 %end;
419
420 %irmst_enrich_mip_results(
421 outds = work.migration_results,
422 in_mip_result_ds = &hResult_ds_dma,
423 in_mip_portfolio_ds = %if %qupcase(&dma_expansion_flg.) eq Y %then
424 &dma_expanded_portfolio,;
425 %else
426 &ds_in_portfolio,;
427 reverse_rename_stmt = &cpty_reverse_rename_stmt,
428 join_vars = instid
429 )
430
431 /*-----------------------------------------------------------
432 * Obtain a ranked list of risk ratings for commercial loans
433 * using instid postfix and to_risk_rating
434 *-----------------------------------------------------------*/
435 %global rating_list;
436 proc sql noprint;
437 select distinct cats(
438 'when(',
439 strip(substr(instid, find(instid, "_", -length(instid))+1)), /* Rating Number */
440 ') initial_risk_rating = "',
441 to_risk_rating, /* Rating Value */
442 '";'
443 ) into :rating_list separated by ' '
444 from work.migration_results
445 where upcase(rating_agency) eq 'S&P';
446 quit;
447
448 /*----------------------------------------------------------------------------------
449 * Create sub-exposure-level output dataset
450 *----------------------------------------------------------------------------------*/
451
452 data &ds_out_migration_detail. (keep = %rsk_getvarlist(&ds_out_migration_detail.));
453
454 /*-----------------------------
455 * Initialize output data set
456 *-----------------------------*/
457 if 0 then
458 set &ds_out_migration_detail;
459
460 set work.migration_results (rename = (initial_risk_rating = initial_risk_rating_num));
461 where upcase(rating_agency) eq 'S&P';
462
463 %if(&scen_weight_flg. = Y) %then
464 if mrsName="Weighted" then evaldate=intnx("&mipInterval.", reporting_dt, horizon, "SAME");;
465
466 /*---------------------------------------------------
467 * Convert Expanded INSTID back to original INSTID
468 *---------------------------------------------------*/
469 instid = substr(instid,1,find(instid,'_',-length(instid))-1);
470
471 /*---------------------------------------------------
472 * Adding variables related to VA reports filter
473 * - Below mvars are retrieved from RUN_OPTION table
474 *---------------------------------------------------*/
475 analysis_run_name = "&analysis_run_name";
476 load_id = "&analysis_run_type";
477 project_name = "&cycle_name";
478
479 /*-----------------------------------
480 * Treatment for initial_risk_rating
481 *-----------------------------------*/
482 /* For S&P ratings: Convert from numeric risk ratings to (character) FR Y-14 risk ratings */
483 if upcase(rating_agency) eq 'S&P' then
484 do;
485 select(initial_risk_rating_num);
486 &rating_list
487 otherwise initial_risk_rating = 'N/A';
488 end;
489 end;
490 /* For FICO score */
491 else if upcase(rating_agency) eq 'FICO' then
492 initial_risk_rating = input(rating_grade, 8.);
493 /* Others */
494 else
495 initial_risk_rating = "N/A";
496
497 /*-----------------------------
498 * Treatment for scenario_name
499 *-----------------------------*/
500 /* Removing BEP name (i.e., The value before the first hyphen) */
501 if not missing(scenario_name) then do;
502 scenario_name = strip(substr(scenario_name, find(scenario_name, "-")+1));
503 output;
504 end;
505 /* Identify BASECASE and assign a scenario name */
506 else if horizon eq 0 then do;
507 %do i=1 %to &scen_cnt;
508 scenario_id = "&&scen_id_&i";
509 scenario_name = "&&scen_nm_&i";
510 output;
511 %end;
512 end;
513 run;
514
515%mend run_postcode_ccar_provision;
516
517%run_postcode_ccar_provision