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
15 create table _fin_stmt_summary_base as
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,
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'
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
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
34 create table _fin_stmt_summary_adverse as
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,
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'
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
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
53 create table reg_cap_own_cap as
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
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
69 proc summary data=_fin_stmt_summary_base nway missing;
70 class entity_id reporting_dt forecast_period measure_type;
72 output out=pillar_1_total (drop=_TYPE_ _FREQ_)
73 sum(measure_value)=measure_value;
77 proc sort data=_fin_stmt_summary_adverse;
78 by entity_id reporting_dt risk_type forecast_period;
81 proc sort data=_fin_stmt_summary_base;
82 by entity_id reporting_dt risk_type forecast_period;
85 data pillar_2_risk_type pillar_2_adv;
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;
92 measure_value=sum(adv_account_value_amt,-base_account_value_amt);
94 risk_type='Stress Test - ' || strip(risk_type);
97 output pillar_2_risk_type;
101 proc summary data=pillar_2_risk_type nway missing;
102 class entity_id reporting_dt forecast_period measure_type;
104 output out=pillar_2_total (drop=_TYPE_ _FREQ_)
105 sum(measure_value)=measure_value;
110 length risk_type $50 sort_order 8.;
111 set pillar_1_total pillar_2_total;
112 risk_type='Capital Required (Standardized)';
116 proc summary data=cap_req_std nway missing;
117 class entity_id reporting_dt risk_type forecast_period measure_type sort_order;
119 output out=cap_req_std_total (drop=_TYPE_ _FREQ_)
120 sum(measure_value)=measure_value;
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;
131 risk_type='Pillar I Capital';
135 risk_type='Pillar II Capital';
142 create table _loss_dist_base as
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,
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'
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
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
161 create table _loss_dist_adverse as
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,
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'
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
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
181 create table ec_pillar_1_total as
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
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
196 create table cap_req_ec as
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
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
212 proc sort data=_loss_dist_base;
213 by entity_id reporting_dt risk_type forecast_period;
216 proc sort data=_loss_dist_adverse;
217 by entity_id reporting_dt risk_type forecast_period;
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;
227 measure_value=sum(adv_economic_capital,-base_economic_capital);
228 risk_type='Stress Test - ' || strip(risk_type);
232 proc summary data=pillar_2_adv_ec nway missing;
233 class entity_id reporting_dt forecast_period measure_type;
235 output out=pillar_2_total_ec (drop=_TYPE_ _FREQ_)
236 sum(measure_value)=measure_value;
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;
248 risk_type='Pillar I Capital';
252 risk_type='Pillar II Capital';
258 proc summary data=reg_cap_own_cap nway missing;
259 class entity_id reporting_dt forecast_period measure_type;
261 output out=reg_cap_own_cap_total (drop=_TYPE_ _FREQ_)
262 sum(measure_value)=measure_value;
266 proc sort data=cap_req_std_total;
267 by entity_id reporting_dt forecast_period;
270 proc sort data=reg_cap_own_cap_total;
271 by entity_id reporting_dt forecast_period;
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;
284 reg_cap_value=reg_cap_acct_value_amt;
285 cap_req_std_value=cap_req_acct_value_amt;
287 risk_type='Capital Surplus/Shortfall';
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)';
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';
307 proc sort data=capital_adequacy_sum;
308 by sort_order forecast_period risk_type;
314 %include "&irm_fa_path./source/sas/misc/ddl/reportmart/capital_adequacy_summary.sas" / source2 lrecl = 32000;
317 proc append base=CAPITAL_ADEQUACY_SUMMARY data=capital_adequacy_sum;
320 %irm_rgf_store_analysis_data(ds_in = CAPITAL_ADEQUACY_SUMMARY
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
332 , libref = &dr_libref.
333 , schema_name = &solutionId._capital_adequacy_summary
334 , schema_version = &content_version.
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
344 , host = &rgf_protocol.:
345 , server = &rgf_service.
346 , solution = &rgf_solution.
348 , tgt_ticket = &tgt_ticket.
349 , outVarTicket = ticket
350 , outSuccess = httpSuccess
351 , outResponseStatus = responseStatus
354 %if(not &httpSuccess.) %then %do;
355 %put ERROR: The aggregation model failed to produce a CAPITAL_ADEQUACY_SUMMARY dataset;
360 set new_analysis_data;
361 call symputx("analysis_data_key", key, "L");
364 %if %rsk_dsexist(CAPITAL_ADEQUACY_SUMMARY) %then %do;
368 libname cfg_lib "&irm_fa_path./landing_area/base/%lowcase(&config_set_id.)/static";
371 create table reportmart_config as
373 &analysis_data_key. as ANALYSIS_DATA_KEY
376 cfg_lib.reportmart_config as t1
378 data_definition as t2 on
379 lowcase(t1.schema_name) = t2.schemaName
380 and resolve(t1.schema_version)= t2.schemaVersion
382 t1.reportmart_group_id = "Capital Adequacy"
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.
404 %if %rsk_dsexist(load_result) %then %do;
405 %let &outVarStatus. = Successful;