SAS Documentation
SASĀ® Solution for Stress Testing
Reference manual - version 08.2021
Loading...
Searching...
No Matches
irmst_create_capital_adq_summary.sas
1%macro irmst_create_capital_adq_summary(fsp_base_ds = _fin_st_proj_base_dtl
2 , fsp_adverse_ds = _fin_st_proj_adverse_dtl
3 , loss_dist_ds = RISKAGG_SUMMARY_RESULTS
4 , reload_lasr_method =
5 , config_set =
6 , tgt_ticket =
7 , scenario_nm =
8 , analysis_run_nm =
9 , cycle_nm =
10 , outVarStatus =
11 );
12
13/* Roll up the data for the Capital Adequacy Report */
14 proc sql noprint;
15 create table _fin_stmt_summary_base as
16 select
17 min(entity_id) as entity_id,
18 min(reporting_dt) as reporting_dt,
19 strip(upper(min(forecast_period))) || ' ' || strip(put(min(forecast_time),8.)) as forecast_period length=32,
20 case
21 when upper(fs_account_cd)='OF_RWAST_CREDTRSK' then 'Credit Risk'
22 when upper(fs_account_cd)='OF_RWAST_MARKTRSK' then 'Market Risk'
23 when upper(fs_account_cd)='OF_RWAST_OPERNRSK' then 'Operational Risk'
24 else ''
25 end as risk_type length=50,
26 'account_value_amount' as measure_type length=32,
27 sum(account_value_amt,0) as measure_value length=8
28 from &fsp_base_ds.
29 where fs_account_cd in ('OF_RWAST_CREDTRSK','OF_RWAST_MARKTRSK','OF_RWAST_OPERNRSK')
30 group by entity_id, reporting_dt, calculated risk_type, forecast_time
31 order by entity_id, reporting_dt, calculated risk_type, forecast_time
32 ;
33
34 create table _fin_stmt_summary_adverse as
35 select
36 min(entity_id) as entity_id,
37 min(reporting_dt) as reporting_dt,
38 strip(upper(min(forecast_period))) || ' ' || strip(put(min(forecast_time),8.)) as forecast_period length=32,
39 case
40 when upper(fs_account_cd)='OF_RWAST_CREDTRSK' then 'Credit Risk'
41 when upper(fs_account_cd)='OF_RWAST_MARKTRSK' then 'Market Risk'
42 when upper(fs_account_cd)='OF_RWAST_OPERNRSK' then 'Operational Risk'
43 else ''
44 end as risk_type length=50,
45 'account_value_amount' as measure_type length=32,
46 sum(account_value_amt,0) as measure_value length=8
47 from &fsp_adverse_ds.
48 where fs_account_cd in ('OF_RWAST_CREDTRSK','OF_RWAST_MARKTRSK','OF_RWAST_OPERNRSK')
49 group by entity_id, reporting_dt, calculated risk_type, forecast_time
50 order by entity_id, reporting_dt, calculated risk_type, forecast_time
51 ;
52
53 create table reg_cap_own_cap as
54 select
55 min(entity_id) as entity_id,
56 min(reporting_dt) as reporting_dt,
57 strip(upper(min(forecast_period))) || ' ' || strip(put(min(forecast_time),8.)) as forecast_period length=32,
58 'Regulatory Capital (Own Capital)' as risk_type length=50,
59 'account_value_amount' as measure_type length=32,
60 sum(account_value_amt,0) as measure_value length=8
61 from &fsp_base_ds.
62 where fs_account_cd like 'OF_%'
63 group by entity_id, reporting_dt, risk_type, forecast_time
64 order by entity_id, reporting_dt, risk_type, forecast_time
65 ;
66 quit;
67
68 /* Get Pillar I Capital totals */
69 proc summary data=_fin_stmt_summary_base nway missing;
70 class entity_id reporting_dt forecast_period measure_type;
71 vars measure_value;
72 output out=pillar_1_total (drop=_TYPE_ _FREQ_)
73 sum(measure_value)=measure_value;
74 run;
75
76 /* Get risk_type values and Pillar II Capital totals */
77 proc sort data=_fin_stmt_summary_adverse;
78 by entity_id reporting_dt risk_type forecast_period;
79 run;
80
81 proc sort data=_fin_stmt_summary_base;
82 by entity_id reporting_dt risk_type forecast_period;
83 run;
84
85 data pillar_2_risk_type pillar_2_adv;
86 length risk_type $50;
87 merge _fin_stmt_summary_adverse (rename=(measure_value=adv_account_value_amt) in=adverse)
88 _fin_stmt_summary_base (rename=(measure_value=base_account_value_amt) in=base);
89 by entity_id reporting_dt risk_type forecast_period;
90 keep entity_id reporting_dt risk_type measure_value measure_type forecast_period;
91
92 measure_value=sum(adv_account_value_amt,-base_account_value_amt);
93
94 risk_type='Stress Test - ' || strip(risk_type);
95 output pillar_2_adv;
96
97 output pillar_2_risk_type;
98 run;
99
100 /* Get Pillar II Capital totals */
101 proc summary data=pillar_2_risk_type nway missing;
102 class entity_id reporting_dt forecast_period measure_type;
103 vars measure_value;
104 output out=pillar_2_total (drop=_TYPE_ _FREQ_)
105 sum(measure_value)=measure_value;
106 run;
107
108 /* Get Capital Required (Standardized) Total */
109 data cap_req_std;
110 length risk_type $50 sort_order 8.;
111 set pillar_1_total pillar_2_total;
112 risk_type='Capital Required (Standardized)';
113 sort_order=1;
114 run;
115
116 proc summary data=cap_req_std nway missing;
117 class entity_id reporting_dt risk_type forecast_period measure_type sort_order;
118 vars measure_value;
119 output out=cap_req_std_total (drop=_TYPE_ _FREQ_)
120 sum(measure_value)=measure_value;
121 run;
122
123 /* Stack all Capital Required (Standard) results together */
124 data cap_std_results;
125 length risk_type $50 sort_order 8.;
126 set cap_req_std_total pillar_1_total(in=p1) _fin_stmt_summary_base(in=base)
127 pillar_2_total(in=p2) pillar_2_adv(in=adv);
128 if base then sort_order=3;
129 if adv then sort_order=5;
130 if p1 then do;
131 risk_type='Pillar I Capital';
132 sort_order=2;
133 end;
134 if p2 then do;
135 risk_type='Pillar II Capital';
136 sort_order=4;
137 end;
138 run;
139
140 /* Create Capital Required (Economic Capital) section of report */
141 proc sql noprint;
142 create table _loss_dist_base as
143 select
144 min(entity_id) as entity_id,
145 min(reporting_dt) as reporting_dt,
146 strip(upper(min(forecast_period))) || ' ' || strip(put(min(forecast_time),8.)) as forecast_period length=32,
147 case
148 when upper(risk_type)='CREDIT' then 'Credit Risk'
149 when upper(risk_type)='MARKET' then 'Market Risk'
150 when upper(risk_type)='OPERATIONAL' then 'Operational Risk'
151 else ''
152 end as risk_type length=50,
153 'economic_capital' as measure_type length=32,
154 sum(economic_capital,0) as measure_value length=8
155 from &loss_dist_ds.
156 where upper(scenario_type) contains 'BASE' and entity_id ne '+' and risk_type ne '+'
157 group by entity_id, reporting_dt, calculated risk_type, forecast_time
158 order by entity_id, reporting_dt, calculated risk_type, forecast_time
159 ;
160
161 create table _loss_dist_adverse as
162 select
163 min(entity_id) as entity_id,
164 min(reporting_dt) as reporting_dt,
165 strip(upper(min(forecast_period))) || ' ' || strip(put(min(forecast_time),8.)) as forecast_period length=32,
166 case
167 when upper(risk_type)='CREDIT' then 'Credit Risk'
168 when upper(risk_type)='MARKET' then 'Market Risk'
169 when upper(risk_type)='OPERATIONAL' then 'Operational Risk'
170 else ''
171 end as risk_type length=50,
172 'economic_capital' as measure_type length=32,
173 sum(economic_capital,0) as measure_value length=8
174 from &loss_dist_ds.
175 where upper(scenario_type) contains 'ADV' and entity_id ne '+' and risk_type ne '+'
176 group by entity_id, reporting_dt, calculated risk_type, forecast_time
177 order by entity_id, reporting_dt, calculated risk_type, forecast_time
178 ;
179
180 /* Get Pillar I Capital totals */
181 create table ec_pillar_1_total as
182 select
183 min(entity_id) as entity_id,
184 min(reporting_dt) as reporting_dt,
185 strip(upper(min(forecast_period))) || ' ' || strip(put(min(forecast_time),8.)) as forecast_period length=32,
186 min(risk_type) as risk_type length=50,
187 'economic_capital' as measure_type length=32,
188 sum(economic_capital,0) as measure_value length=8
189 from &loss_dist_ds.
190 where upper(scenario_type) contains 'BASE' and risk_type='+' and entity_id ne '+'
191 group by entity_id, reporting_dt, risk_type, forecast_time
192 order by entity_id, reporting_dt, risk_type, forecast_time
193 ;
194
195 /* Get Capital Required (Economic Capital) total */
196 create table cap_req_ec as
197 select
198 min(entity_id) as entity_id,
199 min(reporting_dt) as reporting_dt,
200 strip(upper(min(forecast_period))) || ' ' || strip(put(min(forecast_time),8.)) as forecast_period length=32,
201 'Capital Required (Economic Capital)' as risk_type length=50,
202 'economic_capital' as measure_type length=32,
203 sum(economic_capital,0) as measure_value length=8
204 from &loss_dist_ds.
205 where upper(scenario_type) contains 'ADV' and risk_type='+' and entity_id ne '+'
206 group by entity_id, reporting_dt, risk_type, forecast_time
207 order by entity_id, reporting_dt, risk_type, forecast_time
208 ;
209 quit;
210
211 /* Get risk_type values and Pillar II Capital totals */
212 proc sort data=_loss_dist_base;
213 by entity_id reporting_dt risk_type forecast_period;
214 run;
215
216 proc sort data=_loss_dist_adverse;
217 by entity_id reporting_dt risk_type forecast_period;
218 run;
219
220 data pillar_2_adv_ec;
221 length risk_type $50;
222 merge _loss_dist_adverse (rename=(measure_value=adv_economic_capital) in=adverse)
223 _loss_dist_base (rename=(measure_value=base_economic_capital) in=base);
224 by entity_id reporting_dt risk_type forecast_period;
225 keep entity_id reporting_dt risk_type measure_value measure_type forecast_period;
226
227 measure_value=sum(adv_economic_capital,-base_economic_capital);
228 risk_type='Stress Test - ' || strip(risk_type);
229 run;
230
231 /* Get Pillar II Capital totals */
232 proc summary data=pillar_2_adv_ec nway missing;
233 class entity_id reporting_dt forecast_period measure_type;
234 vars measure_value;
235 output out=pillar_2_total_ec (drop=_TYPE_ _FREQ_)
236 sum(measure_value)=measure_value;
237 run;
238
239 /* Stack all Capital Required (Economic Capital) results together */
240 data cap_ec_results;
241 length risk_type $50 sort_order 8.;
242 set cap_req_ec(in=ec_tot) ec_pillar_1_total(in=p1) _loss_dist_base(in=base)
243 pillar_2_total_ec(in=p2) pillar_2_adv_ec(in=adv);
244 if ec_tot then sort_order=6;
245 if base then sort_order=8;
246 if adv then sort_order=10;
247 if p1 then do;
248 risk_type='Pillar I Capital';
249 sort_order=7;
250 end;
251 if p2 then do;
252 risk_type='Pillar II Capital';
253 sort_order=9;
254 end;
255 run;
256
257 /* Roll up the Regulatory Capital (Own Capital) Results */
258 proc summary data=reg_cap_own_cap nway missing;
259 class entity_id reporting_dt forecast_period measure_type;
260 vars measure_value;
261 output out=reg_cap_own_cap_total (drop=_TYPE_ _FREQ_)
262 sum(measure_value)=measure_value;
263 run;
264
265 /* Get the Capital Surplus/Shortfall Results */
266 proc sort data=cap_req_std_total;
267 by entity_id reporting_dt forecast_period;
268 run;
269
270 proc sort data=reg_cap_own_cap_total;
271 by entity_id reporting_dt forecast_period;
272 run;
273
274 data cap_surplus_shortfall;
275 length risk_type $50 sort_order reg_cap_acct_value_amt cap_req_std_value 8.;
276 merge cap_req_std_total (rename=(measure_value=cap_req_acct_value_amt))
277 reg_cap_own_cap_total (rename=(measure_value=reg_cap_acct_value_amt));
278 by entity_id reporting_dt forecast_period;
279 keep entity_id reporting_dt risk_type measure_value measure_type reg_cap_value cap_req_std_value forecast_period sort_order;
280
281 /* Measure value is set to missing b/c the capital surplus/shortfall needs to be calculated */
282 /* in VA in order to include the basel_minimum_pt in the following formula: reg_cap_value - (cap_req_std_value*basel_minimum_pct) */
283 measure_value=.;
284 reg_cap_value=reg_cap_acct_value_amt;
285 cap_req_std_value=cap_req_acct_value_amt;
286
287 risk_type='Capital Surplus/Shortfall';
288 sort_order=12;
289 run;
290
291 /* Put all results together to load into the capital_adequacy_summary table */
292 data capital_adequacy_sum;
293 length load_id $32 analysis_run_name $150 scenario_name $512 sort_order analysis_run_id 8. project_name $64.;
294 set cap_std_results cap_ec_results reg_cap_own_cap_total(in=reg_cap_own)
295 cap_surplus_shortfall;
296 if reg_cap_own then do;
297 risk_type='Regulatory Capital (Own Capital)';
298 sort_order=11;
299 end;
300 analysis_run_name="&analysis_run_nm.";
301 scenario_name="&scenario_nm.";
302 project_name = "&cycle_nm.";
303 analysis_run_id = &analysis_run_key.;
304 load_id = 'Production';
305 run;
306
307 proc sort data=capital_adequacy_sum;
308 by sort_order forecast_period risk_type;
309 run;
310
311 /* Get Reportmart data structure */
312 %let libref = work;
313 proc sql;
314 %include "&irm_fa_path./source/sas/misc/ddl/reportmart/capital_adequacy_summary.sas" / source2 lrecl = 32000;
315 quit;
316
317 proc append base=CAPITAL_ADEQUACY_SUMMARY data=capital_adequacy_sum;
318 run;
319
320 %irm_rgf_store_analysis_data(ds_in = CAPITAL_ADEQUACY_SUMMARY
321 /* Analysis Data Parameters */
322 , analysis_data_name = &outDsName.
323 , analysis_data_desc = Capital Adequacy Summary results with the &modelName. model executed on &run_date. at server time: &run_time.
324 , base_date = &base_dt.
325 , cycle_id = &cycle_key.
326 , entity_id = &entity_id.
327 , status_cd = Preliminary
328 , analysis_run_id = &analysis_run_key.
329 , load_id = Production
330
331 /* Data Definition Parameters */
332 , libref = &dr_libref.
333 , schema_name = &solutionId._capital_adequacy_summary
334 , schema_version = &content_version.
335
336 /* Output tables */
337 , out_exceptions = data_exceptions
338 , out_analysis_data = new_analysis_data
339 , out_partition_list = new_partitions
340 , out_data_definition = data_definition
341 , out_link_instance = link_instance
342
343 /* Connection Parameters */
344 , host = &rgf_protocol.://&rgf_host.
345 , server = &rgf_service.
346 , solution = &rgf_solution.
347 , port = &rgf_port.
348 , tgt_ticket = &tgt_ticket.
349 , outVarTicket = ticket
350 , outSuccess = httpSuccess
351 , outResponseStatus = responseStatus
352 );
353
354 %if(not &httpSuccess.) %then %do;
355 %put ERROR: The aggregation model failed to produce a CAPITAL_ADEQUACY_SUMMARY dataset;
356 %end;
357 %else; %do;
358
359 data _null_;
360 set new_analysis_data;
361 call symputx("analysis_data_key", key, "L");
362 run;
363
364 %if %rsk_dsexist(CAPITAL_ADEQUACY_SUMMARY) %then %do;
365
366 /* Subset the reportmart_config for Capital Adequacy */
367 /* Assign config and mapping libraries */
368 libname cfg_lib "&irm_fa_path./landing_area/base/%lowcase(&config_set_id.)/static";
369
370 proc sql;
371 create table reportmart_config as
372 select
373 &analysis_data_key. as ANALYSIS_DATA_KEY
374 , t1.*
375 from
376 cfg_lib.reportmart_config as t1
377 join
378 data_definition as t2 on
379 lowcase(t1.schema_name) = t2.schemaName
380 and resolve(t1.schema_version)= t2.schemaVersion
381 where
382 t1.reportmart_group_id = "Capital Adequacy"
383 ;
384 quit;
385
386 /* Load the reportmart table */
387 %irmc_load_reportmart(partition_no = 1
388 , ds_in_cardinality =
389 , ds_in_reportmart_config = reportmart_config
390 , ds_out_load_result = load_result
391 , irm_input_libraries =
392 , dr_libref = &dr_libref.
393 , dr_library_name = &dr_library_name.
394 , mart_libref = &mart_libref.
395 , rgf_protocol = &rgf_protocol.
396 , rgf_host = &rgf_host.
397 , rgf_port = &rgf_port.
398 , rgf_service = &rgf_service.
399 , rgf_solution = &rgf_solution.
400 , rgf_tgt_ticket = &tgt_ticket.
401 );
402
403 /* Check to see if the load was successful */
404 %if %rsk_dsexist(load_result) %then %do;
405 %let &outVarStatus. = Successful;
406 %end;
407 %end;
408 %end;
409 %mend;