Macros for SAS Application Developers
https://github.com/sasjs/core
mp_retainedkey.test.sas
Go to the documentation of this file.
1 /**
2  @file
3  @brief Testing mp_retainedkey macro
4 
5  <h4> SAS Macros </h4>
6  @li mp_assert.sas
7  @li mp_assertcolvals.sas
8  @li mp_retainedkey.sas
9 
10 **/
11 
12 /**
13  * Setup base tables
14  */
15 proc sql;
16 create table work.maxkeytable(
17  keytable varchar(41) label='Base table in libref.dataset format',
18  keycolumn char(32) format=$32.
19  label='The Retained key field containing the key values.',
20  max_key num label=
21  'Integer representing current max RK or SK value in the KEYTABLE',
22  processed_dttm num format=E8601DT26.6
23  label='Datetime this value was last updated',
24  constraint pk_mpe_maxkeyvalues
25  primary key(keytable));
26 
27 create table work.locktable(
28  lock_lib char(8),
29  lock_ds char(32),
30  lock_status_cd char(10) not null,
31  lock_user_nm char(100) not null ,
32  lock_ref char(200),
33  lock_pid char(10),
34  lock_start_dttm num format=E8601DT26.6,
35  lock_end_dttm num format=E8601DT26.6,
36  constraint pk_mp_lockanytable primary key(lock_lib,lock_ds));
37 
38 data work.targetds;
39  rk_col=_n_;
40  set sashelp.class;
41 run;
42 
43 data work.appendtable;
44  set sashelp.class;
45  if mod(_n_,2)=0 then name=cats('New',_n_);
46  if _n_<7;
47 run;
48 
49 libname x (work);
50 
51 /** Test 1 - base case **/
52 %mp_retainedkey(
53  base_lib=X
54  ,base_dsn=targetds
55  ,append_lib=X
56  ,append_dsn=APPENDTABLE
57  ,retained_key=rk_col
58  ,business_key= name
59  ,check_uniqueness=NO
60  ,maxkeytable=0
61  ,locktable=0
62  ,outds=work.APPEND
63  ,filter_str=
64 )
65 %mp_assert(
66  iftrue=(&syscc=0),
67  desc=Checking errors in test 1,
68  outds=work.test_results
69 )
70 
71 data work.check;
72  do val=1,3,5,20,21,22;
73  output;
74  end;
75 run;
76 %mp_assertcolvals(work.append.rk_col,
77  checkvals=work.check.val,
78  desc=All values have a match,
79  test=ALLVALS
80 )
81 
82 /** Test 2 - all new records, with metadata logging and unique check **/
83 data work.targetds2;
84  rk_col=_n_;
85  set sashelp.class;
86 run;
87 
88 data work.appendtable2;
89  set sashelp.class;
90  do x=1 to 21;
91  name=cats('New',x);
92  output;
93  end;
94  stop;
95 run;
96 
97 %mp_retainedkey(base_dsn=targetds2
98  ,append_dsn=APPENDTABLE2
99  ,retained_key=rk_col
100  ,business_key= name
101  ,check_uniqueness=YES
102  ,maxkeytable=x.maxkeytable
103  ,locktable=work.locktable
104  ,outds=WORK.APPEND2
105  ,filter_str=
106 )
107 %mp_assert(
108  iftrue=(&syscc=0),
109  desc=Checking errors in test 2,
110  outds=work.test_results
111 )
112 %mp_assert(
113  iftrue=(%mf_nobs(work.append2)=21),
114  desc=Checking append records created,
115  outds=work.test_results
116 )