SAS Documentation
SASĀ® Solution for Stress Testing
Reference manual - version 08.2021
Loading...
Searching...
No Matches
irmst_apply_volume_calculations.sas
1%macro irmst_apply_volume_calculations( map_movement_type =
2 , actualsData =
3 , activationHorizon =
4 );
5
6 %let j = %eval(&activationHorizon.-1);
7
8 /* Start with VOL assumptions input from BEP */
9 proc sort data=work.volassump out=work.bep_vol_dtls;
10 by account_cd;
11 where activationHorizon = &activationHorizon.;
12 run;
13
14 /* Table with the sum of the funding source percentages for each row in case those values havn't been normalized */
15 proc sql noprint;
16 create table
17 fundSourceTotals
18 as select
19 account_cd,
20 sum(fund_src_vol_percent) as sum_percent
21 from
22 bep_vol_dtls
23 group by
24 account_cd;
25 quit;
26
27 /* Calculate net variation from VOL assumptions */
28 data work.bep_vol_posting;
29 attrib
30 posting_group_id length = $50. label = "Posting Group Id"
31 movement_id length = 8. label = "Movement Id"
32 movement_desc length = $100. label = "Movement Description"
33 %irm_get_attrib_def(ds_in = &map_movement_type.)
34 value_src length = $64. label = "Source Model (Projection or External)"
35 ;
36 drop _rc_;
37 set work.bep_vol_dtls;
38 by account_cd;
39 if _N_ = 1 then do;
40 /* Get t-1 amount */
41 declare hash hVol(dataset: "&actualsData.");
42 hVol.defineKey("account_cd");
43 hVol.defineData("amountT&j.");
44 hVol.defineDone();
45
46 /* Declare lookup for retrieving the movement type and category */
47 declare hash hMvmt(dataset: "&map_movement_type.");
48 hMvmt.defineKey("movement_type_cd");
49 hMvmt.definedata("movement_type", "movement_category");
50 hMvmt.defineDone();
51
52 /* Lookup model result for time t */
53 declare hash hMod(dataset: "work.modelResults(rename=(forecast_horizon=activationHorizon))");
54 hMod.defineKey("account_cd","activationHorizon");
55 hMod.definedata("amount_model");
56 hMod.defineDone();
57
58 /* Lookup funding source percentage totals */
59 declare hash perTotLookup(dataset: "work.fundSourceTotals");
60 perTotLookup.defineKey("account_cd");
61 perTotLookup.definedata("sum_percent");
62 perTotLookup.defineDone();
63
64 end;
65 call missing(amountT&j.,amount_model,movement_type,movement_category);
66
67 /* Set the movement code description */
68 movement_type_cd = "BS_MODEL";
69 movement_desc = "Volume Assumptions";
70 movement_id = 2;
71
72 /* Lookup movement type and category */
73 _rc_ = hMvmt.find();
74
75 posting_group_id = cats('VOL_',account_cd);
76
77 if hVol.find() = 0 then do;
78 if value_src = 'external_model' then do;
79 _rc_= hMod.find();
80 var = (amount_model-amountT&j.);
81 end;
82 else if value_src = 'projection_model' then do;
83 bbook = amountT&j.*(1-runoff_bb);
84 fbook = (amountT&j.*growth)*(1-runoff_fb);
85 var = (bbook-amountT&j.)+fbook;
86 end;
87 /* Postings transactions */
88 if first.account_cd then do;
89 posting_account_id = account_cd;
90 variation = var;
91 output;
92 end;
93 posting_account_id = fund_src_vol_source;
94 call missing(sum_percent);
95 _rc_= perTotLookup.find();
96 if _rc_ ne 0 then sum_percent = 100;
97 variation = -(var * fund_src_vol_percent / sum_percent);
98 output;
99 end;
100 else do;
101 put "WARNING: No matching record found in actualsData for record " account_id;
102 end;
103 run;
104
105 /* Consolidate all postings from VOL assumptions */
106 proc sql;
107 create table work.vol_aggregate as
108 select t1.account_id
109 , t1.account_cd
110 , coalesce(t2.amountT&j.,t1.amountT&j.) as amountT&j.
111 , t2.amount_model
112 , t2.bbook
113 , t2.fbook
114 , coalesce(t3.vol_variation,0) as vol_variation
115 , t2.value_src
116 from (select distinct account_id
117 , account_cd
118 , amountT&j.
119 from &actualsData.) t1
120 left join (select distinct account_id
121 , account_cd
122 , amountT&j.
123 , amount_model
124 , bbook
125 , fbook
126 , value_src
127 from work.bep_vol_posting) t2
128 on t1.account_cd=t2.account_cd
129 left join (select posting_account_id
130 , sum(variation) as vol_variation
131 from work.bep_vol_posting
132 group by posting_account_id) t3
133 on t1.account_cd=t3.posting_account_id
134 ;
135 quit;
136%mend irmst_apply_volume_calculations;