SAS Documentation
SASĀ® Solution for Stress Testing
Reference manual - version 08.2021
Loading...
Searching...
No Matches
irmst_aggregate_from_rules.sas
Go to the documentation of this file.
1/*
2 Copyright (C) 2020 SAS Institute Inc. Cary, NC, USA
3 */
4
5/**
6 \file
7 \anchor irmst_aggregate_from_rules
8
9 \brief Perform aggregation of a dataset based on aggregation rules
10
11 \param[in] ds_in_src_tbl A table whose values will be aggregated
12 \param[in] ds_in_agg_rules_config A table that provides the aggregation rules to be applied to the source table. See aggregation_rules_config ddl for required table structure
13 \param[out] out_ds A table that will contain the aggregations calculated from the source table.
14
15 \details
16
17 This macro reads in 2 tables: a source table and a table that contains aggregation rules for the source table.
18 The output dataset is structured like the following example:
19
20 | SRC_COL | AGGREGATION_RULE | AGG_VALUE | <SRC_WHERE_VARS1> | <SRC_WHERE_VARS2> | <...> | <SRC_WHERE_VARSN> |
21 |--------------------|------------------|-------------|--------------------------|--------------------------|-------|-----------------------|
22 | UNPAID_BALANCE_AMT | SUM | 2928719.51 | Automobile_Loans | Retail | <...> | Retail Customer |
23 | UNPAID_BALANCE_AMT | SUM | 8027100.29 | CI_Term_Loans | Commercial | <...> | Manufacturing |
24 | UNPAID_BALANCE_AMT | SUM | 240477.45 | CRE_Construction_Loans_P | Commercial | <...> | Manufacturing |
25 | UNPAID_BALANCE_AMT | SUM | 15279922.83 | CRE_Construction_Loans | Commercial | <...> | Manufacturing |
26 | UNPAID_BALANCE_AMT | SUM | 21339203.81 | CRE_Perm_Loans | Commercial | <...> | Manufacturing |
27 | UNPAID_BALANCE_AMT | SUM | 5305.26 | Construction | Retail | <...> | Retail Customer |
28 | UNPAID_BALANCE_AMT | SUM | 3341369.31 | Credit_cards | Retail | <...> | Retail Customer |
29 | UNPAID_BALANCE_AMT | SUM | 525732.27 | HELOCs | Retail | <...> | Retail Customer |
30 | UNPAID_BALANCE_AMT | SUM | 11139530.81 | Line_of_Credit | Commercial | <...> | Manufacturing |
31 | UNPAID_BALANCE_AMT | SUM | 24406.49 | Residential_mortgages_P | Retail | <...> | Retail Customer |
32 | UNPAID_BALANCE_AMT | SUM | 855434.25 | Residential_mortgages | Retail | <...> | Retail Customer |
33
34 The number of additional columns is based on the number of unique SRC_WHERE_VARS columns in the input aggregation rules dataset.
35
36 \author SAS Institute INC.
37 \date 2020
38
39*/
40
41%macro irmst_aggregate_from_rules(ds_in_src_tbl =
42 , ds_in_agg_rules_config =
43 , out_ds = WORK.RESULT
44 );
45
46 /* Add rule data to macro variables for use in proc hpsummary */
47 data _null_;
48 length src_where_vars_all src_col_all aggregation_rule_all _tmp_agg_stmt $ 2000 _tmp_src_where_var _tmp_src_agg_var $ 32;
49 do until(eof);
50 set &ds_in_agg_rules_config. end=eof;
51 /* list of unique SRC_WHERE_VARS values */
52 do i = 1 to countw(SRC_WHERE_VARS);
53 _tmp_src_where_var = scan(SRC_WHERE_VARS, i);
54 if findw(src_where_vars_all, _tmp_src_where_var, ' ', 'R') = 0 then src_where_vars_all = catx(' ', src_where_vars_all, _tmp_src_where_var);
55 end;
56 /* list of unique SRC_COL values */
57 _tmp_src_agg_var = upcase(SRC_COL);
58 if findw(src_col_all, _tmp_src_agg_var, ' ', 'R') = 0 then src_col_all = catx(' ', src_col_all, _tmp_src_agg_var);
59 /* list of unique output variable names: <SRC_COL>_<AGGREGATION_RULE> */
60 _tmp_agg_stmt = cats(upcase(AGGREGATION_RULE), '(', upcase(SRC_COL), ')=', upcase(SRC_COL), '_', upcase(AGGREGATION_RULE));
61 if findw(aggregation_rule_all, _tmp_agg_stmt, ' ', 'R') = 0 then aggregation_rule_all = catx(' ', aggregation_rule_all, _tmp_agg_stmt);
62 end;
63 call symputx("src_where_vars", src_where_vars_all, "L");
64 call symputx("src_agg_vars", src_col_all, "L");
65 call symputx("agg_type_out_stmt", aggregation_rule_all, "L");
66 run;
67
68 /* Calculate aggregations from source table based on rules */
69 proc hpsummary data=&ds_in_src_tbl.;
70 classes &src_where_vars.;
71 vars &src_agg_vars.;
72 output out=_tmp_agg (drop=_TYPE_ _FREQ_) &agg_type_out_stmt.;
73 run;
74
75 /* Format aggregation output dataset for easier mapping to target table */
76 data &out_ds.(keep=SRC_COL AGGREGATION_RULE AGG_VALUE &src_where_vars.);
77 length SRC_COL AGGREGATION_RULE $ 32. AGG_VALUE 8.;
78 set _tmp_agg;
79
80 array num{*} _NUMERIC_;
81 do i=1 to dim(num);
82 if vname(num[i]) ne 'AGG_VALUE' then do;
83 do j = 1 to (countc(vname(num[i]), '_') + 1);
84 if j < (countc(vname(num[i]), '_') + 1) then SRC_COL = catx('_', SRC_COL, scan(vname(num[i]), j, '_'));
85 AGGREGATION_RULE = scan(vname(num[i]), j, '_');
86 end;
87 AGG_VALUE = num[i];
88 output;
89 end;
90 end;
91 run;
92
93%mend;