SAS Documentation
SASĀ® Solution for Stress Testing
Reference manual - version 08.2021
Loading...
Searching...
No Matches
irmst_apply_agg.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_apply_agg
8
9 \brief Apply aggregated values to a target dataset
10
11 \param[in] ds_in_agg A mapping table with aggregated values that can be applied to the target table
12 \param[in] ds_in_tgt A table with numeric columns that will be replaced by the aggregated values
13 \param[in] ds_in_agg_rules_config A table that provides the mapping between the aggregations and the target table. See aggregation_rules_config ddl for required table structure
14 \param[out] out_ds A table with the same structure and row count of the target table but replaced with aggregated values inserted into the aggregation column(s)
15
16 \details
17
18 This macro reads in 3 tables: a mapping table of aggregations, a target table and a table that contains aggregations to be inserted into the target table.
19 If a row in the target table does not match any of the aggregations, it will have a blank value in its aggregation column(s)
20
21 \author SAS Institute INC.
22 \date 2020
23
24*/
25
26%macro irmst_apply_agg(ds_in_agg =
27 , ds_in_tgt =
28 , ds_in_agg_rules_config =
29 , ds_out =
30 );
31
32 %local
33 agg_vars
34 i
35 ;
36
37 /* Create list of aggregation variables in target table that will be compared */
38 proc sql noprint;
39 select
40 distinct(upcase(TGT_TBL_VAL_COL))
41 into
42 : agg_vars separated by ' '
43 from
44 &ds_in_agg_rules_config.
45 ;
46 quit;
47
48 /* Build &ds_out table from &ds_in_tgt, dropping aggregation variable(s) */
49 data _tmp_ds_out_agg;
50 set &ds_in_tgt.(drop=&agg_vars.);
51 __row_num__ = _N_;
52 run;
53
54 /* Iterate through &ds_in_rules. and map each aggregation value to the applicable row in target table */
55 %do i = 1 %to %rsk_attrn(&ds_in_agg_rules_config., nlobs);
56
57 data _tmp_rule_pre_agg;
58 pt = &i.;
59 set &ds_in_agg_rules_config. point=pt;
60 call symputx("TGT_TBL_ID_COL&i.", TGT_TBL_ID_COL, "L");
61 call symputx("TGT_TBL_ID_VAL&i.", TGT_TBL_ID_VAL, "L");
62 call symputx("TGT_TBL_VAL_COL&i.", TGT_TBL_VAL_COL, "L");
63 call symputx("SRC_COL&i.", SRC_COL, "L");
64 call symputx("SRC_WHERE_COND&i.", SRC_WHERE_COND, "L");
65 call symputx("AGGREGATION_RULE&i.", AGGREGATION_RULE, "L");
66 output;
67 stop;
68 run;
69
70 /* Map aggregation to row in target table */
71 proc sql;
72 create table _tmp_rule_w_agg as
73 select
74 (select
75 __row_num__
76 from
77 _tmp_ds_out_agg
78 where
79 &&TGT_TBL_ID_COL&i. = "&&TGT_TBL_ID_VAL&i."
80 ) as __row_num__,
81 (select
82 SUM(AGG_VALUE)
83 from
84 &ds_in_agg.
85 where
86 &&SRC_WHERE_COND&i. and
87 AGGREGATION_RULE = "&&AGGREGATION_RULE&i." and
88 SRC_COL = "&&SRC_COL&i."
89 ) as &&TGT_TBL_VAL_COL&i.
90 from
91 _tmp_rule_pre_agg
92 ;
93 quit;
94
95 /* Append single-row dataset to combined dataset */
96 data _tmp_rules_w_agg;
97 set
98 %if %rsk_dsexist(_tmp_rules_w_agg) %then %do;
99 _tmp_rules_w_agg
100 %end;
101 _tmp_rule_w_agg;
102 run;
103
104 %end;
105
106 proc sort data=_tmp_rules_w_agg;
107 by __row_num__;
108 run;
109
110 /* Join aggregations and target table based on row-mapping */
111 data &ds_out_agg.(drop=__row_num__);
112 merge _tmp_ds_out_agg(in=in_tgt)
113 _tmp_rules_w_agg;
114 by __row_num__;
115 if in_tgt;
116 run;
117
118%mend;