Macros for SAS Application Developers
No Matches File Reference

Generate and apply retained key values to a staging table. More...

Go to the source code of this file.

Detailed Description

This macro will populate a staging table with a Retained Key based on a business key and a base (target) table.

Definition of retained key (source):

‍The retained key is a key which is mapped to business key one-to-one. In comparison, the surrogate key includes time and there can be many surrogate keys corresponding to one business key. This explains the name of the key, it is retained with insertion of a new version of a row while surrogate key is increasing.

This macro is designed to be used as part of a wider load / ETL process (such as the one in Data Controller for SAS).

Specifically, the macro assumes that the base table has already been 'locked' (eg with the macro) prior to invocation. Also, several tables are assumed to exist (names are configurable):

  • work.staging_table - the staged data, minus the retained key element
  • permlib.base_table - the target table to be loaded (not loaded by this macro)
  • permlib.maxkeytable - optional, used to store load metaadata. The definition is available by running as follows: mp_coretable(MAXKEYTABLE).
  • permlib.locktable - Necessary if maxkeytable is being populated. The definition is available by running as follows: mp_coretable(LOCKTABLE).
[in]base_lib=(WORK) Libref of the base (target) table.
[in]base_dsn=(BASETABLE) Name of the base (target) table.
[in]append_lib=(WORK) Libref of the staging table
[in]append_dsn=(APPENDTABLE) Name of the staging table
[in]retained_key=(DEFAULT_RK) Name of RK to generate (should exist on base table)
[in]business_key=(PK1 PK2) Business key against which to generate RK values. Should be unique and not null on the staging table.
[in]check_uniqueness=(NO)Set to yes to perform a uniqueness check. Recommended if there is a chance that the staging data is not unique on the business key.
[in]maxkeytable=(0) Provide a maxkeytable libds reference here, to store load metadata (maxkey val, load time). Set to zero if metadata is not required, eg, when preparing a 'dummy' load. Structure is described above. See below for sample data.
DC487173.MPE_SELECTBOX SELECTBOX_RK 55 1950427787.8
DC487173.MPE_FILTERANYTABLE filter_rk 14 1951053886.8
[in]locktable=(0) If updating the maxkeytable, provide the libds reference to the lock table (per macro)
[in]filter_str=Apply a filter - useful for SCD2 or BITEMPORAL loads. Example: filter_str=str( (where=( &now < &tech_to)) )
[out]outds=(WORK.APPEND) Output table (staging table + retained key)

SAS Macros

Related Macros


Definition in file