bitemporal_dataloader.test.4.sas
Go to the documentation of this file.
1 /**
2  @file
3  @brief Test Harness for bitemporal dataloader - deletes only
4  @details When an upload is 'deletes only' we need to ensure that the audit
5  table is still updated accordingly
6 
7  <h4> SAS Macros </h4>
8  @li bitemporal_dataloader.sas
9  @li mddl_dc_difftable.sas
10  @li mp_assert.sas
11  @li mf_nobs.sas
12 
13  @version 9.2
14  @author 4GL Apps Ltd
15  @copyright 4GL Apps Ltd. This code may only be used within Data Controller
16  and may not be re-distributed or re-sold without the express permission of
17  4GL Apps Ltd.
18 **/
19 
20 
21 %let syscc=0;
22 %let now=%sysfunc(datetime());
23 %let high_date='31DEC8888:23:59:59'dt;
24 
25 /* create base table */
26 data work.basetable;
27  PK='PK1';field1='somevalue';numvar=.; output;
28  PK='PK2';field1='newvalue';numvar=0; output;
29  PK='PK3';field1='somevalue';numvar=._; output;
30  PK='PK4';field1='newvalue';numvar=.z; output;
31  PK='PK5';field1='newvalue';numvar=.z; output;
32 run;
33 
34 data work.stagetable;
35  set work.basetable;
36  _____DELETE__THIS__RECORD_____='Yes';
37  if _n_>2 then stop;
38 run;
39 libname work2(work);
40 
41 proc sql;
42 create table work.mpe_dataloads(
43  libref varchar(8) ,
44  dsn varchar(32) ,
45  etlsource varchar(100) ,
46  loadtype varchar(20) ,
47  changed_records int,
48  new_records int,
49  deleted_records int,
50  duration num,
51  user_nm varchar(50) ,
52  processed_dttm num format=datetime19.3,
53  mac_ver varchar(5)
54 );quit;
55 proc datasets lib=work noprint;
56  modify mpe_dataloads;
57  index create
58  pk_mpe_dataloads=(processed_dttm libref dsn etlsource)
59  /nomiss unique;
60 quit;
61 
62 %mddl_dc_difftable(libds=work.mpe_audit)
63 
64 %bitemporal_dataloader(dclib=work2
65  ,PK=pk
66  ,ETLSOURCE=bitemporal_dataloader.test.4
67  ,base_dsn=BASETABLE
68  ,append_dsn=stagetable
69  ,LOG=1
70  ,outds_mod=work.changes
71  ,outds_del=work.deleted
72  ,loadtype=UPDATE
73  ,outds_audit=work.mpe_audit
74 )
75 
76 proc sql noprint;
77 select count(distinct key_hash) into: dels
78  from work.mpe_audit
79  where move_type='D';
80 
81 %mp_assert(iftrue=(&dels=2),
82  desc=2 deleted records present in audit table
83 )
84 
85 %mp_assert(iftrue=(%mf_nobs(work.basetable)=3),
86  desc=Ensuring 3 records are now in base table
87 )
88 
89 %mp_assert(iftrue=(%mf_nobs(work.deleted)=2),
90  desc=Confirming 2 deleted records on output table
91 )