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

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.