SAS Documentation
SASĀ® Solution for Stress Testing
Reference manual - version 08.2021
Loading...
Searching...
No Matches
irmst_enrich_portfolio.sas
1/* ************************************** */
2/* Create Enriched Portfolio table */
3/* ************************************** */
4%macro irmst_enrich_portfolio(ds_in_portfolio =
5 , ds_in_counterparty =
6 , ds_in_sppi =
7 , ds_in_entity =
8 , ds_in_map_geo_hierarchy =
9 , ds_in_map_product_hierarchy =
10 , ds_in_map_reg_product_hierarchy =
11 , ds_in_map_lob_hierarchy =
12 , ds_in_map_dpd_type =
13 , ds_in_map_ltv_type =
14 , ds_out =
15 , enrich_counterparty_flg = N
16 , enrich_sppi_flg = N
17 );
18 %local
19 ptf_table
20 ctpy_vars
21 ;
22
23 %let ptf_table = &ds_in_portfolio.;
24
25 %if (%sysevalf(%superq(enrich_counterparty_flg) =, boolean)) %then
26 %let enrich_counterparty_flg = Y;
27 %else
28 %let enrich_counterparty_flg = %upcase(&enrich_counterparty_flg.);
29
30
31 %if (%sysevalf(%superq(enrich_sppi_flg) =, boolean)) %then
32 %let enrich_sppi_flg = Y;
33 %else
34 %let enrich_sppi_flg = %upcase(&enrich_sppi_flg.);
35
36
37 %if(&enrich_counterparty_flg. = Y and &enrich_sppi_flg. = Y) %then %do;
38 /* Create empty table structure holding all variables across PORTFOLIO, COUNTERPARTY and SPPI tables */
39 %let ptf_table = _tmp_portfolio_cpty_sppi;
40 data _tmp_portfolio_cpty_sppi;
41 set
42 &ds_in_portfolio.
43 &ds_in_counterparty.
44 &ds_in_sppi.
45 ;
46 stop;
47 run;
48 %end;
49 %else %if(&enrich_counterparty_flg. = N and &enrich_sppi_flg. = Y) %then %do;
50 /* Create empty table structure holding all variables across PORTFOLIO and SPPI tables */
51 %let ptf_table = _tmp_portfolio_sppi;
52 data _tmp_portfolio_sppi;
53 set
54 &ds_in_portfolio.
55 &ds_in_sppi.
56 ;
57 stop;
58 run;
59 %end;
60 %else %if(&enrich_counterparty_flg. = Y and &enrich_sppi_flg. = N) %then %do;
61 /* Create empty table structure holding all variables across PORTFOLIO and COUNTERPARTY tables */
62 %let ptf_table = _tmp_portfolio_cpty;
63 data _tmp_portfolio_cpty;
64 set
65 &ds_in_portfolio.
66 &ds_in_counterparty.
67 ;
68 stop;
69 run;
70 %end;
71
72 /* Create a view instead of a physical table to reduce I/O operations */
73 data &ds_out. / view = &ds_out.;
74
75 attrib
76 /* Get variables definition */
77 %irm_get_attrib_def(ds_in = &ptf_table.)
78 %irm_get_attrib_def(ds_in = &ds_in_map_geo_hierarchy., drop_vars = geography_cd)
79 %irm_get_attrib_def(ds_in = &ds_in_map_product_hierarchy., drop_vars = product_cd)
80 %if (%sysevalf(%superq(ds_in_map_reg_product_hierarchy) ne, boolean)) %then
81 %do;
82 %irm_get_attrib_def(ds_in = &ds_in_map_reg_product_hierarchy., drop_vars = product_cd)
83 %end;
84 %irm_get_attrib_def(ds_in = &ds_in_map_lob_hierarchy., drop_vars = lob_id)
85 %irm_get_attrib_def(ds_in = &ds_in_map_dpd_type., drop_vars = dpd_start dpd_end)
86 %irm_get_attrib_def(ds_in = &ds_in_map_ltv_type., drop_vars = ltv_start ltv_end)
87 ORIG_CREDIT_STATE length = 8. label = "Credit State at origination"
88 CREDIT_STATE length = 8. label = "Current Credit State"
89 ORIG_STD_RATING_GRADE length = $32. label = 'Standardized Rating Grade at Origination'
90 STD_RATING_GRADE length = $32. label = 'Standardized Rating Grade'
91 DMA_EXPANSION_FLAG length = $1. label = 'DMA Expansion Flag'
92 TO_RISK_RATING length = $8. label = 'Market Risk Rating'
93 EXPOSURE_DELTA_MULTIPLIER length = 8. label = 'Exposure Delta Multiplier'
94 ;
95
96 drop
97 __rch__
98 __rx__
99 /* Drop the start/end fields used in hash iterators */
100 dpd_start
101 dpd_end
102 ltv_start
103 ltv_end
104 ;
105 retain
106 __rx__
107 ;
108
109 /* Read Portfolio table */
110 set &ds_in_portfolio.;
111
112 /* Set the EXPOSURE_DELTA_MULTIPLIER=0 - needed for Market BEP */
113 EXPOSURE_DELTA_MULTIPLIER=0;
114
115 /* ******************************** */
116 /* Define lookup tables */
117 /* ******************************** */
118 if _N_ = 1 then do;
119
120 /* Compile Regex */
121 __rx__ = prxparse("/^\d\s/");
122
123 /* Define Entity Lookup */
124 declare hash hEntity(dataset: "&ds_in_entity.");
125 hEntity.defineKey("reporting_dt", "entity_id");
126 hEntity.defineData("entity_id");
127 hEntity.defineDone();
128
129 /* Define Geo Hierarchy Lookup */
130 declare hash hGeo(dataset: "&ds_in_map_geo_hierarchy.");
131 hGeo.defineKey("geography_cd");
132 hGeo.defineData(all: "yes");
133 hGeo.defineDone();
134
135 /* Define Product Hierarchy Lookup */
136 declare hash hProduct(dataset: "&ds_in_map_product_hierarchy.");
137 hProduct.defineKey("product_cd");
138 hProduct.defineData(all: "yes");
139 hProduct.defineDone();
140
141 %if (%sysevalf(%superq(ds_in_map_reg_product_hierarchy) ne, boolean)) %then
142 %do;
143 /* Define Regulatory Product Hierarchy Lookup */
144 declare hash hRegProduct(dataset: "&ds_in_map_reg_product_hierarchy.");
145 hRegProduct.defineKey("product_cd");
146 hRegProduct.defineData(all: "yes");
147 hRegProduct.defineDone();
148 %end;
149
150 /* Define LOB Hierarchy Lookup */
151 declare hash hLob(dataset: "&ds_in_map_lob_hierarchy.");
152 hLob.defineKey("lob_id");
153 hLob.defineData(all: "yes");
154 hLob.defineDone();
155
156 /* Define DPD Lookup */
157 declare hash hDPD(dataset: "&ds_in_map_dpd_type.");
158 hDPD.defineKey("dpd_start");
159 hDPD.defineData("dpd_class", "dpd_start","dpd_end");
160 declare hiter hiterDPD("hDPD");
161 hDPD.defineDone();
162
163 /* Define LTV Lookup */
164 declare hash hLTV(dataset: "&ds_in_map_ltv_type.");
165 hLTV.defineKey("ltv_start");
166 hLTV.defineData("ltv_desc", "ltv_start","ltv_end");
167 declare hiter hiterLTV("hLTV");
168 hLTV.defineDone();
169
170 %if(&enrich_counterparty_flg. = Y) %then %do;
171 /* Define Counterparty Hierarchy Lookup */
172 declare hash hCpty(dataset: "&ds_in_counterparty.");
173 hCpty.defineKey("reporting_dt", "counterpartyid");
174 hCpty.defineData(all: "yes");
175 hCpty.defineDone();
176 %end;
177
178
179 %if(&enrich_sppi_flg. = Y) %then %do;
180 /* Define Sppi Hierarchy Lookup */
181 declare hash hSppi(dataset: "&ds_in_sppi.");
182 hSppi.defineKey("reporting_dt", "instid", "entity_id");
183 hSppi.defineData(all: "yes");
184 hSppi.defineDone();
185 %end;
186
187 end;
188
189 /* Process only records that have a match in the Entity table */
190 if(hEntity.check() = 0) then do;
191 /* Perform lookups */
192 call missing(of geography_lev:, of product_lev:, of lob_lev:, dpd_class, ltv_desc);
193 __rch__ = hGeo.find();
194 __rch__ = hProduct.find();
195 %if (%sysevalf(%superq(ds_in_map_reg_product_hierarchy) ne, boolean)) %then
196 %do;
197 __rch__ = hRegProduct.find();
198 %end;
199 __rch__ = hLob.find();
200
201 /* Iterate through the DPD hash table to find value for dpd_class */
202 __rch__ = hiterDPD.first();
203 do while (__rch__ = 0);
204 if dpd_start <= days_past_due_cnt <= dpd_end then leave;
205 __rch__ = hiterDPD.next();
206 end;
207
208 /* Iterate through the LTV hash table to find value for ltv_desc */
209 __rch__ = hiterLTV.first();
210 do while (__rch__ = 0);
211 if ltv_start <= ltv_rt <= ltv_end then leave;
212 __rch__ = hiterLTV.next();
213 end;
214
215 %if(&enrich_counterparty_flg. = Y) %then %do;
216 /* Check if there is a match in the counterparty table */
217 if(hCpty.check() = 0) then do;
218 /* Lookup Counterparty variables */
219 %let ctpy_vars = %rsk_getvarlist(&ds_in_counterparty., pattern =^(?!\b(REPORTING_DT|COUNTERPARTYID)\b));
220 call missing(%sysfunc(prxchange(s/\s+/%str(,) /i, -1, &ctpy_vars.)));
221 __rch__ = hCpty.find();
222 end;
223 %end;
224
225 %if(&enrich_sppi_flg. = Y) %then %do;
226 /* Check if there is a match in the sppi table */
227 if(hSppi.check() = 0) then do;
228 /* Lookup Sppi variables */
229 %let sppi_vars = %rsk_getvarlist(&ds_in_sppi., pattern =^(?!\b(REPORTING_DT|INSTID|ENTITY_ID)\b));
230 call missing(%sysfunc(prxchange(s/\s+/%str(,) /i, -1, &sppi_vars.)));
231 __rch__ = hSppi.find();
232 end;
233 %end;
234
235 /* Extract the credit state (scale 1 - 9) from the Standard Rating Grade */
236 if(prxmatch(__rx__, ORIG_STD_RATING_GRADE)) then
237 ORIG_CREDIT_STATE = input(scan(ORIG_STD_RATING_GRADE, 1, " "), 8.);
238 if(prxmatch(__rx__, STD_RATING_GRADE)) then
239 CREDIT_STATE = input(scan(STD_RATING_GRADE, 1, " "), 8.);
240
241 /* Write record to output */
242 output;
243 end;
244
245 run;
246
247%mend;