SAS Documentation
SASĀ® Solution for Stress Testing
Reference manual - version 08.2021
Loading...
Searching...
No Matches
fs_projection.sas
Go to the documentation of this file.
1/*
2 Copyright (C) 2019 SAS Institute Inc. Cary, NC, USA
3*/
4
5/**
6 \file
7 \brief The report mart table FS_PROJECTION contains the required information for the SAS Visual Analytics Management Report - Balance Sheet Projection
8 \details
9
10 | PK | Variable | Type | Required? | Relationships | Label | Description |
11 |--------------|-----------------------|--------------|-----------|-----------------------------------------------------------------------------------------------------------------------------------------------------------------|------------------------------------|-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
12 | ![ ](pk.jpg) | ANALYSIS_RUN_ID | NUMERIC | Y | | Analysis Run Id | Unique key of the Analysis Run that created the results |
13 | ![ ](pk.jpg) | WORKGROUP | VARCHAR(32) | Y | | Workgroup | SAS Risk Workgroup |
14 | ![ ](pk.jpg) | LOAD_ID | VARCHAR(32) | Y | | Load Id | Identifies whether the current data was created as part of a production analysis run or not. \nValues: \n - Production \n - What-If |
15 | ![ ](pk.jpg) | <b>ENTITY_ID</b> | VARCHAR(32) | Y | | Entity | Entity Identifier |
16 | ![ ](pk.jpg) | <b>REPORTING_DT</b> | DATE | Y | | Reporting Date | Reference date for the calculation. Also known as the base date or evaluation date of the portfolio |
17 | ![ ](pk.jpg) | FS_LINEITEM | VARCHAR(64) | Y | | Financial Account Identifier | Financial Account Identifier |
18 | ![ ](pk.jpg) | FS_ACCOUNT_CD | VARCHAR(32) | Y | \link map_balance_sheet_account_hierarchy \endlink \link map_profit_loss_account_hierarchy \endlink \link map_own_funds_account_hierarchy \endlink | Financial Account Code | Identifies the financial account code for Balance Sheet, Profit and Loss and Own Funds accounts |
19 | ![ ](pk.jpg) | SCENARIO_ID | VARCHAR(32) | Y | | Scenario Id | Unique scenario id used for the analysis. Each forecast time has a separate scenario id |
20 | ![ ](pk.jpg) | FORECAST_TIME | NUMERIC(8) | N | | Forecast Time | Forecast time horizon |
21 | ![ ](pk.jpg) | MOVEMENT_ID | NUMERIC(8) | N | | Movement Id | Identifier for any changes that might occurr to the data as a result of a management adjustment/override. Model output has movement_id = 1, any adjustment is measured as a delta change from the previous movement_id, and stored using incremental movement_id values |
22 | | PROJECT_NAME | VARCHAR(64) | Y | | Project Name | Name of the cycle in which the data was created |
23 | | ANALYSIS_RUN_NAME | VARCHAR(150) | N | | Analysis Run Name | Name of the analysis run that created this data |
24 | | SCENARIO_NAME | VARCHAR(512) | N | | Scenario Name | Name of the business scenario that was selected as input for analysis run |
25 | | FORECAST_PERIOD | VARCHAR(32) | N | | Forecast Period | Forecast Period expressed as the number of intervals from the Reporting Date. I.e. Year 0 (current period), Year 1, .., Year N |
26 | | GEOGRAPHY_CD | VARCHAR(32) | N | \link map_geo_hierarchy \endlink | Geography Code | Geography Code (reporting dimension). |
27 | | GEOGRAPHY_LEV1 | VARCHAR(100) | N | | Geography Level 1 | Geography Level 1 (reporting dimension). |
28 | | GEOGRAPHY_LEV2 | VARCHAR(100) | N | | Geography Level 2 | Geography Level 2 (reporting dimension). |
29 | | GEOGRAPHY_LEV3 | VARCHAR(100) | N | | Geography Level 3 | Geography Level 3 (reporting dimension). |
30 | | GEOGRAPHY_LEV4 | VARCHAR(100) | N | | Geography Level 4 | Geography Level 4 (reporting dimension). |
31 | | CHART_OF_ACCOUNT_CD | VARCHAR(32) | N | | Chart of Account Code | Chart of account code of the financial account |
32 | | CHART_OF_ACCOUNT_NAME | VARCHAR(100) | N | | Chart of Account Name | Chart of Account Name |
33 | | MOVEMENT_DESC | VARCHAR(100) | N | | Movement Description | Custom description provided by the user when executing the management adjustment rules. Used to provide a user specific comment about the change being applied on top of the model results |
34 | | MOVEMENT_TYPE_CD | VARCHAR(32) | N | | Movement Type Code | Movement type code. See \link map_movement_type.sas \endlink for a list of valid values |
35 | | MOVEMENT_TYPE | VARCHAR(100) | N | | Movement Type | Type of movement or adjustment. Looked up from \link map_movement_type.sas \endlink |
36 | | MOVEMENT_CATEGORY | VARCHAR(100) | N | | Movement Category | Movement or Adjustment category. Looked up from \link map_movement_type.sas \endlink |
37 | | CURRENCY | VARCHAR(3) | N | | Currency | Original currency |
38 | | ACCOUNT_VALUE_AMT | NUMERIC(8) | Y | | Account Value Amount | Measure value of the financial line item account from GL, PL and Own Funds table |
39 | | FS_TYPE | VARCHAR(10) | Y | | Financial Statement Type | Identifies the type of financial statement for an account such as GL, PL or Own Funds |
40 | | FS_ACCOUNT_LEV1 | VARCHAR(100) | N | | FS Line Item Level 1 | Financial Statement Account Hierarchy - Level 1. |
41 | | FS_ACCOUNT_LEV2 | VARCHAR(100) | N | | FS Line Item Level 2 | Financial Statement Account Hierarchy - Level 2. |
42 | | FS_ACCOUNT_LEV3 | VARCHAR(100) | N | | FS Line Item Level 3 | Financial Statement Account Hierarchy - Level 3. |
43 | | FS_ACCOUNT_LEV4 | VARCHAR(100) | N | | FS Line Item Level 4 | Financial Statement Account Hierarchy - Level 4. |
44 | | FS_ACCOUNT_LEV5 | VARCHAR(100) | N | | FS Line Item Level 5 | Financial Statement Account Hierarchy - Level 5. |
45 | | TGT_CET1 | NUMERIC(8) | N | | Target CET1 Ratio | Target CET1 Ratio. Only populated for Own Funds Account. |
46 | | TGT_TIER1 | NUMERIC(8) | N | | Target Tier1 Ratio | Target Tier1 Ratio. Only populated for Own Funds Account. |
47 | | TGT_TOT_CAP | NUMERIC(8) | N | | Target Total Capital Ratio | Target Total Capital Ratio. Only populated for Own Funds Account. | |
48 | | TGT_LEV | NUMERIC(8) | N | | Target Leverage Ratio | Target Leverage Ratio. Only populated for Own Funds Account. | |
49 | | RULE_ID | VARCHAR(32) | N | | Posting Id | Management action posting id |
50 | | RULE_DESC | VARCHAR(4096)| N | | Action Description | Management action description |
51 | | ROLL_UP | VARCHAR(32) | N | | Roll up | Flags (Y/N) that indicates if an account gets values by rolling-up other accounts. Roll up property is defined in the mapping tables. |
52
53 \ingroup ddlReportMart
54 \author SAS Institute Inc.
55 \date 2018
56
57*/
58create table &libref..FS_PROJECTION (
59 ANALYSIS_RUN_ID NUMERIC label = 'Analysis Run Id',
60 ANALYSIS_RUN_NAME VARCHAR(150) label = 'Analysis Run Name',
61 LOAD_ID VARCHAR(32) label = 'Load Id',
62 REPORTING_DT DATE label = 'Reporting Date' FORMAT = yymmddd10.,
63 ENTITY_ID VARCHAR(32) label = 'Entity',
64 WORKGROUP VARCHAR(32) label = 'Workgroup',
65 PROJECT_NAME VARCHAR(64) label = 'Project Name',
66 SCENARIO_ID VARCHAR(32) label = 'Scenario Id',
67 SCENARIO_NAME VARCHAR(512) label = 'Scenario Name',
68 FS_LINEITEM VARCHAR(64) label = 'Financial Account Identifier',
69 FORECAST_PERIOD VARCHAR(32) label = 'Forecast Period',
70 FORECAST_TIME NUMERIC(8) label = 'Forecast Time',
71 GEOGRAPHY_CD VARCHAR(32) label = 'Geography Code',
72 GEOGRAPHY_LEV1 VARCHAR(100) label = 'Geography Level 1',
73 GEOGRAPHY_LEV2 VARCHAR(100) label = 'Geography Level 2',
74 GEOGRAPHY_LEV3 VARCHAR(100) label = 'Geography Level 3',
75 GEOGRAPHY_LEV4 VARCHAR(100) label = 'Geography Level 4',
76 CHART_OF_ACCOUNT_CD VARCHAR(32) label = 'Chart of Account Code',
77 CHART_OF_ACCOUNT_NAME VARCHAR(100) label = 'Chart of Account Name',
78 MOVEMENT_ID NUMERIC(8) label = 'Movement Id',
79 MOVEMENT_DESC VARCHAR(100) label = 'Movement Description',
80 MOVEMENT_TYPE_CD VARCHAR(32) label = 'Movement Type Code',
81 MOVEMENT_TYPE VARCHAR(100) label = 'Movement Type',
82 MOVEMENT_CATEGORY VARCHAR(100) label = 'Movement Category',
83 CURRENCY_CD VARCHAR(3) label = 'Currency Code',
84 ACCOUNT_VALUE_AMT NUMERIC(8) label = 'Account Value Amount' FORMAT = comma25.2,
85 FS_ACCOUNT_CD VARCHAR(32) label = 'Financial Account Code',
86 FS_ACCOUNT_LEV1 VARCHAR(100) label = 'FS Line Item Level 1',
87 FS_ACCOUNT_LEV2 VARCHAR(100) label = 'FS Line Item Level 2',
88 FS_ACCOUNT_LEV3 VARCHAR(100) label = 'FS Line Item Level 3',
89 FS_ACCOUNT_LEV4 VARCHAR(100) label = 'FS Line Item Level 4',
90 FS_ACCOUNT_LEV5 VARCHAR(100) label = 'FS Line Item Level 5',
91 FS_TYPE VARCHAR(10) label = 'Financial Statement Type',
92 TGT_CET1 NUMERIC(8) label = 'Target CET1 Ratio',
93 TGT_TIER1 NUMERIC(8) label = 'Target Tier1 Ratio',
94 TGT_TOT_CAP NUMERIC(8) label = 'Target Total Capital Ratio',
95 TGT_LEV NUMERIC(8) label = 'Target Leverage Ratio',
96 RULE_ID VARCHAR(32) label = 'Posting Id',
97 RULE_DESC VARCHAR(4096) label = 'Management Action Description',
98 ROLL_UP VARCHAR(10) label = 'Roll up'
99
100);
101
102