mddl_dc_difftable.sas
Go to the documentation of this file.
1 /**
2  @file
3  @brief Difftable DDL
4  @details Used to store changes to tables. Used by mp_storediffs.sas
5  and mp_stackdiffs.sas
6 
7 **/
8 
9 
10 %macro mddl_dc_difftable(libds=WORK.DIFFTABLE);
11 
12  proc sql;
13  create table &libds(
14  load_ref char(36) label='unique load reference',
15  processed_dttm num format=E8601DT26.6 label='Processed at timestamp',
16  libref char(8) label='Library Reference (8 chars)',
17  dsn char(32) label='Dataset Name (32 chars)',
18  key_hash char(32) label=
19  'MD5 Hash of primary key values (pipe seperated)',
20  move_type char(1) label='Either (A)ppended, (D)eleted or (M)odified',
21  is_pk num label='Is Primary Key Field? (1/0)',
22  is_diff num label=
23  'Did value change? (1/0/-1). Always -1 for appends and deletes.',
24  tgtvar_type char(1) label='Either (C)haracter or (N)umeric',
25  tgtvar_nm char(32) label='Target variable name (32 chars)',
26  oldval_num num format=best32. label='Old (numeric) value',
27  newval_num num format=best32. label='New (numeric) value',
28  oldval_char char(32765) label='Old (character) value',
29  newval_char char(32765) label='New (character) value'
30  );
31 
32  %local lib;
33  %let libds=%upcase(&libds);
34  %if %index(&libds,.)=0 %then %let lib=WORK;
35  %else %let lib=%scan(&libds,1,.);
36 
37  proc datasets lib=&lib noprint;
38  modify %scan(&libds,-1,.);
39  index create
40  pk_mpe_audit=(load_ref libref dsn key_hash tgtvar_nm)
41  /nomiss unique;
42  quit;
43 
44 %mend mddl_dc_difftable;