Converts deletes/changes/appends into a single audit table. More...

Detailed Description

When tracking changes to data over time, it can be helpful to have a single base table to track ALL modifications - enabling audit trail, data recovery, and change re-application. This macro is one of many data management utilities used in Data Controller for SAS - a comprehensive data ingestion solution, which works on any SAS platform (Viya, SAS 9, Foundation).

NOTE - this macro does not validate the inputs. It is assumed that the datasets containing the new / changed / deleted rows are CORRECT, contain no additional (or missing columns), and that the originals dataset contains all relevant base records (and no additionals).

Usage:

data work.orig work.deleted work.changed work.appended;
  set sashelp.class;
  if _n_=1 then do;
    output work.orig work.deleted;
  end;
  else if _n_=2 then do;
    output work.orig;
    age=99;
    output work.changed;
  end;
  else do;
    name='Newbie';
    output work.appended;
    stop;
  end;
run;

%mp_storediffs(sashelp.class,work.orig,NAME
  ,delds=work.deleted
  ,modds=work.changed
  ,appds=work.appended
  ,outds=work.final
  ,mdebug=1
)

Parameters

[in]	libds	Target table against which the changes were applied
[in]	origds	Dataset with original (unchanged) records. Can be empty if only appending.
[in]	key	Space seperated list of key variables
[in]	delds=	(0) Dataset with deleted records
[in]	appds=	(0) Dataset with appended records
[in]	modds=	(0) Dataset with modified records
[out]	outds=	(work.mp_storediffs) Output table containing stored data. DDL as follows: mp_coretable(DIFFTABLE)
[in]	processed_dttm=	(0) Provide a datetime constant in relation to the actual load time. If not provided, current timestamp is used.
[in]	mdebug=	set to 1 to enable DEBUG messages and preserve outputs
[out]	loadref=	(0) Provide a unique key to reference the load, otherwise a UUID will be generated.

SAS Macros

Related Macros

Version: 9.2

Author: Allan Bowe

Definition in file mp_storediffs.sas.