The `globalReconcile` action, part of the `Reconcile Forecasts Action Set`, simultaneously reconciles multiple levels within a hierarchy of forecasts. This utility function streamlines the process of adjusting forecasts across various aggregation levels to ensure consistency and coherence. It automatically infers the correct order of hierarchy levels based on the `groupBy` variables provided, allowing for flexible input specification.
| Parameter | Description |
|---|---|
| groupBy | Specifies the groupBy variables. Alias: by. |
| levels | Specifies each level of the hierarchy of forecasts. You can specify levels in any order, and the action infers the correct order on the basis of the groupBy variables. |
| levels.casOut | Specifies the output table of reconciled forecasts for this level of the hierarchy. This parameter is required for the bottom level of the hierarchy, but optional for other levels. |
| levels.casOut.caslib | Specifies the caslib for the output table. Defaults to the active caslib. |
| levels.casOut.compress | Specifies whether to compress the output table. Values: TRUE | FALSE. |
| levels.casOut.indexVars | Specifies a list of variables to use for indexing the output table. |
| levels.casOut.label | Specifies a descriptive label for the output table. |
| levels.casOut.lifetime | Specifies the lifetime of the in-memory output table in minutes (64-bit integer). |
| levels.casOut.maxMemSize | Specifies the maximum memory size for the output table in bytes (64-bit integer). |
| levels.casOut.memoryFormat | Specifies the memory format for the output table. Values: "DVR" | "INHERIT" | "STANDARD". |
| levels.casOut.name | Specifies the name of the output table. Required. |
| levels.casOut.promote | Specifies whether to promote the output table to a global scope. Values: TRUE | FALSE. |
| levels.casOut.replace | Specifies whether to replace an existing output table with the same name. Values: TRUE | FALSE. |
| levels.casOut.replication | Specifies the number of redundant copies of the output table (integer). |
| levels.casOut.tableRedistUpPolicy | Specifies the table redistribution policy. Values: "DEFER" | "NOREDIST" | "REBALANCE". |
| levels.casOut.threadBlockSize | Specifies the thread block size for the output table (64-bit integer). |
| levels.casOut.timeStamp | Specifies a timestamp for the output table (string). |
| levels.casOut.where | Specifies WHERE expressions for subsetting the output table. |
| levels.roles | Specifies the roles of each input table variable for this level of the hierarchy. |
| levels.roles.actual | Specifies the name of the variable that contains the actual values. Default: "ACTUAL". |
| levels.roles.error | Specifies the name of the variable that contains the prediction errors. Default: "ERROR". |
| levels.roles.name | Specifies the name of the variable that contains the name of the series. Default: "_NAME_". |
| levels.roles.predict | Specifies the name of the variable that contains the predicted values. Default: "PREDICT". |
| levels.table | Specifies the forecast input table for this level of the hierarchy. Alias: inputTable. Required. |
| levels.table.caslib | Specifies the caslib for the input table. Defaults to the active caslib. |
| levels.table.computedOnDemand | When set to True, creates computed variables when the table is loaded instead of when the action begins. Alias: compOnDemand. Default: FALSE. |
| levels.table.computedVars | Specifies the names of the computed variables to create. If not specified, all variables from `computedVarsProgram` are included. Alias: compVars. |
| levels.table.computedVars.format | Specifies the format to apply to the variable. |
| levels.table.computedVars.formattedLength | Specifies the length of the format field plus the length of the format precision. |
| levels.table.computedVars.label | Specifies the descriptive label for the variable. |
| levels.table.computedVars.name | Specifies the name for the variable. Required. |
| levels.table.computedVars.nfd | Specifies the length of the format precision. |
| levels.table.computedVars.nfl | Specifies the length of the format field. |
| levels.table.computedVarsProgram | Specifies an expression for each computed variable. Alias: compPgm. |
| levels.table.dataSourceOptions | Specifies data source options. Aliases: options, dataSource. |
| levels.table.groupByMode | Specifies how to create groups. Values: "NOSORT" (default: groups data without sorting on each machine, then on controller) | "REDISTRIBUTE" (transfers rows between nodes for ordering, slower). |
| levels.table.importOptions | Specifies settings for reading a table from a data source. Alias: import. (See common `importOptions` parameter for details). |
| levels.table.name | Specifies the name of the input table. Required. |
| levels.table.singlePass | When set to True, does not create a transient table on the server. Default: FALSE. |
| levels.table.vars | Specifies the variables to use in the action. |
| levels.table.vars.format | Specifies the format to apply to the variable. |
| levels.table.vars.formattedLength | Specifies the length of the format field plus the length of the format precision. |
| levels.table.vars.label | Specifies the descriptive label for the variable. |
| levels.table.vars.name | Specifies the name for the variable. Required. |
| levels.table.vars.nfd | Specifies the length of the format precision. |
| levels.table.vars.nfl | Specifies the length of the format field. |
| levels.table.where | Specifies a WHERE expression for subsetting the input data. |
| levels.table.whereTable | Specifies an input table containing rows to use as a WHERE filter. Applied before the input table's `where` parameter. |
| levels.table.whereTable.casLib | Specifies the caslib for the filter table. Defaults to the active caslib. |
| levels.table.whereTable.dataSourceOptions | Specifies data source options for the filter table. (See common `dataSourceOptions` parameter for details). |
| levels.table.whereTable.importOptions | Specifies settings for reading the filter table from a data source. (See common `importOptions` parameter for details). |
| levels.table.whereTable.name | Specifies the name of the filter table. Required. |
| levels.table.whereTable.vars | Specifies the variable names to use from the filter table. |
| levels.table.whereTable.vars.format | Specifies the format to apply to the variable. |
| levels.table.whereTable.vars.formattedLength | Specifies the length of the format field plus the length of the format precision. |
| levels.table.whereTable.vars.label | Specifies the descriptive label for the variable. |
| levels.table.whereTable.vars.name | Specifies the name for the variable. Required. |
| levels.table.whereTable.vars.nfd | Specifies the length of the format precision. |
| levels.table.whereTable.vars.nfl | Specifies the length of the format field. |
| levels.table.whereTable.where | Specifies a WHERE expression for subsetting data from the filter table. |
| nThreads | Specifies the number of threads to use for each worker node in a CAS session. Default: 0. Range: 0–32767. |
| timeId | Specifies the time ID variable. Required. |
To use the `globalReconcile` action, you typically need hierarchical forecast data. This example demonstrates how to create a sample dataset that could be used as input.
| 1 | DATA mycas.forecasts; |
| 2 | LENGTH date 8 region $10 product $10 actual predict error 8; |
| 3 | FORMAT date date9.; |
| 4 | DO date = '01JAN2023'd to '31DEC2023'd; |
| 5 | DO region = 'East', 'West'; |
| 6 | DO product = 'A', 'B', 'Total'; |
| 7 | actual = rand('Normal', 100, 10); |
| 8 | predict = actual + rand('Normal', 0, 5); |
| 9 | error = actual - predict; |
| 10 | OUTPUT; |
| 11 | END; |
| 12 | END; |
| 13 | END; |
| 14 | RUN; |
This example demonstrates how to use the `globalReconcile` action to reconcile a simple two-level hierarchy of forecasts. The hierarchy consists of forecasts grouped by 'region' and 'product'.
| 1 | PROC CAS; |
| 2 | tsReconcile.globalReconcile |
| 3 | groupBy={'region', 'product'}, |
| 4 | levels={ |
| 5 | {TABLE={name='forecasts'}, |
| 6 | casOut={name='reconciled_forecasts_bottom', replace=true}}, |
| 7 | {TABLE={name='forecasts', where='product="Total"'}, |
| 8 | casOut={name='reconciled_forecasts_top', replace=true}} |
| 9 | }, |
| 10 | timeId='date'; |
| 11 | RUN; |
This comprehensive example illustrates the use of `globalReconcile` with custom variable roles, data filtering (using both `where` and `whereTable`), and output table options for a more complex forecasting hierarchy. It demonstrates how to handle input tables where actuals, predictions, and errors are named differently, and how to apply conditions to both the main input and a filtering table.
| 1 | PROC CAS; |
| 2 | /* Create a filtering table for specific regions */ |
| 3 | DATA mycas.region_filter; |
| 4 | LENGTH region $10; |
| 5 | region = 'East'; OUTPUT; |
| 6 | region = 'South'; OUTPUT; |
| 7 | RUN; |
| 8 | |
| 9 | /* Assume 'forecast_data' contains forecast results with custom column names */ |
| 10 | /* Also assume there's a 'forecast_summary' table for an aggregated level */ |
| 11 | DATA mycas.forecast_data; |
| 12 | LENGTH forecast_date 8 region_group $10 item_code $10 actual_sales predict_sales pred_error 8; |
| 13 | FORMAT forecast_date date9.; |
| 14 | DO forecast_date = '01JAN2023'd to '31MAR2023'd BY 7; |
| 15 | DO region_group = 'East', 'West', 'North', 'South'; |
| 16 | DO item_code = 'Item1', 'Item2', 'Total_Items'; |
| 17 | actual_sales = rand('Normal', 200, 20); |
| 18 | predict_sales = actual_sales + rand('Normal', 0, 10); |
| 19 | pred_error = actual_sales - predict_sales; |
| 20 | OUTPUT; |
| 21 | END; |
| 22 | END; |
| 23 | END; |
| 24 | RUN; |
| 25 | |
| 26 | DATA mycas.forecast_summary; |
| 27 | LENGTH forecast_date 8 region_group $10 actual_sales predict_sales pred_error 8; |
| 28 | FORMAT forecast_date date9.; |
| 29 | DO forecast_date = '01JAN2023'd to '31MAR2023'd BY 7; |
| 30 | DO region_group = 'East', 'West', 'North', 'South'; |
| 31 | actual_sales = rand('Normal', 500, 50); |
| 32 | predict_sales = actual_sales + rand('Normal', 0, 20); |
| 33 | pred_error = actual_sales - predict_sales; |
| 34 | OUTPUT; |
| 35 | END; |
| 36 | END; |
| 37 | RUN; |
| 38 | |
| 39 | tsReconcile.globalReconcile |
| 40 | groupBy={'region_group', 'item_code'}, |
| 41 | levels={ |
| 42 | /* Bottom level with custom roles and input table filtering */ |
| 43 | {TABLE={name='forecast_data', |
| 44 | where="item_code ne 'Total_Items'", |
| 45 | whereTable={name='region_filter'}}, |
| 46 | roles={actual='actual_sales', predict='predict_sales', error='pred_error', name='item_code'}, |
| 47 | casOut={name='reconciled_detailed', replace=true, |
| 48 | promote=true, label='Reconciled Detailed Forecasts'}}, |
| 49 | |
| 50 | /* Middle level - aggregated by region_group */ |
| 51 | {TABLE={name='forecast_summary'}, |
| 52 | roles={actual='actual_sales', predict='predict_sales', error='pred_error'}, |
| 53 | casOut={name='reconciled_region_summary', replace=true}}, |
| 54 | |
| 55 | /* Top level - overall total, inferred from 'forecast_summary' if 'item_code' is removed from groupBy */ |
| 56 | {TABLE={name='forecast_summary', where='region_group ne "East"'}, |
| 57 | roles={actual='actual_sales', predict='predict_sales', error='pred_error'}, |
| 58 | casOut={name='reconciled_overall_total', replace=true, |
| 59 | maxMemSize=104857600 /* 100MB */}} |
| 60 | }, |
| 61 | nThreads=4, /* Using 4 threads for processing */ |
| 62 | timeId='forecast_date'; |
| 63 | RUN; |