SAS Documentation
SASĀ® Solution for Stress Testing
Reference manual - version 08.2021
Loading...
Searching...
No Matches
irmst_apply_value_calculations.sas
1%macro irmst_apply_value_calculations( map_movement_type =
2 , actualsData =
3 , activationHorizon =
4 );
5
6 %let j = %eval(&activationHorizon.-1);
7
8 /* Get VAL assumptions input from BEP */
9 /* Join with YLD Aggregate data to use the preliminary total calcualted from VOL and YLD calculations */
10 proc sql;
11 create table bep_val_dtls as
12 select t1.*
13 , t2.prelim_total
14 from (select *
15 from valassump
16 where activationHorizon=&activationHorizon.) t1
17 left join work.yld_aggregate t2
18 on t1.account_cd=t2.account_cd
19 order by account_cd
20 ;
21 quit;
22
23 /* Table with the sum of the funding source percentages for each row in case those values havn't been normalized */
24 proc sql noprint;
25 create table
26 fundSourceTotals
27 as select
28 account_cd,
29 sum(oci_acct_percent) as sum_oci_percent
30 from
31 bep_val_dtls
32 group by
33 account_cd;
34 quit;
35
36 /* Calculate net variation from VAL assumptions */
37 data work.bep_val_posting;
38 attrib
39 posting_group_id length = $50. label = "Posting Group Id"
40 movement_id length = 8. label = "Movement Id"
41 movement_desc length = $100. label = "Movement Description"
42 %irm_get_attrib_def(ds_in = &map_movement_type.)
43 ;
44 drop _rc_;
45 set work.bep_val_dtls;
46 by account_cd;
47
48 posting_group_id = cats('VAL_',account_cd);
49
50 if _N_ = 1 then do;
51 /* Declare lookup for retrieving the movement type and category */
52 declare hash hMvmt(dataset: "&map_movement_type.");
53 hMvmt.defineKey("movement_type_cd");
54 hMvmt.definedata("movement_type", "movement_category");
55 hMvmt.defineDone();
56
57 /* Lookup funding source percentage totals */
58 declare hash perTotLookup(dataset: "work.fundSourceTotals");
59 perTotLookup.defineKey("account_cd");
60 perTotLookup.definedata("sum_oci_percent");
61 perTotLookup.defineDone();
62 end;
63 /* Set the movement code description */
64 movement_type_cd = "BS_MODEL";
65 movement_desc = "Value Assumptions";
66 movement_id = 4;
67 /* Lookup movement type and category */
68 _rc_ = hMvmt.find();
69
70 var = prelim_total * value_adjust;
71 if first.account_cd then do;
72 posting_account_id = account_cd;
73 variation = var; /* main-account */
74 output;
75 end;
76
77 /* PL or OCI account */
78 call missing(sum_oci_percent);
79 _rc_= perTotLookup.find();
80 if _rc_ ne 0 then sum_oci_percent = 100;
81
82 posting_account_id = oci_acct_source;
83 variation = -var * oci_acct_percent / sum_oci_percent;
84 output;
85 run;
86
87 /* Consolidate all postings from VOL, YLD, VAL assumptions and create final projection to T=i */
88 proc sql;
89 create table fsStructureT&activationHorizon. as
90 select t1.account_id
91 , t1.account_cd
92 , t1.fs_type
93 , t1.accnt_type
94 , t2.amountT&j.
95 ,t2.amount_model as model_result
96 , case
97 when t1.fs_type = 'PL' /*PL numbers are not additive. Do not use T-1 values for PL. */
98 then sum(t2.vol_variation,t3.yld_variation,t4.val_variation)
99 when t1.fs_type = 'BS' and strip(t1.accnt_type) in ('02. Liabilities','03. Equity') /* Liabilities and Equities */
100 then sum(t2.amountT&j.,-t2.vol_variation,-t3.yld_variation,-t4.val_variation)
101 else sum(t2.amountT&j.,t2.vol_variation,t3.yld_variation, t4.val_variation) /* All BS and Assets*/
102 end as amountT&activationHorizon.
103 , &activationHorizon. as activationHorizon
104 from (select distinct account_id
105 , account_cd
106 , fs_type
107 , accnt_type
108 from &actualsData.) t1
109 left join (select distinct account_id
110 , account_cd
111 , amountT&j
112 ,amount_model
113 , vol_variation
114 from work.vol_aggregate) t2
115 on t1.account_cd=t2.account_cd
116 left join (select account_id
117 , account_cd
118 , yld_variation
119 from work.yld_aggregate) t3
120 on t1.account_cd=t3.account_cd
121 left join (select posting_account_id
122 , sum(variation) as val_variation
123 from work.bep_val_posting
124 group by posting_account_id) t4
125 on t1.account_cd=t4.posting_account_id
126 ;
127 quit;
128%mend irmst_apply_value_calculations;