SAS Documentation
SASĀ® Solution for Stress Testing
Reference manual - version 08.2021
Loading...
Searching...
No Matches
irmst_import_atm_spreadsheet.sas
Go to the documentation of this file.
1/*
2 Copyright (C) 2021 SAS Institute Inc. Cary, NC, USA
3*/
4
5/**
6 \file
7 \anchor irmst_import_atm_spreadsheet.sas
8
9 \brief irmst_import_atm_spreadsheet processes the ATM spreadsheet uploaded by a user in the UI.
10
11 \param [in] in_model_groups Space separated list of model groups associated with an existing transition matrix
12 \param [in] atmupload Data set generated from RGF ProcessSpreadsheetParameter from xlsx file selected by the user in the UI
13 \param [out] outds Specifies the output overlay to be joined to the existing transition matrix
14
15 \author SAS Institute Inc.
16 \date 2021
17*/
18
19%macro irmst_import_atm_spreadsheet(in_model_groups = ,
20 atmupload = ,
21 outds =
22 );
23
24 %local system_fatal_error
25 atm_set_name
26 time_interval
27 num_atms_in_sheet
28 num_distinct_transmat_dt
29 i
30 var_num
31 num_from
32 num_to
33 error
34 mg_dim_error
35 distinct_dim
36 duped_tms;
37
38 %let system_fatal_error = N;
39 %let atm_set_name = atm_upload;
40 %let time_interval = 12;
41
42 /*---------------------------------------------------------------------------------
43 * Process generated upload table - drop key, retain model group name, time period
44 * and frequency from the previous record if the current record is blank
45 *---------------------------------------------------------------------------------*/
46 data work.atm_tab(drop = model_group_name_tmp
47 time_period_tmp
48 frequency_tmp
49 rating_tmp);
50 attrib
51 model_group_name length = $32. label = 'Model Group Name'
52 time_period length = $32. label = 'Time Period'
53 frequency length = $32. label = 'Frequency'
54 rating length = $32. label = 'From/To'
55 ;
56 set &atmupload(
57 drop = key
58 rename = (
59 model_group_name = model_group_name_tmp
60 time_period = time_period_tmp
61 frequency = frequency_tmp
62 rating = rating_tmp
63 )
64 );
65 where rating_tmp ne '';
66 retain model_group_name
67 time_period
68 frequency;
69 if model_group_name_tmp ne '' then
70 model_group_name = upcase(model_group_name_tmp);
71 if time_period_tmp ne '' then
72 time_period = upcase(time_period_tmp);
73 if frequency_tmp ne '' then
74 frequency = upcase(frequency_tmp);
75 rating = upcase(rating_tmp);
76 run;
77
78 proc sql noprint;
79 select distinct model_group_name,
80 time_period,
81 frequency
82 into :model_group_name1-,
83 :time_period1-,
84 :frequency1-
85 from work.atm_tab
86 quit;
87
88 %let num_atms_in_sheet = &sqlobs;
89
90 /*---------------------------------------------------------------------------
91 * Check ATMs within the same model group from different time periods
92 * that get aligned to the same date
93 *---------------------------------------------------------------------------*/
94 proc sql noprint;
95 select distinct model_group_name,
96 intnx(frequency,input(time_period,anydtdte32.),0,'e')
97 into :model_group_name_dt1-,
98 :transmat_dt1-
99 from work.atm_tab;
100 quit;
101
102 %let num_distinct_transmat_dt = &sqlobs;
103
104 %if &num_atms_in_sheet ne &num_distinct_transmat_dt %then
105 %do;
106 %put ERROR: The frequency specified in the spreadsheet upload leads to ATMs within the same model group from different time periods getting aligned to the same date.;
107 %let system_fatal_error = Y;
108 %abort cancel;
109 %end;
110
111 /*------------------------------
112 * Loop over ATMs in sheet
113 *------------------------------*/
114 %do i = 1 %to &num_atms_in_sheet;
115
116 /*-----------------------------------------------
117 * Transpose to create columns from to ratings
118 *-----------------------------------------------*/
119 %local to_vars
120 to_vars_rename
121 to_vars_change_type;
122
123 %if %sysfunc(find(%upcase(&in_model_groups.),%upcase(&&model_group_name&i))) lt 1 %then %do;
124 %put ERROR: The model group: &&model_group_name&i which was specified in the import file does not exist in the input transition matrix.;
125 %abort cancel;
126 %end;
127
128 proc sql noprint;
129 select distinct upcase(name) as to_vars,
130 catx('=_',name,name) as rename,
131 case
132 when type eq 'char'
133 then catt(name,'=input(_',name,',best32.);')
134 else catt(name,'=_',name,';')
135 end as change_type,
136 varnum
137 into : to_vars separated by ' ',
138 : to_vars_rename separated by ' ',
139 : to_vars_change_type separated by ' ',
140 : var_num
141 from dictionary.columns
142 where libname eq 'WORK' and
143 memname eq 'ATM_TAB' and
144 /* memtype eq 'DATA' and */
145 upcase(name) not in ('MODEL_GROUP_NAME','TIME_PERIOD','FREQUENCY','RATING')
146 order by varnum;
147 quit;
148
149 data work.tab_atm&i._0 (keep = rating &to_vars);
150 set atm_tab (rename = ( &to_vars_rename ));
151 where model_group_name eq "&&model_group_name&i" and
152 time_period eq "&&time_period&i" and
153 frequency eq "&&frequency&i";
154 length &to_vars 8.;
155 &to_vars_change_type.;
156 run;
157
158 proc transpose data = work.tab_atm&i._0
159 out = work.tab_atm&i._1 (rename = (_name_ = to_rating));
160 id rating;
161 run;
162
163 /*--------------------------------
164 * Check for a non-square matrix
165 *--------------------------------*/
166 %local bad_rows;
167
168 proc sql noprint;
169 select count(*)
170 into :num_from
171 from work.tab_atm&i._1;
172 /* counts number of rating columns and to_rating column */
173 select nvar
174 into : num_to
175 from dictionary.tables
176 where libname eq 'WORK' and
177 memname eq "%upcase(tab_atm&i._1)" and
178 memtype eq 'DATA';
179 quit;
180
181 %if &num_from ne %eval(&num_to - 1) %then
182 %do;
183 %let system_fatal_error = Y;
184 %let error = ERROR: At least one of the input matrices in the spreadsheet upload is not square.;
185 %put &error;
186 %abort cancel;
187 %end;
188
189 /*---------------------------------------------------
190 * Check matrix dimension under the same model_group
191 *---------------------------------------------------*/
192 proc sql;
193 create table work.dimension as
194 select model_group_name,
195 frequency,
196 time_period,
197 count(*) as dimension
198 from work.atm_tab
199 group by model_group_name,
200 frequency,
201 time_period;
202 quit;
203
204 proc sql noprint;
205 select model_group_name,
206 count(distinct dimension) as distinct_dimension
207 into :mg_dim_error separated by ' ',
208 :distinct_dim separated by ' '
209 from work.dimension
210 group by model_group_name
211 having distinct_dimension gt 1;
212 quit;
213
214 %if &sqlobs ne 0 %then
215 %do;
216 %let system_fatal_error = Y;
217 %let error = ERROR: Matrices in model_group(s) %nrbquote('&mg_dim_error') have different dimensions.;
218 %put &error;
219 %abort cancel;
220 %end;
221
222 /*-----------------------
223 * Get actual ratings
224 *-----------------------*/
225 data work.ratings_tab_atm&i;
226 set work.atm_tab (keep = model_group_name
227 time_period
228 frequency
229 rating);
230 where model_group_name eq "&&model_group_name&i" and
231 time_period eq "&&time_period&i" and
232 frequency eq "&&frequency&i";
233 order = _n_;
234 run;
235
236 /*-----------------------------------
237 * Merge in ratings for to ratings
238 *-----------------------------------*/
239 proc sql;
240 create table work.tab_atm&i._2 as
241 select a.model_group_name,
242 a.time_period,
243 a.frequency,
244 b.*
245 from work.ratings_tab_atm&i as a,
246 work.tab_atm&i._1 as b
247 where a.rating eq b.to_rating
248 order by a.order;
249 quit;
250
251 /*------------------------------------
252 * Transpose back to original form
253 *------------------------------------*/
254 proc transpose data = work.tab_atm&i._2
255 out = work.tab_atm&i._3 (rename = (_name_ = from_rating));
256 id to_rating;
257 run;
258
259 /*------------------------------------
260 * Integrity checks
261 *------------------------------------*/
262 data work.tab_atm&i._4 (drop = model_group_name
263 time_period);
264 set work.tab_atm&i._3 end=last;
265 array num[*] _numeric_;
266
267 /* Check for missing values */
268 if cmiss(of num[*]) gt 0 then
269 do;
270 call symputx('system_fatal_error', 'Y', 'l');
271 model_group_name = symget(cats('model_group_name',"&i"));
272 time_period = symget(cats('time_period',"&i"));
273 putlog 'ERROR: Missing migration probablities were detected in row "' from_rating +(-1) '" for model group ' model_group_name +(-1) ' and time period ' time_period;
274 stop;
275 end;
276
277 from_order = _n_;
278 run;
279
280 %if &system_fatal_error eq Y %then
281 %abort cancel;
282
283 /*-----------------------------------
284 * Transpose for storage in the RRR
285 *-----------------------------------*/
286 proc transpose data = work.tab_atm&i._4
287 out = work.tab_atm&i._5 (rename = (col1 = migrate_prob
288 _name_ = to_rating));
289 by from_order
290 from_rating;
291 run;
292
293 /*-----------------------------------
294 * Assign to-order and to-rating
295 *-----------------------------------*/
296 data work.tab_atm&i._6(drop=rating
297 order
298 rc
299 to_order
300 from_order);
301 /* initialize variables for smooth proc append */
302 length rating_order
303 migrate_prob
304 time_interval 8
305 atm_set_nm
306 model_group_name
307 time_period
308 frequency
309 from_rating
310 to_rating $32;
311 if 0 then set work.ratings_tab_atm&i;
312 set work.tab_atm&i._5;
313 by from_order;
314
315 atm_set_nm = symget('atm_set_name');
316
317 rating_order = _n_;
318
319 if first.from_order then
320 to_order = 1;
321 else
322 to_order + 1;
323
324 if _n_ eq 1 then
325 do;
326 declare hash rating_order_lookup(dataset: "work.ratings_tab_atm&i");
327 rating_order_lookup.definekey('order');
328 rating_order_lookup.definedata('rating');
329 rating_order_lookup.definedone();
330 end;
331
332 rc = rating_order_lookup.find(key: from_order);
333 from_rating = rating;
334 rc = rating_order_lookup.find(key: to_order);
335 to_rating = rating;
336
337 model_group_name = resolve('&&model_group_name&i');
338 time_period = "&&time_period&i";
339 frequency = "&&frequency&i";
340
341 /*---------------------------------------------
342 * Check for invalid migration probabilities
343 *---------------------------------------------*/
344 if migrate_prob lt 0 or migrate_prob gt 1 then
345 do;
346 call symputx('system_fatal_error', 'Y', 'l');
347 putlog 'ERROR: The following cell in the input matrix is outside of the range between 0 and 1:';
348 putlog "ERROR- Model group name: '&&model_group_name&i'";
349 putlog "ERROR- Time period: '&&time_period&i'";
350 putlog "ERROR- Cell: '" from_rating +(-1) ':' to_rating +(-1) "'";
351 stop;
352 end;
353
354 time_interval = &time_interval;
355 time_interval_units = 'Months';
356
357 upload_type_cd = 'ATM';
358 run;
359
360 %if &system_fatal_error eq Y %then
361 %abort cancel;
362
363 /*--------------------------------------
364 * Check for rows that do not sum to 1
365 *--------------------------------------*/
366 data _null_;
367 set work.tab_atm&i._3 end=last;
368
369 array num[*] _numeric_;
370 length error_msg $1000;
371
372 rowsum = round(sum(of num[*]),1E-8);
373
374 /* Check if row sums to 1 */
375 if rowsum ne 1 then
376 do;
377 error_msg = cat("WARNING: The migration probablities for row '",strip(from_rating),
378 "' do not sum to 1. The row sum was ",
379 strip(put(rowsum,best32.)),'.');
380 log_warn = resolve(error_msg);
381 putlog log_warn;
382 end;
383 run;
384
385 /*------------------------------------
386 * Append ATMs into a single data set
387 *------------------------------------*/
388 %if (&i. eq 1) %then
389 %do;
390 data work.output_atm;
391 set work.tab_atm&i._6;
392 run;
393 %end;
394 %else
395 %do;
396 proc append base = work.output_atm
397 data = work.tab_atm&i._6;
398 run;
399 %end;
400 %end;
401
402 /*---------------------------
403 * Check for duplicate ATMs
404 *---------------------------*/
405 proc sort data = work.output_atm nodupkey
406 dupout = work.duped_tms;
407 by model_group_name
408 time_period
409 frequency
410 time_interval
411 from_rating
412 to_rating;
413 run;
414
415 proc sql noprint;
416 select distinct catx(':', time_period, model_group_name)
417 into :duped_tms separated by ','
418 from work.duped_tms;
419 quit;
420
421 %if %superq(duped_tms) ne %then
422 %do;
423 %let system_fatal_error = Y;
424 %put ERROR: Duplicated transition matrices detected. %superq(duped_tms);
425 %abort cancel;
426 %end;
427
428 /*---------------------------------------------------
429 * Rename and sort variables for better presentation
430 *---------------------------------------------------*/
431 proc sort data = work.output_atm;
432 by model_group_name
433 time_period
434 frequency
435 time_interval
436 rating_order;
437 run;
438
439 proc datasets lib=work nolist;
440 modify output_atm;
441 rename model_group_name = Model_Group_Name
442 atm_set_nm = ATM_Set_NM
443 from_rating = From_Rating
444 time_period = Time_Period
445 frequency = Frequency
446 to_rating = To_Rating
447 rating_order = Rating_Order
448 migrate_prob = Migrate_Prob
449 time_interval = Time_Interval
450 time_interval_units = Time_Interval_Units
451 upload_type_cd = Upload_Type_CD;
452 format rating_order
453 migrate_prob best32.;
454 quit;
455
456 /*-----------------------------
457 * Adding scenario_date column
458 *-----------------------------*/
459 data &outds.;
460 set work.output_atm(drop=Time_Interval ATM_Set_NM Upload_Type_CD Time_Interval_Units);
461 format Scenario_Date date9.;
462 Scenario_Date = intnx(Frequency, input(Time_Period, anydtdte.), 0, 'e');;
463 run;
464
465%mend irmst_import_atm_spreadsheet;