19%macro irmst_import_atm_spreadsheet(in_model_groups = ,
24 %local system_fatal_error
28 num_distinct_transmat_dt
38 %let system_fatal_error = N;
39 %let atm_set_name = atm_upload;
40 %let time_interval = 12;
46 data work.atm_tab(drop = model_group_name_tmp
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'
59 model_group_name = model_group_name_tmp
60 time_period = time_period_tmp
61 frequency = frequency_tmp
65 where rating_tmp ne
'';
66 retain model_group_name
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);
79 select distinct model_group_name,
82 into :model_group_name1-,
88 %let num_atms_in_sheet = &sqlobs;
95 select distinct model_group_name,
96 intnx(frequency,input(time_period,anydtdte32.),0,
'e')
97 into :model_group_name_dt1-,
102 %let num_distinct_transmat_dt = &sqlobs;
104 %if &num_atms_in_sheet ne &num_distinct_transmat_dt %then
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;
114 %do i = 1 %to &num_atms_in_sheet;
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.;
129 select distinct upcase(name) as to_vars,
130 catx('=_',name,name) as rename,
133 then catt(name,'=input(_',name,',best32.);')
134 else catt(name,'=_',name,';')
137 into : to_vars separated by ' ',
138 : to_vars_rename separated by ' ',
139 : to_vars_change_type separated by ' ',
141 from dictionary.columns
142 where libname eq 'WORK' and
143 memname eq 'ATM_TAB' and
145 upcase(name) not in ('MODEL_GROUP_NAME','TIME_PERIOD','FREQUENCY','RATING')
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";
155 &to_vars_change_type.;
158 proc transpose data = work.tab_atm&i._0
159 out = work.tab_atm&i._1 (rename = (_name_ = to_rating));
171 from work.tab_atm&i._1;
175 from dictionary.tables
176 where libname eq 'WORK' and
177 memname eq "%upcase(tab_atm&i._1)" and
181 %if &num_from ne %eval(&num_to - 1) %then
183 %let system_fatal_error = Y;
184 %let error = ERROR: At least one of the input matrices in the spreadsheet upload is not square.;
193 create table work.dimension as
194 select model_group_name,
197 count(*) as dimension
199 group by model_group_name,
205 select model_group_name,
206 count(distinct dimension) as distinct_dimension
207 into :mg_dim_error separated by ' ',
208 :distinct_dim separated by ' '
210 group by model_group_name
211 having distinct_dimension gt 1;
214 %if &sqlobs ne 0 %then
216 %let system_fatal_error = Y;
217 %let error = ERROR: Matrices in model_group(s) %nrbquote('&mg_dim_error') have different dimensions.;
225 data work.ratings_tab_atm&i;
226 set work.atm_tab (keep = model_group_name
230 where model_group_name eq "&&model_group_name&i" and
231 time_period eq "&&time_period&i" and
232 frequency eq "&&frequency&i";
240 create table work.tab_atm&i._2 as
241 select a.model_group_name,
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
254 proc transpose data = work.tab_atm&i._2
255 out = work.tab_atm&i._3 (rename = (_name_ = from_rating));
262 data work.tab_atm&i._4 (drop = model_group_name
264 set work.tab_atm&i._3 end=last;
265 array num[*] _numeric_;
268 if cmiss(of num[*]) gt 0 then
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;
280 %if &system_fatal_error eq Y %then
286 proc transpose data = work.tab_atm&i._4
287 out = work.tab_atm&i._5 (rename = (col1 = migrate_prob
288 _name_ = to_rating));
296 data work.tab_atm&i._6(drop=rating
311 if 0 then set work.ratings_tab_atm&i;
312 set work.tab_atm&i._5;
315 atm_set_nm = symget('atm_set_name');
319 if first.from_order then
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();
332 rc = rating_order_lookup.find(key: from_order);
333 from_rating = rating;
334 rc = rating_order_lookup.find(key: to_order);
337 model_group_name = resolve('&&model_group_name&i');
338 time_period = "&&time_period&i";
339 frequency = "&&frequency&i";
344 if migrate_prob lt 0 or migrate_prob gt 1 then
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) "'";
354 time_interval = &time_interval;
355 time_interval_units = 'Months';
357 upload_type_cd = 'ATM';
360 %if &system_fatal_error eq Y %then
367 set work.tab_atm&i._3 end=last;
369 array num[*] _numeric_;
370 length error_msg $1000;
372 rowsum = round(sum(of num[*]),1E-8);
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);
390 data work.output_atm;
391 set work.tab_atm&i._6;
396 proc append base = work.output_atm
397 data = work.tab_atm&i._6;
405 proc sort data = work.output_atm nodupkey
406 dupout = work.duped_tms;
416 select distinct catx(':', time_period, model_group_name)
417 into :duped_tms separated by ','
421 %if %superq(duped_tms) ne %then
423 %let system_fatal_error = Y;
424 %put ERROR: Duplicated transition matrices detected. %superq(duped_tms);
431 proc sort data = work.output_atm;
439 proc datasets lib=work nolist;
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;
453 migrate_prob best32.;
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');;
465%mend irmst_import_atm_spreadsheet;