SAS Documentation
SASĀ® Solution for Stress Testing
Reference manual - version 08.2021
Loading...
Searching...
No Matches
irmst_node_prep_agg_tables.sas
Go to the documentation of this file.
1/*
2 Copyright (C) 2018 SAS Institute Inc. Cary, NC, USA
3*/
4
5/** \file
6 \brief partition node, prepares the input/configuration tables for aggregation
7
8 \param[in] ST_PRM.RUN_OPTION parameter table
9 \param[in] %ds_in_bep_summary = ST_STG.BEP_SUMMARY Input table containing a list of the Business Evolution Plans associated with this Analysis Run
10 \param[in] %ds_in_bep_details = ST_STG.BEP_DETAILS Input table containing details of the Business Evolution Plans associated with this Analysis Run
11 \param[in] %ds_in_agg_rules_config = ST_CFG.AGGREGATION_RULES_CONFIG Contains the rules for aggregating the credit risk detail results
12 \param[in] %ds_in_bsp_data_config = ST_CFG.BSP_DATA_CONFIG Contains information on the source table and scenario name
13 \param[out] %ds_out_agg_rules_config = P_ST_STG.AGGREGATION_RULES_CONFIG Output table containing the aggregation rules filtered to include only those with a schema name/version matching the planning data in the BSP summary
14 \param[out] %ds_out_credit_risk_detail = P_ST_STG.AGG_SRC_TBL Output table containing the results from the source tbl filtered by scenario and only including variables needed for aggregation
15 \param[out] %ds_out_bsp_data_config = P_ST_STG.BSP_DATA_CONFIG Contains information on the source table and scenario name filtered by source.
16
17 \details
18
19 This node filters the aggregation rules configuration table based on the planning data schemas found in the BSP summary table.
20 It also retrieves the source data from the provided instance key and filters by scenario, only keeping the variables needed
21 based on the aggregation rules configuration table. The table bsp_data_config is partitioned to include only one source per partition.
22
23 In addition the following macro utilities are called:
24
25 | Macro name | Description | Further information |
26 |---------------------------|--------------------------------------------------------------------------------------------------------------|-----------------------------------------------|
27 | irm_session_prepare | Reads RUN_OPTION table and sets logging options | \link irm_session_prepare.sas \endlink |
28 | irm_session_cleanup | Removes all non-IRM WORK datasets/views and deletes all user-created macro variables from workspace session | \link irm_session_cleanup.sas \endlink |
29 | irmst_fltr_src_agg_tbl | Filters the source table by scenario name | \link irmst_fltr_src_agg_tbl.sas \endlink |
30
31 \ingroup nodes
32 \author SAS Institute Inc.
33 \date 2018
34*/
35
36/* Initialize session */
37%irm_session_prepare();
38
39 /* Find out the total number of partitions */
40 data _null_;
41 set &ds_in_cardinality.;
42 call symputx("n_partitions", max_rank_no, "L");
43 run;
44
45 /* Expand the BEP summary table to one record per planning data */
46 data work.bep_summary_exp;
47 set &ds_in_bep_summary.;
48 original_targetVariable = targetVariable;
49 original_accountID = accountID;
50 original_planningDataKey = planningDataKey;
51 original_dataDefSchemaName = dataDefSchemaName;
52 original_dataDefSchemaVersion = dataDefSchemaVersion;
53 do i=1 to countw(original_targetVariable,",");
54 targetVariable = scan(original_targetVariable,i,",");
55 accountID = scan(original_accountID,i,",");
56 planningDataKey = scan(original_planningDataKey,i,",");
57 dataDefSchemaName = scan(original_dataDefSchemaName,i,",");
58 dataDefSchemaVersion = scan(original_dataDefSchemaVersion,i,",");
59 output;
60 end;
61 drop original_: i;
62 run;
63
64 /* Total number of records inside the input configuration table */
65 %let TotRows = %rsk_attrn(work.bep_summary_exp, nobs);
66
67 /* Number of records to process for each partition */
68 %let rows_x_partition =1;
69 %if %sysevalf(&rows_x_partition. * &n_partitions. > &TotRows., boolean) %then %do;
70 %put ERROR:You system has &n_partitions threads which means you can have that many source tables as max;
71 %end;
72 %let fromRow = %sysevalf((&rank. -1 ) * &rows_x_partition. + 1);
73 %let toRow = &fromRow.;
74 /* Load all parameters into macro variable arrays */
75 data &ds_out_bsp_data_config.;
76 /* Subset the records for the current partition */
77 set &ds_in_bsp_data_config.(firstobs = &fromRow. obs = &toRow.);
78
79 /* Set all macro variables */
80 call symputx("source_data_name",source_data_name , "L");
81 call symputx("source_data_version",source_data_version , "L");
82 call symputx("analysis_data_key",analysis_data_key , "L");
83 call symputx("scenario_name", scenario_name, "L");
84
85 run;
86/* Filter the aggregation rules config table to include only rows with schema names/versions matching the planning data */
87proc sql;
88 create table work.ds_out_agg_rules_config as
89 select config.*
90 from
91 (select rules.*
92 from &ds_in_agg_rules_config. rules
93 inner join work.bep_summary_exp summary
94 on upcase(rules.tgt_table_schema_nm) = upcase(summary.dataDefSchemaName)
95 and upcase(rules.src_table_schema_nm) ne upcase(rules.tgt_table_schema_nm)
96 and upcase(resolve(rules.tgt_table_schema_ver)) = upcase(resolve(summary.dataDefSchemaVersion))
97 ) as config
98 where upcase(config.src_table_schema_nm) = upcase("&source_data_name.")
99 and upcase(resolve(config.src_table_schema_ver)) = upcase("&source_data_version.")
100 order by weight_var
101 ;
102quit;
103/* Resovle the schema version in the filtered aggregation rules config table */
104data &ds_out_agg_rules_config.;
105 set work.ds_out_agg_rules_config;
106 tgt_table_schema_ver = resolve(tgt_table_schema_ver);
107 src_table_schema_ver = resolve(src_table_schema_ver);
108run;
109
110/* Make sure there are aggregation rules to process */
111%if not %rsk_attrn(&ds_out_agg_rules_config.,nobs) %then %do;
112 %put ERROR: There are no aggregation rules configured for the selected planning data.;
113%end;
114
115/* Filter the source table by scenario name */
116%irmst_fltr_src_agg_tbl(scenario_name = &scenario_name.
117 , ds_in_source_key = &analysis_data_key.
118 , ds_in_agg_rules_config = &ds_out_agg_rules_config.
119 , ds_out_filtered = &ds_out_src_tbl_detail.
120 );
121
122/* Make sure there are obs in the source table after filtering */
123%if not %rsk_attrn(&ds_out_src_tbl_detail.,nobs) %then %do;
124 %put WARNING: There are no observations in the source table.;
125%end;
126/* Cleanup session */
127%irm_session_cleanup;