SAS Documentation
SASĀ® Solution for Stress Testing
Reference manual - version 08.2021
Loading...
Searching...
No Matches
irmst_apply_agg_bep.sas
1%macro irmst_apply_agg_bep( ds_in_agg_values =
2 , ds_in_agg_rules =
3 , src_keep_var_list =
4 , ds_out_agg_result =
5 );
6
7 %local
8 i
9 ii
10 j
11 l
12 ll
13 ;
14
15 /* Get the target table schema name and the count of source columns from ds_in_agg_rules */
16 proc sql;
17 select count(src_col)
18 , TGT_TABLE_SCHEMA_NM
19 into :n_obs
20 , :tgt_tbl separated by ' '
21 from &ds_in_agg_rules.;
22 quit;
23
24 /* Get the number of vars to keep along with the variable names and types */
25 data headers;
26 set &ds_in_agg_values. (obs=0);
27 keep &src_keep_var_list.;
28 run;
29 proc contents data=headers noprint out=meta_headers (keep=name type);
30 run;
31 proc sql noprint;
32 select count(name)
33 , name
34 , type
35 into :n_keep_vars
36 , :src_keep_var_list separated by ' '
37 , :types separated by ' '
38 from meta_headers;
39 quit;
40
41 data agg_rules_cnf;
42 set &ds_in_agg_rules.;
43 src_where_cond=prxchange('s/([a-zA-Z])(\s*[=])/$1 $2/',-1, src_where_cond);
44 run;
45
46 /* Loop through all observations in the ds_in_agg_rules table, reading only one obs per loop */
47 %do i=1 %to &n_obs;
48 data _null_;
49 set agg_rules_cnf (firstobs=&i. obs=&i.);
50 /* Put each where condition in its own macro variable for both source and target tables */
51 call symputx("src_where_cond_&i.", src_where_cond);
52 call symputx("tgt_tbl_id_val&i.", tgt_tbl_id_val);
53 call symputx("tgt_tbl_val_col_&i.", tgt_tbl_val_col);
54 call symputx("tgt_col_&i.", tgt_tbl_id_col);
55 call symputx("src_col_&i.",trim(src_col)||'_'||trim(aggregation_rule));
56 /* Initialize all the macro variables to a, this is to prevent the code from thowign an error when a
57 configuration is not present.*/
58 call symputx("agg_val_&i.",'a');
59 run;
60
61 %do ii=1 %to &n_forecast_time;
62 /* Retrieve the relevant aggregated value from ds_in_agg_values and store it in a macro variable */
63 data _null_;
64 length enriched_whre_stmt $1000
65 cum $1000
66 temp $100
67 ;
68 enriched_whre_stmt="' "||"&&src_where_cond_&i."||"'";
69 cum="&&src_where_cond_&i.";
70 do j=1 to countw("&src_keep_var_list.",' ');
71 word=upcase(scan("&src_keep_var_list.",j,' '));
72 word_type=scan("&types.",j,' ');
73 present=findw(upcase(enriched_whre_stmt),word,'','R');
74 if present=0 then do;
75 if word_type=2 then do;
76 temp=trim(word)||"=''";
77 cum=catx(" and ",trim(cum),trim(temp));
78 end;
79 else do;
80 temp=trim(word)||"= .";
81 cum=catx(" and ",trim(cum),trim(temp));
82 end;
83 end;
84 end;
85 call symputx("fnl_where_stmt_&ii.",trim(cum));
86 run;
87 %let agg_val_&i._&ii.=;
88 %let match=0;
89 data _null_;
90 id1=prxparse('/[><]/');
91 id2=prxparse('/ [lg]t /');
92 match1=prxmatch(id1,"&&fnl_where_stmt_&ii.");
93 match2=prxmatch(id2,"&&fnl_where_stmt_&ii.");
94 call symputx('match',max(match1,match2));
95 run;
96 %if &match %then %do;
97 %let type_agg=%scan(&&src_col_&i.,-1,'_');
98 data temp_tbl;
99 set &ds_in_agg_values.;
100 where &&fnl_where_stmt_&ii. and forecast_time=&ii.;
101 run;
102 proc summary data=temp_tbl;
103 var &&src_col_&i.;
104 output out=rslt &type_agg.=&&src_col_&i.;
105 run;
106 data _null_;
107 set rslt;
108 call symputx("agg_val_&i._&ii.",&&src_col_&i.);
109 run;
110 %end;
111 %else %do;
112 data _null_;
113 set &ds_in_agg_values.;
114 where &&fnl_where_stmt_&ii. and forecast_time=&ii.;
115 call symputx("agg_val_&i._&ii.",&&src_col_&i.);
116 run;
117 %end;
118 %end;
119 %end;
120
121 /* Update the target table with the calculated aggregated values */
122 %do l=1 %to &n_obs;
123 proc sql ;
124 %do ll=1 %to &n_forecast_time;
125 %if %sysevalf(%superq(agg_val_&l._&ll.) ne, boolean) %then %do;
126 update &ds_out_agg_result.
127 set &&tgt_tbl_val_col_&l.= &&agg_val_&l._&ll.
128 where &&tgt_col_&l. eq "&&tgt_tbl_id_val&l."
129 and forecast_horizon = &&ll.;
130 %end;
131 %end;
132 quit;
133 %end;
134
135%mend;