Macros for SAS Application Developers File Reference

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

Go to the source code of this file.

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).


data work.orig work.deleted work.changed work.appended;
  set sashelp.class;
  if _n_=1 then do;
    output work.orig work.deleted;
  else if _n_=2 then do;
    output work.orig;
    output work.changed;
  else do;
    output work.appended;

[in]libdsTarget table against which the changes were applied
[in]origdsDataset with original (unchanged) records. Can be empty if only appending.
[in]keySpace 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

Allan Bowe

Definition in file