SAS Documentation
SASĀ® Solution for Stress Testing
Reference manual - version 08.2021
Loading...
Searching...
No Matches
irmst_create_port_expanded.sas
1/*------------------------------------------------------------------------------------------
2 * NAME: irmst_create_port_expanded.sas
3 *
4 * PURPOSE: Macro used to create portfolio subset and expand wholesale exposures
5 *
6 * NOTES: - Logic relevant to in_classification_vars is commented out until when override
7 * and growth are implemented
8 * - Portfolio always uses INSTTYPE variable (not PRODUCT) to accommodate lookups
9 * done in UDL which reference INSTTYPE variable
10 *
11 * MACRO OPTIONS:
12 * function_package - OPTIONAL: Two-level name for function package
13 * in_control_table - OPTIONAL: Input control table containing required portfolio
14 * variables
15 * in_model_group_info - Input dataset containing model group information
16 * in_portfolio - Input portfolio data set
17 * in_classification_vars - Input dataset containing classification variables
18 * out_unrequired_subset - Output dataset containing unrequired subset
19 * out_portfolio - Two-level name of expanded portfolio data set for DMA
20 * where_stmt - OPTIONAL: Specify WHERE statement to subset rows of input
21 * portfolio
22 * obs - OPTIONAL: Specify number of observations in output table
23 * DEFAULT: MAX
24 * debug - OPTIONAL: For debugging API calls and keeping WORK tables
25 * mipurl - MIP URL to make rest API calls
26 * tgt_ticket - Specify TGT used to generate service tickets
27 *
28 * MACRO PARAMETERS FOR WORKGROUP SWITCHING:
29 * host workgroup outVarTicket restartLUA
30 * server authMethod outSuccess clearCache
31 * port outds outResponseStatus
32 *
33 *------------------------------------------------------------------------------------------*/
34%macro irmst_create_port_expanded(
35 function_package = work.rating_format,
36 in_control_table = ,
37 in_model_group_info = ,
38 in_portfolio = ,
39 /* in_classification_vars = , */
40 out_unrequired_subset = work.unrequired,
41 out_portfolio = ,
42 where_stmt = 1 eq 1,
43 obs = MAX,
44 debug = false,
45 mipurl = ,
46 tgt_ticket = ,
47 host = ,
48 server = ,
49 port = ,
50 workgroup = ,
51 authMethod = token,
52 outds = work._tmp_workgroups_,
53 outVarTicket = ,
54 outSuccess = 0,
55 outResponseStatus = ,
56 restartLUA = Y,
57 clearCache = Y
58 ) / minoperator;
59
60 %local apply_control_flag
61 required_variables
62 unrequired_variables
63 missing_variables
64 default_inst_var
65 model_sk
66 required_vars_to_rename
67 i
68 var;
69
70 /* ---------------------------
71 * Initialize macro variables
72 * --------------------------- */
73 %let required_variables =;
74 %Let unrequired_variables =;
75 %let missing_variables =;
76
77 proc sql noprint;
78 select upcase(name)
79 into :default_inst_var separated by ' '
80 from dictionary.columns
81 where libname eq "%qupcase(%scan(&in_portfolio, 1, %str(.)))" and
82 memname eq "%qupcase(%scan(&in_portfolio, 2, %str(.)))" and
83 upcase(name) in ("INSTID"
84 "INSTTYPE"
85 "PRODUCT"
86 "COUNTERPARTYID"
87 "CPTYID");
88 quit;
89
90 /* -------------------------------------------------------
91 * Determine whether to drop unneeded portfolio variables
92 * ------------------------------------------------------- */
93 %let apply_control_flag = %sysevalf(%superq(in_control_table) ne, boolean);
94
95 /* ------------------------------------------------------------------------
96 * CONDITIONAL : Select variables to go into KEEP statement for each table
97 * ------------------------------------------------------------------------ */
98 %if &apply_control_flag %then
99 %do;
100 proc sql noprint;
101 select distinct upcase(variable)
102 into :required_variables separated by ' '
103 from (
104 select upcase(variable) as variable
105 from &in_control_table
106 /* union
107 select upcase(variable) as variable
108 from &in_classification_vars */
109 )
110 where upcase(variable) in
111 (select upcase(name)
112 from dictionary.columns
113 where libname eq "%qupcase(%scan(&in_portfolio, 1, %str(.)))" and
114 memname eq "%qupcase(%scan(&in_portfolio, 2, %str(.)))");
115
116 select upcase(name)
117 into :unrequired_variables separated by ' '
118 from dictionary.columns
119 where libname eq "%qupcase(%scan(&in_portfolio, 1, %str(.)))" and
120 memname eq "%qupcase(%scan(&in_portfolio, 2, %str(.)))" and
121 upcase(name) not in (%rsk_quote_list(list=&required_variables));
122
123 select distinct upcase(variable)
124 into :missing_variables separated by ' '
125 from &in_control_table
126 where upcase(variable) not in (%rsk_quote_list(list=&required_variables));
127 quit;
128
129 %let required_variables = &default_inst_var. &required_variables;
130 %let unrequired_variables = &default_inst_var. &unrequired_variables;
131 %end;
132
133 /* ----------------------------------------------------------------
134 * Generate warning message for missing variables within portfolio
135 * ---------------------------------------------------------------- */
136 %if %length(&missing_variables) gt 0 %then
137 %let missing_variables = %rsk_quote_list(list=&missing_variables, dlm=%str( ));
138
139 %if %index(&default_inst_var, INSTTYPE) eq 0 and
140 %index(&default_inst_var, PRODUCT) eq 0 %then
141 %let missing_variables = 'INSTTYPE or PRODUCT' &missing_variables;
142
143 %if %index(&default_inst_var, INSTID) eq 0 %then
144 %let missing_variables = 'INSTID' &missing_variables;
145
146 %if %length(&missing_variables) gt 0 %then
147 %put WARNING: The following expected variables were not found in %upcase(&input_port). &missing_variables;
148
149 /*-------------------------------------------
150 * Get SK of the model with TM rating grades
151 *-------------------------------------------*/
152 proc sql noprint;
153 select tm_model_sk
154 into :model_sk
155 from &in_model_group_info
156 where tm_model_sk ne .;
157 quit;
158
159 /*----------------------
160 * Create rating format
161 *----------------------*/
162 %irmst_create_rating_formats(
163 tm_model_sk = &model_sk,
164 function_lib = %scan(&function_package, 1, .),
165 package_name = %scan(&function_package, 2, .),
166 debug = &debug,
167 mipurl = %superq(mipurl),
168 tgt_ticket = %superq(tgt_ticket),
169 host = &host,
170 server = &server,
171 port = &port,
172 workgroup = &workgroup,
173 authMethod = &authMethod,
174 outds = &outds,
175 outVarTicket = &outVarTicket,
176 outSuccess = &outSuccess,
177 outResponseStatus = &outResponseStatus,
178 restartLUA = &restartLUA,
179 clearCache = &clearCache
180 )
181
182 %irm_add_function_package(
183 cmplib = &function_package,
184 check_function = rating_num_to_grade
185 )
186
187 /*--------------------------
188 * Build model_group filter
189 *--------------------------*/
190 proc sql;
191 create table work.model_group_dim_info as
192 select distinct upcase(insttype) as seg_lvl_insttype,
193 tm_dim
194 from &in_model_group_info
195 where upcase(model_group_kind) eq "EVALUATION";
196 quit;
197
198 /*--------------------------
199 * Apply model_group filter
200 *--------------------------*/
201 filename mgfilter temp;
202 data _null_;
203 file mgfilter lrecl = 32767;
204 set work.model_group_dim_info;
205
206 if (tm_dim ne .) then
207 do;
208 put 'when(' 'upcase(insttype) = upcase("' seg_lvl_insttype +(-1) '"))';
209 put 'do;';
210 put 3*' ' 'dma_expansion_flag = "Y";';
211 put 3*' ' 'do i=1 to' + 1 tm_dim + (-1) ';';
212 put 6*' ' 'instid = catx("_",original_instid,i);';
213 put 6*' ' 'to_risk_rating = upcase(rating_num_to_grade(i));';
214 put 6*' ' "output &out_portfolio;";
215 put 3*' ' 'end;';
216 put 'end;';
217 end;
218 run;
219
220 /*----------------------------
221 * CONDITIONAL : Rename logic
222 *----------------------------*/
223 %if &apply_control_flag %then
224 %do;
225 %put NOTE: Required variables before rename - &required_variables;
226
227 %let required_vars_to_rename = &required_variables;
228 %let required_variables = ;
229
230 %do i = 1 %to %sysfunc(countw(&required_vars_to_rename));
231 %let var = %scan(&required_vars_to_rename, &i, %str( ));
232 %if (&var eq PRODUCT) %then
233 %let var = INSTTYPE;
234 %let required_variables = &required_variables. &var;
235 %end;
236
237 %put NOTE: Required variables after rename - &required_variables;
238 %end;
239
240 /*-----------------------------------------------------
241 * Create output datasets - Expand wholesale exposures
242 *-----------------------------------------------------*/
243 proc sql noprint;
244 select max(length(strip(instid)))
245 into :length trimmed
246 from &in_portfolio;
247 quit;
248
249 data %if &apply_control_flag %then
250 %do;
251 &out_portfolio(
252 keep = &required_variables.
253 insttype
254 reporting_dt
255 original_instid
256 to_risk_rating
257 dma_expansion_flag
258 )
259 &out_unrequired_subset(keep = &unrequired_variables);
260 %end;
261 %else
262 %do;
263 &out_portfolio(drop = i);
264 %end;
265
266 length %upcase(instid) $%eval(&length. +3)
267 to_risk_rating $32
268 dma_expansion_flag $1;
269
270 label to_risk_rating = 'Migrated Risk Rating';
271
272 set &in_portfolio(
273 rename = (
274 instid = original_instid
275 %if %rsk_varexist(&in_portfolio, PRODUCT) and
276 not %rsk_varexist(&in_portfolio, INSTTYPE) %then
277 product = insttype;
278 )
279 obs = &obs
280 where = (&where_stmt)
281 );
282
283 /*------------------------------------------------
284 * Logic for expansion -- output to out_portfolio
285 *------------------------------------------------*/
286 select;
287 %include mgfilter / nosource lrecl = 32767;
288 otherwise
289 do;
290 dma_expansion_flag = 'N';
291 instid = original_instid;
292 output &out_portfolio;
293 end;
294 end;
295 /*------------------------------------------------
296 * Reseting instid to output to unrequired subset
297 *------------------------------------------------*/
298 instid = original_instid;
299 %if &apply_control_flag %then
300 output &out_unrequired_subset;;
301 run;
302
303 filename mgfilter clear;
304
305 /*----------------------------------------------------
306 * If debug not set to TRUE, drop intermediate tables
307 *----------------------------------------------------*/
308 %if %qupcase(&debug) ne TRUE %then
309 %do;
310 proc sql;
311 drop table work.model_group_dim_info;
312 quit;
313 %end;
314
315%mend irmst_create_port_expanded;