bitemporal_dataloader.test.4.sas

Go to the documentation of this file.

/**
  @file
  @brief Test Harness for bitemporal dataloader - deletes only
  @details When an upload is 'deletes only' we need to ensure that the audit
  table is still updated accordingly
 
  <h4> SAS Macros </h4>
  @li bitemporal_dataloader.sas
  @li mddl_dc_difftable.sas
  @li mp_assert.sas
  @li mf_nobs.sas
 
  @version 9.2
  @author 4GL Apps Ltd
  @copyright 4GL Apps Ltd.  This code may only be used within Data Controller
    and may not be re-distributed or re-sold without the express permission of
    4GL Apps Ltd.
**/
 
 
%let syscc=0;
%let now=%sysfunc(datetime());
%let high_date='31DEC8888:23:59:59'dt;
 
/* create base table */
data work.basetable;
  PK='PK1';field1='somevalue';numvar=.; output;
  PK='PK2';field1='newvalue';numvar=0; output;
  PK='PK3';field1='somevalue';numvar=._; output;
  PK='PK4';field1='newvalue';numvar=.z; output;
  PK='PK5';field1='newvalue';numvar=.z; output;
run;
 
data work.stagetable;
  set work.basetable;
  _____DELETE__THIS__RECORD_____='Yes';
  if _n_>2 then stop;
run;
libname work2(work);
 
proc sql;
create table work.mpe_dataloads(
  libref varchar(8) ,
  dsn varchar(32) ,
  etlsource varchar(100) ,
  loadtype varchar(20) ,
  changed_records int,
  new_records int,
  deleted_records int,
  duration num,
  user_nm varchar(50) ,
  processed_dttm num format=datetime19.3,
  mac_ver varchar(5)
);quit;
proc datasets lib=work noprint;
  modify mpe_dataloads;
  index create
    pk_mpe_dataloads=(processed_dttm libref dsn etlsource)
    /nomiss unique;
quit;
 
%mddl_dc_difftable(libds=work.mpe_audit)
 
%bitemporal_dataloader(dclib=work2
  ,PK=pk
  ,ETLSOURCE=bitemporal_dataloader.test.4
  ,base_dsn=BASETABLE
  ,append_dsn=stagetable
  ,LOG=1
  ,outds_mod=work.changes
  ,outds_del=work.deleted
  ,loadtype=UPDATE
  ,outds_audit=work.mpe_audit
)
 
proc sql noprint;
select count(distinct key_hash) into: dels
  from work.mpe_audit
  where move_type='D';
 
%mp_assert(iftrue=(&dels=2),
  desc=2 deleted records present in audit table
)
 
%mp_assert(iftrue=(%mf_nobs(work.basetable)=3),
  desc=Ensuring 3 records are now in base table
)
 
%mp_assert(iftrue=(%mf_nobs(work.deleted)=2),
  desc=Confirming 2 deleted records on output table
)