SAS Documentation
SASĀ® Solution for Stress Testing
Reference manual - version 08.2021
Loading...
Searching...
No Matches
irmst_apply_yield_calculations.sas
1%macro irmst_apply_yield_calculations( map_movement_type =
2 , actualsData =
3 , activationHorizon =
4 );
5
6 %let j = %eval(&activationHorizon.-1);
7
8 /* Start with YLD assumptions input from BEP */
9 /* Join with VOL Aggregate data since YLD is calcualted on BBOOK and FBOOK volumes */
10 proc sql;
11 create table work.bep_yld_dtls as
12 select t1.*
13 , t2.amountT&j.
14 , t2.amount_model
15 , t2.bbook
16 , t2.fbook
17 , coalescec(t2.value_src,yld_value_src) as value_src
18 from (select *
19 from work.yldassump (rename=(value_src=yld_value_src))
20 where activationHorizon = &activationHorizon.) t1
21 left join work.vol_aggregate t2
22 on t1.account_cd = t2.account_cd
23 order by account_cd
24 ;
25 quit;
26
27 /* Table with the sum of the funding source percentages for each row in case those values havn't been normalized */
28 proc sql noprint;
29 create table
30 fundSourceTotals
31 as select
32 account_cd,
33 sum(cash_flow_src_percent) as sum_cash_percent,
34 sum(non_cash_flow_src_percent) as sum_noncash_percent
35 from
36 bep_yld_dtls
37 group by
38 account_cd;
39 quit;
40
41 /* Calculate net variation from YLD assumptions */
42 data work.bep_yld_posting;
43 attrib
44 posting_group_id length = $50. label = "Posting Group Id"
45 movement_id length = 8. label = "Movement Id"
46 movement_desc length = $100. label = "Movement Description"
47 %irm_get_attrib_def(ds_in = &map_movement_type.)
48 ;
49 drop _rc_;
50 set work.bep_yld_dtls;
51 by account_cd;
52
53 posting_group_id = cats('YLD_',account_cd);
54
55 if _N_ = 1 then do;
56 /* Declare lookup for retrieving the movement type and category */
57 declare hash hMvmt(dataset: "&map_movement_type.");
58 hMvmt.defineKey("movement_type_cd");
59 hMvmt.definedata("movement_type", "movement_category");
60 hMvmt.defineDone();
61
62 /* Lookup funding source percentage totals */
63 declare hash perTotLookup(dataset: "work.fundSourceTotals");
64 perTotLookup.defineKey("account_cd");
65 perTotLookup.definedata("sum_cash_percent","sum_noncash_percent");
66 perTotLookup.defineDone();
67 end;
68
69 /* Set the movement code description */
70 movement_type_cd = "BS_MODEL";
71 movement_desc = "Yield Assumptions";
72 movement_id = 3;
73 /* Lookup movement type and category */
74 _rc_ = hMvmt.find();
75
76 if value_src = 'external_model' then do;
77 /* (Average of begining amount and end amount) * (average of the yield) */
78 var = (sum(amount_model,amountT&j.)/2)*(sum(yield_bb,yield_fb)/2);
79 end;
80 else do;
81 var = sum((amountT&j.+bbook)/2*yield_bb,(fbook/2)*yield_fb);
82 end;
83 if first.account_cd then do;
84 posting_account_id = account_cd;
85 variation = var*(1-cashflow_rate); /* main-account */
86 output;
87 end;
88
89 call missing(sum_cash_percent);
90 call missing(sum_noncash_percent);
91 _rc_= perTotLookup.find();
92 if _rc_ ne 0 then do;
93 sum_cash_percent = 100;
94 sum_noncash_percent = 100;
95 end;
96
97 /* PL account */
98 if cash_flow_src_source ne '' then do;
99 posting_account_id = cash_flow_src_source;
100 variation = -var*cash_flow_src_percent / sum_cash_percent;
101 output;
102 end;
103
104 /* Non-PL account */
105 if non_cash_flow_src_source ne '' then do;
106 posting_account_id = non_cash_flow_src_source;
107 variation = (var*cashflow_rate)*non_cash_flow_src_percent / sum_noncash_percent;
108 output;
109 end;
110 run;
111
112 /* Consolidate all postings from YLD assumptions */
113 proc sql;
114 create table work.yld_aggregate as
115 select t1.account_id
116 , t1.account_cd
117 , t2.bbook
118 , t2.fbook
119 , t2.vol_variation
120 , t3.yld_variation
121 , sum(t2.amountT&j. ,t2.vol_variation ,t3.yld_variation) as prelim_total
122 from (select distinct account_id
123 , account_cd
124 from &actualsData.) t1
125 left join (select distinct account_id
126 , account_cd
127 , amountT&j.
128 , bbook
129 , fbook
130 , vol_variation
131 from work.vol_aggregate) t2
132 on t1.account_cd=t2.account_cd
133 left join (select posting_account_id
134 , sum(variation) as yld_variation
135 from work.bep_yld_posting
136 group by posting_account_id) t3
137 on t1.account_cd=t3.posting_account_id
138 ;
139 quit;
140%mend irmst_apply_yield_calculations;