SAS Documentation
SASĀ® Solution for Stress Testing
Reference manual - version 08.2021
Loading...
Searching...
No Matches
irmst_apply_aggregation.sas
1%macro irmst_apply_aggregation(ds_in_bep_summary =
2 , ds_in_agg_values =
3 , ds_in_agg_rules =
4 , out_libref =
5 );
6
7 %local
8 n_forecast_time
9 n_data
10 src_keep_var
11 out_libref_tmp
12 ;
13
14 /* Set up the data extraction table to pull the planning data */
15 data work.data_extraction_config;
16 set &ds_in_bep_summary. (keep = planningDataKey dataDefSchemaName)
17 end = last;
18 keep source_data_name
19 analysis_data_key
20 aggregate_flg
21 ;
22 /* Store the fields needed for irm_rgf_retrieve_data */
23 source_data_name = dataDefSchemaName;
24 analysis_data_key = planningDataKey;
25 aggregate_flg = "N";
26 /* Store the planning data schema name */
27 call symputx(cats("planning_data_", put(_N_, 8.)), dataDefSchemaName, "L");
28 /* Store the number of planning data keys found */
29 if last then call symputx("n_data", _N_, "L");
30 run;
31
32 /* Store a backup of out_libref since it gets reassigned in the next macro call */
33 %let out_libref_tmp = &out_libref.;
34
35 /* Retrieve all analysis data */
36 %irm_rgf_retrieve_data(dr_libref = &dr_libref.
37 , dr_library_name = &dr_library_name.
38 , ds_in = work.data_extraction_config
39 , ds_out = work.export_summary
40 , out_type = data
41 , rgf_protocol = &rgf_protocol.
42 , rgf_host = &rgf_host.
43 , rgf_service = &rgf_service.
44 , rgf_solution = &rgf_solution.
45 , rgf_port = &rgf_port.
46 , tgt_ticket = &tgt_ticket.
47 );
48
49 /* Reset out_libref to the backup value */
50 %let out_libref = &out_libref_tmp.;
51
52 /* Get the interval count from the BSP summary */
53 data _null_;
54 set &ds_in_bep_summary. (obs = 1);
55 call symputx("n_forecast_time", intervalCount, "L");
56 run;
57
58
59 /* For each planning data, duplicate the data across n forecast times */
60 %do i = 1 %to &n_data;
61 /* initiate the columns in target tbl */
62 %let tgt_cols=;
63 %let empty_tgt_cols=;
64 /* Get a subset of the aggregation rules that applies to the current planning data */
65 data work.ds_in_agg_rules_fltrd;
66 set &ds_in_agg_rules.;
67 where upcase(TGT_TABLE_SCHEMA_NM) = upcase("&&planning_data_&i..");
68 run;
69 /* Get the variables in the configuration tbl and if they are not in the trgt tbl create them */
70 proc sql;
71 select distinct tgt_tbl_val_col ,tgt_tbl_val_col
72 into: tgt_cols separated by ' ' ,: empty_tgt_cols separated by '=.;'
73 from work.ds_in_agg_rules_fltrd;
74 quit;
75 %do m=1 %to %sysfunc(countw(&tgt_cols.,' '));
76 %let var_exist=%rsk_varexist(work.&&planning_data_&i.., %scan(&tgt_cols.,&m.,' '));
77 %if &var_exist.=0 %then %do;
78 proc sql;
79 alter table work.&&planning_data_&i.. add %scan(&tgt_cols.,&m.,' ') num(8);
80 quit;
81 %end;
82 %end;
83 proc sql;
84 select distinct tgt_tbl_id_col, count(tgt_tbl_id_col)
85 into: tgt_cols_id separated by " ", : n_tgt_tbl_id_cols
86 from (select distinct tgt_tbl_id_col from work.ds_in_agg_rules_fltrd)
87 ;
88 quit;
89
90 %let fnl_in_stmnt=;
91 %let del= %str(%'),%str(%');
92 %if &n_tgt_tbl_id_cols. >0 %then %do;
93 %do ii=1 %to &n_tgt_tbl_id_cols.;
94 %let in_clause_&ii.=;
95 data _null_;
96 call symputx("temp_tgt_col_id_&ii.",scan("&tgt_cols_id",&ii.," "));
97 run;
98
99 proc sql;
100 select tgt_tbl_id_val into: tgt_id_whr_cond separated by "&del."
101 from work.ds_in_agg_rules_fltrd
102 where tgt_tbl_id_col="&&temp_tgt_col_id_&ii..";
103 quit;
104
105 data _null_;
106 call symputx("in_clause_&ii.","&&temp_tgt_col_id_&ii.. in ('&tgt_id_whr_cond.')");
107 run;
108 %end;
109 data _null_;
110 length tmp_in_stm $30000.;
111 %do k=1 %to &n_tgt_tbl_id_cols.;
112 tmp_in_stm=catx(' or ', trim(tmp_in_stm), "&&in_clause_&k");
113 %end;
114 call symputx('fnl_in_stmnt',trim(tmp_in_stm));
115 run;
116 proc sql;
117 create table &out_libref..&&planning_data_&i..
118 like work.&&planning_data_&i..;
119 quit;
120 %do j = 1 %to &n_forecast_time.;
121 data work.temp_planning_data;
122 set work.&&planning_data_&i..;
123 forecast_horizon = &j.;
124 &empty_tgt_cols.=.;
125 where &fnl_in_stmnt.;
126 run;
127 proc append base = &out_libref..&&planning_data_&i.. data = temp_planning_data;
128 run;
129 %end;
130 %end;
131 %else %do;
132 proc sql;
133 create table &out_libref..&&planning_data_&i..
134 like work.&&planning_data_&i..;
135 quit;
136
137 %end;
138 /* Get a list of variables used in the where conditions */
139 %let src_keep_vars=;
140 %irm_get_vars_where_cond(ds_in = work.ds_in_agg_rules_fltrd
141 , where_vars_col = src_where_vars
142 , var_list = src_keep_vars
143 );
144
145 /* Apply the aggregated values to the given planning data */
146 %irmst_apply_agg_bep( ds_in_agg_values = &ds_in_agg_values.
147 , ds_in_agg_rules = work.ds_in_agg_rules_fltrd
148 , src_keep_var_list = &src_keep_vars.
149 , ds_out_agg_result = &out_libref..&&planning_data_&i..
150 );
151 %end;
152
153%mend;