Repository navigation
Expand file tree
/
Copy pathassertlist_export_ids.ado
More file actions
342 lines (273 loc) · 11.2 KB
/
Copy pathassertlist_export_ids.ado
File metadata and controls
342 lines (273 loc) · 11.2 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
*! assertlist_export_all_ids version 1.05 - Biostat Global Consulting - 2023-01-29
* This program can be used after assertlist or assertlist_cleanup to grab the list of IDs
* that failed all assertions in a spreadsheet and export to single tab.
*******************************************************************************
* Change log
* Updated
* version
* Date number Name What Changed
* 2020-08-13 1.00 MK Trimner Original program
* 2020-09-01 1.01 MK Trimner Corrected placement of sheetcount & datacount locals
* 2020-09-07 1.02 MK Trimner Updated to accomodate idlist added to non-fix excel tabs
* 2021-0-31 1.03 MK Trimner Added format option to allow for a faster Stata run
* and to avoid excel formatting errors for large spreadsheets
* 2022-03-29 1.04 MK Trimner Added _al_obs_number to all assertions so changed this code to sort by _al_obs_number instead of idlist
* idlist is used as a secondary sorting method
* 2023-01-29 1.05 MK TRimner Changed noFORMAT to FORMAT. it is more logical. The default now is to not format.
* Added fmtids if using stata version >=15
* 2024-03-20 1.06 MK trimner Added code to only keep the _al_* variables
*******************************************************************************
*
* Contact Dale Rhoda (Dale.Rhoda@biostatglobal.com) with comments & suggestions.
*
capture program drop assertlist_export_ids
program define assertlist_export_ids
syntax , EXCEL(string asis) [FORMAT]
qui {
* Grab the version of stata to be used for formatting
if c(stata_version) < 15 global FORMATTING_VERSION 14
else global FORMATTING_VERSION 15
noi di as text "Confirming excel file exists..."
* If the user specified a .xls or .xlsx extension in NAME or EXCEL, strip it off here
foreach v in excel name {
if lower(substr("``v''",-4,.)) == ".xls" ///
local `v' `=substr("``v''",1,length("``v''")-4)'
if lower(substr("``v''",-5,.)) == ".xlsx" ///
local `v' `=substr("``v''",1,length("``v''")-5)'
}
* Make sure file provided exists
capture confirm file "`excel'.xlsx"
if _rc!=0 {
* If file not found, display error and exit program
noi di as error "Spreadsheet provided in macro EXCEL does not exist." ///
" Current value provided was: `excel'"
noi di as error "Exiting program..."
exit 99
}
else {
* Now lets create a single dataset with the cards that need reviewed
capture import excel using "`excel'.xlsx", describe
local f `=r(N_worksheet)'
local sheetcount 0
local datacount 0
forvalues b = 1/`f' {
* Bring in the sheet
capture import excel using "`excel'.xlsx", describe
* Capture the sheet name
local sheet `=r(worksheet_`b')'
if "`sheet'" != "Assertlist_Summary" {
local ++sheetcount
* Import file
noi di as text "Importing excel sheet: `sheet'..."
import excel "`excel'.xlsx", sheet("`sheet'") firstrow clear allstring
* Only keep the variables needed
local clean 0
capture confirm var _al_check_sequence
if _rc != 0 {
local clean 1
capture rename AssertionSequenceNumber _al_check_sequence
capture rename UserSpecifiedAdditionalInform _al_tag
capture rename ObservationNumberinDataset _al_obs_number
capture rename AssertionSyntaxThatFailed _al_assertion_syntax
capture drop NumberofVariablesCheckedinA
}
capture drop _al_num_var_checked
* Create local with keep variables
local keepvars
local vcount 0
foreach v of varlist* {
if `vcount' == 0 local keepvars `keepvars' `v'
if "`v'" == "_al_tag" local ++vcount
}
keep `keepvars'
* Drop any that do not start with _al*
* The fix tab may have vars before the tag varaible
keep _al_*
tempfile data
save `data', replace
capture confirm file "`data'"
if _rc == 0 {
use "`data'", clear
* Create an idlist from the variables
local idlist
foreach v of varlist* {
if !inlist("`v'","_al_tag", "_al_check_sequence", "_al_assertion_syntax","_al_obs_number") local idlist `idlist' `v'
}
gen _al_idlist = "`idlist'"
replace _al_idlist = "_al_obs_number" if missing(_al_idlist)
duplicates drop
local ++datacount
if `sheetcount' == 1 & `datacount' == 1 {
tempfile assertion_ids_for_review
save `assertion_ids_for_review', replace
}
else {
append using `assertion_ids_for_review'
duplicates drop
save `assertion_ids_for_review', replace
}
}
}
}
noi di as text "Create one dataset..."
* replace tag value
replace _al_tag = _al_check_sequence + " : " + _al_tag if !missing(_al_tag)
replace _al_tag = _al_check_sequence + " : " + _al_assertion_syntax if missing(_al_tag)
drop _al_assertion_syntax
rename _al_tag _al_assertion_details
destring _al_check_sequence, replace
capture destring _al_obs_number, replace
* Now we want to sort based on IDs
local orderlist
levelsof _al_obs_number, local(id)
foreach i in `id' {
levelsof _al_idlist if _al_obs_number == `i', local(list)
local uniquelist
foreach w in `list' {
local wc = wordcount("`w'")
forvalues n = 1/`wc' {
local uniquelist `uniquelist' `=word("`w'",`n')'
}
}
local list2 : list uniq uniquelist
replace _al_idlist = `"`list2'"' if _al_obs_number == `i'
local orderlist `orderlist' `uniquelist'
foreach v in `list2' {
if "`v'" != "_al_obs_number" {
levelsof `v' if _al_obs_number == `i', local(list)
local uniquelist
foreach w in `list' {
local wc = wordcount("`w'")
forvalues n = 1/`wc' {
local uniquelist `uniquelist' `=word("`w'",`n')'
}
local list2 : list uniq uniquelist
replace `v' = `"`list2'"' if _al_obs_number == `i'
}
}
}
}
local order2 : list uniq orderlist
local order2 =subinstr("`order2'","_al_obs_number","",.)
di "`order2'"
sort _al_obs_number _al_check_sequence `order2', stable
bysort _al_obs_number: gen n = _n
drop _al_check_sequence
reshape wide _al_assertion_details, i(_al_obs_number) j(n)
order _al_obs_number _al_idlist `order2'
* create count of the number of assertions failed
gen _al_number_assertions_failed = 0
label var _al_number_assertions_failed "Total Number of assertions that child failed"
foreach v of varlist _al_assertion_details* {
replace _al_number_assertions_failed = _al_number_assertions_failed + 1 if !missing(`v')
}
order _al_idlist
order _al_number_assertions_failed, before(_al_assertion_details1)
sort _al_obs_number _al_idlist `idlist'
compress
save `assertion_ids_for_review', replace
export excel using "`excel'.xlsx", firstrow (var) sheet("List of IDs failed assertions", replace) nolabel
*******************************************************************************
* If the Spreadsheet thas run through assertlist_cleanup
* tidy up the variable names
if `clean' == 1 assertlist_export_ids_clean_up, excel(`excel') sheet(List of IDs failed assertions) `format'
*******************************************************************************
if "`format'" != "" {
* Format the excel spreadsheet
noi di as text "Format excel sheet..."
describe
local col `=r(k)'
local row `=r(N)+1'
local i 1
foreach v of varlist * {
local varname = length("`v'")
tostring `v', replace
tempvar `v'_l
gen ``v'_l'=length(`v')
summarize ``v'_l'
local varstring =`=`r(max)'+5'
local uselength `varstring'
if `varname' > `varstring' local uselength `=`varname'+2'
local maxlength 20
if `varname' > 20 local maxlength `varname'
local m`i'=min(`uselength',`maxlength')
drop ``v'_l'
local ++i
}
* Now format the excel
mata: b = xl()
mata: b.load_book("`excel'.xlsx")
mata: b.set_mode("open")
mata: b.set_sheet("List of IDs failed assertions")
if $FORMATTING_VERSION == 15 {
* Create a bold font
mata: bold_font = b.add_fontid()
mata: b.fontid_set_font(bold_font, "Calibri", 11, "black")
mata: b.fontid_set_font_bold(bold_font, "on" )
mata: b.fontid_set_font_italic(bold_font, "off" )
mata assertlist_header = b.add_fmtid()
mata: b.fmtid_set_fontid(assertlist_header, bold_font)
mata: b.fmtid_set_fill_pattern(assertlist_header, "solid","lightgray")
mata: b.fmtid_set_horizontal_align(assertlist_header, "left")
mata: b.fmtid_set_text_wrap(assertlist_header, "on")
mata: b.set_fmtid(1,(1,`col'),assertlist_header)
forvalues i = 1/`col' {
mata format_width_`m`i'' = b.add_fmtid()
mata: b.fmtid_set_text_wrap(format_width_`m`i'', "on")
mata: b.fmtid_set_column_width(format_width_`m`i'',`i',`i', `m`i'')
mata: b.set_fmtid(2,`i',format_width_`m`i'')
}
mata assertlist_all_others = b.add_fmtid()
mata: b.fmtid_set_text_wrap(assertlist_all_others, "on")
mata: b.set_fmtid((3,`row'),(1,`col'),assertlist_all_others)
}
if $FORMATTING_VERSION == 14 {
forvalues i = 1/`col' {
mata: b.set_column_width(`i',`i', `m`i'')
mata: b.set_text_wrap((2,`row'),`i',"on")
}
mata: b.set_fill_pattern(1,(1,`col'),"solid","lightgray")
mata: b.set_font_bold(1,(1,`col'),"on")
mata: b.set_horizontal_align(1,(1,`col'),"left")
}
mata b.close_book()
}
}
}
end
********************************************************************************
********************************************************************************
****** Rename Excel Variables if Assertlit_cleanup has already ran *****
********************************************************************************
********************************************************************************
capture program drop assertlist_export_ids_clean_up
program define assertlist_export_ids_clean_up
qui {
syntax, EXCEL(string asis) SHEET(string asis) [FORMAT]
capture destring _al_number_assertions_failed, replace
summarize _al_number_assertions_failed
local max = r(max)
foreach v of varlist* {
local `v' `v'
if "`v'" == "_al_idlist" local _al_idlist List of Variables Used to Identify Line in Assertion
if "`v'" == "_al_number_assertions_failed" local _al_number_assertions_failed Number of Assertions Line Failed
}
forvalues i = 1/`max' {
local abbreviation st
if `=substr("`i'",-1,1)' == 2 local abbreviation nd
if `=substr("`i'",-1,1)' == 3 local abbreviation rd
if inlist("`=substr("`i'",-1,1)'","4","5","6","7","8","9","0") local abbreviation th
if `i' >= 11 & `i' <= 19 local abbreviation th
local _al_assertion_details`i' Assertion Tag or Syntax for `i'`abbreviation' Failed Assertion
}
* Put the new variable name into excel file
putexcel set "`excel'.xlsx", modify sheet("`sheet'")
local n 1
foreach v of varlist* {
mata: st_local("xlcolname", invtokens(numtobase26(`n')))
if "`format'" != "" putexcel `xlcolname'1 = "``v''", txtwrap bold left fpattern("solid", "lightgray")
else putexcel `xlcolname'1 = "``v''"
local ++n
}
}
end