SAS Documentation
SASĀ® Solution for Stress Testing
Reference manual - version 08.2021
Loading...
Searching...
No Matches
irmc_apply_rollup_aggregation.sas
Go to the documentation of this file.
1/*
2 Copyright (C) 2019 SAS Institute Inc. Cary, NC, USA
3*/
4
5/**
6 \file
7
8 \brief Apply aggregation rules that map a table onto itself (e.g. rollup rules)
9
10 \param [in] aggregation_rules Dataset with agrgegation rules. This is meant to be aggregation_rules_config or some subset of it.
11 \param [in] schema_name Schema name associated with the data to apply the mapping to
12 \param [in] schema_version Schema version associated with the data to apply the mapping to
13 \param [in] input_ds The table that the aggregation will be applied to.
14 \param [in] period_column The name of the column in input_ds that represents the time horizon. If all values are at the same time horizon, add a dummy column and set the value to 0.
15 \param [out] output_ds Name of the dataset to create - input_ds with aggregations applied.
16
17 \details
18 Rows in aggregation_rules_config that have the same schema name and version for the source and target tables represent aggregation rules that
19 define values in a dataset as functions of other values in the same dataset. For these rules, the src_where_cond column contains the logic that
20 defines how the target value is updated. To reference values from the table, the notation %GETVAL(ID,PERIOD) is used. ID should be a string that matches some value of
21 the column specified in SRC_COL. PERIOD should be an integer and references the lag relative to the current row's period. For example %GETVAL(GL_ASSET_LONOUTST_AUTO,0)
22 with SRC_COL = GL_ACCOUNT_ID references the row in the table with 'GL_ASSET_LONOUTST_AUTO' as the GL_ACCOUNT_ID at the current period.
23
24
25 \ingroup macroUtils
26
27 \author SAS Institute Inc.
28 \date 2019
29*/
30%macro irmc_apply_rollup_aggregation( aggregation_rules =
31 ,schema_name =
32 ,schema_version =
33 ,input_ds =
34 ,period_column =
35 ,output_ds =
36 ) / minoperator;
37
38 /* Check for log_level */
39 %let debug = N;
40 %if not %symexist(log_level) %then %let log_level = 0;
41 %if &log_level. in (3 4 5) %then %do;
42 %let debug = Y;
43 %end;
44
45 /* Does aggregation_rules exist? */
46 %let N_AGGS = 0;
47 %if(not %rsk_dsexist(&aggregation_rules.)) %then %do;
48 /* If there is no aggregation data, put an error in the log and return the input data as the output data */
49 %put %str(ERR)OR: aggregation rules table &aggregation_rules. does not exist or this user does not have access to it. No aggregations can be performed.;
50 %GOTO DONE;
51 %end;
52
53 /* First get the correct rules from aggregation config */
54 data applicable_aggregations;
55 set &aggregation_rules.;
56 where lowcase(TGT_TABLE_SCHEMA_NM) = lowcase("&schema_name.")
57 and lowcase(resolve(TGT_TABLE_SCHEMA_VER)) = lowcase("&schema_version.")
58 and TGT_TABLE_SCHEMA_NM = SRC_TABLE_SCHEMA_NM
59 and TGT_TABLE_SCHEMA_VER = SRC_TABLE_SCHEMA_VER
60 ;
61 %if &debug. eq Y %then %do;
62 put '0a'x;
63 put '--- Aggregation to be applied:';
64 put '0a'x;
65 put TGT_TABLE_SCHEMA_NM=;
66 put TGT_TABLE_SCHEMA_VER=;
67 put TGT_TBL_VAL_COL=;
68 put TGT_TBL_VAL_COL=;
69 put TGT_TBL_ID_VAL=;
70 put SRC_COL=;
71 put '0a'x;
72 put SRC_WHERE_COND=;
73 put '0a'x;
74 put '------------------ ';
75 put '0a'x;
76 %end;
77 run;
78
79 /* Check if any rows were found, if not, just quit */
80 %let TotRows = %rsk_attrn(work.applicable_aggregations, nobs);
81 %if &TotRows. eq 0 %then %do;
82 /* If there is no applicable aggregation data, put a warning in the log and return the input data as the output data */
83 %put %str(WAR)NING: No aggregation rules in &aggregation_rules. apply to table with schema_name &schema_name. and schema_version &schema_version.. No aggregations will be performed.;
84 %GOTO DONE;
85 %end;
86
87 /* Make sure period_column and all necessary mapping columns exist in the input data */
88 %let TGT_ID_COLS = ;
89 %let TGT_VAL_COLS = ;
90 %let SRC_COLS = ;
91 proc sql noprint;
92 select distinct
93 TGT_TBL_ID_COL,
94 TGT_TBL_VAL_COL,
95 SRC_COL
96 into
97 : TGT_ID_COLS separated by ' ',
98 : TGT_VAL_COLS separated by ' ',
99 : SRC_COLS separated by ' '
100 from
101 work.applicable_aggregations
102 ;
103 quit;
104 %let success_flg = N;
105 %let missing_inst_var = ;
106 %rsk_verify_ds_col(REQUIRED_COL_LIST = &period_column. &TGT_ID_COLS. &TGT_VAL_COLS. &SRC_COLS.,
107 IN_DS_LIB =%scan(&input_ds,1),
108 IN_DS_NM =%scan(&input_ds,2),
109 OUT_SUCCESS_FLG =success_flg,
110 OUT_MISSING_VAR =missing_inst_var);
111
112 %if %upcase(&success_flg) eq N %then %do;
113 /*throw error*/
114 %put %str(ERR)OR: Required variable "&missing_inst_var" is missing in data set "&input_ds". No aggregations will be performed;
115 %GOTO DONE;
116 %end;
117
118 /* Parse the aggregation rules */
119 data aggregations_parsed;
120 set applicable_aggregations end = last;
121 length parsedRule $ 12000 tempstr $12000 INIT_STRING $32000 GETVAL_STR $32000 APPLYAGG_STR $32000 AGGCRITERIA_STR $32000;
122 index = find(SRC_WHERE_COND,'%GETVAL(');
123 parsedRule = "";
124 count = 1;
125 do while(index >0);
126 index2 = findc(SRC_WHERE_COND,')',index);
127 /* parsedRule stores the name of the variable and the time period specified inside the %GETVAL call */
128 parsedRule = strip(parsedRule) || substr(SRC_WHERE_COND,index,index2-index) || ',__x' || strip(count) || ');';
129 if index > 1 then
130 SRC_WHERE_COND = substr(SRC_WHERE_COND,1,index-1) || '__x' || strip(count) || substr(SRC_WHERE_COND,index2+1);
131 else
132 SRC_WHERE_COND = '__x' || strip(count) || substr(SRC_WHERE_COND,index2+1);
133 count = count + 1;
134 index = find(SRC_WHERE_COND,'%GETVAL(');
135 end;
136 parsedRule = strip(parsedRule) || strip(TGT_TBL_VAL_COL) || ' = coalesce(' || strip(SRC_WHERE_COND) || ',' || strip(TGT_TBL_VAL_COL) || ');';
137 parsedRule =tranwrd(parsedRule, "GETVAL", "GETVAL"||strip(_N_));
138
139 /* Define the inithash macro for this row, which will define the hash lookup that all the getval calls will use */
140 INIT_STRING =
141'%macro INITHASH'||strip(_N_)||'();
142if missing(_temp_hashinit'||strip(_N_)||') then do;
143 declare hash hash'||strip(_N_)||'(dataset: "' || "&input_ds." ||'");
144 _temp_rc = hash'||strip(_N_)||'.defineKey("' || strip(SRC_COL) || '","' || "&period_column." || '");
145 _temp_rc = hash'||strip(_N_)||'.defineData("' || strip(TGT_TBL_VAL_COL) || '");
146 _temp_rc = hash'||strip(_N_)||'.defineDone();
147 _temp_hashinit'||strip(_N_)||' = 1;
148end;
149%mend;';
150 call execute(INIT_STRING);
151
152 /* Define the GETVAL macro for this row. It uses the previously defined hash to grab the values specified. Because the hash uses columns already in the
153 table to lookup values and store them, these are all first stored in temp variables, then the values are replaced with those required by the lookup. After the
154 hash lookup, the original values are restored */
155 GETVAL_STR =
156'%macro GETVAL'||strip(_N_)||'(col_val,period,varname);
157 _temp_col_id = '|| strip(SRC_COL) || '; ' || SRC_COL || ' = strip("&col_val.");
158 _temp_period = ' || "&period_column.; &period_column. = " || '_temp_period - &period.;
159 _temp_col_val =' || strip(TGT_TBL_VAL_COL) ||';
160 call missing(' || strip(TGT_TBL_VAL_COL) || ');
161 _temp_rc = hash'||strip(_N_)||'.find();
162 if _temp_rc ne 0 then do;
163 put "Lookup failed: col_val=&col_val";
164 put &period_column.=;
165 put ' || strip(SRC_COL) || '=;
166 put _temp_col_id=;
167 end;
168 &varname. = ' || strip(TGT_TBL_VAL_COL) || '; ' ||
169 TGT_TBL_VAL_COL || ' = _temp_col_val; ' ||
170 SRC_COL || ' = _temp_col_id; ' || "
171 &period_column. = _temp_period; " || '
172%mend;';
173 call execute(GETVAL_STR);
174
175 /* Macro that will execute our parsed aggregation rule */
176 APPLYAGG_STR =
177'%macro APPLYAGG'||strip(_N_)||'(); ' ||
178 strip(parsedRule) ||
179'%mend;';
180 call execute(APPLYAGG_STR);
181
182 /* Finally, define a macro that checks if the aggregation defined in this row is applicable */
183 AGGCRITERIA_STR =
184'%macro AGGCRITERIA'||strip(_N_)||'();
185 upcase(' || strip(TGT_TBL_ID_COL) || ') eq upcase("' || strip(TGT_TBL_ID_VAL) || '")
186%mend;';
187 call execute(AGGCRITERIA_STR);
188
189 %if &debug. eq Y %then %do;
190 put '0a'x;
191 put '--- Parsed aggregation is:';
192 put '0a'x;
193 put parsedRule;
194 put '0a'x;
195 put 'Macros are: ';
196 put '0a'x;
197 %printMacro(INIT_STRING);
198 %printMacro(GETVAL_STR);
199 %printMacro(APPLYAGG_STR);
200 %printMacro(AGGCRITERIA_STR);
201 put '0a'x;
202 put '------------------ ';
203 put '0a'x;
204 %end;
205
206 if last then do;
207 call symputX('N_AGGS',_N_);
208 end;
209 run;
210
211 %DONE:
212
213 /* NOTE: If we got here via a GOTO statement (wanring or error), then N_AGGS is 0 and very little happens here.
214 Otherwise, for each row the match criteria for each aggregation is checked and if a match is found, the agrgegation is applied
215 */
216 data &output_ds.;
217 set &input_ds.;
218 length _temp_col_id $ 512;
219 %do i=1 %to &N_AGGS.;
220 if %AGGCRITERIA&i.() then do;
221 %INITHASH&i.();
222 %APPLYAGG&i.();
223 end;
224 %end;
225
226 drop
227 _temp_:
228 %if &debug. eq N %then %do;
229 __x:
230 %end;
231 ;
232 run;
233
234%mend irmc_apply_rollup_aggregation;
235
236/* For debugging the generated macros */
237%macro printMacro(varname);
238 cnt = 1;
239 ind = findc(&varname.,';');
240 pind=1;
241 do while(ind>0 and cnt<1000);
242 tempstr = substr(&varname.,pind,ind-pind+1);
243 put tempstr;
244 pind = ind+1;
245 nind = findc(substr(&varname.,ind+1),';');
246 ind = min(1,nind)*(nind + ind);
247 end;
248%mend printMacro;
249
250/*
251If you have a cycle with the sample data loaded, get the cycle ID and the ID of the GL table. You can then test the macro as follows:
252
253%let cycleID = 10000;
254%let GLKEY = 10008;
255
256%let log_level = 3;
257
258%include "/opt/sas/config/Lev1/AppData/SASRiskWorkGroup/groups/Public/SASRiskManagementCore/cycles/&cycleID./init.sas";
259%include "/opt/sas/repositories/risk/rqsst/irm/sampledata/config/ewst/static/aggregation_rules_config.sas";
260
261 %irm_rgf_retrieve_analysis_data(key = &GLKEY.
262 , libref = rqsst
263 , outds = GLTABLE
264 , host = &rgf_protocol.://&rgf_host.
265 , server = &rgf_service.
266 , solution = rmc
267 , port = &rgf_port.
268 , outSuccess = httpSuccess
269 , outResponseStatus = responseStatus
270 , restartLUA = Y
271 , clearCache = Y
272 );
273
274 %irmc_apply_rollup_aggregation(aggregation_rules = aggregation_rules_config
275 ,schema_name = st_gl_account
276 ,schema_version = &content_version.
277 ,input_ds =WORK.GLTABLE
278 ,period_column = FORECAST_HORIZON
279 ,output_ds = WORK.GLOUT
280 );
281
282*/