bitemporal_closeouts.sas
Go to the documentation of this file.
1 /**
2  @file
3  @brief Closes out records
4  @details Closes out records from a temporal table by reference to a single
5  temporal range + business key. Only live records are closed out, so the
6  entire key should be provided in the input table EXCEPT the TECH_FROM.
7  All records matching the key (as per the input table) are closed out
8  on TECH_TO.
9 
10  Returns an updated base table and `&mpelib..mpe_dataloads` table
11 
12  Potential improvements - write the update statements as a text file and retain
13  for future reference!
14 
15  @param [in] now= (DEFINE) Allows consistent tracking of tech dates. Should be
16  a date literal, not a numeric constant, for DB compatibility.
17  @param [in] load_type= Set to UPDATE if non-temporal, else assumed
18  to be TXTEMPORAL. Note that BITEMPORAL is treated the same as TXTEMPORAL
19  given that BUS_FROM should be supplied in the PK.
20  @param [in] tech_from= (tx_from_dttm) Technical FROM datetime variable.
21  Required on BASE table only.
22 
23  <h4> Global Variables </h4>
24  @li `dc_dttmtfmt`
25 
26 
27  <h4> SAS Macros </h4>
28  @li mp_abort.sas
29  @li mf_existvar.sas
30  @li mf_getattrn.sas
31  @li mf_getuser.sas
32  @li mf_getvartype.sas
33  @li mp_lockanytable.sas
34  @li dc_assignlib.sas
35 
36 
37  @version 9.2
38  @author 4GL Apps Ltd
39  @copyright 4GL Apps Ltd. This code may only be used within Data Controller
40  and may not be re-distributed or re-sold without the express permission of
41  4GL Apps Ltd.
42 **/
43 
44 %macro bitemporal_closeouts(
45  tech_from=tx_from_dttm
46  ,tech_to = tx_to_dttm /* Technical TO datetime variable.
47  Req'd on BASE table only. */
48  ,base_lib=WORK /* Libref of the BASE table. */
49  ,base_dsn=BASETABLE /* Name of BASE table. */
50  ,append_lib=WORK /* Libref of the STAGING table. */
51  ,append_dsn=APPENDTABLE /* Name of STAGING table. */
52  ,PK= name sex /* Business key, space separated. */
53  /* Should INCLUDE BUS_FROM field if relevant. */
54  ,NOW=DEFINE
55  ,FILTER= /* supply a filter to limit the update */
56  ,outdest= /* supply an unquoted filepath/filename.ext to get
57  a text file containing the update statements */
58  ,loadtype=
59  ,loadtarget=YES /* if <> YES will return without changing anything */
60 );
61 %put ENTERING &sysmacroname;
62 %local x var start;
63 %let start=%sysfunc(datetime());
64 %dc_assignlib(WRITE,&base_lib)
65 %dc_assignlib(WRITE,&append_lib)
66 
67 %if &now=DEFINE %then %let now=&dc_dttmtfmt.;
68 %put &=now;
69 /**
70  * perform basic checks
71  */
72 /* do tables exist? */
73 %if not %sysfunc(exist(&base_lib..&base_dsn)) %then %do;
74  %mp_abort(msg=&base_lib..&base_dsn does not exist)
75 %end;
76 %else %if %sysfunc(exist(&append_lib..&append_dsn))=0
77  and %sysfunc(exist(&append_lib..&append_dsn,VIEW))=0 %then %do;
78  %mp_abort(msg=&append_lib..&append_dsn does not exist)
79 %end;
80 /* do TX columns exist? */
81 %if &loadtype ne UPDATE %then %do;
82  %if not %mf_existvar(&base_lib..&base_dsn,&tech_from) %then %do;
83  %mp_abort(msg=&tech_from does not exist on &base_lib..&base_dsn)
84  %end;
85  %else %if not %mf_existvar(&base_lib..&base_dsn,&tech_to) %then %do;
86  %mp_abort(msg=&tech_to does not exist on &base_lib..&base_dsn)
87  %end;
88 %end;
89 /* do PK columns exist? */
90 %do x=1 %to %sysfunc(countw(&PK));
91  %let var=%scan(&pk,&x,%str( ));
92  %if not %mf_existvar(&base_lib..&base_dsn,&var) %then %do;
93  %mp_abort(msg=&var does not exist on &base_lib..&base_dsn)
94  %end;
95  %else %if not %mf_existvar(&append_lib..&append_dsn,&var) %then %do;
96  %mp_abort(msg=&var does not exist on &append_lib..&append_dsn)
97  %end;
98 %end;
99 /* check uniqueness */
100 proc sort data=&append_lib..&append_dsn
101  out=___closeout1 noduprecs dupout=___closeout1a;
102  by &pk;
103 run;
104 %if %mf_getattrn(___closeout1a,NLOBS)>0 %then
105  %put NOTE: dups on (&PK) in (&append_lib..&append_dsn);
106 /* is &NOW value within a tolerance? Should not allow renegade closeouts.. */
107 %local gap;
108 %let gap=0;
109 data _null_;
110  now=&now;
111  gap=intck('HOURS',now,datetime());
112  call symputx('gap',gap,'l');
113 run;
114 %mf_abort(
115  iftrue=(&gap > 24),
116  msg=NOW variable (&now) is not within a 24hr tolerance
117 )
118 
119 /* have any warnings / errs occurred thus far? If so, abort */
120 %mf_abort(
121  iftrue=(&syscc>0),
122  msg=Aborted due to SYSCC=&SYSCC status
123 )
124 
125 /**
126  * Create closeout statements. These are sent as individual SQL statements
127  * to ensure pass-through utilisation. The update_cnt variable monitors
128  * how many records were actually updated on the target table.
129  */
130 %local update_cnt;
131 %let update_cnt=0;
132 filename tmp temp;
133 data _null_;
134  set ___closeout1;
135  file tmp;
136  if _n_=1 then put 'proc sql noprint;' ;
137  length string $32767.;
138  %if &loadtype=UPDATE %then %do;
139  put "delete from &base_lib..&base_dsn where 1";
140  %end;
141  %else %do;
142  now=symget('now');
143  put "update &base_lib..&base_dsn set &tech_to= " now @;
144  %if %mf_existvar(&base_lib..&base_dsn,PROCESSED_DTTM) %then %do;
145  put " ,PROCESSED_DTTM=" now @;
146  %end;
147  put " where " now " lt &tech_to ";
148  %end;
149  %do x=1 %to %sysfunc(countw(&PK));
150  %let var=%scan(&pk,&x,%str( ));
151  %if %mf_getvartype(&base_lib..&base_dsn,&var)=C %then %do;
152  /* use single quotes to avoid ampersand resolution in data */
153  string=" & &var='"!!trim(prxchange("s/'/''/",-1,&var))!!"'";
154  %end;
155  %else %do;
156  string=cats(" & &var=",&var);
157  %end;
158  put string;
159  %end;
160  put "&filter ;";
161  put '%let update_cnt=%eval(&update_cnt+&sqlobs);%put update_cnt=&update_cnt;';
162 run;
163 
164 data _null_;
165  infile tmp;
166  input;
167  putlog _infile_;
168 run;
169 
170 %if &loadtarget ne YES %then %return;
171 
172 /* ensure we have a lock */
173 %mp_lockanytable(LOCK,
174  lib=&base_lib,ds=&base_dsn
175  ,ref=bitemporal_closeouts
176  ,ctl_ds=&mpelib..mpe_lockanytable
177 )
178 
179 options source2;
180 %inc tmp;
181 
182 filename tmp clear;
183 
184 /**
185  * Update audit tracker
186  */
187 
188 %local newobs; %let newobs=%mf_getattrn(work.___closeout1,NLOBS);
189 %local user; %let user=%mf_getuser();
190 proc sql;
191 insert into &mpelib..mpe_dataloads
192  set libref=%upcase("&base_lib")
193  ,DSN=%upcase("&base_dsn")
194  ,ETLSOURCE="&append_lib..&append_dsn contained &newobs records"
195  ,LOADTYPE="CLOSEOUT"
196  ,DELETED_RECORDS=&update_cnt
197  ,NEW_RECORDS=0
198  ,DURATION=%sysfunc(datetime())-&start
199  ,USER_NM="&user"
200  ,PROCESSED_DTTM=&now;
201 quit;
202 
203 
204 %mend bitemporal_closeouts;