SAS Documentation
SASĀ® Solution for Stress Testing
Reference manual - version 08.2021
Loading...
Searching...
No Matches
irmst_model_fs_projection.sas
1/* ************************************************** */
2/* Financial Statement Projection */
3/* ************************************************** */
4
5%macro fs_projection(input_data_rwa_key =
6 , bsRetainRREAccnt =
7 , bsRetainYPFAccnt =
8 , ofRWAAccnt =
9 , data_map_gl =
10 , data_map_pl =
11 , data_map_of =
12 ) / minoperator;
13
14 %local
15 interval
16 interval_count
17 i
18 k
19 schema_gl
20 schema_pl
21 schema_gl_version
22 schema_pl_version
23 ds_in_aggregation_config
24 tbls_info
25 input_data_of_key
26 input_data_rwa_key
27 id_var_bs
28 id_var_pl
29 id_var_of
30 target_var_bs
31 target_var_pl
32 target_var_of
33 plan_data_bs_key
34 plan_data_pl_key
35 plan_data_bs
36 plan_data_pl
37 plan_data_of
38 plan_data_rwa
39 balance_sheet_cd
40 profit_loss_cd
41 cd_var_bs
42 cd_var_pl
43 ds_acct_cd_lookup
44 scenario_name
45 ;
46
47 /* Set up macro variables needed for this model */
48 %let cd_var_bs = balance_sheet_cd;
49 %let cd_var_pl = profit_loss_cd;
50 %let schema_gl = st_gl_account;
51 %let schema_pl = st_profit_loss_account;
52 %let schema_of = st_own_funds_account;
53 %let ds_in_aggregation_config = ST_CFG.aggregation_rules_config;
54 %let tbls_info = ST_STG.tbls_info;
55 %let plan_data_of = ST_STG.st_own_funds_account;
56 %let plan_data_rwa = ST_STG.st_rwa_weights;
57 %let map_movement_type = ST_MAP.map_movement_type;
58 %let map_profit_loss_hierarchy = ST_MAP.map_profit_loss_hierarchy;
59 %let map_balance_sheet_hierarchy = ST_MAP.map_balance_sheet_hierarchy;
60 %let ds_acct_cd_lookup = ST_STG.account_cd_lookup;
61
62 /* Current content package does not provide coverage for Market Risk and Operational Risk.
63 Instead a growth for incrementing the value from previous period.
64 The same growth percent is used for line items in Own Funds that does not have mapping equity line item*/
65 %let of_growth_pct = 0.1;
66
67
68 /* Create expanded version of BEP summary (one row per planning data) */
69 data work.bep_summary_exp;
70 set &ds_in_bep_summary.;
71 original_targetVariable = targetVariable;
72 original_accountID = accountID;
73 original_planningDataKey = planningDataKey;
74 original_dataDefSchemaName = dataDefSchemaName;
75 original_dataDefSchemaVersion = dataDefSchemaVersion;
76 do i=1 to countw(original_targetVariable,",");
77 targetVariable = scan(original_targetVariable,i,",");
78 accountID = scan(original_accountID,i,",");
79 planningDataKey = scan(original_planningDataKey,i,",");
80 dataDefSchemaName = scan(original_dataDefSchemaName,i,",");
81 dataDefSchemaVersion = scan(original_dataDefSchemaVersion,i,",");
82 output;
83 end;
84
85 call symputx("fund_src_vars", fundSrcVars, "L");
86
87 /* Interval count and interval will be the same across all records in the bep */
88 call symputx("interval_count", intervalCount, "L");
89 call symputx("interval", interval, "L");
90
91 drop original_: i;
92 run;
93
94 /* Validate the model assumptions for the BEP and store the schema versions */
95 data _null_;
96 set work.bep_summary_exp end=last;
97 retain gl_found pl_found of_found 0;
98 /* Make sure there only GL or PL planning data instances were selected */
99 if upcase(dataDefSchemaName) not in (%upcase("&schema_gl."), %upcase("&schema_pl."), %upcase("&schema_of.")) then do;
100 put "ERROR: Expecting only planning data with schema name &schema_gl. or &schema_pl. or &schema_of..";
101 stop;
102 end;
103 /* Store the GL schema version */
104 else if upcase(dataDefSchemaName) eq upcase("&schema_gl.") then do;
105 gl_found = 1;
106 call symputx("schema_gl_version", dataDefSchemaVersion, "L");
107 end;
108 /* Store the OF schema version */
109 else if upcase(dataDefSchemaName) eq upcase("&schema_of.") then do;
110 of_found = 1;
111 call symputx("schema_of_version", dataDefSchemaVersion, "L");
112 end;
113 /* Store the PL schema version */
114 else do;
115 pl_found = 1;
116 call symputx("schema_pl_version", dataDefSchemaVersion, "L");
117 end;
118 /* Make sure there is 1 GL and 1 PL planning data instance */
119 if last and (_N_ ne 3 or gl_found = 0 or pl_found = 0 or of_found = 0) then do;
120 put "ERROR: Expecting exactly one planning data with schema name &schema_gl. and one with schema name &schema_pl. and one with schema name &schema_of..";
121 stop;
122 end;
123 run;
124
125 /* Get interval count, segmentation variables, target variabes from bep */
126 data _null_;
127 set work.bep_summary_exp;
128 length type $ 2;
129 /* Get the segmentation variable (account id) and target variable */
130 if upcase(dataDefSchemaName) = upcase("&schema_gl.") then type = "bs";
131 else if upcase(dataDefSchemaName) = upcase("&schema_pl.") then type = "pl";
132 else if upcase(dataDefSchemaName) = upcase("&schema_of.") then type = "of";
133 call symputx(cats("id_var_",type), accountId, "L");
134 call symputx(cats("target_var_",type), targetVariable, "L");
135 call symputx(cats("plan_data_", type, "_key"), planningDataKey, "L");
136 run;
137
138 /* Get PL and GL tables to create the account code lookup table */
139 %let httpSuccess = 0;
140 %let responseStatus =;
141 %let ticket =;
142 %irm_rgf_retrieve_analysis_data(key = &plan_data_bs_key.
143 , libref = rqsst
144 , outds = work.plan_data_bs_tmp
145 , custom_code = length movement_desc $ 100.; movement_id = 1; movement_desc = "Opening Balance"
146 , out_type = view
147 , outds_dataInfo = planning_dataInfo_bs
148 , outds_dataDef = planning_dataDef_bs
149 , host = &rgf_protocol.://&rgf_host.
150 , server = &rgf_service.
151 , solution = rmc
152 , port = &rgf_port.
153 , tgt_ticket = &tgt_ticket.
154 , outVarTicket = ticket
155 , outSuccess = httpSuccess
156 , outResponseStatus = responseStatus
157 , restartLUA = Y
158 , clearCache = Y
159 );
160 %let httpSuccess = 0;
161 %let responseStatus =;
162 %let ticket =;
163 %irm_rgf_retrieve_analysis_data(key = &plan_data_pl_key.
164 , libref = rqsst
165 , outds = work.plan_data_pl_tmp
166 , custom_code = length movement_desc $ 100.; movement_id = 1; movement_desc = "Opening Balance"
167 , out_type = view
168 , outds_dataInfo = planning_dataInfo_pl
169 , outds_dataDef = planning_dataDef_pl
170 , host = &rgf_protocol.://&rgf_host.
171 , server = &rgf_service.
172 , solution = rmc
173 , port = &rgf_port.
174 , tgt_ticket = &tgt_ticket.
175 , outVarTicket = ticket
176 , outSuccess = httpSuccess
177 , outResponseStatus = responseStatus
178 , restartLUA = Y
179 , clearCache = Y
180 );
181
182
183 /* Create a lookup table with GL/PL account ids and account codes */
184 data &ds_acct_cd_lookup;
185 length account_id $ 32.
186 account_cd $ 32.;
187 keep account_cd account_id;
188 set work.plan_data_bs_tmp (keep = &id_var_bs. &cd_var_bs.)
189 work.plan_data_pl_tmp (keep = &id_var_pl. &cd_var_pl.);
190 account_id = coalescec(&id_var_bs., &id_var_pl.);
191 account_cd = coalescec(&cd_var_bs., &cd_var_pl.);
192 output;
193 account_id = account_cd;
194 output;
195 run;
196
197
198 /* Update ds_in_bep_details with account code using account id. There are 2 conversions that need to take place:
199 1) account_id column needs to be used to get the account_cd
200 2) Same as above but for each of the funding source columns.
201
202 Note that account_id is the key for the hash in all cases, but is also a column we want to keep
203 in the dataset, so we assign it to a temp variable and then use account_is as the key for several lookups,
204 then assign the value back again.
205 */
206 %if not (&cd_var_bs. = &id_var_bs. and &cd_var_pl. = &id_var_pl.) %then %do;
207 data work.ds_in_bep_details;
208 set &ds_in_bep_details. (where=(strip(planningDataKey) ne strip("&plan_data_of_key.")) rename=(accountId=account_id));
209 length account_cd $ 32. lookup_id $ 32. lookup_cd $ 32.;
210 if _N_ = 1 then do;
211 /* Get the account id-code mapping */
212 declare hash hAcct(dataset: "&ds_acct_cd_lookup.");
213 hAcct.defineKey("account_id");
214 hAcct.defineData("account_cd");
215 hAcct.defineDone();
216
217 declare hash hfundsrc(dataset: "&ds_acct_cd_lookup.(rename=(account_id=lookup_id account_cd=lookup_cd))");
218 hfundsrc.defineKey("lookup_id");
219 hfundsrc.defineData("lookup_cd");
220 hfundsrc.defineDone();
221 end;
222
223 call missing(account_cd);
224 _rc_ = hAcct.find();
225
226 %do x=1 %to %sysfunc(countw(%quote(&fund_src_vars),%str(,)));
227 call missing(lookup_cd,lookup_id);
228 %let varname = %scan(%quote(&fund_src_vars),&x.);
229 lookup_id = &varname.;
230 _rc_ = hfundsrc.find();
231 if _rc_ eq 0 then do;
232 &varname. = lookup_cd;
233 end;
234 %end;
235
236 drop lookup_id lookup_cd _rc_;
237 run;
238 %end;
239 /* Account id variables are the same as the account code variables, no need to look up account codes */
240 %else %do;
241 data work.ds_in_bep_details;
242 set &ds_in_bep_details. (where=(strip(planningDataKey) ne strip("&plan_data_of_key.")) rename=(accountId=account_id));
243 length account_cd $ 32. ;
244 account_cd = account_id;
245 run;
246 %end;
247
248 /* Split the BEP into VOLASSUMP, YLDASSUMP and VALASSUMP - Own Funds is treated separately */
249 data volAssump yldAssump valAssump;
250 set work.ds_in_bep_details;
251 if varFamily='volume' then output work.volAssump;
252 else if varFamily='yield' then output work.yldAssump;
253 else if varFamily='value' then output work.valAssump;
254 run;
255
256 /* Get the planning data librefs/table names */
257 data _null_;
258 set &tbls_info.;
259 if tbl_name = "&schema_gl." then type = "BS";
260 else if tbl_name = "&schema_pl." then type = "PL";
261 call symputx(cats("plan_data_",type), cats(tbl_lib, ".", tbl_name), "L");
262 /* Scenario Name will be the same across all records in the table */
263 call symputx("scenario_name", scenario_name, "L");
264 run;
265
266 /* Make sure the planning data exists and the aggregated planning data exists*/
267 %if(not %rsk_dsexist(work.plan_data_bs_tmp) or not %rsk_dsexist(work.plan_data_pl_tmp)) %then %do;
268 %put ERROR: Planning data does not exist. Could not perform financial statement projection.;
269 %return;
270 %end;
271 %if(not %symexist(plan_data_bs) or not %symexist(plan_data_pl)) %then %do;
272 %put ERROR: Aggregated planning data does not exist. Could not perform financial statement projection.;
273 %return;
274 %end;
275 %if(not %rsk_dsexist(&plan_data_bs.) or not %rsk_dsexist(&plan_data_pl.)) %then %do;
276 %put ERROR: Aggregated planning data does not exist. Could not perform financial statement projection.;
277 %return;
278 %end;
279
280 /* Get OF Planning Data */
281 %let httpSuccess = 0;
282 %let responseStatus =;
283 %let ticket =;
284 %irm_rgf_retrieve_analysis_data(key = &plan_data_of_key.
285 , libref = rqsst
286 , outds = &plan_data_of.
287 , custom_code = length movement_desc $ 100.; movement_id = 1; movement_desc = "Opening Balance"
288 , out_type = view
289 , outds_dataInfo = planning_dataInfo_of
290 , outds_dataDef = planning_dataDef_of
291 , host = &rgf_protocol.://&rgf_host.
292 , server = &rgf_service.
293 , solution = rmc
294 , port = &rgf_port.
295 , tgt_ticket = &tgt_ticket.
296 , outVarTicket = ticket
297 , outSuccess = httpSuccess
298 , outResponseStatus = responseStatus
299 , restartLUA = Y
300 , clearCache = Y
301 );
302
303 /* Exit in case of errors */
304 %if(not &httpSuccess. or not %rsk_dsexist(&plan_data_of.)) %then %do;
305 %put ERROR: Own Funds data with key &input_data_of_key. does not exist.;
306 %return;
307 %end;
308
309 /* Get RWA input data */
310 %let httpSuccess = 0;
311 %let responseStatus =;
312 %let ticket =;
313 %irm_rgf_retrieve_analysis_data(key = &input_data_rwa_key.
314 , libref = rqsst
315 , outds = &plan_data_rwa.
316 , custom_code = length movement_desc $ 100.; movement_id = 1; movement_desc = "Opening Balance"
317 , outds_dataInfo = planning_dataInfo_rwa
318 , outds_dataDef = planning_dataDef_rwa
319 , host = &rgf_protocol.://&rgf_host.
320 , server = &rgf_service.
321 , solution = rmc
322 , port = &rgf_port.
323 , tgt_ticket = &tgt_ticket.
324 , outVarTicket = ticket
325 , outSuccess = httpSuccess
326 , outResponseStatus = responseStatus
327 , restartLUA = Y
328 , clearCache = Y
329 );
330
331 /* Exit in case of errors */
332 %if(not &httpSuccess. or not %rsk_dsexist(&plan_data_rwa.)) %then %do;
333 %put ERROR: RWA data with key &input_data_rwa_key. does not exist.;
334 %return;
335 %end;
336
337
338 /* Using analysis data info construct Financial Statement strucutre (GL and PL in one table with ACTUAL AMOUNT at T0)*/
339 proc sql;
340 create table fsStructureT0 as
341 select distinct &cd_var_bs. as account_cd
342 , &id_var_bs. as account_id
343 , sum(&target_var_bs.) as amountT0
344 , forecast_horizon as activationHorizon
345 ,'BS' as fs_type length=10
346 ,BALANCE_SHEET_LEV1 as accnt_type length=200
347
348 from &plan_data_bs.
349 where forecast_horizon = 0
350 group by account_cd, account_id
351 union
352 select distinct &cd_var_pl. as account_cd
353 , &id_var_pl. as account_id
354 , sum(&target_var_pl.) as amountT0
355 , forecast_horizon as activationHorizon
356 , 'PL' as fs_type length=10
357 ,PROFIT_LOSS_LEV1 as accnt_type length=200
358 from &plan_data_pl.
359 where forecast_horizon = 0
360 group by account_cd, account_id
361 ;
362 quit;
363
364 proc sql;
365 create table ownFundsT0 as
366 select distinct &cd_var_bs. as account_cd
367 , &id_var_of. as account_id
368 , sum(&target_var_of.) as amountT0
369 , forecast_horizon as activationHorizon
370 from &plan_data_of.
371 where forecast_horizon = 0
372 group by account_cd, account_id
373 ;
374 quit;
375
376 /* Get model results (from BS Aggregation engine) for interval i */
377 data modelResults;
378 set &plan_data_bs. (rename=(&cd_var_bs. = account_cd
379 &target_var_bs. = amount_model))
380 &plan_data_pl. (rename=(&cd_var_pl. = account_cd
381 &target_var_pl. = amount_model))
382 ;
383 length account_cd $32.;
384 keep account_cd
385 forecast_horizon
386 amount_model
387 ;
388 run;
389
390 %let i = 1;
391 %do %while(&i. le &interval_count.);
392
393 %let j = %eval(&i.-1);
394 %let actualsData = fsStructureT&j.;
395
396 /****************************************************************/
397 /* Period-Opening Adjustments */
398 /****************************************************************/
399 /* Calcualte PL from previous time period to net the amount to 0 */
400 proc sql noprint;
401 select sum(amountT&j.) format=BEST12.
402 into :profit_loss_amt_yr&j.
403 from work.fsStructureT&j.
404 where account_cd in (select distinct &cd_var_pl.
405 from &plan_data_pl.)
406 and account_cd in (select distinct &cd_var_pl.
407 from &map_profit_loss_hierarchy.
408 where roll_up ne 'Yes')
409 ;
410 quit;
411
412 %let profit_loss_amt_yr&j.=&&profit_loss_amt_yr&j.;
413
414 data work.bep_yr_bg_posting (rename=(account_cd = posting_account_id)
415 keep= posting_group_id
416 account_cd
417 movement:
418 variation
419 )
420 work.fsStructureT&j.(drop=posting_group_id movement:);
421 attrib
422 posting_group_id length = $50. label = "Posting Group Id"
423 movement_id length = 8. label = "Movement Id"
424 movement_desc length = $100. label = "Movement Description"
425 %irm_get_attrib_def(ds_in = &map_movement_type.)
426 ;
427 set work.fsStructureT&j.(keep= account_cd account_id amountT&j. activationHorizon fs_type accnt_type);
428 posting_group_id = cats('PRE_',account_cd);
429
430 if _N_ = 1 then do;
431 /* Declare lookup for retrieving the movement type and category */
432 declare hash hMvmt(dataset: "&map_movement_type.");
433 hMvmt.defineKey("movement_type_cd");
434 hMvmt.definedata("movement_type", "movement_category");
435 hMvmt.defineDone();
436 end;
437 /* Set the movement code description */
438 movement_type_cd = "BS_MODEL";
439 movement_desc = "Period Opening Adjustments";
440 movement_id = 1;
441 /* Lookup movement type and category */
442 _rc_ = hMvmt.find();
443
444 if account_cd = "&bsRetainRREAccnt." then do;
445 /* Move Amount from Net Income to Shareholders into Retained Earning. Net income to shareholders is equal to PL total from previous period */
446 /*BS Retained Earning Account(T1) = BS Retained Earning Account(T1)- PL_TOTAL(T0)*/
447 amountT&j. = sum(amountT&j.,&&profit_loss_amt_yr&j..);
448 variation = &&profit_loss_amt_yr&j..;
449 output work.bep_yr_bg_posting;
450 end;
451
452 if account_cd = "&bsRetainYPFAccnt." then do;
453 /*Set the Net income to shareholders to zero*/
454 /*Net Income to Shareholders(T1) = Net Income to Shareholders(T1) - PL_TOTAL(T0) */
455 amountT&j. = sum(amountT&j.,-&&profit_loss_amt_yr&j..);
456 variation= -&&profit_loss_amt_yr&j..;
457 output work.bep_yr_bg_posting;
458 /*This must make the bsRetainYPFAccnt to zero*/
459 if amountT&j. ne 0 then
460 put "WARNING: &bsRetainYPFAccnt. from period &j is not equal to total PL";
461 end;
462
463 output work.fsStructureT&j.;
464 drop _rc_;
465 run;
466 /****************************************************************/
467 /* Volume Calculations */
468 /****************************************************************/
469 %irmst_apply_volume_calculations( map_movement_type = &map_movement_type.
470 , actualsData = &actualsData.
471 , activationHorizon = &i.
472 );
473
474
475 /****************************************************************/
476 /* Yield Calculations */
477 /****************************************************************/
478 %irmst_apply_yield_calculations( map_movement_type = &map_movement_type.
479 , actualsData = &actualsData.
480 , activationHorizon = &i.
481 );
482
483 /****************************************************************/
484 /* Value Calculations */
485 /****************************************************************/
486 %irmst_apply_value_calculations( map_movement_type = &map_movement_type.
487 , actualsData = &actualsData.
488 , activationHorizon = &i.
489 )
490
491 /****************************************************************/
492 /* Period-Close Adjustments */
493 /****************************************************************/
494
495 /*Calculate the PL total for the current time period (&i)*/
496 proc sql noprint;
497 select sum(amountT&i.) format=BEST12.
498 into :profit_loss_amt_yr&i.
499 from work.fsStructureT&i.
500 where account_cd in (select distinct &cd_var_pl.
501 from &plan_data_pl.)
502 and account_cd in (select distinct &cd_var_pl.
503 from &map_profit_loss_hierarchy.
504 where roll_up ne 'Yes')
505 ;
506 quit;
507
508 %let profit_loss_amt_yr&i.=&&profit_loss_amt_yr&i.;
509
510 /*The bsRetainYPFAccnt must represent the PL of the current time period */
511 data work.bep_ynd_posting (rename=(account_cd = posting_account_id)
512 keep= posting_group_id
513 account_cd
514 movement:
515 variation
516 )
517 work.fsStructureT&i. (drop= posting_group_id
518 variation);
519 attrib
520 posting_group_id length = $50. label = "Posting Group Id"
521 movement_id length = 8. label = "Movement Id"
522 movement_desc length = $100. label = "Movement Description"
523 %irm_get_attrib_def(ds_in = &map_movement_type.)
524 ;
525 set work.fsStructureT&i.;
526 posting_group_id = cats('YND_',account_cd);
527
528 if _N_ = 1 then do;
529 /* Declare lookup for retrieving the movement type and category */
530 declare hash hMvmt(dataset: "&map_movement_type.");
531 hMvmt.defineKey("movement_type_cd");
532 hMvmt.definedata("movement_type", "movement_category");
533 hMvmt.defineDone();
534 end;
535 /* Set the movement code description */
536 movement_type_cd = "BS_MODEL";
537 movement_desc = "Year end Adjustments";
538 movement_id = 6;
539 /* Lookup movement type and category */
540 _rc_ = hMvmt.find();
541
542 if account_cd = "&bsRetainYPFAccnt." then do;
543 /*Net income account represents the total PL amount. Move the total PL amount to this account.*/
544 /*Net Income to Shareholders(T1) = PL_TOTAL(T1) */
545 amountT&i. = &&profit_loss_amt_yr&i..;
546 variation= &&profit_loss_amt_yr&i..;
547 output work.bep_ynd_posting;
548 end;
549
550 /* Set movement type in fsStructureT&i. */
551 movement_desc = "BS Projection Model";
552 movement_id = 10;
553 output work.fsStructureT&i.;
554 drop _rc_;
555 run;
556
557 /****************************************************************/
558 /* Record Movements in the input data */
559 /****************************************************************/
560
561 /*Combine all postings to record each posting as movements*/
562 %let movement_out_vars = posting_group_id
563 posting_account_id
564 variation
565 movement:
566 ;
567
568 data movementsT&i.;
569 set
570 work.bep_yr_bg_posting(keep= &movement_out_vars)
571 work.bep_vol_posting(keep= &movement_out_vars)
572 work.bep_yld_posting(keep= &movement_out_vars)
573 work.bep_val_posting(keep= &movement_out_vars)
574 %if(%rsk_dsexist(lineitem_rule_posting)) %then %do;
575 work.lineitem_rule_posting(keep= &movement_out_vars)
576 %end;
577 work.bep_ynd_posting(keep= &movement_out_vars)
578 ;
579 rename posting_account_id = account_id;
580 length scenario_name $512. forecast_horizon 8.;
581 scenario_name = "&scenario_name.";
582 forecast_horizon = &i.;
583 run;
584
585 /****************************************************************/
586 /* RWA Calculation */
587 /****************************************************************/
588
589 /* Update the RWA values with aggregation from credit risk detail */
590 data _null_;
591 set fsStructureT&i. end=last;
592 length forecast_horizon 8.
593 roll_up $10.;
594 retain rwa_total 0;
595 if _N_ = 1 then do;
596 /* Define lookup table of RWA input Analysis Data */
597 declare hash hRWA(dataset: "&plan_data_rwa.(rename=(&cd_var_bs=account_cd rwa=rwa_input forecast_horizon=activationHorizon))");
598 hRWA.defineKey("account_cd","activationHorizon");
599 hRWA.defineData("rwa_input");
600 hRWA.defineDone();
601 /* Define lookup table of BS hierarchy mapping table to identify roll-up accounts */
602 declare hash hBSMap(dataset:"&map_balance_sheet_hierarchy.(rename=(&cd_var_bs=account_cd))");
603 hBSMap.defineKey("account_cd");
604 hBSMap.defineData("roll_up");
605 hBSMap.defineDone();
606 /* Define lookup table for data coming form aggregation engine */
607 declare hash hModelDataBS(dataset: "&plan_data_bs.(rename=(&cd_var_bs=account_cd forecast_horizon=activationHorizon rwa=rwa_model))");
608 hModelDataBS.defineKey("account_cd","activationHorizon");
609 hModelDataBS.defineData("rwa_model");
610 hModelDataBS.defineDone();
611 end;
612 call missing(rwa_input);
613 call missing(rwa_model);
614
615 forecast_horizon = activationHorizon;
616
617 _rc_ = hRWA.find();
618 _rc1_ = hModelDataBS.find();
619 _rc3_ = hBSMap.find();
620
621 rwa = coalesce(rwa_model, rwa_input * amountT&i.);
622
623 if roll_up ne 'Yes' then
624 rwa_total=sum(rwa_total,rwa);
625
626 if last then do;
627 call symputx("rwa_credit", rwa_total);
628 end;
629 run;
630
631 %put NOTE: RWA Credit = &rwa_credit.;
632
633 /****************************************************************/
634 /* OF Calculation */
635 /****************************************************************/
636 /* For Own Funds, just use the Value adustment */
637 data work.OF_adjustments;
638 set &ds_in_bep_details. (where=(strip(planningDataKey) eq strip("&plan_data_of_key.") and varFamily eq 'value') rename=(accountId=account_id activationHorizon=forecast_horizon));
639 run;
640 data ownFundsT&i.;
641 set ownFundsT&j.;
642 attrib
643 movement_id length = 8. label = "Movement Id"
644 movement_desc length = $100. label = "Movement Description"
645 forecast_horizon length = 8. label = "Forecast Horizon"
646 %irm_get_attrib_def(ds_in = &map_movement_type.)
647 ;
648 forecast_horizon = &i.;
649 if _N_ = 1 then do;
650 /* Declare lookup for retrieving the movement type and category */
651 declare hash fsProjection(dataset: "work.fsStructureT&i.");
652 fsProjection.defineKey("account_cd");
653 fsProjection.definedata("amountT&i.");
654 fsProjection.defineDone();
655 /* Declare lookup for retrieving the movement type and category */
656 declare hash hMvmt(dataset: "&map_movement_type.");
657 hMvmt.defineKey("movement_type_cd");
658 hMvmt.definedata("movement_type", "movement_category");
659 hMvmt.defineDone();
660 /* Declare lookup for value adjustment */
661 declare hash hValAdj(dataset: "OF_adjustments");
662 hValAdj.defineKey("forecast_horizon", "account_id");
663 hValAdj.definedata("value_adjust");
664 hValAdj.defineDone();
665 end;
666 call missing(amountT&i.);
667 /* Set the movement code description */
668 movement_type_cd = "BS_MODEL";
669 movement_desc = "BS Projection Model";
670 movement_id = 10;
671
672 /* Lookup movement type and category */
673 _rc1_ = hMvmt.find();
674 _rc2_ = fsProjection.find();
675
676 if _rc2_ ne 0 then do;
677 amountT&i.= amountT&j.;
678 call missing(value_adjust);
679 _rc3_ = hValAdj.find();
680 amountT&i. = amountT&i.*(1+coalesce(value_adjust,0));
681 end;
682
683 /*Apply the Creditrisk RWA value to corresponding line item in own funds*/
684 if account_id = "&ofRWAAccnt." then
685 amountT&i.= &rwa_credit.;
686
687 /****************************************************************************************/
688 /* Insert any custom code here to lookup or calcualte own funds value for market risk */
689 /* and operational risk */
690 /****************************************************************************************/
691
692 forecast_horizon = &i.;
693
694 drop _rc1_
695 _rc2_
696 activationHorizon
697 ;
698 run;
699
700 %let dropvars = account_cd account_id amountT&j. accnt_type;
701 /* Merge projections with analysis data - BS to get values for columns such as GEOGRAPHY, CHART_OF_ACCOUNT, HIERARCHY, etc.*/
702 proc sql;
703 create table work.plan_data_bs&i._out (drop=&dropvars.) as
704 select *
705 from work.plan_data_bs_tmp (drop = &target_var_bs.
706 scenario_id
707 forecast_horizon
708 movement:
709 ) t1
710 inner join fsStructureT&i.(rename=(amountT&i. = &target_var_bs
711 activationHorizon = forecast_horizon)) t2
712 on t1.&cd_var_bs. = t2.account_cd
713 ;
714 quit;
715
716 /* Merge projections with analysis data - PL */
717 proc sql;
718 create table work.plan_data_pl&i._out (drop = &dropvars.) as
719 select *
720 from work.plan_data_pl_tmp (drop = &target_var_pl.
721 scenario_id
722 forecast_horizon
723 movement:) t1
724 inner join fsStructureT&i.(rename=(amountT&i. = &target_var_pl
725 activationHorizon = forecast_horizon)) t2
726 on t1.&cd_var_pl. = t2.account_cd
727 ;
728 quit;
729
730 /* Record OF output as movement */
731 proc sql;
732 create table work.plan_data_of&i._out (drop = &dropvars.) as
733 select *
734 from &plan_data_of. (drop = &target_var_of.
735 forecast_horizon
736 scenario_id
737 movement:
738 ) t1
739 left join work.ownFundsT&i (rename=(amountT&i. = &target_var_of)) t2
740 on t1.&id_var_of. = t2.account_id
741 ;
742 quit;
743
744 %let i = %eval(&i.+1); /* increment &i. */
745 %end; /* end do while loop */
746
747 /****************************************************************/
748 /* Combine the plan_data_<type>&i tables and apply mapping */
749 /****************************************************************/
750
751 /* Get the BS/PL/OF data maps */
752 %irmc_map_data(dataMap_key = &data_map_gl.
753 , ds_in_map_config =
754 , map_type = MODEL
755 , workgroup =
756 , modeling_system =
757 , ds_out = _tmp_data_structure_
758 , fout = fmap_gl
759 , outVarTicket = ticket
760 , outSuccess = httpSuccess
761 , outResponseStatus = responseStatus
762 );
763 %irmc_map_data(dataMap_key = &data_map_pl.
764 , ds_in_map_config =
765 , map_type = MODEL
766 , workgroup =
767 , modeling_system =
768 , ds_out = _tmp_data_structure_
769 , fout = fmap_pl
770 , outVarTicket = ticket
771 , outSuccess = httpSuccess
772 , outResponseStatus = responseStatus
773 );
774 %irmc_map_data(dataMap_key = &data_map_of.
775 , ds_in_map_config =
776 , map_type = MODEL
777 , workgroup =
778 , modeling_system =
779 , ds_out = _tmp_data_structure_
780 , fout = fmap_of
781 , outVarTicket = ticket
782 , outSuccess = httpSuccess
783 , outResponseStatus = responseStatus
784 );
785
786 /* Update planning data with projected values */
787 /* BS Projection */
788 data glAnalysisDataOut;
789 set work.plan_data_bs_tmp
790 %do k=1 %to &interval_count.;
791 work.plan_data_bs&k._out
792 %end;
793 ;
794 attrib
795 fs_type length = $10. label = "Financial Statement Type"
796 scenario_name length = $512. label = "Scenario Name"
797 forecast_period length = $32. label = "Forecast Period"
798 ;
799 fs_type = "BS";
800 scenario_name = "&scenario_name.";
801 forecast_period = "&interval.";
802 run;
803
804 /* PL Projection */
805 data plAnalysisDataOut;
806 set work.plan_data_pl_tmp
807 %do k=1 %to &interval_count.;
808 work.plan_data_pl&k._out
809 %end;
810 ;
811 attrib
812 fs_type length = $10. label = "Financial Statement Type"
813 scenario_name length = $512. label = "Scenario Name"
814 forecast_period length = $32. label = "Forecast Period"
815 ;
816 fs_type = "PL";
817 scenario_name = "&scenario_name.";
818 forecast_period = "&interval.";
819 run;
820
821 /* OF Projection */
822 data ofAnalysisDataOut;
823 set &plan_data_of.
824 %do k=1 %to &interval_count.;
825 work.plan_data_of&k._out
826 %end;
827 ;
828 attrib
829 fs_type length = $10. label = "Financial Statement Type"
830 scenario_name length = $512. label = "Scenario Name"
831 forecast_period length = $32. label = "Forecast Period"
832 ;
833 fs_type = "OF";
834 scenario_name = "&scenario_name.";
835 forecast_period = "&interval.";
836 run;
837
838 /* Apply any rollups */
839 %irmc_apply_rollup_aggregation( aggregation_rules = &ds_in_aggregation_config.
840 ,schema_name = st_gl_account
841 ,schema_version = &content_version.
842 ,input_ds = work.glAnalysisDataOut
843 ,period_column = forecast_horizon
844 ,output_ds = work.glAnalysisDataOut
845 );
846 %irmc_apply_rollup_aggregation( aggregation_rules = &ds_in_aggregation_config.
847 ,schema_name = st_profit_loss_account
848 ,schema_version = &content_version.
849 ,input_ds = work.plAnalysisDataOut
850 ,period_column = forecast_horizon
851 ,output_ds = work.plAnalysisDataOut
852 );
853 %irmc_apply_rollup_aggregation( aggregation_rules = &ds_in_aggregation_config.
854 ,schema_name = st_own_funds_account
855 ,schema_version = &content_version.
856 ,input_ds = work.ofAnalysisDataOut
857 ,period_column = forecast_horizon
858 ,output_ds = work.ofAnalysisDataOut
859 );
860
861 /* Apply mappings */
862 data glAnalysisDataOut;
863 set glAnalysisDataOut;
864 %include fmap_gl / source2 lrecl = 32000;
865 run;
866
867 data plAnalysisDataOut;
868 set plAnalysisDataOut;
869 %include fmap_pl / source2 lrecl = 32000;
870 run;
871
872 data ofAnalysisDataOut;
873 set ofAnalysisDataOut;
874 %include fmap_of / source2 lrecl = 32000;
875 run;
876
877 /****************************************************************/
878 /* Combine OF data with target ratios for reporting */
879 /****************************************************************/
880
881 /* Get Reportmart data structure */
882 %let libref = work;
883 proc sql;
884 %include "&fa_path./source/sas/misc/ddl/reportmart/fs_projection.sas" / source2 lrecl = 32000;
885 quit;
886
887 /* Transponse target_ratios_config to one one with all ratios */
888 proc transpose data=st_cfg.target_ratios_config
889 out=work.target_ratios_config_t(drop=_name_ _label_);
890 var target_ratio_value;
891 id target_ratio_key;
892 idlabel target_ratio_name;
893 run;
894
895 /* The target ratios record is joined to each record of the OF table*/
896 proc sql;
897 create table ofAnalysisDataOut as
898 select t1.*
899 ,t2.*
900 from ofAnalysisDataOut t1,
901 work.target_ratios_config_t t2;
902 quit;
903
904 data ds_out_bep_expectations;
905 attrib %irm_get_attrib_def(ds_in = work.fs_projection);
906 set glAnalysisDataOut
907 plAnalysisDataOut
908 ofAnalysisDataOut
909 ;
910 forecast_time = forecast_horizon;
911 analysis_run_name = "&analysis_run_name.";
912 analysis_run_id = &analysis_run_id.;
913 project_name = "&cycle_name.";
914 drop forecast_horizon;
915 run;
916
917 /****************************************************************/
918 /* Update the movements table with account code */
919 /****************************************************************/
920
921 /* Comine the movements tables */
922 data st_stg.movements;
923 set %do k=1 %to &interval_count.;
924 movementsT&k.
925 %end;
926 ;
927 run;
928
929%mend;