Macros for SAS Application Developers
https://github.com/sasjs/core
mp_storediffs.sas 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).

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]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

Version
9.2
Author
Allan Bowe

Definition in file mp_storediffs.sas.