Macros for SAS Application Developers
https://github.com/sasjs/core
mp_retainedkey.sas 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 mp_lockanytable.sas 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 mp_coretable.sas as follows: mp_coretable(MAXKEYTABLE).
  • permlib.locktable - Necessary if maxkeytable is being populated. The definition is available by running mp_coretable.sas as follows: mp_coretable(LOCKTABLE).
Parameters
[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.
KEYTABLE:$32. KEYCOLUMN:$32. MAX_KEY:best. PROCESSED_DTTM:E8601DT26.6
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 mp_lockanytable.sas 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

Version
9.2

Definition in file mp_retainedkey.sas.