bitemporal_dataloader.test.2.sas
Go to the documentation of this file.
1 /**
2  @file
3  @brief Test Harness for bitemporal dataloader
4  @details see below for usage:
5 
6  options mprint;
7  options insert=(sasautos="/pub/programs/macrocore/base");
8  options insert=(sasautos="/pub/programs/macrocore/meta");
9  options insert=(sasautos="/pub/programs/datacontroller/macros");
10 
11  %mpeinit()
12 
13  %bitemporal_tester(test=1)
14 
15  TODO - add short numerics!!!
16 
17 
18  <h4> SAS Macros </h4>
19  @li bitemporal_dataloader.sas
20  @li dc_assignlib.sas
21  @li mf_getattrn.sas
22 
23  @version 9.2
24  @author 4GL Apps Ltd
25  @copyright 4GL Apps Ltd. This code may only be used within Data Controller
26  and may not be re-distributed or re-sold without the express permission of
27  4GL Apps Ltd.
28 **/
29 
30 
31 proc sql;
32 create table work.maxkeytable
33 (
34  KEYTABLE char(41) format=$41.
35  label='Key table in 2 part format (libref.dataset)',
36  KEYCOLUMN char(32) format=$32.
37  label='The Surrogate / Retained key field containing the key values.',
38  MAX_KEY num format=8.
39  label=
40  'Integer value representing current max RK or SK value in the KEYTABLE',
41  PROCESSED_DTTM num format=DATETIME21.
42  label='Datetime this value was last updated'
43 );
44 proc sql;
45 create table work.mpe_lockanytable(
46  lock_lib char(8),
47  lock_ds char(32),
48  lock_status_cd char(10) ,
49  lock_user_nm char(100) ,
50  lock_ref char(200),
51  lock_pid char(10),
52  lock_start_dttm num format=E8601DT26.6,
53  lock_end_dttm num format=E8601DT26.6
54 );
55 quit;
56 proc datasets lib=work noprint;
57  modify mpe_lockanytable;
58  index create
59  pk_mpe_lockanytable=(lock_lib lock_ds)
60  /nomiss unique;
61 quit;
62 
63 
64 %let syscc=0;
65 %let now=%sysfunc(datetime());
66 %let high_date='31DEC8888:23:59:59'dt;
67 
68 /* create base table */
69 data basetable;
70  tx_from_dttm=0;tx_to_dttm=&high_date; PK='PK1';
71  eff_from_dttm=0; eff_to_dttm=&now-10000; field1='somevalue';
72  field2='someothervalue'; output;
73  eff_from_dttm=&now-10000; eff_to_dttm=&high_date; PK='PK1'; field1='newvalue';
74  field2='somenewvalue'; output;
75 run;
76 /* create results table */
77 data results;
78  format test $7. result $4. reason $50.;
79  call missing(of _all_);
80  stop;
81 run;
82 
83 %put assigning lib..;
84 %dc_assignlib(WRITE,&dc_libref)
85 
86 %macro bitemporal_tester(test=ALL);
87 %let test=%upcase(&test);
88 
89 %if &test=ALL or &test=1 %then %do;
90  /* test 1 - new record */
91  data basetable1; set basetable;run;
92  data appendtable;
93  if 0 then set basetable;
94  eff_from_dttm=&now; eff_to_dttm=&high_date; pk='PK1'; field1='blah';
95  field2='blah';
96  output;
97  run;
98  %bitemporal_dataloader(dclib=work
99  ,processed=
100  ,PK=pk
101  ,ETLSOURCE=bitemporal_tester test1
102  ,base_dsn=BASETABLE1
103  ,bus_from=eff_from_dttm, bus_to=eff_to_dttm
104  ,tech_from=tx_from_dttm, tech_to=tx_to_dttm
105  ,LOG=NO);
106  %if %mf_getattrn(basetable1,NLOBS)=4 %then %let test1=PASS;
107  %else %let test1=FAIL;
108  proc sql;
109  insert into results set test='TEST001',result="&test1";
110 %end;
111 
112 %if &test=ALL or &test=2 %then %do;
113  /* test 2 - insert record */
114  data basetable2; set basetable;run;
115  data appendtable;
116  if 0 then set basetable;
117  eff_from_dttm=&now-500; eff_to_dttm=&now+500; pk='PK1'; field1='blah';
118  field2='blah';
119  run;
120  %bitemporal_dataloader(dclib=work
121  ,processed=
122  ,PK=pk
123  ,ETLSOURCE=bitemporal_tester test2
124  ,base_dsn=BASETABLE2
125  ,bus_from=eff_from_dttm, bus_to=eff_to_dttm
126  ,tech_from=tx_from_dttm, tech_to=tx_to_dttm
127  ,LOG=NO);
128  %if %mf_getattrn(basetable2,NLOBS)=5 %then %let test2=PASS;
129  %else %let test2=FAIL;
130  proc sql;
131  insert into results set test='TEST002',result="&test2";
132 %end;
133 
134 %if &test=ALL or &test=3 %then %do;
135  /* test 3 - wide table (350 columns) */
136  data basetable3; set basetable;
137  format var1-var350 $40.;
138  %do x=1 %to 350;
139  var&x=subpad("this will be a loooong string!!",1,40);
140  %end;
141  run;
142  data appendtable;
143  if 0 then set basetable3;
144  eff_from_dttm=&now-500; eff_to_dttm=&now+500; pk='PK1'; field1='blah';
145  field2='blah';
146  run;
147  %bitemporal_dataloader(dclib=work,processed=
148  ,PK=pk
149  ,ETLSOURCE=bitemporal_tester test3 wide
150  ,base_dsn=BASETABLE3
151  ,bus_from=eff_from_dttm, bus_to=eff_to_dttm
152  ,tech_from=tx_from_dttm, tech_to=tx_to_dttm
153  ,LOG=NO);
154  %if %mf_getattrn(basetable3,NLOBS)=5 %then %let test3=PASS;
155  %else %let test3=FAIL;
156  proc sql;
157  insert into results set test='TEST003',result="&test3";
158 %end;
159 
160 /* test 4 - Txtemporal update */
161 %if &test=ALL or &test=4 %then %do;
162  /* test 3 - wide table (50 columns) */
163  proc sort data= basetable (drop=eff:) out=basetable4 nodupkey;
164  by tx_from_dttm pk;
165  run;
166  data appendtable;
167  if 0 then set basetable4;
168  pk='PK1'; field1='blah'; field2='blah';
169  run;
170  %bitemporal_dataloader(dclib=work,processed=
171  ,PK=pk
172  ,ETLSOURCE=bitemporal_tester test3 wide
173  ,base_dsn=BASETABLE4
174  ,bus_from=eff_from_dttm, bus_to=eff_to_dttm
175  ,tech_from=tx_from_dttm, tech_to=tx_to_dttm
176  ,LOADTYPE=TXTEMPORAL
177  ,LOG=NO);
178  %if %mf_getattrn(basetable4,NLOBS)=2 %then %let test4=PASS;
179  %else %let test4=FAIL;
180  proc sql;
181  insert into results set test='TEST004',result="&test4";
182 %end;
183 
184 /* test 5 - Numeric variables */
185 %if &test=ALL or &test=5 %then %do;
186  data basetable5; set basetable;
187  val1=ranuni(0); val2=ranuni(0);
188  run;
189  data appendtable;
190  if 0 then set basetable5;
191  eff_from_dttm=&now; eff_to_dttm=&high_date;
192  pk='PK1'; field1='blah'; field2='blah';val1=ranuni(0); val2=ranuni(0);
193  run;
194  %bitemporal_dataloader(dclib=work,processed=
195  ,PK=pk
196  ,ETLSOURCE=bitemporal_tester test&test wide
197  ,base_dsn=BASETABLE5
198  ,bus_from=eff_from_dttm, bus_to=eff_to_dttm
199  ,tech_from=tx_from_dttm, tech_to=tx_to_dttm
200  ,LOG=NO);
201  %if %mf_getattrn(basetable5,NLOBS)=4 %then %let test5=PASS;
202  %else %let test5=FAIL;
203  proc sql;
204  insert into results set test='TEST005',result="&test5";
205 %end;
206 
207 /* test 6 - change business dates but nothing else */
208 %if &test=ALL or &test=6 %then %do;
209  %let testnum=6;
210  data basetable6 basetable6_show;
211  eff_from_dttm=0; eff_to_dttm=&high_date;tx_from_dttm=0;
212  tx_to_dttm=&high_date;
213  PK='PK1';field1='test';field2='test';
214  run;
215  data appendtable;
216  set basetable6;
217  eff_from_dttm=&now; eff_to_dttm=&now+11111;
218  run;
219  run;
220  %bitemporal_dataloader(dclib=work,processed=
221  ,PK=pk
222  ,ETLSOURCE=bitemporal_tester test&testnum wide
223  ,base_dsn=BASETABLE&testnum
224  ,bus_from=eff_from_dttm, bus_to=eff_to_dttm
225  ,tech_from=tx_from_dttm, tech_to=tx_to_dttm
226  ,LOG=NO);
227  %if %mf_getattrn(basetable&testnum,NLOBS)=1 %then %let test&testnum=PASS;
228  %else %let test&testnum=FAIL;
229  proc sql;
230  insert into results set test="TEST00&testnum",result="&&test&testnum";
231 %end;
232 
233 /* test 7 - provide hard coded bus_from and to dates (override) */
234 %if &test=ALL or &test=7 %then %do;
235  %let testnum=7;
236  data basetable&testnum basetable&testnum._show;
237  format comment $1000.;
238  set basetable end=last;
239  output;
240  if last then do;
241  PK='2'; field1='base';field2='base';
242  comment='will be preceded (so should not be closed out)';output;
243  PK='4'; field1='base'; field2='base';eff_from_dttm=4; eff_to_dttm=40;
244  comment='will be overwritten (overlapped) '; output;
245  PK='5'; field1='base'; field2='base';eff_from_dttm=0;
246  eff_to_dttm='31DEC9999:23:59:59'dt;
247  comment='will not be replaced'; output;
248  end;
249  run;
250  data appendtable;
251  if 0 then set basetable&testnum;
252  PK='PK1'; field1='Pk_test7'; field2='test7';
253  comment='inserted record ';output;
254  PK='2'; field1='test7_changed'; field2='test7';
255  comment='precedes record'; output;
256  PK='3'; field1='new7'; field2='new7';comment='new field';output;
257  PK='4'; field1='new'; field2='new';comment='replaces';output;
258  PK='5'; field1='base'; field2='base'; comment='will not be replaced';output;
259  run;
260  %bitemporal_dataloader(dclib=work,processed=
261  ,PK=pk
262  ,bus_from_override=2
263  ,bus_to_override=999999999
264  ,ETLSOURCE=bitemporal_tester test&testnum override
265  ,base_dsn=BASETABLE&testnum
266  ,bus_from=eff_from_dttm, bus_to=eff_to_dttm
267  ,tech_from=tx_from_dttm, tech_to=tx_to_dttm
268  ,RK_MAXKEYTABLE=maxkeytable
269  ,LOG=NO);
270  %if %mf_getattrn(basetable&testnum,NLOBS)=11 %then %let test&testnum=PASS;
271  %else %let test&testnum=FAIL;
272  proc sql;
273  insert into results set test="TEST00&testnum",result="&&test&testnum";
274 %end;
275 
276 /* test 8 - RK generator */
277 %if &test=ALL or &test=8 %then %do;
278  %let testnum=8;
279  data basetable&testnum basetable&testnum._show;
280  format comment $1000.;
281  set basetable end=last;
282  RK=4;
283  output;
284  if last then do;
285  RK+1;PK='2'; field1='base';field2='base';
286  comment='will be preceded (so should not be closed out)';output;
287  RK+1;PK='4'; field1='base'; field2='base';eff_from_dttm=4;
288  eff_to_dttm=40;
289  comment='will be overwritten (overlapped) '; output;
290  RK+1;PK='5'; field1='base'; field2='base';eff_from_dttm=0;
291  eff_to_dttm='31DEC9999:23:59:59'dt;
292  comment='will not be replaced'; output;
293  end;
294  run;
295  data appendtable;
296  if 0 then set basetable&testnum ;
297  PK='PK1'; field1='Pk_test7'; field2='test7';
298  comment='inserted record ';output;
299  PK='2'; field1='test7_changed'; field2='test7';
300  comment='precedes record'; output;
301  PK='3'; field1='new7'; field2='new7';comment='new field';output;
302  PK='4'; field1='new'; field2='new';comment='replaces';output;
303  PK='5'; field1='base'; field2='base';
304  comment='will not be replaced'; output;
305  stop;
306  run;
307 
308 
309  %bitemporal_dataloader(dclib=work,processed=
310  ,PK=rk
311  ,ETLSOURCE=bitemporal_tester test&testnum override
312  ,base_dsn=BASETABLE&testnum
313  ,bus_from_override=2
314  ,bus_to_override=999999999
315  ,RK_UNDERLYING=PK
316  ,RK_UPDATE_MAXKEYTABLE=YES
317  ,CHECK_UNIQUENESS=YES
318  ,bus_from=eff_from_dttm, bus_to=eff_to_dttm
319  ,tech_from=tx_from_dttm, tech_to=tx_to_dttm
320  ,RK_MAXKEYTABLE=maxkeytable
321  ,LOG=NO);
322  %let check=;
323  proc sql noprint; select max(rk) into :check from basetable&testnum;
324  %if %mf_getattrn(basetable&testnum,NLOBS)=11 and &check=8
325  %then %let test&testnum=PASS;
326  %else %let test&testnum=FAIL;
327  proc sql;
328  insert into results set test="TEST00&testnum",result="&&test&testnum";
329 %end;
330 
331 /* test 9 - RK generator (more simple) */
332 %if &test=ALL or &test=9 %then %do;
333  %let testnum=9;
334  data basetable&testnum basetable&testnum._show;
335  tx_from_dttm=0;tx_to_dttm=&high_date;
336  processed_dttm=%sysfunc(datetime());
337  format text $1000.;
338  do x=1 to 10;
339  PK1=cats('blah',x);
340  PK2=cats('blah haha',x);
341  RK+1;
342  if x=2 then text='no change'; else text='';
343  eff_from_dttm=0; eff_to_dttm=6666; value=cats('value',x); output;
344  eff_from_dttm=6666; eff_to_dttm=&high_date; value=cats('devalue',x);
345  output;
346  end;
347  drop x;
348  run;
349  data appendtable;
350  if 0 then set basetable&testnum (drop=tx_: rk) ;
351  PK1='blah1';PK2='blah haha1'; text='updated record (on end)';output;
352  PK1='blah2';PK2='blah haha2'; value='devalue2'; text='no change';output;
353  PK1='blah3';PK2='new'; value='devalue3'; text='new record';output;
354  do x=1 to 10;
355  PK1=cats('new',x);PK2='';text='More new records';
356  output;
357  end;
358  drop x;
359  stop;
360  run;
361 
362  proc sql;
363  create table work.max_key_values
364  (
365  KEYTABLE char(41) format=$41.
366  label='Key table in 2 part format (libref.dataset)',
367  KEYCOLUMN char(32) format=$32.
368  label='The Surrogate / Retained key field containing the key values.',
369  MAX_KEY num format=8.
370  label=
371  'Integer value representing current max RK or SK value in the KEYTABLE',
372  PROCESSED_DTTM num format=DATETIME21.
373  label='Datetime this value was last updated'
374  );
375  %bitemporal_dataloader(dclib=work,processed= PROCESSED_DTTM
376  ,PK=rk
377  ,ETLSOURCE=bitemporal_tester test&testnum override
378  ,base_dsn=BASETABLE&testnum
379  ,bus_from_override=77777
380  ,bus_to_override=9999999999
381  ,RK_UNDERLYING=PK1 PK2
382  ,RK_UPDATE_MAXKEYTABLE=YES
383  ,bus_from=eff_from_dttm, bus_to=eff_to_dttm
384  ,tech_from=tx_from_dttm, tech_to=tx_to_dttm
385  ,CHECK_UNIQUENESS=YES
386  ,RK_MAXKEYTABLE=max_key_values
387  ,LOG=NO);
388 
389  /* run it twice for sh*ts and giggles */
390  %bitemporal_dataloader(dclib=work,processed= PROCESSED_DTTM
391  ,PK=rk
392  ,ETLSOURCE=bitemporal_tester test&testnum override
393  ,base_dsn=BASETABLE&testnum
394  ,bus_from_override=888888
395  ,bus_to_override=999988888
396  ,RK_UNDERLYING=PK1 PK2
397  ,RK_UPDATE_MAXKEYTABLE=YES
398  ,bus_from=eff_from_dttm, bus_to=eff_to_dttm
399  ,tech_from=tx_from_dttm, tech_to=tx_to_dttm
400  ,CHECK_UNIQUENESS=YES
401  ,RK_MAXKEYTABLE=max_key_values
402  ,LOG=NO);
403 
404  %let check=0;
405  proc sql; select max(rk) into :check from basetable&testnum;
406  %if %mf_getattrn(basetable&testnum,NLOBS)=34 and &check=21
407  %then %let test&testnum=PASS;
408  %else %let test&testnum=FAIL;
409  proc sql;
410  insert into results set test="TEST00&testnum",result="&&test&testnum";
411 %end;
412 
413 /* test 10 - keeping only relevant variables */
414 %if &test=ALL or &test=10 %then %do;
415  %let testnum=10;
416  data basetable&testnum basetable&testnum._show;
417  set basetable;
418  PROCESSED_DTTM=datetime();
419  run;
420  data appendtable&testnum;
421  set basetable&testnum ;
422  field1='testing 567';
423  newfield='to be dropped';
424  output;
425  stop;
426  run;
427 
428  %bitemporal_dataloader(dclib=work,processed= PROCESSED_DTTM
429  ,PK=pk
430  ,append_dsn=appendtable&testnum
431  ,ETLSOURCE=bitemporal_tester test&testnum override
432  ,base_dsn=BASETABLE&testnum
433  ,bus_from=eff_from_dttm, bus_to=eff_to_dttm
434  ,tech_from=tx_from_dttm, tech_to=tx_to_dttm
435  ,keepvars=PK field1 field2
436  ,RK_MAXKEYTABLE=maxkeytable
437  ,LOG=NO);
438 
439 
440  data test&testnum;
441  set basetable&testnum;
442  if tx_from_dttm le %sysfunc(datetime()) lt tx_to_dttm;
443  if eff_from_dttm le %sysfunc(datetime())+4 lt eff_to_dttm;
444  run;
445 
446  %if %mf_getattrn(basetable&testnum,NLOBS)=3 %then %let test&testnum=PASS;
447  %else %let test&testnum=FAIL;
448  proc sql;
449  insert into results set test="TEST0&testnum",result="&&test&testnum";
450 %end;
451 
452 /* test 11 - append different records with same business dates and PK*/
453 %if &test=ALL or &test=11 %then %do;
454  %let testnum=11;
455  data basetable&testnum basetable&testnum._show;
456  set basetable;
457  PROCESSED_DTTM=%sysfunc(datetime());
458  run;
459  data appendtable&testnum;
460  set basetable&testnum ;
461  field1='testing123';
462  output;
463  stop;
464  run;
465 
466  %bitemporal_dataloader(dclib=work,PK=pk
467  ,append_dsn=appendtable&testnum
468  ,ETLSOURCE=bitemporal_tester test&testnum override
469  ,base_dsn=BASETABLE&testnum
470  ,bus_from=eff_from_dttm, bus_to=eff_to_dttm
471  ,tech_from=tx_from_dttm, tech_to=tx_to_dttm
472  ,RK_MAXKEYTABLE=maxkeytable
473 
474  ,LOG=NO);
475  /* does it return just one record? */
476  proc sql;
477  create table test11 as select * from basetable11
478  where tx_from_dttm le %sysfunc(datetime()) lt tx_to_dttm
479  and eff_from_dttm le 100 lt eff_to_dttm;
480  %if %mf_getattrn(test11,NLOBS)>1 %then %do;
481  proc sql;
482  insert into results
483  set test="TEST0&testnum",result="FAIL",reason="Duplicates in base table";
484  %end;
485  %else %do;
486  %let test&testnum=PASS;
487  proc sql;
488  insert into results set test="TEST0&testnum",result="&&test&testnum";
489  %end;
490 %end;
491 
492 /* test 12 - append table with fewer records than the base table */
493 %if &test=ALL or &test=12 %then %do;
494  %let testnum=12;
495  data basetable&testnum basetable&testnum._show;
496  set basetable;
497  extravar1='blah';
498  extravar2=123;
499  extravar3='01AUG1969'd;
500  run;
501  data appendtable&testnum(drop=extravar:);
502  set basetable&testnum ;
503  if tx_from_dttm le %sysfunc(datetime()) lt tx_to_dttm
504  and eff_from_dttm le %sysfunc(datetime()) lt eff_to_dttm;
505  run;
506 
507  %bitemporal_dataloader(dclib=work,PK=pk
508  ,append_dsn=appendtable&testnum
509  ,ETLSOURCE=bitemporal_tester test&testnum override
510  ,base_dsn=BASETABLE&testnum
511  ,RK_MAXKEYTABLE=maxkeytable
512 
513  ,bus_from=eff_from_dttm, bus_to=eff_to_dttm
514  ,tech_from=tx_from_dttm, tech_to=tx_to_dttm
515  ,LOG=NO);
516 
517  /* does it return just one record? */
518  proc sql;
519  create table test11 as select * from basetable11
520  where tx_from_dttm le %sysfunc(datetime()) lt tx_to_dttm
521  and eff_from_dttm le 100 lt eff_to_dttm;
522  %if %mf_getattrn(basetable&testnum,NLOBS) ne 3 %then %do;
523  proc sql;
524  insert into results
525  set test="TEST0&testnum"
526  ,result="FAIL",reason="Expected 3 records in basetable&testnum";
527  %end;
528  %else %do;
529  %let test&testnum=PASS;
530  proc sql;
531  insert into results set test="TEST0&testnum",result="&&test&testnum";
532  %end;
533 %end;
534 
535 /* test 13 - perform an insert. */
536 %if &test=ALL or &test=13 %then %do;
537  %let testnum=13;
538 
539  /* two base table records */
540  data basetable&testnum basetable&testnum._start;
541  tx_from_dttm=0;tx_to_dttm=&high_date; PK='PK1';pk2=2;
542  eff_from_dttm=0; eff_to_dttm=&now-10000; field1='somevalue ';
543  field2='someothervalue'; output;
544  eff_from_dttm=&now-10000; eff_to_dttm=&high_date; PK='PK1';
545  field1='newvalue'; field2='somenewvalue'; output;
546  run;
547 
548  /* one INSERT record */
549  data appendtable&testnum._a;
550  set basetable&testnum ;
551  eff_from_dttm=4; eff_to_dttm=5; field1='somevalue2'; output; stop;
552  run;
553 
554  %bitemporal_dataloader(dclib=work,PK=pk pk2
555  ,append_dsn=appendtable&testnum._a
556  ,ETLSOURCE=bitemporal_tester test&testnum override
557  ,base_dsn=BASETABLE&testnum
558  ,RK_MAXKEYTABLE=maxkeytable
559  ,bus_from=eff_from_dttm, bus_to=eff_to_dttm
560  ,tech_from=tx_from_dttm, tech_to=tx_to_dttm
561  ,LOG=NO
562  )
563 
564 
565  /* insert another record with same values */
566  data appendtable&testnum._b;
567  set appendtable&testnum._a ;
568  field1='somevalue3'; output; stop;
569  run;
570 
571  %bitemporal_dataloader(dclib=work,PK=pk pk2
572  ,append_dsn=appendtable&testnum._b
573  ,ETLSOURCE=bitemporal_tester test&testnum override
574  ,base_dsn=BASETABLE&testnum
575  ,RK_MAXKEYTABLE=maxkeytable
576  ,bus_from=eff_from_dttm, bus_to=eff_to_dttm
577  ,tech_from=tx_from_dttm, tech_to=tx_to_dttm
578  ,LOG=NO
579  )
580 
581  data _null_;
582  set work.basetable13;
583  putlog (_all_)(=);
584  run;
585 
586  /* TEST RESULTS HERE */
587  proc sql noprint;
588  select count(distinct tx_from_dttm) into: test13_tx_count from basetable13;
589  %put &=test13_tx_count;
590  select distinct tx_From_dttm format=16.2
591  into: test13_array separated by ' ' from basetable13;
592  %do x=1 %to %sysfunc(countw(&test13_array,' '));
593  select count(*) into: test13_&x from BASETABLE&testnum
594  where tx_from_dttm <= %scan(&test13_array,&x,%str( )) < tx_to_dttm;
595  %put scan(&test13_array,&x)=%scan(&test13_array,&x,%str( ));
596  %put test13_&x=&&test13_&x;
597  %end;
598 
599  quit;
600 
601  %if &test13_tx_count=3 /* base plus two appends = three load stamps */
602  and &test13_1 = 2 /* basetable13_start began with two records */
603  and &test13_2 = 4 /* insert closed 1 record, added a new one,
604  changed the start of the third and left 4th open */
605  and &test13_3 = 4 /* this was a pure overlay so only one record changed
606  from above */
607  and %mf_getattrn(basetable&testnum,NLOBS) = 6
608  %then %let result=PASS;
609  %else %let result=FAIL;
610  proc sql; insert into results set test="TEST&testnum",result="&result";
611 %end;
612 
613 
614 /* test 14 - perform an pre-sert. Note that the data does not change,
615  so should just extend the bus_From. */
616 %if &test=ALL or &test=14 %then %do;
617  %let testnum=14;
618 
619  data basetable&testnum basetable&testnum._start;
620  tx_from_dttm=0;tx_to_dttm=&high_date; PK='PK1';pk2=2;
621  eff_from_dttm=90; eff_to_dttm=&now-10000; field1='somevalue ';
622  field2='someothervalue'; output;
623  eff_from_dttm=&now-10000; eff_to_dttm=&high_date; PK='PK1';
624  field1='newvalue'; field2='somenewvalue'; output;
625  run;
626 
627  data appendtable&testnum._a;
628  set basetable&testnum ;
629  eff_from_dttm=4; eff_to_dttm=90; output; stop;
630  run;
631  %bitemporal_dataloader(dclib=work,PK=pk pk2
632  ,append_dsn=appendtable&testnum._a
633  ,ETLSOURCE=bitemporal_tester test&testnum override
634  ,base_dsn=BASETABLE&testnum
635  ,RK_MAXKEYTABLE=maxkeytable
636 
637  ,bus_from=eff_from_dttm, bus_to=eff_to_dttm
638  ,tech_from=tx_from_dttm, tech_to=tx_to_dttm
639  ,LOG=NO);
640 
641 
642  %if %mf_getattrn(basetable&testnum,NLOBS) = 3 %then %let result=PASS;
643  %else %let result=FAIL;
644  proc sql; insert into results set test="TEST&testnum",result="&result";
645 %end;
646 
647 /* test 15 - perform a pre-sert with changed data */
648 %if &test=ALL or &test=15 %then %do;
649  %let testnum=15;
650 
651  data basetable&testnum basetable&testnum._start;
652  tx_from_dttm=0;tx_to_dttm=&high_date; PK='PK1';pk2=2;
653  eff_from_dttm=90; eff_to_dttm=&now-10000; field1='somevalue ';
654  field2='someothervalue'; output;
655  eff_from_dttm=&now-10000; eff_to_dttm=&high_date; PK='PK1';
656  field1='newvalue'; field2='somenewvalue'; output;
657  run;
658 
659  data appendtable&testnum._a;
660  set basetable&testnum ;
661  eff_from_dttm=84; eff_to_dttm=90; field1='newvalue'; output; stop;
662  run;
663  %bitemporal_dataloader(dclib=work,PK=pk pk2
664  ,append_dsn=appendtable&testnum._a
665  ,ETLSOURCE=bitemporal_tester test&testnum override
666  ,base_dsn=BASETABLE&testnum
667  ,bus_from=eff_from_dttm, bus_to=eff_to_dttm
668  ,tech_from=tx_from_dttm, tech_to=tx_to_dttm
669  ,RK_MAXKEYTABLE=maxkeytable
670 
671  ,LOG=NO
672  );
673 
674 
675  %if %mf_getattrn(basetable&testnum,NLOBS) = 3 %then %let result=PASS;
676  %else %let result=FAIL;
677  proc sql; insert into results set test="TEST&testnum",result="&result";
678 %end;
679 
680 /* test 16 - perform an update with very slightly changed data */
681 %if &test=ALL or &test=16 %then %do;
682  %let testnum=16;
683 
684  data basetable&testnum basetable&testnum._start;
685  tx_from_dttm=0;tx_to_dttm=&high_date; PK='PK1';pk2=2;
686  field3=1.0000000012355555;
687  eff_from_dttm=90; eff_to_dttm=&now-10000; field1='somevalue ';
688  field2='someothervalue'; output;
689  eff_from_dttm=&now-10000; eff_to_dttm=&high_date; PK='PK1';
690  field1='newvalue'; field2='somenewvalue'; output;
691  run;
692 
693  data appendtable&testnum._a;
694  set basetable&testnum end=last;
695  field3=1.0000000012355559;
696  eff_from_dttm=&now;
697 /*eff_to_dttm=90; */
698  if last then output;
699  run;
700  %bitemporal_dataloader(dclib=work,PK=pk pk2
701  ,append_dsn=appendtable&testnum._a
702  ,ETLSOURCE=bitemporal_tester test&testnum override
703  ,base_dsn=BASETABLE&testnum
704  ,RK_MAXKEYTABLE=maxkeytable
705 
706  ,bus_from=eff_from_dttm, bus_to=eff_to_dttm
707  ,tech_from=tx_from_dttm, tech_to=tx_to_dttm
708  ,LOG=NO
709  );
710 
711 
712  %if %mf_getattrn(basetable&testnum,NLOBS) = 4 %then %let result=PASS;
713  %else %let result=FAIL;
714  proc sql; insert into results set test="TEST&testnum",result="&result";
715 %end;
716 
717 /* test 17 - perform an UPDATE style update */
718 %if &test=ALL or &test=17 %then %do;
719  %let testnum=17;
720 
721  data basetable&testnum basetable&testnum._start;
722  PK='PK1';pk2=2; field3=1.012355555; output;
723  PK='PK1';pk2=3; field3=1.022355555; output;
724  PK='PK1';pk2=4; field3=1.032355555; output;
725  run;
726 
727  data appendtable&testnum.;
728  PK='PK1';pk2=2; field3=3; output;
729  run;
730  %bitemporal_dataloader(dclib=work,PK=pk pk2
731  ,append_dsn=appendtable&testnum.
732  ,ETLSOURCE=bitemporal_tester test&testnum override
733  ,base_dsn=BASETABLE&testnum
734  ,RK_MAXKEYTABLE=maxkeytable
735 
736  ,LOG=NO
737  ,LOADTYPE=UPDATE
738  ,bus_from= ,bus_to =
739  );
740 
741 
742  %if %mf_getattrn(basetable&testnum,NLOBS) ne 3 %then %let result=FAIL;
743  %else %do;
744  proc sql noprint;
745  select case when field3=3 then 'PASS' else 'FAIL' end into: result
746  from basetable&testnum
747  where PK='PK1' and pk2=2;
748  %end;
749  proc sql; insert into results set test="TEST&testnum",result="&result";
750 %end;
751 
752 /* test 18 - perform CLOSEOUTS on a TXTemporal table */
753 %if &test=ALL or &test=18 %then %do;
754  %let testnum=18;
755 
756  data basetable&testnum basetable&testnum._start;
757  tx_from_dttm=0;tx_to_dttm=&high_date;
758  PK='PK1';pk2=2; field3=1.012355555; output;
759  PK='PK1';pk2=3; field3=1.022355555; output;
760  PK='PK1';pk2=4; field3=1.032355555; output;
761  PK='PK2';pk2=3; field3=1.022355555; output;
762  PK='PK2';pk2=4; field3=1.032355555; output;
763  run;
764 
765  data appendtable&testnum.;
766  PK='PK1';pk2=2; field3=3; output;
767  PK='PK1';pk2=4; field3=3; output;
768  run;
769 
770  %bitemporal_closeouts(
771  tech_from=tx_from_dttm
772  ,tech_to = tx_to_dttm
773  ,base_lib=WORK /* Libref of the BASE table. */
774  ,base_dsn=basetable&testnum
775  ,append_lib=WORK /* Libref of the STAGING table. */
776  ,append_dsn=appendtable&testnum.
777  ,PK= pk pk2
778  ,NOW=%sysfunc(datetime()) /* allows consistent tracking of tech dates */
779  ,FILTER= /* supply a filter to limit the update */
780  );
781 
782  %let result=;
783  %if %mf_getattrn(basetable&testnum,NLOBS) ne
784  %mf_getattrn(basetable&testnum._start,NLOBS) %then %let result=FAIL;
785  %else %do;
786  proc sql noprint;
787  select count(*) into: tst18 from basetable&testnum
788  where tx_from_dttm le %sysfunc(datetime()) lt tx_to_dttm;
789  %if &tst18=3 & %mf_getattrn(basetable&testnum,NLOBS)=5
790  %then %let result=PASS;
791  %else %let result=FAIL;
792  %end;
793  proc sql; insert into results set test="TEST&testnum",result="&result";
794 %end;
795 
796 /* test 19 - perform CLOSEOUTS via bitemporal loader (on txtemporal table) */
797 %if &test=ALL or &test=19 %then %do;
798  %let testnum=19;
799 
800  data basetable&testnum basetable&testnum._start;
801  format tx_from_dttm tx_to_dttm datetime21.2;
802  tx_from_dttm=0;tx_to_dttm=&high_date;
803  PK='PK1';pk2=2; field3=1.012355555; output;
804  PK='PK1';pk2=3; field3=3; output;
805  PK='PK1';pk2=4; field3=1.032355555; output;
806 
807  PK='PK2';pk2=3; field3=3; output;
808  PK='PK2';pk2=4; field3=1.032355555; output;
809  PK='PK2';pk2=5; field3=1.032355555; output;
810 
811  run;
812 
813  data appendtable&testnum.;
814  _____DELETE__THIS__RECORD_____='YES';PK='PK1';pk2=2; field3=3; output;
815  _____DELETE__THIS__RECORD_____='YES';PK='PK1';pk2=4; field3=3; output;
816 
817  _____DELETE__THIS__RECORD_____='No'; PK='PK2';pk2=3; field3=3; output;
818  _____DELETE__THIS__RECORD_____='Y'; PK='PK2';pk2=4; field3=3; output;
819  _____DELETE__THIS__RECORD_____='N'; PK='PK2';pk2=5; field3=3; output;
820 
821  _____DELETE__THIS__RECORD_____='YES';PK='PK3';pk2=6; field3=3; output;
822  _____DELETE__THIS__RECORD_____='No'; PK='PK3';pk2=7; field3=3; output;
823  run;
824 
825 
826  %bitemporal_dataloader(dclib=work,PK=pk pk2
827  ,append_dsn=appendtable&testnum.
828  ,ETLSOURCE=bitemporal_tester test&testnum override
829  ,base_dsn=BASETABLE&testnum
830  ,LOG=NO
831  ,LOADTYPE=txtemporal
832  ,tech_from=tx_from_dttm
833  ,tech_to = tx_to_dttm
834  );
835 
836  %let result=;
837  %let test1=;
838  %let test2=;
839  %let test3=;
840  %if %mf_getattrn(basetable&testnum,NLOBS) =
841  %mf_getattrn(basetable&testnum._start,NLOBS) %then %let result=FAIL;
842  %else %do;
843  proc sql noprint;
844  select count(*) into: test1 from basetable&testnum
845  where tx_from_dttm le %sysfunc(datetime()) lt tx_to_dttm
846  and pk='PK1';
847  select count(*) into: test2 from basetable&testnum
848  where tx_from_dttm le %sysfunc(datetime()) lt tx_to_dttm
849  and pk='PK2';
850  select count(*) into: test3 from basetable&testnum
851  where tx_from_dttm le %sysfunc(datetime()) lt tx_to_dttm
852  and pk='PK3';
853  %put tests=&test1 &test2 &test3;
854 
855  %if &test1=1 & &test2=3 & &test3=1 & %mf_getattrn(basetable&testnum,NLOBS)=9
856  %then %let result=PASS;
857  %else %let result=FAIL;
858  %end;
859  proc sql; insert into results set test="TEST&testnum",result="&result";
860 %end;
861 
862 /* test 20 - perform CLOSEOUTS via bitemporal loader (on BItemporal table) */
863 %if &test=ALL or &test=20 %then %do;
864  %let testnum=20;
865 
866  data basetable&testnum basetable&testnum._start;
867  format tx_from_dttm tx_to_dttm datetime21.2;
868  processed_dttm=3;
869  tx_from_dttm=0;tx_to_dttm=&high_date; pk2=2; field3=3;
870  eff_from_dttm=0; eff_to_dttm=&now-10000; PK='PK1';
871  field1='somevalue '; field2='someothervalue'; output;
872  eff_from_dttm=&now-10000; eff_to_dttm=&high_date; PK='PK1';
873  field1='newvalue'; field2='somenewvalue'; output;
874  eff_from_dttm=0; eff_to_dttm=&now-10000; PK='PK2';
875  field1='somevalue '; field2='someothervalue'; output;
876  eff_from_dttm=&now-10000; eff_to_dttm=&high_date; PK='PK3';
877  field1='newvalue'; field2='somenewvalue'; output;
878  eff_from_dttm=1; eff_to_dttm=&high_date; PK='PK4';
879  field1='newvalue'; field2='somenewvalue'; output;
880  run;
881 
882  data appendtable&testnum.;
883  set basetable&testnum;
884  if _n_=1 or _n_=5 then _____DELETE__THIS__RECORD_____='YES';
885  else _____DELETE__THIS__RECORD_____='';
886  if _n_=3 then eff_from_dttm=eff_from_dttm+1; /* not a change */
887  if _n_=4 then field3=field3*3; /* a change */
888  run;
889 
890 
891  %bitemporal_dataloader(dclib=work,PK=pk pk2
892  ,append_dsn=appendtable&testnum.
893  ,ETLSOURCE=bitemporal_tester test&testnum override
894  ,base_dsn=BASETABLE&testnum
895  ,LOG=NO
896  ,LOADTYPE=bitemporal
897  ,tech_from=tx_from_dttm
898  ,tech_to = tx_to_dttm
899  ,bus_From=eff_from_dttm
900  ,bus_to=eff_to_dttm
901  );
902 
903  %let result=;
904  %let test1=;
905  %let test2=;
906  %let test3=;
907  %let test4=;
908  %let test5=;
909  %if %mf_getattrn(basetable&testnum,NLOBS) =
910  %mf_getattrn(basetable&testnum._start,NLOBS) %then %let result=FAIL;
911  %else %do;
912  proc sql noprint;
913  select count(*) into: test1 from basetable&testnum
914  where tx_from_dttm le %sysfunc(datetime()) lt tx_to_dttm
915  and pk='PK1';
916  select count(*) into: test2 from basetable&testnum
917  where tx_from_dttm le %sysfunc(datetime()) lt tx_to_dttm
918  and pk='PK2';
919  select count(*) into: test3 from basetable&testnum
920  where pk='PK3';
921  select count(*) into: test4 from basetable&testnum
922  where processed_dttm>3;
923  select count(*) into: test5 from basetable&testnum
924  where tx_from_dttm le %sysfunc(datetime()) lt tx_to_dttm
925  and pk='PK4';
926  %put tests=&test1 &test2 &test3 &test4 &test5;
927 
928  %if &test1=1 & &test2=1 & &test3=2 & %mf_getattrn(basetable&testnum,NLOBS)=6
929  & &test4=4 & &test5= 0 %then %let result=PASS;
930  %else %let result=FAIL;
931  %end;
932  proc sql; insert into results set test="TEST&testnum",result="&result";
933 %end;
934 
935 
936 /* test 21 - perform CLOSEOUTS via bitemporal loader (on UPDATE style table) */
937 %if &test=ALL or &test=21 %then %do;
938  %let testnum=21;
939 
940  data basetable&testnum basetable&testnum._start;
941  processed_dttm=10;
942  PK='PK1';pk2=1; field3=1.012355555; output;
943  PK='PK2';pk2=2; field3=3; output;
944  PK='PK3';pk2=3; field3=1.032355555; output;
945  PK='PK4';pk2=4; field3=4; output;
946  PK='PK5';pk2=5; field3=1.032355555; output;
947  PK='PK6';pk2=6; field3=1.032355555; output;
948  PK='PK7';pk2=7; field3=0; output;
949  run;
950 
951  data appendtable&testnum.;
952  _____DELETE__THIS__RECORD_____='YES';PK='PK1';pk2=1; output; /* gone */
953  _____DELETE__THIS__RECORD_____='YES';PK='PK2';pk2=2; output; /* gone */
954  _____DELETE__THIS__RECORD_____='N';PK='PK3';pk2=3; output; /* changed */
955  _____DELETE__THIS__RECORD_____='N';PK='PK4';pk2=4; field3=3; output;
956  _____DELETE__THIS__RECORD_____='N';PK='PK5';pk2=5; output; /* changed */
957  _____DELETE__THIS__RECORD_____='YES';PK='PK6';pk2=6; output; /* gone */
958  _____DELETE__THIS__RECORD_____='N';PK='PK7';pk2=7; field3=0; output;
959  run;
960 
961 
962 
963  %bitemporal_dataloader(dclib=work,PK=pk pk2
964  ,append_dsn=appendtable&testnum.
965  ,ETLSOURCE=bitemporal_tester test&testnum override
966  ,base_dsn=BASETABLE&testnum
967  ,LOG=NO
968  ,LOADTYPE=update
969  );
970 
971  %let result=;
972  %let test1=.;
973  %let test2=.;
974  %let test3=.;
975  %let test4=.;
976  %let test5=.;
977  %if %mf_getattrn(basetable&testnum,NLOBS) =
978  %mf_getattrn(basetable&testnum._start,NLOBS) %then %let result=FAIL;
979  %else %do;
980  proc sql noprint;
981  select count(*) into: test1 from basetable&testnum
982  where pk in ('PK1','PK2','PK6');
983  select field3 into: test2 from basetable&testnum
984  where pk='PK3';
985  select count(*) into: test3 from basetable&testnum
986  where processed_dttm=10;
987  select field3 into: test4 from basetable&testnum
988  where pk='PK4';
989  select field3 into: test5 from basetable&testnum
990  where pk='PK5';
991  %put tests=&test1 &test2 &test3 &test4 &test5;
992 
993  %if &test1=0 & &test2=. & &test3=1 & %mf_getattrn(basetable&testnum,NLOBS)=4
994  & &test4=3 & &test5=3 %then %let result=PASS;
995  %else %let result=FAIL;
996  %end;
997  proc sql; insert into results set test="TEST&testnum",result="&result";
998 %end;
999 
1000 /* test 22 - perform CLOSEOUTS via bitemporal loader (on RK style table) */
1001 %if &test=ALL or &test=22 %then %do;
1002  %let testnum=22;
1003 
1004  data basetable&testnum basetable&testnum._show;
1005  tx_from_dttm=0;tx_to_dttm=&high_date;
1006  processed_dttm=5;
1007  format text $1000.;
1008  do x=1 to 10;
1009  PK1=cats('blah',x);
1010  PK2=cats('blah haha',x);
1011  RK+1;
1012  if x=2 then text='no change';
1013  else if mod(x,2)=0 then text='deleted record';
1014  else text='';
1015  value=cats('devalue',x); output;
1016  end;
1017  drop x;
1018  run;
1019  data appendtable&testnum;
1020  if 0 then set basetable&testnum (drop=tx_: rk) ;
1021  PK1='blah1';PK2='blah haha1';
1022  text='updated record (on end)';value='newval';output;
1023  PK1='blah2';PK2='blah haha2'; value='devalue2'; text='no change';output;
1024  PK1='blah3';PK2='new'; value='devalue3'; text='new record';output;
1025  do x=4 to 10;
1026  PK1=cats('blah',x);pk2=cats('blah haha',x);
1027  text='';value=cats('devalue',x);
1028  output;
1029  end;
1030  drop x;
1031  stop;
1032  run;
1033 
1034  data appendtable&testnum;
1035  set appendtable&testnum;
1036  /* delete even numbered observations */
1037  if mod(_n_,2)=0 and _n_ ne 2 then _____DELETE__THIS__RECORD_____='YES';
1038  else _____DELETE__THIS__RECORD_____=0;
1039  run;
1040 
1041  %bitemporal_dataloader(dclib=work
1042  ,PK=rk
1043  ,tech_from=tx_from_dttm, tech_to=tx_to_dttm
1044  ,ETLSOURCE=bitemporal_tester test&testnum override
1045  ,base_dsn=BASETABLE&testnum
1046  ,append_dsn=appendtable&testnum
1047  ,RK_UNDERLYING=PK1 PK2
1048  ,RK_UPDATE_MAXKEYTABLE=YES
1049  ,RK_MAXKEYTABLE=max_key_values
1050  ,LOG=NO
1051  ,loadtype=txtemporal
1052  )
1053 
1054 
1055  %let result=;
1056  %let test1=.;
1057  %let test2=.;
1058  %let test3=.;
1059  %let test4=.;
1060  %if %mf_getattrn(basetable&testnum,NLOBS) =
1061  %mf_getattrn(basetable&testnum._show,NLOBS) %then %let result=FAIL;
1062  %else %do;
1063  proc sql noprint;
1064  select count(*) into: test1 from basetable&testnum
1065  where pk1 in ('blah1');
1066  select value into: test2 from basetable&testnum
1067  where pk1='blah1' and tx_from_dttm le %sysfunc(datetime()) lt tx_to_dttm;
1068  select count(*) into: test3 from basetable&testnum
1069  where processed_dttm=5;
1070  select count(*) into: test4 from basetable&testnum
1071  where pk1='blah4' and tx_from_dttm le %sysfunc(datetime()) lt tx_to_dttm;
1072  %put tests=&test1 &test2 &test3 &test4 ;
1073 
1074  %if &test1=2 & &test2=newval & &test3=5
1075  & %mf_getattrn(basetable&testnum,NLOBS)=12
1076  & &test4=0 %then %let result=PASS;
1077  %else %let result=FAIL;
1078  %end;
1079  proc sql; insert into results set test="TEST&testnum",result="&result";
1080 %end;
1081 
1082 /* test 23 - testing LOADTARGET option on BITEMPORAL */
1083 %if &test=ALL or &test=23 %then %do;
1084  %let testnum=23;
1085 
1086  data basetable&testnum basetable&testnum._show;
1087  set basetable;
1088  run;
1089 
1090  data appendtable&testnum;
1091  if 0 then set basetable;
1092  eff_from_dttm=&now; eff_to_dttm=&high_date;
1093  pk='PK1'; field1='blah'; field2='blah';
1094  run;
1095 
1096  %bitemporal_dataloader(dclib=work
1097  ,PK=pk
1098  ,ETLSOURCE=bitemporal_tester test&testnum override
1099  ,base_dsn=BASETABLE23
1100  ,append_dsn=appendtable&testnum
1101  ,bus_from=eff_from_dttm, bus_to=eff_to_dttm
1102  ,tech_from=tx_from_dttm, tech_to=tx_to_dttm
1103  ,LOG=NO
1104  ,LOADTARGET=NO);
1105 
1106  %if %mf_getattrn(basetable23,NLOBS)=2 %then %let test23=PASS;
1107  %else %let test23=FAIL;
1108  proc sql; insert into results set test="TEST&testnum",result="&test23";
1109 
1110 %end;
1111 
1112 /* test 24 - testing LOADTARGET option on TXTEMPORAL */
1113 %if &test=ALL or &test=24 %then %do;
1114  %let testnum=24;
1115 
1116  proc sort data= basetable (drop=eff:) out=basetable&testnum nodupkey;
1117  by tx_from_dttm pk;
1118  run;
1119  data appendtable&testnum;
1120  if 0 then set basetable&testnum;
1121  pk='PK1'; field1='blah'; field2='blah';
1122  run;
1123 
1124  %bitemporal_dataloader(dclib=work,PK=pk
1125  ,ETLSOURCE=bitemporal_tester test&testnum override
1126  ,base_dsn=BASETABLE24
1127  ,append_dsn=appendtable&testnum
1128  ,LOADTYPE=TXTEMPORAL
1129  ,bus_from=eff_from_dttm, bus_to=eff_to_dttm
1130  ,tech_from=tx_from_dttm, tech_to=tx_to_dttm
1131  ,LOG=NO
1132  ,LOADTARGET=NO);
1133  %if %mf_getattrn(basetable24,NLOBS)=1 %then %let test24=PASS;
1134  %else %let test24=FAIL;
1135  proc sql; insert into results set test="TEST&testnum",result="&test24";
1136 %end;
1137 
1138 /* test 25 - testing LOADTARGET option on UPDATE */
1139 %if &test=ALL or &test=25 %then %do;
1140  %let testnum=25;
1141 
1142  data basetable&testnum basetable&testnum._start;
1143  PROCESSED_DTTM=0; /* if this changes, an update happened! */
1144  PK='PK1';pk2=2; field3=1.012355555; output;
1145  PK='PK1';pk2=3; field3=1.022355555; output;
1146  PK='PK1';pk2=4; field3=1.032355555; output;
1147  run;
1148 
1149  data appendtable&testnum.;
1150  PK='PK1';pk2=2; field3=3; output;
1151  run;
1152  %bitemporal_dataloader(dclib=work,PK=pk pk2
1153  ,append_dsn=appendtable&testnum.
1154  ,ETLSOURCE=bitemporal_tester test&testnum override
1155  ,base_dsn=BASETABLE&testnum
1156  ,RK_MAXKEYTABLE=maxkeytable
1157 
1158  ,LOG=NO
1159  ,LOADTARGET=NO
1160  ,LOADTYPE=UPDATE
1161  ,bus_from= ,bus_to =
1162  );
1163 
1164 
1165  %if %mf_getattrn(basetable&testnum,NLOBS) ne 3 %then %let result=FAIL;
1166  %else %do;
1167  proc sql noprint;
1168  select count(*) into: check&testnum from basetable&testnum
1169  where processed_dttm>0;
1170  %if &&check&testnum>0 %then %let result=FAIL;
1171  %else %let result=PASS;
1172  %end;
1173 
1174  proc sql; insert into results set test="TEST&testnum",result="&result";
1175 %end;
1176 
1177 /* test 26 - testing LOADTARGET option on CLOSEOUTS */
1178 %if &test=ALL or &test=26 %then %do;
1179  %let testnum=26;
1180 
1181  data basetable&testnum basetable&testnum._start;
1182  tx_from_dttm=0;tx_to_dttm=&high_date;
1183  PK='PK1';pk2=2; field3=1.012355555; output;
1184  PK='PK1';pk2=3; field3=1.022355555; output;
1185  PK='PK1';pk2=4; field3=1.032355555; output;
1186  PK='PK2';pk2=3; field3=1.022355555; output;
1187  PK='PK2';pk2=4; field3=1.032355555; output;
1188  run;
1189 
1190  data appendtable&testnum.;
1191  PK='PK1';pk2=2; field3=3; output;
1192  PK='PK1';pk2=4; field3=3; output;
1193  run;
1194 
1195  %bitemporal_closeouts(
1196  tech_from=tx_from_dttm
1197  ,tech_to = tx_to_dttm
1198  ,base_lib=WORK /* Libref of the BASE table. */
1199  ,base_dsn=basetable&testnum
1200  ,append_lib=WORK /* Libref of the STAGING table. */
1201  ,append_dsn=appendtable&testnum.
1202  ,PK= pk pk2
1203  ,NOW=%sysfunc(datetime())
1204  ,LOADTARGET=NO
1205  ,FILTER= /* supply a filter to limit the update */
1206  );
1207 
1208  %let result=;
1209  %if %mf_getattrn(basetable&testnum,NLOBS) ne
1210  %mf_getattrn(basetable&testnum._start,NLOBS) %then %let result=FAIL;
1211  %else %do;
1212  proc sql noprint;
1213  select count(*) into: tst26 from basetable&testnum
1214  where tx_from_dttm le %sysfunc(datetime()) lt tx_to_dttm;
1215  %if &tst26=5 & %mf_getattrn(basetable&testnum,NLOBS)=5
1216  %then %let result=PASS;
1217  %else %let result=FAIL;
1218  %end;
1219  proc sql; insert into results set test="TEST&testnum",result="&result";
1220 %end;
1221 
1222 /* test 27 - testing a load using a view (with records) */
1223 %if &test=ALL or &test=27 %then %do;
1224  %let testnum=27;
1225 
1226  data basetable&testnum basetable&testnum._start;
1227  tx_from_dttm=0;tx_to_dttm=&high_date;
1228  PK='PK1';pk2=2; field3=1.012355555; output;
1229  PK='PK1';pk2=3; field3=1.022355555; output;
1230  PK='PK1';pk2=4; field3=1.032355555; output;
1231  PK='PK2';pk2=3; field3=1.022355555; output;
1232  PK='PK2';pk2=4; field3=1.032355555; output;
1233  run;
1234 
1235  data appendtable&testnum /view=appendtable&testnum;
1236  PK='PK1';pk2=2; field3=3; output;
1237  PK='PK1';pk2=4; field3=3; output;
1238  run;
1239 
1240  %bitemporal_closeouts(
1241  tech_from=tx_from_dttm
1242  ,tech_to = tx_to_dttm
1243  ,base_lib=WORK /* Libref of the BASE table. */
1244  ,base_dsn=basetable&testnum
1245  ,append_lib=WORK /* Libref of the STAGING table. */
1246  ,append_dsn=appendtable&testnum.
1247  ,PK= pk pk2
1248  ,NOW=%sysfunc(datetime())
1249  ,LOADTARGET=NO
1250  ,FILTER= /* supply a filter to limit the update */
1251  );
1252 
1253  %let result=;
1254  proc sql noprint;
1255  select count(*) into:testnobs&testnum from basetable&testnum;
1256  select count(*) into:testnobs2&testnum from basetable&testnum._start;
1257  %if &&testnobs&testnum ne &&testnobs2&testnum %then %let result=FAIL;
1258  %else %do;
1259  proc sql noprint;
1260  select count(*) into: tst27 from basetable&testnum
1261  where tx_from_dttm le %sysfunc(datetime()) lt tx_to_dttm;
1262  %if &tst27=5 & %mf_getattrn(basetable&testnum,NLOBS)=5
1263  %then %let result=PASS;
1264  %else %let result=FAIL;
1265  %end;
1266  proc sql; insert into results set test="TEST&testnum",result="&result";
1267 %end;
1268 
1269 /* test 28 - testing a load using a view (without records) */
1270 %if &test=ALL or &test=28 %then %do;
1271  %let testnum=28;
1272 
1273  data basetable&testnum basetable&testnum._start;
1274  tx_from_dttm=0;tx_to_dttm=&high_date;
1275  PK='PK1';pk2=2; field3=1.012355555; output;
1276  PK='PK1';pk2=3; field3=1.022355555; output;
1277  PK='PK1';pk2=4; field3=1.032355555; output;
1278  PK='PK2';pk2=3; field3=1.022355555; output;
1279  PK='PK2';pk2=4; field3=1.032355555; output;
1280  run;
1281 
1282  data appendtable&testnum /view=appendtable&testnum;
1283  set basetable&testnum;
1284  stop;
1285  run;
1286 
1287  %bitemporal_closeouts(
1288  tech_from=tx_from_dttm
1289  ,tech_to = tx_to_dttm
1290  ,base_lib=WORK /* Libref of the BASE table. */
1291  ,base_dsn=basetable&testnum
1292  ,append_lib=WORK /* Libref of the STAGING table. */
1293  ,append_dsn=appendtable&testnum.
1294  ,PK= pk pk2
1295  ,NOW=%sysfunc(datetime())
1296  ,LOADTARGET=NO
1297  );
1298 
1299  %let result=;
1300  proc sql noprint;
1301  select count(*) into:testnobs&testnum from basetable&testnum;
1302  select count(*) into:testnobs2&testnum from basetable&testnum._start;
1303  %if (&&testnobs&testnum = &&testnobs2&testnum) and &&testnobs&testnum=5
1304  %then %let result=PASS;
1305  %else %let result=FAIL;
1306  proc sql; insert into results set test="TEST&testnum",result="&result";
1307 %end;
1308 
1309 /* test 29 - testing a load with different formats to base table */
1310 %if &test=ALL or &test=29 %then %do;
1311  %let testnum=29;
1312 
1313  data basetable&testnum basetable&testnum._start;
1314  format test_tm datetime19.3;
1315  tx_from_dttm=0;tx_to_dttm=&high_date; test_tm=datetime();
1316  PK='PK1';pk2=2; field3=1; output;
1317  PK='PK1';pk2=3; field3=1; output;
1318  PK='PK1';pk2=4; field3=1; output;
1319  PK='PK2';pk2=3; field3=1; output;
1320  PK='PK2';pk2=4; field3=1; output;
1321  run;
1322 
1323  data appendtable&testnum ;
1324  format field3 3. test_tm datetime17.2;
1325  set basetable&testnum;
1326  field3=3; test_tm=datetime();output;
1327  stop;
1328  run;
1329 
1330  %bitemporal_dataloader(dclib=work,PK=pk pk2
1331  ,append_dsn=appendtable&testnum.
1332  ,ETLSOURCE=bitemporal_tester test&testnum override
1333  ,base_dsn=BASETABLE&testnum
1334  ,RK_MAXKEYTABLE=maxkeytable
1335 
1336  ,LOG=NO
1337  ,LOADTARGET=YES
1338  ,tech_from=tx_from_dttm, tech_to=tx_to_dttm
1339  ,LOADTYPE=TXTEMPORAL
1340  );
1341 
1342 
1343  %let result=;
1344  proc sql noprint;
1345  select count(*) into:testnobs&testnum from basetable&testnum;
1346  select count(*) into:testnobs2&testnum from basetable&testnum._start;
1347  %if (&&testnobs&testnum =6) and &&testnobs2&testnum=5 %then %let result=PASS;
1348  %else %let result=FAIL;
1349  proc sql; insert into results set test="TEST&testnum",result="&result";
1350 %end;
1351 
1352 /* test 30 - testing a load to OLEDB (should use SQL server temp table) */
1353 %if (&test=ALL or &test=30 ) and 0 %then %do; /* skip until have a db */
1354  %let testnum=30;
1355 
1356 %assign_direct_lib(libref=XXX);
1357  data appendtable&testnum ;
1358  set XXX.temp_rk_table
1359  (where=(tx_from le %sysfunc(datetime()) lt tx_to));
1360  some_num+1;
1361  output;
1362  some_id=put(ranuni(0)*10000000,8.);output;
1363  stop;
1364  run;
1365  proc sql noprint;
1366  select count(*) into:testnobs&testnum from web.temp_rk_table;
1367 
1368  %bitemporal_dataloader(dclib=work,PK=some_rk
1369  ,append_dsn=appendtable&testnum.
1370  ,ETLSOURCE=bitemporal_tester test&testnum override
1371  ,tech_from=tx_from
1372  ,tech_to=tx_to
1373  ,base_lib=web
1374  ,base_dsn=temp_rk_table
1375  ,LOG=YES
1376  ,rk_underlying=some_id
1377  ,LOADTARGET=YES
1378  ,LOADTYPE=TXTEMPORAL
1379  );
1380 
1381 
1382  %let result=;
1383  proc sql noprint;
1384  select count(*) into:testnobs2&testnum from web.temp_rk_table;
1385  %if (&&testnobs2&testnum =%eval(&&testnobs&testnum+2)) %then %let result=PASS;
1386  %else %let result=FAIL;
1387  proc sql; insert into results set test="TEST&testnum",result="&result";
1388 %end;
1389 
1390 /* test 31 - bitemporal closeouts with embedded speechmarks in PK */
1391 %let testnum=31;
1392 %if &test=ALL or &test=&testnum %then %do;
1393  data basetable&testnum basetable&testnum._start;
1394  tx_from_dttm=0;tx_to_dttm=&high_date;
1395  PK='PK1';pk2=2; field3=1.012355555;field4='blah '; output;
1396  PK='PK1';pk2=3; field3=1.022355555;field4='blah2'; output;
1397  PK='PK1';pk2=4; field3=1.032355555;field4='blah3'; output;
1398  PK='PK2';pk2=3; field3=1.022355555;field4='bla"h'; output;
1399  PK='PK2';pk2=4; field3=1.032355555;field4="blah'h"; output;
1400  run;
1401 
1402  data appendtable&testnum.;
1403  PK='PK1';pk2=2;field4='blah '; output;
1404  PK='PK1';pk2=3;field4='blah2'; output;
1405  PK='PK1';pk2=4;field4='blah3'; output;
1406  PK='PK2';pk2=3;field4='bla"h'; output;
1407  PK='PK2';pk2=4;field4="blah'h"; output;
1408  run;
1409 
1410  %bitemporal_closeouts(
1411  tech_from=tx_from_dttm
1412  ,tech_to = tx_to_dttm
1413  ,base_lib=WORK /* Libref of the BASE table. */
1414  ,base_dsn=basetable&testnum
1415  ,append_lib=WORK /* Libref of the STAGING table. */
1416  ,append_dsn=appendtable&testnum.
1417  ,PK= pk pk2 field4
1418  ,NOW=%sysfunc(datetime()) /* allows consistent tracking of tech dates */
1419  ,FILTER= /* supply a filter to limit the update */
1420  );
1421 
1422  %let result=;
1423  %if %mf_getattrn(basetable&testnum,NLOBS) ne
1424  %mf_getattrn(basetable&testnum._start,NLOBS) %then %let result=FAIL;
1425  %else %do;
1426  proc sql noprint;
1427  select count(*) into: tst31 from basetable&testnum
1428  where tx_from_dttm le %sysfunc(datetime()) lt tx_to_dttm;
1429  %if &tst31=0 & %mf_getattrn(basetable&testnum,NLOBS)=5
1430  %then %let result=PASS;
1431  %else %let result=FAIL;
1432  %end;
1433  proc sql; insert into results set test="TEST&testnum",result="&result";
1434 %end;
1435 /* test 32 - testing closevars process*/
1436 %let testnum=32;
1437 %if &test=ALL or &test=&testnum %then %do;
1438  data basetable&testnum basetable&testnum._start;
1439  tx_from_dttm=0;tx_to_dttm=&high_date; some_val=0;
1440  closeme='NO ';x=1;y=2;z=2;output;
1441  closeme='NO';x=1;y=2;z=3;output;
1442  closeme='NO';x=1;y=2;z=4;output;
1443  closeme='YES';x=1;y=3;z=1;output;
1444  closeme='YES';x=1;y=3;z=2;output;
1445  closeme='YES';x=1;y=3;z=3;output;
1446  closeme='NO';x=4;y=3;z=3;output;
1447  closeme='YES';x=5;y=3;z=1;output;
1448  closeme='YES';x=5;y=3;z=2;output;
1449  closeme='YES';x=5;y=3;z=3;output;
1450  run;
1451 
1452  data appendtable&testnum.;
1453  closeme='YES';x=1;y=3;z=1;some_val=0;output;
1454  closeme='YES';x=5;y=3;z=1;some_val=0;output;
1455  closeme='NO ';x=1;y=2;z=2;some_val=1;output;
1456  run;
1457 
1458  %bitemporal_dataloader(dclib=work,PK=CLOSEME X Y Z
1459  ,append_dsn=appendtable&testnum.
1460  ,ETLSOURCE=bitemporal_tester test&testnum override
1461  ,tech_from=tx_from_dttm
1462  ,tech_to=tx_to_dttm
1463  ,base_lib=work
1464  ,base_dsn=basetable&testnum
1465  ,LOG=YES
1466  ,LOADTARGET=YES
1467  ,LOADTYPE=TXTEMPORAL
1468  ,CLOSE_VARS=CLOSEME X Y
1469  );
1470 
1471  %let result=;
1472  %if %mf_getattrn(basetable&testnum,NLOBS) ne 11 %then %let result=FAIL;
1473  %else %do;
1474  proc sql noprint;
1475  select count(*) into: tst32 from basetable&testnum
1476  where %sysfunc(datetime()) lt tx_to_dttm
1477  and closeme='YES';
1478  %if &tst32=2 %then %let result=PASS;
1479  %else %let result=FAIL;
1480  %end;
1481  proc sql; insert into results set test="TEST&testnum",result="&result";
1482 %end;
1483 
1484 /* test 33- bitemporal load with high precision decimals (and nulls) */
1485 %let testnum=33;
1486 %if &test=ALL or &test=&testnum %then %do;
1487  data basetable&testnum basetable&testnum._start;
1488  tx_from_dttm=0;tx_to_dttm=&high_date;
1489  PK='PK1';pk2=2; field3=0.0026539721926;field4='blah '; output;
1490  PK='PK1';pk2=3; field3=._;field4='blah2'; output;
1491  PK='PK1';pk2=4; field3=.;field4='blah3'; output;
1492  PK='PK2';pk2=3; field3=1.0026239721926;field4='bla"h'; output;
1493  PK='PK2';pk2=4; field3=1.0026139721926;field4="blah'h"; output;
1494  run;
1495 
1496  data appendtable&testnum.;
1497  set basetable&testnum;
1498  run;
1499 
1500 
1501  %bitemporal_dataloader(dclib=work,PK= pk pk2 field4
1502  ,append_dsn=appendtable&testnum.
1503  ,ETLSOURCE=bitemporal_tester test&testnum precision
1504  ,tech_from=tx_from_dttm
1505  ,tech_to=tx_to_dttm
1506  ,base_lib=work
1507  ,base_dsn=basetable&testnum
1508  ,LOG=YES
1509  ,LOADTARGET=YES
1510  ,LOADTYPE=TXTEMPORAL
1511  )
1512 
1513  %let result=;
1514  %if %mf_getattrn(basetable&testnum,NLOBS) ne
1515  %mf_getattrn(basetable&testnum._start,NLOBS) %then %let result=FAIL;
1516  %else %do;
1517  proc sql noprint;
1518  select count(*) into: tst32 from basetable&testnum
1519  where tx_from_dttm le %sysfunc(datetime()) lt tx_to_dttm;
1520  %if &tst32=5 & %mf_getattrn(basetable&testnum,NLOBS)=5
1521  %then %let result=PASS;
1522  %else %let result=FAIL;
1523  %end;
1524  proc sql; insert into results set test="TEST&testnum",result="&result";
1525 %end;
1526 
1527 quit;
1528 %mend bitemporal_tester;
1529 
1530 options mprint;
1531 %bitemporal_tester(test=ALL)
1532 
1533 
1534 data work.test_results;
1535  set work.results;
1536  rename test=test_description result=test_result;
1537 run;
1538