Macros for SAS Application Developers
https://github.com/sasjs/core
mp_storediffs.sas
Go to the documentation of this file.
1/**
2 @file
3 @brief Converts deletes/changes/appends into a single audit table.
4 @details When tracking changes to data over time, it can be helpful to have
5 a single base table to track ALL modifications - enabling audit trail,
6 data recovery, and change re-application. This macro is one of many
7 data management utilities used in [Data Controller for SAS](
8 https:datacontroller.io) - a comprehensive data ingestion solution, which
9 works on any SAS platform (Viya, SAS 9, Foundation) and is free for up to 5
10 users.
11
12 NOTE - this macro does not validate the inputs. It is assumed that the
13 datasets containing the new / changed / deleted rows are CORRECT, contain
14 no additional (or missing columns), and that the originals dataset contains
15 all relevant base records (and no additionals).
16
17 Usage:
18
19 data work.orig work.deleted work.changed work.appended;
20 set sashelp.class;
21 if _n_=1 then do;
22 output work.orig work.deleted;
23 end;
24 else if _n_=2 then do;
25 output work.orig;
26 age=99;
27 output work.changed;
28 end;
29 else do;
30 name='Newbie';
31 output work.appended;
32 stop;
33 end;
34 run;
35
36 %mp_storediffs(sashelp.class,work.orig,NAME
37 ,delds=work.deleted
38 ,modds=work.changed
39 ,appds=work.appended
40 ,outds=work.final
41 ,mdebug=1
42 )
43
44 @param [in] libds Target table against which the changes were applied
45 @param [in] origds Dataset with original (unchanged) records. Can be empty if
46 only appending.
47 @param [in] key Space seperated list of key variables
48 @param [in] delds= (0) Dataset with deleted records
49 @param [in] appds= (0) Dataset with appended records
50 @param [in] modds= (0) Dataset with modified records
51 @param [out] outds= (work.mp_storediffs) Output table containing stored data.
52 DDL as follows: %mp_coretable(DIFFTABLE)
53
54 @param [in] processed_dttm= (0) Provide a datetime constant in relation to
55 the actual load time. If not provided, current timestamp is used.
56 @param [in] mdebug= set to 1 to enable DEBUG messages and preserve outputs
57 @param [out] loadref= (0) Provide a unique key to reference the load,
58 otherwise a UUID will be generated.
59
60 <h4> SAS Macros </h4>
61 @li mf_getquotedstr.sas
62 @li mf_getuniquename.sas
63 @li mf_getvarlist.sas
64
65 <h4> Related Macros </h4>
66 @li mp_stackdiffs.sas
67 @li mp_storediffs.test.sas
68
69 @version 9.2
70 @author Allan Bowe
71**/
72/** @cond */
73
74%macro mp_storediffs(libds
75 ,origds
76 ,key
77 ,delds=0
78 ,appds=0
79 ,modds=0
80 ,outds=work.mp_storediffs
81 ,loadref=0
82 ,processed_dttm=0
83 ,mdebug=0
84)/*/STORE SOURCE*/;
85%local dbg;
86%if &mdebug=1 %then %do;
87 %put &sysmacroname entry vars:;
88 %put _local_;
89%end;
90%else %let dbg=*;
91
92/* set up unique and temporary vars */
93%local ds1 ds2 ds3 ds4 hashkey inds_auto inds_keep dslist vlist;
94%let ds1=%upcase(work.%mf_getuniquename(prefix=mpsd_ds1));
95%let ds2=%upcase(work.%mf_getuniquename(prefix=mpsd_ds2));
96%let ds3=%upcase(work.%mf_getuniquename(prefix=mpsd_ds3));
97%let ds4=%upcase(work.%mf_getuniquename(prefix=mpsd_ds4));
98%let hashkey=%upcase(%mf_getuniquename(prefix=mpsd_hashkey));
99%let inds_auto=%upcase(%mf_getuniquename(prefix=mpsd_inds_auto));
100%let inds_keep=%upcase(%mf_getuniquename(prefix=mpsd_inds_keep));
101
102%let dslist=&origds;
103%if &delds ne 0 %then %do;
104 %let delds=%upcase(&delds);
105 %if %scan(&delds,-1,.)=&delds %then %let delds=WORK.&delds;
106 %let dslist=&dslist &delds;
107%end;
108%if &appds ne 0 %then %do;
109 %let appds=%upcase(&appds);
110 %if %scan(&appds,-1,.)=&appds %then %let appds=WORK.&appds;
111 %let dslist=&dslist &appds;
112%end;
113%if &modds ne 0 %then %do;
114 %let modds=%upcase(&modds);
115 %if %scan(&modds,-1,.)=&modds %then %let modds=WORK.&modds;
116 %let dslist=&dslist &modds;
117%end;
118
119%let origds=%upcase(&origds);
120%if %scan(&origds,-1,.)=&origds %then %let origds=WORK.&origds;
121
122%let key=%upcase(&key);
123
124/* hash the key and append all the tables (marking the source) */
125data &ds1;
126 set &dslist indsname=&inds_auto;
127 &hashkey=put(md5(catx('|',%mf_getquotedstr(&key,quote=N))),$hex32.);
128 &inds_keep=upcase(&inds_auto);
129proc sort;
130 by &inds_keep &hashkey;
131run;
132
133/* transpose numeric & char vars */
134proc transpose data=&ds1
135 out=&ds2(rename=(&hashkey=key_hash _name_=tgtvar_nm col1=newval_num));
136 by &inds_keep &hashkey;
137 var _numeric_;
138run;
139proc transpose data=&ds1
140 out=&ds3(
141 rename=(&hashkey=key_hash _name_=tgtvar_nm col1=newval_char)
142 where=(tgtvar_nm not in ("&hashkey","&inds_keep"))
143 );
144 by &inds_keep &hashkey;
145 var _character_;
146run;
147
148%if %index(&libds,-)>0 and %scan(&libds,2,-)=FC %then %do;
149 /* this is a format catalog - cannot query cols directly */
150 %let vlist="FMTNAME","START","END","LABEL","MIN","MAX","DEFAULT","LENGTH"
151 ,"FUZZ","PREFIX","MULT","FILL","NOEDIT","TYPE","SEXCL","EEXCL","HLO"
152 ,"DECSEP","DIG3SEP","DATATYPE","LANGUAGE";
153%end;
154%else %let vlist=%mf_getvarlist(&libds,dlm=%str(,),quote=DOUBLE);
155
156data &ds4;
157 length &inds_keep $41 tgtvar_nm $32 _label_ $256;
158 if _n_=1 then call missing(_label_);
159 drop _label_;
160 set &ds2 &ds3 indsname=&inds_auto;
161
162 tgtvar_nm=upcase(tgtvar_nm);
163 if tgtvar_nm in (%upcase(&vlist));
164
165 if upcase(&inds_auto)="&ds2" then tgtvar_type='N';
166 else if upcase(&inds_auto)="&ds3" then tgtvar_type='C';
167 else do;
168 putlog "%str(ERR)OR: unidentified vartype input!" &inds_auto;
169 call symputx('syscc',98);
170 end;
171
172 if &inds_keep="&appds" then move_type='A';
173 else if &inds_keep="&delds" then move_type='D';
174 else if &inds_keep="&modds" then move_type='M';
175 else if &inds_keep="&origds" then move_type='O';
176 else do;
177 putlog "%str(ERR)OR: unidentified movetype input!" &inds_keep;
178 call symputx('syscc',99);
179 end;
180 tgtvar_nm=upcase(tgtvar_nm);
181 if tgtvar_nm in (%mf_getquotedstr(&key)) then is_pk=1;
182 else is_pk=0;
183 drop &inds_keep;
184run;
185
186%if "&loadref"="0" %then %let loadref=%sysfunc(uuidgen());
187%if &processed_dttm=0 %then %let processed_dttm=%sysfunc(datetime());
188%let libds=%upcase(&libds);
189
190/* join orig vals for modified & deleted */
191proc sql;
192create table &outds as
193 select "&loadref" as load_ref length=36
194 ,&processed_dttm as processed_dttm format=E8601DT26.6
195 ,"%scan(&libds,1,.)" as libref length=8
196 ,"%scan(&libds,2,.)" as dsn length=32
197 ,b.key_hash length=32
198 ,b.move_type length=1
199 ,b.tgtvar_nm length=32
200 ,b.is_pk
201 ,case when b.move_type ne 'M' then -1
202 when a.newval_num=b.newval_num and a.newval_char=b.newval_char then 0
203 else 1
204 end as is_diff
205 ,b.tgtvar_type length=1
206 ,case when b.move_type='D' then b.newval_num
207 else a.newval_num
208 end as oldval_num format=best32.
209 ,case when b.move_type='D' then .
210 else b.newval_num
211 end as newval_num format=best32.
212 ,case when b.move_type='D' then b.newval_char
213 else a.newval_char
214 end as oldval_char length=32765
215 ,case when b.move_type='D' then ''
216 else b.newval_char
217 end as newval_char length=32765
218 from &ds4(where=(move_type='O')) as a
219 right join &ds4(where=(move_type ne 'O')) as b
220 on a.tgtvar_nm=b.tgtvar_nm
221 and a.key_hash=b.key_hash
222 order by move_type, key_hash,is_pk desc, tgtvar_nm;
223
224%if &mdebug=0 %then %do;
225 proc sql;
226 drop table &ds1, &ds2, &ds3, &ds4;
227%end;
228
229%mend mp_storediffs;
230/** @endcond */