bitemporal_dataloader.test.2.sas

Go to the documentation of this file.

/**
  @file
  @brief Test Harness for bitemporal dataloader
  @details see below for usage:
 
      options mprint;
      options insert=(sasautos="/pub/programs/macrocore/base");
      options insert=(sasautos="/pub/programs/macrocore/meta");
      options insert=(sasautos="/pub/programs/datacontroller/macros");
 
      %mpeinit()
 
      %bitemporal_tester(test=1)
 
  TODO - add short numerics!!!
 
 
  <h4> SAS Macros </h4>
  @li bitemporal_dataloader.sas
  @li dc_assignlib.sas
  @li mf_getattrn.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.
**/
 
 
proc sql;
create table work.maxkeytable
(
  KEYTABLE char(41) format=$41.
    label='Key table in 2 part format (libref.dataset)',
  KEYCOLUMN char(32) format=$32.
    label='The Surrogate / Retained key field containing the key values.',
  MAX_KEY num format=8.
    label=
  'Integer value representing current max RK or SK value in the KEYTABLE',
  PROCESSED_DTTM num format=DATETIME21.
    label='Datetime this value was last updated'
);
proc sql;
create table work.mpe_lockanytable(
    lock_lib char(8),
    lock_ds char(32),
    lock_status_cd char(10) ,
    lock_user_nm char(100)  ,
    lock_ref char(200),
    lock_pid char(10),
    lock_start_dttm num format=E8601DT26.6,
    lock_end_dttm num format=E8601DT26.6
);
quit;
proc datasets lib=work noprint;
  modify mpe_lockanytable;
  index create
    pk_mpe_lockanytable=(lock_lib lock_ds)
    /nomiss unique;
quit;
 
 
%let syscc=0;
%let now=%sysfunc(datetime());
%let high_date='31DEC8888:23:59:59'dt;
 
/* create base table */
data basetable;
  tx_from_dttm=0;tx_to_dttm=&high_date; PK='PK1';
  eff_from_dttm=0; eff_to_dttm=&now-10000; field1='somevalue';
  field2='someothervalue'; output;
  eff_from_dttm=&now-10000; eff_to_dttm=&high_date; PK='PK1'; field1='newvalue';
  field2='somenewvalue'; output;
run;
/* create results table */
data results;
  format test $7. result $4. reason $50.;
  call missing(of _all_);
  stop;
run;
 
%put assigning lib..;
%dc_assignlib(WRITE,&dc_libref)
 
%macro bitemporal_tester(test=ALL);
%let test=%upcase(&test);
 
%if &test=ALL or &test=1 %then %do;
  /* test 1 - new record */
  data basetable1; set basetable;run;
  data appendtable;
    if 0 then set basetable;
    eff_from_dttm=&now; eff_to_dttm=&high_date; pk='PK1'; field1='blah';
    field2='blah';
    output;
  run;
  %bitemporal_dataloader(dclib=work
        ,processed=
        ,PK=pk
        ,ETLSOURCE=bitemporal_tester test1
        ,base_dsn=BASETABLE1
        ,bus_from=eff_from_dttm, bus_to=eff_to_dttm
        ,tech_from=tx_from_dttm, tech_to=tx_to_dttm
        ,LOG=NO);
  %if %mf_getattrn(basetable1,NLOBS)=4 %then %let test1=PASS;
  %else %let test1=FAIL;
  proc sql;
  insert into results set test='TEST001',result="&test1";
%end;
 
%if &test=ALL or &test=2 %then %do;
  /* test 2 - insert record */
  data basetable2; set basetable;run;
  data appendtable;
    if 0 then set basetable;
    eff_from_dttm=&now-500; eff_to_dttm=&now+500; pk='PK1'; field1='blah';
    field2='blah';
  run;
  %bitemporal_dataloader(dclib=work
      ,processed=
      ,PK=pk
      ,ETLSOURCE=bitemporal_tester test2
      ,base_dsn=BASETABLE2
      ,bus_from=eff_from_dttm, bus_to=eff_to_dttm
      ,tech_from=tx_from_dttm, tech_to=tx_to_dttm
      ,LOG=NO);
  %if %mf_getattrn(basetable2,NLOBS)=5 %then %let test2=PASS;
  %else %let test2=FAIL;
  proc sql;
  insert into results set test='TEST002',result="&test2";
%end;
 
%if &test=ALL or &test=3 %then %do;
  /* test 3 - wide table (350 columns) */
  data basetable3; set basetable;
  format var1-var350 $40.;
  %do x=1 %to 350;
    var&x=subpad("this will be a loooong string!!",1,40);
  %end;
  run;
  data appendtable;
    if 0 then set basetable3;
    eff_from_dttm=&now-500; eff_to_dttm=&now+500; pk='PK1'; field1='blah';
    field2='blah';
  run;
    %bitemporal_dataloader(dclib=work,processed=
          ,PK=pk
          ,ETLSOURCE=bitemporal_tester test3 wide
          ,base_dsn=BASETABLE3
          ,bus_from=eff_from_dttm, bus_to=eff_to_dttm
          ,tech_from=tx_from_dttm, tech_to=tx_to_dttm
          ,LOG=NO);
    %if %mf_getattrn(basetable3,NLOBS)=5 %then %let test3=PASS;
    %else %let test3=FAIL;
    proc sql;
    insert into results set test='TEST003',result="&test3";
%end;
 
/* test 4 - Txtemporal update */
%if &test=ALL or &test=4 %then %do;
  /* test 3 - wide table (50 columns) */
  proc sort data= basetable (drop=eff:) out=basetable4 nodupkey;
  by tx_from_dttm pk;
  run;
  data appendtable;
    if 0 then set basetable4;
      pk='PK1'; field1='blah'; field2='blah';
  run;
  %bitemporal_dataloader(dclib=work,processed=
        ,PK=pk
        ,ETLSOURCE=bitemporal_tester test3 wide
        ,base_dsn=BASETABLE4
        ,bus_from=eff_from_dttm, bus_to=eff_to_dttm
        ,tech_from=tx_from_dttm, tech_to=tx_to_dttm
        ,LOADTYPE=TXTEMPORAL
        ,LOG=NO);
  %if %mf_getattrn(basetable4,NLOBS)=2 %then %let test4=PASS;
  %else %let test4=FAIL;
  proc sql;
  insert into results set test='TEST004',result="&test4";
%end;
 
/* test 5 - Numeric variables */
%if &test=ALL or &test=5 %then %do;
  data basetable5; set basetable;
  val1=ranuni(0); val2=ranuni(0);
  run;
  data appendtable;
    if 0 then set basetable5;
    eff_from_dttm=&now; eff_to_dttm=&high_date;
      pk='PK1'; field1='blah'; field2='blah';val1=ranuni(0); val2=ranuni(0);
  run;
  %bitemporal_dataloader(dclib=work,processed=
        ,PK=pk
        ,ETLSOURCE=bitemporal_tester test&test wide
        ,base_dsn=BASETABLE5
        ,bus_from=eff_from_dttm, bus_to=eff_to_dttm
        ,tech_from=tx_from_dttm, tech_to=tx_to_dttm
        ,LOG=NO);
  %if %mf_getattrn(basetable5,NLOBS)=4 %then %let test5=PASS;
  %else %let test5=FAIL;
  proc sql;
  insert into results set test='TEST005',result="&test5";
%end;
 
/* test 6 - change business dates but nothing else */
%if &test=ALL or &test=6 %then %do;
  %let testnum=6;
  data basetable6 basetable6_show;
    eff_from_dttm=0; eff_to_dttm=&high_date;tx_from_dttm=0;
    tx_to_dttm=&high_date;
        PK='PK1';field1='test';field2='test';
  run;
  data appendtable;
    set basetable6;
    eff_from_dttm=&now; eff_to_dttm=&now+11111;
  run;
  run;
  %bitemporal_dataloader(dclib=work,processed=
        ,PK=pk
        ,ETLSOURCE=bitemporal_tester test&testnum wide
        ,base_dsn=BASETABLE&testnum
        ,bus_from=eff_from_dttm, bus_to=eff_to_dttm
        ,tech_from=tx_from_dttm, tech_to=tx_to_dttm
        ,LOG=NO);
  %if %mf_getattrn(basetable&testnum,NLOBS)=1 %then %let test&testnum=PASS;
  %else %let test&testnum=FAIL;
  proc sql;
  insert into results set test="TEST00&testnum",result="&&test&testnum";
%end;
 
/* test 7 - provide hard coded bus_from and to dates (override) */
%if &test=ALL or &test=7 %then %do;
  %let testnum=7;
  data basetable&testnum basetable&testnum._show;
    format comment $1000.;
    set basetable end=last;
    output;
    if last then do;
        PK='2'; field1='base';field2='base';
        comment='will be preceded (so should not be closed out)';output;
        PK='4'; field1='base'; field2='base';eff_from_dttm=4; eff_to_dttm=40;
            comment='will be overwritten (overlapped)   '; output;
        PK='5'; field1='base'; field2='base';eff_from_dttm=0;
        eff_to_dttm='31DEC9999:23:59:59'dt;
            comment='will not be replaced'; output;
    end;
  run;
  data appendtable;
    if 0 then set basetable&testnum;
    PK='PK1'; field1='Pk_test7'; field2='test7';
    comment='inserted record           ';output;
    PK='2'; field1='test7_changed'; field2='test7';
    comment='precedes record'; output;
    PK='3'; field1='new7'; field2='new7';comment='new field';output;
    PK='4'; field1='new'; field2='new';comment='replaces';output;
    PK='5'; field1='base'; field2='base'; comment='will not be replaced';output;
  run;
  %bitemporal_dataloader(dclib=work,processed=
        ,PK=pk
        ,bus_from_override=2
        ,bus_to_override=999999999
        ,ETLSOURCE=bitemporal_tester test&testnum override
        ,base_dsn=BASETABLE&testnum
        ,bus_from=eff_from_dttm, bus_to=eff_to_dttm
        ,tech_from=tx_from_dttm, tech_to=tx_to_dttm
        ,RK_MAXKEYTABLE=maxkeytable
        ,LOG=NO);
  %if %mf_getattrn(basetable&testnum,NLOBS)=11 %then %let test&testnum=PASS;
  %else %let test&testnum=FAIL;
  proc sql;
  insert into results set test="TEST00&testnum",result="&&test&testnum";
%end;
 
/* test 8 - RK generator */
%if &test=ALL or &test=8 %then %do;
  %let testnum=8;
  data basetable&testnum basetable&testnum._show;
    format comment $1000.;
    set basetable end=last;
    RK=4;
    output;
    if last then do;
        RK+1;PK='2'; field1='base';field2='base';
        comment='will be preceded (so should not be closed out)';output;
        RK+1;PK='4'; field1='base'; field2='base';eff_from_dttm=4;
        eff_to_dttm=40;
            comment='will be overwritten (overlapped)   '; output;
        RK+1;PK='5'; field1='base'; field2='base';eff_from_dttm=0;
        eff_to_dttm='31DEC9999:23:59:59'dt;
            comment='will not be replaced'; output;
    end;
  run;
  data appendtable;
    if 0 then set basetable&testnum ;
    PK='PK1'; field1='Pk_test7'; field2='test7';
    comment='inserted record           ';output;
    PK='2'; field1='test7_changed'; field2='test7';
    comment='precedes record'; output;
    PK='3'; field1='new7'; field2='new7';comment='new field';output;
    PK='4'; field1='new'; field2='new';comment='replaces';output;
    PK='5'; field1='base'; field2='base';
    comment='will not be replaced'; output;
    stop;
  run;
 
 
  %bitemporal_dataloader(dclib=work,processed=
        ,PK=rk
        ,ETLSOURCE=bitemporal_tester test&testnum override
        ,base_dsn=BASETABLE&testnum
        ,bus_from_override=2
        ,bus_to_override=999999999
        ,RK_UNDERLYING=PK
        ,RK_UPDATE_MAXKEYTABLE=YES
        ,CHECK_UNIQUENESS=YES
        ,bus_from=eff_from_dttm, bus_to=eff_to_dttm
        ,tech_from=tx_from_dttm, tech_to=tx_to_dttm
        ,RK_MAXKEYTABLE=maxkeytable
        ,LOG=NO);
  %let check=;
  proc sql noprint; select max(rk) into :check from basetable&testnum;
  %if %mf_getattrn(basetable&testnum,NLOBS)=11 and &check=8
  %then %let test&testnum=PASS;
  %else %let test&testnum=FAIL;
  proc sql;
  insert into results set test="TEST00&testnum",result="&&test&testnum";
%end;
 
/* test 9 - RK generator (more simple) */
%if &test=ALL or &test=9 %then %do;
  %let testnum=9;
  data basetable&testnum basetable&testnum._show;
  tx_from_dttm=0;tx_to_dttm=&high_date;
  processed_dttm=%sysfunc(datetime());
  format text $1000.;
    do x=1 to 10;
        PK1=cats('blah',x);
        PK2=cats('blah haha',x);
        RK+1;
        if x=2 then text='no change'; else text='';
        eff_from_dttm=0; eff_to_dttm=6666; value=cats('value',x); output;
        eff_from_dttm=6666; eff_to_dttm=&high_date; value=cats('devalue',x);
        output;
    end;
    drop x;
  run;
  data appendtable;
    if 0 then set basetable&testnum (drop=tx_: rk) ;
    PK1='blah1';PK2='blah haha1'; text='updated record (on end)';output;
    PK1='blah2';PK2='blah haha2'; value='devalue2'; text='no change';output;
    PK1='blah3';PK2='new'; value='devalue3'; text='new record';output;
    do x=1 to 10;
        PK1=cats('new',x);PK2='';text='More new records';
        output;
    end;
    drop x;
    stop;
  run;
 
  proc sql;
  create table work.max_key_values
  (
    KEYTABLE char(41) format=$41.
      label='Key table in 2 part format (libref.dataset)',
    KEYCOLUMN char(32) format=$32.
      label='The Surrogate / Retained key field containing the key values.',
    MAX_KEY num format=8.
      label=
    'Integer value representing current max RK or SK value in the KEYTABLE',
    PROCESSED_DTTM num format=DATETIME21.
      label='Datetime this value was last updated'
  );
  %bitemporal_dataloader(dclib=work,processed= PROCESSED_DTTM
        ,PK=rk
        ,ETLSOURCE=bitemporal_tester test&testnum override
        ,base_dsn=BASETABLE&testnum
        ,bus_from_override=77777
        ,bus_to_override=9999999999
        ,RK_UNDERLYING=PK1 PK2
        ,RK_UPDATE_MAXKEYTABLE=YES
        ,bus_from=eff_from_dttm, bus_to=eff_to_dttm
        ,tech_from=tx_from_dttm, tech_to=tx_to_dttm
        ,CHECK_UNIQUENESS=YES
        ,RK_MAXKEYTABLE=max_key_values
        ,LOG=NO);
 
  /* run it twice for sh*ts and giggles */
  %bitemporal_dataloader(dclib=work,processed= PROCESSED_DTTM
        ,PK=rk
        ,ETLSOURCE=bitemporal_tester test&testnum override
        ,base_dsn=BASETABLE&testnum
        ,bus_from_override=888888
        ,bus_to_override=999988888
        ,RK_UNDERLYING=PK1 PK2
        ,RK_UPDATE_MAXKEYTABLE=YES
        ,bus_from=eff_from_dttm, bus_to=eff_to_dttm
        ,tech_from=tx_from_dttm, tech_to=tx_to_dttm
        ,CHECK_UNIQUENESS=YES
        ,RK_MAXKEYTABLE=max_key_values
        ,LOG=NO);
 
  %let check=0;
  proc sql; select max(rk) into :check from basetable&testnum;
  %if %mf_getattrn(basetable&testnum,NLOBS)=34 and &check=21
  %then %let test&testnum=PASS;
  %else %let test&testnum=FAIL;
  proc sql;
  insert into results set test="TEST00&testnum",result="&&test&testnum";
%end;
 
/* test 10 - keeping only relevant variables */
%if &test=ALL or &test=10 %then %do;
  %let testnum=10;
  data basetable&testnum basetable&testnum._show;
    set basetable;
    PROCESSED_DTTM=datetime();
  run;
  data appendtable&testnum;
    set basetable&testnum ;
    field1='testing 567';
    newfield='to be dropped';
    output;
    stop;
  run;
 
  %bitemporal_dataloader(dclib=work,processed= PROCESSED_DTTM
        ,PK=pk
        ,append_dsn=appendtable&testnum
        ,ETLSOURCE=bitemporal_tester test&testnum override
        ,base_dsn=BASETABLE&testnum
        ,bus_from=eff_from_dttm, bus_to=eff_to_dttm
        ,tech_from=tx_from_dttm, tech_to=tx_to_dttm
        ,keepvars=PK field1 field2
        ,RK_MAXKEYTABLE=maxkeytable
        ,LOG=NO);
 
 
  data test&testnum;
    set basetable&testnum;
    if tx_from_dttm le %sysfunc(datetime()) lt tx_to_dttm;
    if eff_from_dttm le %sysfunc(datetime())+4 lt eff_to_dttm;
  run;
 
  %if %mf_getattrn(basetable&testnum,NLOBS)=3 %then %let test&testnum=PASS;
  %else %let test&testnum=FAIL;
  proc sql;
  insert into results set test="TEST0&testnum",result="&&test&testnum";
%end;
 
/* test 11 - append different records with same business dates and PK*/
%if &test=ALL or &test=11 %then %do;
  %let testnum=11;
  data basetable&testnum basetable&testnum._show;
    set basetable;
    PROCESSED_DTTM=%sysfunc(datetime());
  run;
  data appendtable&testnum;
    set basetable&testnum ;
    field1='testing123';
    output;
    stop;
  run;
 
  %bitemporal_dataloader(dclib=work,PK=pk
        ,append_dsn=appendtable&testnum
        ,ETLSOURCE=bitemporal_tester test&testnum override
        ,base_dsn=BASETABLE&testnum
        ,bus_from=eff_from_dttm, bus_to=eff_to_dttm
        ,tech_from=tx_from_dttm, tech_to=tx_to_dttm
        ,RK_MAXKEYTABLE=maxkeytable
 
        ,LOG=NO);
  /* does it return just one record?  */
  proc sql;
  create table test11 as select * from basetable11
  where tx_from_dttm le %sysfunc(datetime()) lt tx_to_dttm
    and eff_from_dttm le 100 lt eff_to_dttm;
  %if %mf_getattrn(test11,NLOBS)>1 %then %do;
    proc sql;
    insert into results
      set test="TEST0&testnum",result="FAIL",reason="Duplicates in base table";
  %end;
  %else %do;
    %let test&testnum=PASS;
    proc sql;
    insert into results set test="TEST0&testnum",result="&&test&testnum";
  %end;
%end;
 
/* test 12 - append table with fewer records than the base table */
%if &test=ALL or &test=12 %then %do;
  %let testnum=12;
  data basetable&testnum basetable&testnum._show;
    set basetable;
    extravar1='blah';
    extravar2=123;
    extravar3='01AUG1969'd;
  run;
  data appendtable&testnum(drop=extravar:);
    set basetable&testnum ;
    if tx_from_dttm le %sysfunc(datetime()) lt tx_to_dttm
      and eff_from_dttm le %sysfunc(datetime()) lt eff_to_dttm;
  run;
 
  %bitemporal_dataloader(dclib=work,PK=pk
        ,append_dsn=appendtable&testnum
        ,ETLSOURCE=bitemporal_tester test&testnum override
        ,base_dsn=BASETABLE&testnum
        ,RK_MAXKEYTABLE=maxkeytable
 
        ,bus_from=eff_from_dttm, bus_to=eff_to_dttm
        ,tech_from=tx_from_dttm, tech_to=tx_to_dttm
        ,LOG=NO);
 
  /* does it return just one record?  */
  proc sql;
  create table test11 as select * from basetable11
  where tx_from_dttm le %sysfunc(datetime()) lt tx_to_dttm
    and eff_from_dttm le 100 lt eff_to_dttm;
  %if %mf_getattrn(basetable&testnum,NLOBS) ne 3 %then %do;
    proc sql;
    insert into results
      set test="TEST0&testnum"
        ,result="FAIL",reason="Expected 3 records in basetable&testnum";
  %end;
  %else %do;
    %let test&testnum=PASS;
    proc sql;
    insert into results set test="TEST0&testnum",result="&&test&testnum";
  %end;
%end;
 
/* test 13 - perform an insert.    */
%if &test=ALL or &test=13 %then %do;
  %let testnum=13;
 
  /* two base table records */
  data basetable&testnum basetable&testnum._start;
    tx_from_dttm=0;tx_to_dttm=&high_date; PK='PK1';pk2=2;
    eff_from_dttm=0; eff_to_dttm=&now-10000; field1='somevalue  ';
      field2='someothervalue'; output;
    eff_from_dttm=&now-10000; eff_to_dttm=&high_date; PK='PK1';
    field1='newvalue'; field2='somenewvalue'; output;
  run;
 
  /* one INSERT record */
  data appendtable&testnum._a;
    set basetable&testnum ;
    eff_from_dttm=4; eff_to_dttm=5; field1='somevalue2'; output; stop;
  run;
 
  %bitemporal_dataloader(dclib=work,PK=pk pk2
        ,append_dsn=appendtable&testnum._a
        ,ETLSOURCE=bitemporal_tester test&testnum override
        ,base_dsn=BASETABLE&testnum
        ,RK_MAXKEYTABLE=maxkeytable
        ,bus_from=eff_from_dttm, bus_to=eff_to_dttm
        ,tech_from=tx_from_dttm, tech_to=tx_to_dttm
        ,LOG=NO
  )
 
 
  /* insert another record with same values */
  data appendtable&testnum._b;
    set appendtable&testnum._a ;
    field1='somevalue3'; output; stop;
  run;
 
  %bitemporal_dataloader(dclib=work,PK=pk pk2
        ,append_dsn=appendtable&testnum._b
        ,ETLSOURCE=bitemporal_tester test&testnum override
        ,base_dsn=BASETABLE&testnum
        ,RK_MAXKEYTABLE=maxkeytable
        ,bus_from=eff_from_dttm, bus_to=eff_to_dttm
        ,tech_from=tx_from_dttm, tech_to=tx_to_dttm
        ,LOG=NO
  )
 
  data _null_;
    set work.basetable13;
    putlog (_all_)(=);
  run;
 
  /* TEST RESULTS HERE */
  proc sql noprint;
  select count(distinct tx_from_dttm) into: test13_tx_count from basetable13;
  %put &=test13_tx_count;
  select distinct tx_From_dttm format=16.2
    into: test13_array separated by ' ' from basetable13;
  %do x=1 %to %sysfunc(countw(&test13_array,' '));
    select count(*)  into: test13_&x from BASETABLE&testnum
      where tx_from_dttm <= %scan(&test13_array,&x,%str( )) < tx_to_dttm;
    %put scan(&test13_array,&x)=%scan(&test13_array,&x,%str( ));
    %put test13_&x=&&test13_&x;
  %end;
 
  quit;
 
  %if &test13_tx_count=3 /* base plus two appends = three load stamps */
    and &test13_1 = 2  /* basetable13_start began with two records */
    and &test13_2 = 4 /* insert closed 1 record, added a new one,
                            changed the start of the third and left 4th open */
    and &test13_3 = 4 /* this was a pure overlay so only one record changed
                            from above */
    and %mf_getattrn(basetable&testnum,NLOBS) = 6
    %then %let result=PASS;
  %else %let result=FAIL;
  proc sql; insert into results set test="TEST&testnum",result="&result";
%end;
 
 
/* test 14 - perform an pre-sert.  Note that the data does not change,
  so should just extend the bus_From.  */
%if &test=ALL or &test=14 %then %do;
  %let testnum=14;
 
  data basetable&testnum basetable&testnum._start;
    tx_from_dttm=0;tx_to_dttm=&high_date; PK='PK1';pk2=2;
    eff_from_dttm=90; eff_to_dttm=&now-10000; field1='somevalue  ';
    field2='someothervalue'; output;
    eff_from_dttm=&now-10000; eff_to_dttm=&high_date; PK='PK1';
    field1='newvalue'; field2='somenewvalue'; output;
  run;
 
  data appendtable&testnum._a;
    set basetable&testnum ;
    eff_from_dttm=4; eff_to_dttm=90; output; stop;
  run;
  %bitemporal_dataloader(dclib=work,PK=pk pk2
        ,append_dsn=appendtable&testnum._a
        ,ETLSOURCE=bitemporal_tester test&testnum override
        ,base_dsn=BASETABLE&testnum
        ,RK_MAXKEYTABLE=maxkeytable
 
        ,bus_from=eff_from_dttm, bus_to=eff_to_dttm
        ,tech_from=tx_from_dttm, tech_to=tx_to_dttm
        ,LOG=NO);
 
 
  %if %mf_getattrn(basetable&testnum,NLOBS) = 3  %then %let result=PASS;
  %else %let result=FAIL;
  proc sql; insert into results set test="TEST&testnum",result="&result";
%end;
 
/* test 15 - perform a pre-sert with changed data   */
%if &test=ALL or &test=15 %then %do;
  %let testnum=15;
 
  data basetable&testnum basetable&testnum._start;
    tx_from_dttm=0;tx_to_dttm=&high_date; PK='PK1';pk2=2;
    eff_from_dttm=90; eff_to_dttm=&now-10000; field1='somevalue  ';
    field2='someothervalue'; output;
    eff_from_dttm=&now-10000; eff_to_dttm=&high_date; PK='PK1';
    field1='newvalue'; field2='somenewvalue'; output;
  run;
 
  data appendtable&testnum._a;
    set basetable&testnum ;
    eff_from_dttm=84; eff_to_dttm=90; field1='newvalue'; output; stop;
  run;
  %bitemporal_dataloader(dclib=work,PK=pk pk2
        ,append_dsn=appendtable&testnum._a
        ,ETLSOURCE=bitemporal_tester test&testnum override
        ,base_dsn=BASETABLE&testnum
        ,bus_from=eff_from_dttm, bus_to=eff_to_dttm
        ,tech_from=tx_from_dttm, tech_to=tx_to_dttm
        ,RK_MAXKEYTABLE=maxkeytable
 
        ,LOG=NO
    );
 
 
  %if %mf_getattrn(basetable&testnum,NLOBS) = 3  %then %let result=PASS;
  %else %let result=FAIL;
  proc sql; insert into results set test="TEST&testnum",result="&result";
%end;
 
/* test 16 - perform an update with very slightly changed data */
%if &test=ALL or &test=16 %then %do;
  %let testnum=16;
 
  data basetable&testnum basetable&testnum._start;
    tx_from_dttm=0;tx_to_dttm=&high_date; PK='PK1';pk2=2;
    field3=1.0000000012355555;
    eff_from_dttm=90; eff_to_dttm=&now-10000; field1='somevalue  ';
    field2='someothervalue'; output;
    eff_from_dttm=&now-10000; eff_to_dttm=&high_date; PK='PK1';
    field1='newvalue'; field2='somenewvalue'; output;
  run;
 
  data appendtable&testnum._a;
    set basetable&testnum end=last;
    field3=1.0000000012355559;
    eff_from_dttm=&now;
/*eff_to_dttm=90;  */
    if last then output;
  run;
  %bitemporal_dataloader(dclib=work,PK=pk pk2
        ,append_dsn=appendtable&testnum._a
        ,ETLSOURCE=bitemporal_tester test&testnum override
        ,base_dsn=BASETABLE&testnum
        ,RK_MAXKEYTABLE=maxkeytable
 
        ,bus_from=eff_from_dttm, bus_to=eff_to_dttm
        ,tech_from=tx_from_dttm, tech_to=tx_to_dttm
        ,LOG=NO
    );
 
 
  %if %mf_getattrn(basetable&testnum,NLOBS) = 4  %then %let result=PASS;
  %else %let result=FAIL;
  proc sql; insert into results set test="TEST&testnum",result="&result";
%end;
 
/* test 17 - perform an UPDATE style update */
%if &test=ALL or &test=17 %then %do;
  %let testnum=17;
 
  data basetable&testnum basetable&testnum._start;
    PK='PK1';pk2=2; field3=1.012355555; output;
    PK='PK1';pk2=3; field3=1.022355555; output;
    PK='PK1';pk2=4; field3=1.032355555; output;
  run;
 
  data appendtable&testnum.;
    PK='PK1';pk2=2; field3=3; output;
  run;
  %bitemporal_dataloader(dclib=work,PK=pk pk2
        ,append_dsn=appendtable&testnum.
        ,ETLSOURCE=bitemporal_tester test&testnum override
        ,base_dsn=BASETABLE&testnum
        ,RK_MAXKEYTABLE=maxkeytable
 
        ,LOG=NO
        ,LOADTYPE=UPDATE
        ,bus_from= ,bus_to =
    );
 
 
  %if %mf_getattrn(basetable&testnum,NLOBS) ne 3  %then %let result=FAIL;
  %else %do;
    proc sql noprint;
    select case when field3=3 then 'PASS' else 'FAIL' end into: result
      from basetable&testnum
      where PK='PK1' and pk2=2;
  %end;
  proc sql; insert into results set test="TEST&testnum",result="&result";
%end;
 
/* test 18 - perform CLOSEOUTS on a TXTemporal table */
%if &test=ALL or &test=18 %then %do;
  %let testnum=18;
 
  data basetable&testnum basetable&testnum._start;
  tx_from_dttm=0;tx_to_dttm=&high_date;
    PK='PK1';pk2=2; field3=1.012355555; output;
    PK='PK1';pk2=3; field3=1.022355555; output;
    PK='PK1';pk2=4; field3=1.032355555; output;
    PK='PK2';pk2=3; field3=1.022355555; output;
    PK='PK2';pk2=4; field3=1.032355555; output;
  run;
 
  data appendtable&testnum.;
    PK='PK1';pk2=2; field3=3; output;
    PK='PK1';pk2=4; field3=3; output;
  run;
 
  %bitemporal_closeouts(
      tech_from=tx_from_dttm
    ,tech_to = tx_to_dttm
    ,base_lib=WORK          /* Libref of the BASE table. */
    ,base_dsn=basetable&testnum
    ,append_lib=WORK        /* Libref of the STAGING table. */
    ,append_dsn=appendtable&testnum.
    ,PK= pk pk2
    ,NOW=%sysfunc(datetime()) /* allows consistent tracking of tech dates */
    ,FILTER=                /* supply a filter to limit the update */
  );
 
  %let result=;
  %if %mf_getattrn(basetable&testnum,NLOBS) ne
      %mf_getattrn(basetable&testnum._start,NLOBS) %then %let result=FAIL;
  %else %do;
    proc sql noprint;
    select count(*) into: tst18 from basetable&testnum
      where tx_from_dttm le %sysfunc(datetime()) lt tx_to_dttm;
    %if &tst18=3 & %mf_getattrn(basetable&testnum,NLOBS)=5
    %then %let result=PASS;
    %else %let result=FAIL;
  %end;
  proc sql; insert into results set test="TEST&testnum",result="&result";
%end;
 
/* test 19 - perform CLOSEOUTS via bitemporal loader (on txtemporal table) */
%if &test=ALL or &test=19 %then %do;
  %let testnum=19;
 
  data basetable&testnum basetable&testnum._start;
  format tx_from_dttm tx_to_dttm datetime21.2;
  tx_from_dttm=0;tx_to_dttm=&high_date;
    PK='PK1';pk2=2; field3=1.012355555; output;
    PK='PK1';pk2=3; field3=3; output;
    PK='PK1';pk2=4; field3=1.032355555; output;
 
    PK='PK2';pk2=3; field3=3; output;
    PK='PK2';pk2=4; field3=1.032355555; output;
    PK='PK2';pk2=5; field3=1.032355555; output;
 
  run;
 
  data appendtable&testnum.;
    _____DELETE__THIS__RECORD_____='YES';PK='PK1';pk2=2; field3=3; output;
    _____DELETE__THIS__RECORD_____='YES';PK='PK1';pk2=4; field3=3; output;
 
    _____DELETE__THIS__RECORD_____='No'; PK='PK2';pk2=3; field3=3; output;
    _____DELETE__THIS__RECORD_____='Y';  PK='PK2';pk2=4; field3=3; output;
    _____DELETE__THIS__RECORD_____='N';  PK='PK2';pk2=5; field3=3; output;
 
    _____DELETE__THIS__RECORD_____='YES';PK='PK3';pk2=6; field3=3; output;
    _____DELETE__THIS__RECORD_____='No'; PK='PK3';pk2=7; field3=3; output;
  run;
 
 
  %bitemporal_dataloader(dclib=work,PK=pk pk2
        ,append_dsn=appendtable&testnum.
        ,ETLSOURCE=bitemporal_tester test&testnum override
        ,base_dsn=BASETABLE&testnum
        ,LOG=NO
        ,LOADTYPE=txtemporal
        ,tech_from=tx_from_dttm
        ,tech_to = tx_to_dttm
    );
 
  %let result=;
  %let test1=;
  %let test2=;
  %let test3=;
  %if %mf_getattrn(basetable&testnum,NLOBS) =
      %mf_getattrn(basetable&testnum._start,NLOBS) %then %let result=FAIL;
  %else %do;
    proc sql noprint;
    select count(*) into: test1 from basetable&testnum
      where tx_from_dttm le %sysfunc(datetime()) lt tx_to_dttm
      and pk='PK1';
    select count(*) into: test2 from basetable&testnum
      where tx_from_dttm le %sysfunc(datetime()) lt tx_to_dttm
      and pk='PK2';
    select count(*) into: test3 from basetable&testnum
      where tx_from_dttm le %sysfunc(datetime()) lt tx_to_dttm
      and pk='PK3';
    %put tests=&test1 &test2 &test3;
 
    %if &test1=1 & &test2=3 & &test3=1 & %mf_getattrn(basetable&testnum,NLOBS)=9
      %then %let result=PASS;
    %else %let result=FAIL;
  %end;
  proc sql; insert into results set test="TEST&testnum",result="&result";
%end;
 
/* test 20 - perform CLOSEOUTS via bitemporal loader (on BItemporal table) */
%if &test=ALL or &test=20 %then %do;
  %let testnum=20;
 
  data basetable&testnum basetable&testnum._start;
  format tx_from_dttm tx_to_dttm datetime21.2;
  processed_dttm=3;
    tx_from_dttm=0;tx_to_dttm=&high_date; pk2=2; field3=3;
    eff_from_dttm=0; eff_to_dttm=&now-10000; PK='PK1';
      field1='somevalue  '; field2='someothervalue'; output;
    eff_from_dttm=&now-10000; eff_to_dttm=&high_date; PK='PK1';
      field1='newvalue'; field2='somenewvalue'; output;
    eff_from_dttm=0; eff_to_dttm=&now-10000; PK='PK2';
      field1='somevalue  '; field2='someothervalue'; output;
    eff_from_dttm=&now-10000; eff_to_dttm=&high_date; PK='PK3';
      field1='newvalue'; field2='somenewvalue'; output;
    eff_from_dttm=1; eff_to_dttm=&high_date; PK='PK4';
      field1='newvalue'; field2='somenewvalue'; output;
  run;
 
  data appendtable&testnum.;
    set basetable&testnum;
    if _n_=1 or _n_=5 then _____DELETE__THIS__RECORD_____='YES';
    else _____DELETE__THIS__RECORD_____='';
    if _n_=3 then eff_from_dttm=eff_from_dttm+1; /* not a change */
    if _n_=4 then field3=field3*3; /* a change */
  run;
 
 
  %bitemporal_dataloader(dclib=work,PK=pk pk2
        ,append_dsn=appendtable&testnum.
        ,ETLSOURCE=bitemporal_tester test&testnum override
        ,base_dsn=BASETABLE&testnum
        ,LOG=NO
        ,LOADTYPE=bitemporal
      ,tech_from=tx_from_dttm
      ,tech_to = tx_to_dttm
      ,bus_From=eff_from_dttm
      ,bus_to=eff_to_dttm
    );
 
  %let result=;
  %let test1=;
  %let test2=;
  %let test3=;
  %let test4=;
  %let test5=;
  %if %mf_getattrn(basetable&testnum,NLOBS) =
      %mf_getattrn(basetable&testnum._start,NLOBS) %then %let result=FAIL;
  %else %do;
    proc sql noprint;
    select count(*) into: test1 from basetable&testnum
      where tx_from_dttm le %sysfunc(datetime()) lt tx_to_dttm
      and pk='PK1';
    select count(*) into: test2 from basetable&testnum
      where tx_from_dttm le %sysfunc(datetime()) lt tx_to_dttm
      and pk='PK2';
    select count(*) into: test3 from basetable&testnum
      where pk='PK3';
    select count(*) into: test4 from basetable&testnum
      where processed_dttm>3;
    select count(*) into: test5 from basetable&testnum
      where tx_from_dttm le %sysfunc(datetime()) lt tx_to_dttm
      and pk='PK4';
    %put tests=&test1 &test2 &test3 &test4 &test5;
 
    %if &test1=1 & &test2=1 & &test3=2 & %mf_getattrn(basetable&testnum,NLOBS)=6
      & &test4=4 & &test5= 0  %then %let result=PASS;
    %else %let result=FAIL;
  %end;
  proc sql; insert into results set test="TEST&testnum",result="&result";
%end;
 
 
/* test 21 - perform CLOSEOUTS via bitemporal loader (on UPDATE style table) */
%if &test=ALL or &test=21 %then %do;
  %let testnum=21;
 
  data basetable&testnum basetable&testnum._start;
  processed_dttm=10;
    PK='PK1';pk2=1; field3=1.012355555; output;
    PK='PK2';pk2=2; field3=3; output;
    PK='PK3';pk2=3; field3=1.032355555; output;
    PK='PK4';pk2=4; field3=4; output;
    PK='PK5';pk2=5; field3=1.032355555; output;
    PK='PK6';pk2=6; field3=1.032355555; output;
    PK='PK7';pk2=7; field3=0; output;
  run;
 
  data appendtable&testnum.;
    _____DELETE__THIS__RECORD_____='YES';PK='PK1';pk2=1; output; /* gone */
    _____DELETE__THIS__RECORD_____='YES';PK='PK2';pk2=2; output; /* gone */
    _____DELETE__THIS__RECORD_____='N';PK='PK3';pk2=3; output; /* changed */
    _____DELETE__THIS__RECORD_____='N';PK='PK4';pk2=4; field3=3; output;
    _____DELETE__THIS__RECORD_____='N';PK='PK5';pk2=5; output; /* changed */
    _____DELETE__THIS__RECORD_____='YES';PK='PK6';pk2=6; output; /* gone */
    _____DELETE__THIS__RECORD_____='N';PK='PK7';pk2=7; field3=0; output;
  run;
 
 
 
  %bitemporal_dataloader(dclib=work,PK=pk pk2
        ,append_dsn=appendtable&testnum.
        ,ETLSOURCE=bitemporal_tester test&testnum override
        ,base_dsn=BASETABLE&testnum
        ,LOG=NO
        ,LOADTYPE=update
    );
 
  %let result=;
  %let test1=.;
  %let test2=.;
  %let test3=.;
  %let test4=.;
  %let test5=.;
  %if %mf_getattrn(basetable&testnum,NLOBS) =
      %mf_getattrn(basetable&testnum._start,NLOBS) %then %let result=FAIL;
  %else %do;
    proc sql noprint;
    select count(*) into: test1 from basetable&testnum
      where pk in ('PK1','PK2','PK6');
    select field3 into: test2 from basetable&testnum
      where  pk='PK3';
    select count(*) into: test3 from basetable&testnum
      where processed_dttm=10;
    select field3 into: test4 from basetable&testnum
      where  pk='PK4';
    select field3 into: test5 from basetable&testnum
      where  pk='PK5';
    %put tests=&test1 &test2 &test3 &test4 &test5;
 
    %if &test1=0 & &test2=. & &test3=1 & %mf_getattrn(basetable&testnum,NLOBS)=4
      & &test4=3 & &test5=3 %then %let result=PASS;
    %else %let result=FAIL;
  %end;
  proc sql; insert into results set test="TEST&testnum",result="&result";
%end;
 
/* test 22 - perform CLOSEOUTS via bitemporal loader (on RK style table) */
%if &test=ALL or &test=22 %then %do;
  %let testnum=22;
 
  data basetable&testnum basetable&testnum._show;
  tx_from_dttm=0;tx_to_dttm=&high_date;
  processed_dttm=5;
  format text $1000.;
    do x=1 to 10;
      PK1=cats('blah',x);
      PK2=cats('blah haha',x);
      RK+1;
      if x=2 then text='no change';
      else if mod(x,2)=0 then text='deleted record';
      else text='';
      value=cats('devalue',x); output;
    end;
    drop x;
  run;
  data appendtable&testnum;
    if 0 then set basetable&testnum (drop=tx_: rk) ;
    PK1='blah1';PK2='blah haha1';
      text='updated record (on end)';value='newval';output;
    PK1='blah2';PK2='blah haha2'; value='devalue2'; text='no change';output;
    PK1='blah3';PK2='new'; value='devalue3'; text='new record';output;
    do x=4 to 10;
        PK1=cats('blah',x);pk2=cats('blah haha',x);
        text='';value=cats('devalue',x);
        output;
    end;
    drop x;
    stop;
  run;
 
  data appendtable&testnum;
    set appendtable&testnum;
    /* delete even numbered observations */
    if mod(_n_,2)=0 and _n_ ne 2 then _____DELETE__THIS__RECORD_____='YES';
    else _____DELETE__THIS__RECORD_____=0;
  run;
 
  %bitemporal_dataloader(dclib=work
    ,PK=rk
    ,tech_from=tx_from_dttm, tech_to=tx_to_dttm
    ,ETLSOURCE=bitemporal_tester test&testnum override
    ,base_dsn=BASETABLE&testnum
    ,append_dsn=appendtable&testnum
    ,RK_UNDERLYING=PK1 PK2
    ,RK_UPDATE_MAXKEYTABLE=YES
    ,RK_MAXKEYTABLE=max_key_values
    ,LOG=NO
    ,loadtype=txtemporal
  )
 
 
  %let result=;
  %let test1=.;
  %let test2=.;
  %let test3=.;
  %let test4=.;
  %if %mf_getattrn(basetable&testnum,NLOBS) =
      %mf_getattrn(basetable&testnum._show,NLOBS) %then %let result=FAIL;
  %else %do;
    proc sql noprint;
    select count(*) into: test1 from basetable&testnum
      where pk1 in ('blah1');
    select value into: test2 from basetable&testnum
      where  pk1='blah1' and tx_from_dttm le %sysfunc(datetime()) lt tx_to_dttm;
    select count(*) into: test3 from basetable&testnum
      where processed_dttm=5;
    select count(*) into: test4 from basetable&testnum
      where  pk1='blah4' and tx_from_dttm le %sysfunc(datetime()) lt tx_to_dttm;
    %put tests=&test1 &test2 &test3 &test4 ;
 
    %if &test1=2 & &test2=newval & &test3=5
      & %mf_getattrn(basetable&testnum,NLOBS)=12
      & &test4=0 %then %let result=PASS;
    %else %let result=FAIL;
  %end;
  proc sql; insert into results set test="TEST&testnum",result="&result";
%end;
 
/* test 23 - testing LOADTARGET option on BITEMPORAL */
%if &test=ALL or &test=23 %then %do;
  %let testnum=23;
 
  data basetable&testnum basetable&testnum._show;
    set basetable;
  run;
 
  data appendtable&testnum;
    if 0 then set basetable;
    eff_from_dttm=&now; eff_to_dttm=&high_date;
    pk='PK1'; field1='blah'; field2='blah';
  run;
 
  %bitemporal_dataloader(dclib=work
      ,PK=pk
      ,ETLSOURCE=bitemporal_tester test&testnum override
      ,base_dsn=BASETABLE23
      ,append_dsn=appendtable&testnum
        ,bus_from=eff_from_dttm, bus_to=eff_to_dttm
        ,tech_from=tx_from_dttm, tech_to=tx_to_dttm
      ,LOG=NO
      ,LOADTARGET=NO);
 
  %if %mf_getattrn(basetable23,NLOBS)=2 %then %let test23=PASS;
  %else %let test23=FAIL;
  proc sql; insert into results set test="TEST&testnum",result="&test23";
 
%end;
 
/* test 24 - testing LOADTARGET option on TXTEMPORAL */
%if &test=ALL or &test=24 %then %do;
  %let testnum=24;
 
  proc sort data= basetable (drop=eff:) out=basetable&testnum nodupkey;
  by tx_from_dttm pk;
  run;
  data appendtable&testnum;
    if 0 then set basetable&testnum;
    pk='PK1'; field1='blah'; field2='blah';
  run;
 
  %bitemporal_dataloader(dclib=work,PK=pk
      ,ETLSOURCE=bitemporal_tester test&testnum override
      ,base_dsn=BASETABLE24
      ,append_dsn=appendtable&testnum
      ,LOADTYPE=TXTEMPORAL
        ,bus_from=eff_from_dttm, bus_to=eff_to_dttm
        ,tech_from=tx_from_dttm, tech_to=tx_to_dttm
      ,LOG=NO
      ,LOADTARGET=NO);
  %if %mf_getattrn(basetable24,NLOBS)=1 %then %let test24=PASS;
  %else %let test24=FAIL;
  proc sql; insert into results set test="TEST&testnum",result="&test24";
%end;
 
/* test 25 - testing LOADTARGET option on UPDATE */
%if &test=ALL or &test=25 %then %do;
  %let testnum=25;
 
  data basetable&testnum basetable&testnum._start;
    PROCESSED_DTTM=0;  /* if this changes, an update happened! */
    PK='PK1';pk2=2; field3=1.012355555; output;
    PK='PK1';pk2=3; field3=1.022355555; output;
    PK='PK1';pk2=4; field3=1.032355555; output;
  run;
 
  data appendtable&testnum.;
    PK='PK1';pk2=2; field3=3; output;
  run;
  %bitemporal_dataloader(dclib=work,PK=pk pk2
      ,append_dsn=appendtable&testnum.
      ,ETLSOURCE=bitemporal_tester test&testnum override
      ,base_dsn=BASETABLE&testnum
      ,RK_MAXKEYTABLE=maxkeytable
 
      ,LOG=NO
      ,LOADTARGET=NO
      ,LOADTYPE=UPDATE
      ,bus_from= ,bus_to =
  );
 
 
  %if %mf_getattrn(basetable&testnum,NLOBS) ne 3  %then %let result=FAIL;
  %else %do;
    proc sql noprint;
    select count(*) into: check&testnum from basetable&testnum
      where processed_dttm>0;
    %if &&check&testnum>0 %then %let result=FAIL;
    %else %let result=PASS;
  %end;
 
  proc sql; insert into results set test="TEST&testnum",result="&result";
%end;
 
/* test 26 - testing LOADTARGET option on CLOSEOUTS */
%if &test=ALL or &test=26 %then %do;
  %let testnum=26;
 
  data basetable&testnum basetable&testnum._start;
  tx_from_dttm=0;tx_to_dttm=&high_date;
    PK='PK1';pk2=2; field3=1.012355555; output;
    PK='PK1';pk2=3; field3=1.022355555; output;
    PK='PK1';pk2=4; field3=1.032355555; output;
    PK='PK2';pk2=3; field3=1.022355555; output;
    PK='PK2';pk2=4; field3=1.032355555; output;
  run;
 
  data appendtable&testnum.;
    PK='PK1';pk2=2; field3=3; output;
    PK='PK1';pk2=4; field3=3; output;
  run;
 
  %bitemporal_closeouts(
      tech_from=tx_from_dttm
    ,tech_to = tx_to_dttm
    ,base_lib=WORK          /* Libref of the BASE table. */
    ,base_dsn=basetable&testnum
    ,append_lib=WORK        /* Libref of the STAGING table. */
    ,append_dsn=appendtable&testnum.
    ,PK= pk pk2
    ,NOW=%sysfunc(datetime())
    ,LOADTARGET=NO
    ,FILTER=                /* supply a filter to limit the update */
  );
 
  %let result=;
  %if %mf_getattrn(basetable&testnum,NLOBS) ne
      %mf_getattrn(basetable&testnum._start,NLOBS) %then %let result=FAIL;
  %else %do;
    proc sql noprint;
    select count(*) into: tst26 from basetable&testnum
      where tx_from_dttm le %sysfunc(datetime()) lt tx_to_dttm;
    %if &tst26=5 & %mf_getattrn(basetable&testnum,NLOBS)=5
    %then %let result=PASS;
    %else %let result=FAIL;
  %end;
  proc sql; insert into results set test="TEST&testnum",result="&result";
%end;
 
/* test 27 - testing a load using a view (with records) */
%if &test=ALL or &test=27 %then %do;
  %let testnum=27;
 
  data basetable&testnum basetable&testnum._start;
  tx_from_dttm=0;tx_to_dttm=&high_date;
    PK='PK1';pk2=2; field3=1.012355555; output;
    PK='PK1';pk2=3; field3=1.022355555; output;
    PK='PK1';pk2=4; field3=1.032355555; output;
    PK='PK2';pk2=3; field3=1.022355555; output;
    PK='PK2';pk2=4; field3=1.032355555; output;
  run;
 
  data appendtable&testnum /view=appendtable&testnum;
    PK='PK1';pk2=2; field3=3; output;
    PK='PK1';pk2=4; field3=3; output;
  run;
 
  %bitemporal_closeouts(
      tech_from=tx_from_dttm
    ,tech_to = tx_to_dttm
    ,base_lib=WORK          /* Libref of the BASE table. */
    ,base_dsn=basetable&testnum
    ,append_lib=WORK        /* Libref of the STAGING table. */
    ,append_dsn=appendtable&testnum.
    ,PK= pk pk2
    ,NOW=%sysfunc(datetime())
    ,LOADTARGET=NO
    ,FILTER=                /* supply a filter to limit the update */
  );
 
  %let result=;
  proc sql noprint;
  select count(*) into:testnobs&testnum from basetable&testnum;
  select count(*) into:testnobs2&testnum from basetable&testnum._start;
  %if &&testnobs&testnum ne &&testnobs2&testnum %then %let result=FAIL;
  %else %do;
    proc sql noprint;
    select count(*) into: tst27 from basetable&testnum
      where tx_from_dttm le %sysfunc(datetime()) lt tx_to_dttm;
    %if &tst27=5 & %mf_getattrn(basetable&testnum,NLOBS)=5
    %then %let result=PASS;
    %else %let result=FAIL;
  %end;
  proc sql; insert into results set test="TEST&testnum",result="&result";
%end;
 
/* test 28 - testing a load using a view (without records) */
%if &test=ALL or &test=28 %then %do;
  %let testnum=28;
 
  data basetable&testnum basetable&testnum._start;
  tx_from_dttm=0;tx_to_dttm=&high_date;
    PK='PK1';pk2=2; field3=1.012355555; output;
    PK='PK1';pk2=3; field3=1.022355555; output;
    PK='PK1';pk2=4; field3=1.032355555; output;
    PK='PK2';pk2=3; field3=1.022355555; output;
    PK='PK2';pk2=4; field3=1.032355555; output;
  run;
 
  data appendtable&testnum /view=appendtable&testnum;
    set basetable&testnum;
    stop;
  run;
 
  %bitemporal_closeouts(
      tech_from=tx_from_dttm
    ,tech_to = tx_to_dttm
    ,base_lib=WORK          /* Libref of the BASE table. */
    ,base_dsn=basetable&testnum
    ,append_lib=WORK        /* Libref of the STAGING table. */
    ,append_dsn=appendtable&testnum.
    ,PK= pk pk2
    ,NOW=%sysfunc(datetime())
    ,LOADTARGET=NO
  );
 
  %let result=;
  proc sql noprint;
  select count(*) into:testnobs&testnum from basetable&testnum;
  select count(*) into:testnobs2&testnum from basetable&testnum._start;
  %if (&&testnobs&testnum = &&testnobs2&testnum) and &&testnobs&testnum=5
  %then %let result=PASS;
  %else %let result=FAIL;
  proc sql; insert into results set test="TEST&testnum",result="&result";
%end;
 
/* test 29 - testing a load with different formats to base table */
%if &test=ALL or &test=29 %then %do;
  %let testnum=29;
 
  data basetable&testnum basetable&testnum._start;
  format test_tm datetime19.3;
  tx_from_dttm=0;tx_to_dttm=&high_date; test_tm=datetime();
    PK='PK1';pk2=2; field3=1; output;
    PK='PK1';pk2=3; field3=1; output;
    PK='PK1';pk2=4; field3=1; output;
    PK='PK2';pk2=3; field3=1; output;
    PK='PK2';pk2=4; field3=1; output;
  run;
 
  data appendtable&testnum ;
  format field3 3. test_tm datetime17.2;
    set basetable&testnum;
    field3=3; test_tm=datetime();output;
    stop;
  run;
 
  %bitemporal_dataloader(dclib=work,PK=pk pk2
        ,append_dsn=appendtable&testnum.
        ,ETLSOURCE=bitemporal_tester test&testnum override
        ,base_dsn=BASETABLE&testnum
        ,RK_MAXKEYTABLE=maxkeytable
 
        ,LOG=NO
        ,LOADTARGET=YES
          ,tech_from=tx_from_dttm, tech_to=tx_to_dttm
        ,LOADTYPE=TXTEMPORAL
    );
 
 
  %let result=;
  proc sql noprint;
  select count(*) into:testnobs&testnum from basetable&testnum;
  select count(*) into:testnobs2&testnum from basetable&testnum._start;
  %if (&&testnobs&testnum =6) and &&testnobs2&testnum=5 %then %let result=PASS;
  %else %let result=FAIL;
  proc sql; insert into results set test="TEST&testnum",result="&result";
%end;
 
/* test 30 - testing a load to OLEDB (should use SQL server temp table) */
%if (&test=ALL or &test=30 ) and 0 %then %do; /* skip until have a db */
  %let testnum=30;
 
%assign_direct_lib(libref=XXX);
  data appendtable&testnum ;
    set XXX.temp_rk_table
    (where=(tx_from le %sysfunc(datetime()) lt tx_to));
    some_num+1;
    output;
    some_id=put(ranuni(0)*10000000,8.);output;
    stop;
  run;
  proc sql noprint;
  select count(*) into:testnobs&testnum from web.temp_rk_table;
 
  %bitemporal_dataloader(dclib=work,PK=some_rk
      ,append_dsn=appendtable&testnum.
      ,ETLSOURCE=bitemporal_tester test&testnum override
      ,tech_from=tx_from
      ,tech_to=tx_to
      ,base_lib=web
      ,base_dsn=temp_rk_table
      ,LOG=YES
      ,rk_underlying=some_id
      ,LOADTARGET=YES
      ,LOADTYPE=TXTEMPORAL
  );
 
 
  %let result=;
  proc sql noprint;
  select count(*) into:testnobs2&testnum from web.temp_rk_table;
  %if (&&testnobs2&testnum =%eval(&&testnobs&testnum+2)) %then %let result=PASS;
  %else %let result=FAIL;
  proc sql; insert into results set test="TEST&testnum",result="&result";
%end;
 
/* test 31 - bitemporal closeouts with embedded speechmarks in PK */
%let testnum=31;
%if &test=ALL or &test=&testnum %then %do;
  data basetable&testnum basetable&testnum._start;
    tx_from_dttm=0;tx_to_dttm=&high_date;
    PK='PK1';pk2=2; field3=1.012355555;field4='blah    ';  output;
    PK='PK1';pk2=3; field3=1.022355555;field4='blah2'; output;
    PK='PK1';pk2=4; field3=1.032355555;field4='blah3'; output;
    PK='PK2';pk2=3; field3=1.022355555;field4='bla"h'; output;
    PK='PK2';pk2=4; field3=1.032355555;field4="blah'h"; output;
  run;
 
  data appendtable&testnum.;
    PK='PK1';pk2=2;field4='blah   ';   output;
    PK='PK1';pk2=3;field4='blah2';  output;
    PK='PK1';pk2=4;field4='blah3';  output;
    PK='PK2';pk2=3;field4='bla"h';  output;
    PK='PK2';pk2=4;field4="blah'h"; output;
  run;
 
  %bitemporal_closeouts(
      tech_from=tx_from_dttm
    ,tech_to = tx_to_dttm
    ,base_lib=WORK          /* Libref of the BASE table. */
    ,base_dsn=basetable&testnum
    ,append_lib=WORK        /* Libref of the STAGING table. */
    ,append_dsn=appendtable&testnum.
    ,PK= pk pk2 field4
    ,NOW=%sysfunc(datetime()) /* allows consistent tracking of tech dates */
    ,FILTER=                /* supply a filter to limit the update */
  );
 
  %let result=;
  %if %mf_getattrn(basetable&testnum,NLOBS) ne
      %mf_getattrn(basetable&testnum._start,NLOBS) %then %let result=FAIL;
  %else %do;
    proc sql noprint;
    select count(*) into: tst31 from basetable&testnum
      where tx_from_dttm le %sysfunc(datetime()) lt tx_to_dttm;
    %if &tst31=0 & %mf_getattrn(basetable&testnum,NLOBS)=5
    %then %let result=PASS;
    %else %let result=FAIL;
  %end;
  proc sql; insert into results set test="TEST&testnum",result="&result";
%end;
/* test 32 - testing closevars process*/
%let testnum=32;
%if &test=ALL or &test=&testnum %then %do;
  data basetable&testnum basetable&testnum._start;
    tx_from_dttm=0;tx_to_dttm=&high_date; some_val=0;
    closeme='NO ';x=1;y=2;z=2;output;
    closeme='NO';x=1;y=2;z=3;output;
    closeme='NO';x=1;y=2;z=4;output;
    closeme='YES';x=1;y=3;z=1;output;
    closeme='YES';x=1;y=3;z=2;output;
    closeme='YES';x=1;y=3;z=3;output;
    closeme='NO';x=4;y=3;z=3;output;
    closeme='YES';x=5;y=3;z=1;output;
    closeme='YES';x=5;y=3;z=2;output;
    closeme='YES';x=5;y=3;z=3;output;
  run;
 
  data appendtable&testnum.;
    closeme='YES';x=1;y=3;z=1;some_val=0;output;
    closeme='YES';x=5;y=3;z=1;some_val=0;output;
    closeme='NO ';x=1;y=2;z=2;some_val=1;output;
  run;
 
  %bitemporal_dataloader(dclib=work,PK=CLOSEME X Y Z
      ,append_dsn=appendtable&testnum.
      ,ETLSOURCE=bitemporal_tester test&testnum override
      ,tech_from=tx_from_dttm
      ,tech_to=tx_to_dttm
      ,base_lib=work
      ,base_dsn=basetable&testnum
      ,LOG=YES
      ,LOADTARGET=YES
      ,LOADTYPE=TXTEMPORAL
      ,CLOSE_VARS=CLOSEME X Y
  );
 
  %let result=;
  %if %mf_getattrn(basetable&testnum,NLOBS) ne 11 %then %let result=FAIL;
  %else %do;
    proc sql noprint;
    select count(*) into: tst32 from basetable&testnum
      where %sysfunc(datetime()) lt tx_to_dttm
      and closeme='YES';
    %if &tst32=2 %then %let result=PASS;
    %else %let result=FAIL;
  %end;
  proc sql; insert into results set test="TEST&testnum",result="&result";
%end;
 
/* test 33- bitemporal load with high precision decimals (and nulls) */
%let testnum=33;
%if &test=ALL or &test=&testnum %then %do;
  data basetable&testnum basetable&testnum._start;
    tx_from_dttm=0;tx_to_dttm=&high_date;
    PK='PK1';pk2=2; field3=0.0026539721926;field4='blah    ';  output;
    PK='PK1';pk2=3; field3=._;field4='blah2'; output;
    PK='PK1';pk2=4; field3=.;field4='blah3'; output;
    PK='PK2';pk2=3; field3=1.0026239721926;field4='bla"h'; output;
    PK='PK2';pk2=4; field3=1.0026139721926;field4="blah'h"; output;
  run;
 
  data appendtable&testnum.;
    set basetable&testnum;
  run;
 
 
  %bitemporal_dataloader(dclib=work,PK= pk pk2 field4
      ,append_dsn=appendtable&testnum.
      ,ETLSOURCE=bitemporal_tester test&testnum precision
      ,tech_from=tx_from_dttm
      ,tech_to=tx_to_dttm
      ,base_lib=work
      ,base_dsn=basetable&testnum
      ,LOG=YES
      ,LOADTARGET=YES
      ,LOADTYPE=TXTEMPORAL
  )
 
  %let result=;
  %if %mf_getattrn(basetable&testnum,NLOBS) ne
      %mf_getattrn(basetable&testnum._start,NLOBS) %then %let result=FAIL;
  %else %do;
    proc sql noprint;
    select count(*) into: tst32 from basetable&testnum
      where tx_from_dttm le %sysfunc(datetime()) lt tx_to_dttm;
    %if &tst32=5 & %mf_getattrn(basetable&testnum,NLOBS)=5
    %then %let result=PASS;
    %else %let result=FAIL;
  %end;
  proc sql; insert into results set test="TEST&testnum",result="&result";
%end;
 
quit;
%mend bitemporal_tester;
 
options mprint;
%bitemporal_tester(test=ALL)
 
 
data work.test_results;
  set work.results;
  rename test=test_description result=test_result;
run;