help for ieduplicates

Title

ieduplicates -- Identify duplicates in ID variable and export them in
an Excel file that also can be used to correct the duplicates

For a more descriptive discussion on the intended usage and work flow of this
command please see the DIME Wiki.

Syntax

ieduplicates
ID_varname using "/path/to/duplicates_report.xlsx"
, uniquevars(varlist)

[force keepvars(varlist) tostringok droprest
nodaily

duplistid(string) datelisted(string)
datefixed(string) correct(string)

drop(string) newid(string)
initials(string) notes(string)
listofdiffs(string)]

where ID_varname is the variable that will be controlled for duplicates, and "/path/to/duplicates_report.xlsx" is the absolute file path where the duplicates report will be saved.

options

uniquevars(varlist) variables used as unique ID within groups of duplicates in ID_varname. May not be in date or time format.

force specifies that all unresolved duplicates with respect to ID_varname be dropped.

keepvars(varlist) variables used to be included in the Excel report in addition to ID_varname and uniquevars()

tostringok allows ID_varname to be recasted to string if required

droprest disables the requirement that duplicates must be explicitly deleted

nodaily disables daily back-up copies of the Excel report

Excel variable name options:

This option allows users to customize the column names in the report Excel spreadsheet. This option is intended for situations when the dataset already has variable(s) named the same as the default Excel spreadsheet name.

duplistid(string) customizes variable duplistid

datelisted(string) customizes variable datelisted

datefixed(string) customizes variable datefixed

correct(string) customizes variable correct

drop(string) customizes variable drop

newid(string) customizes variable newid

initials(string) customizes variable initials

notes(string) customizes variable notes

listofdiffs(string) customizes variable listofdiffs

Description

In brief:

ieduplicates outputs a report with any duplicates in ID_varname to an Excel file
and return the data set without those duplicates. Each time ieduplicates executes, it first
looks for an already created version of the Excel report, and applies any corrections already listed in it
before generating a new report. Note that there is no need to import the corrections manually. This command
reads the corrections directly from the Excel file as long as it is saved with the same file name.

In more detail:

ieduplicates takes duplicates observations in ID_varname and exports
them to a duplicates report in the Excel file specified by using. ID_varname
is by definition not unique in this Excel report and uniquevars(varlist)
needs to be specified in order to have a unique reference for each row in the Excel report
when merging the corrections back to the original data set. The
varlist in uniquevars(varlist) must uniquely and fully identify all
observations in the Excel report, either on its own or together with ID_varname. ieduplicates
then returns the data set without these duplicates.

The Excel report includes three columns called correct, drop and newid.
Each of them represents one way to correct the duplicates. If correct is indicated with
a "correct" then that observation is kept unchanged, if drop is indicated with a "drop" then
that observation is deleted and if newid is indicated then that observation is assigned
a new ID using the value in column newid. After corrections are entered, the report should
be saved in the same location and without any changes to its name.

Before outputting a new report ieduplicates always checks if there already is an
Excel report with corrections and applies those corrections before generating a new report. It is
at this stage that uniquevars(varlist) is required as it otherwise is impossible
to know which duplicate within a group of duplicates that should be corrected in which way.

keepvars(varlist) allows the user to include more variables in the Excel report
that can help identifying each duplicate is supposed to be corrected. The report also includes two
columns initials and notes. Using these columns is not required but it is recommended to use initials
to keep track of who decided how to correct that duplicate and to use notes to document why
the correction was chosen. If initials and notes are used, then the Excel report also functions
as an excellent documentation of the correction made.

Options

using specifies the file path where previous Excel
files will be looked for, and where the updated Excel report will be saved. Note that this needs to be
an absolute file path. A subfolder called Daily, where the duplicate report file is backed up daily,
will be created in the same folder as this file.

uniquevars(varlist) list variables that by themselves or together
with ID_varname uniquely identifies all observations. This varlist is required when the corrections are
imported back into Stata and merged with the original data set. Time variables
are not allowed in uniquevars() as Stata and Excel stores date and time slightly different, which
can casue errors when using these varaibles to merge the input in the Excel report back
into Stata. The time variable can be turned into a string variable using generate timevar_str = string(timevar,"%tc") and
then be used in this options. Data that has been downloaded from
a server usually has a variable called "KEY" or similar. Such a variable would be optimal
for uniquevars(varlist).

force specifies that all unresolved duplicates with respect to ID_varname be dropped.
This option is required when there are unresolved duplicates as a reminder that these observations
will be dropped, and the resulting data will be different from the original. Do not save the
data returned by ieduplicates with option force over the raw data, as information will be lost
and ieduplicates will not function on this data set, preventing reproducibility of the process.

keepvars(varlist) list variables to be included in the exported
Excel report. These variables can help team members identifying which observation to keep,
drop and assign a new ID to. For data integrity reasons, be careful not to export and share
Excel files including both identifying variables and names together with ID_varname.

tostringok allows ID_varname to be turned into a string variable in case
ID_varname is numeric but a value listed in newid is non-numeric. Otherwise an error is generated.

droprest disables the requirement that duplicates must be explicitly deleted.
The default is that if one of the duplicates in a group of duplicates has a
correction, then that correction is only valid if all other duplicates in that
group have a correction as well. For example, if there are four observations with
the same value for ID_varname and one is correct, one needs a new ID and
two are incorrect and should be deleted. Then the first one is indicated to be
kept in the correct column, the second one is given a new ID in newid
and the other two observations must be indicated for deletion in drop
unless droprest. The first two corrections are not considered valid and
will cause an error in case if droprest is not specified and the other
two observations are not explicitly indicated to be dropped. It is recommended
to not use droprest and to manually indicate all deletions to avoid
mistakes, but this option exists for cases when that might be very inconvenient.

nodaily disables the generation of daily back-up copies of the
Excel report. The default is that the command saves dated copies of the Excel
report in a sub-folder called Daily in the folder specified in using. If
the folder /Daily/ does not exist, then it is created unless the
option nodaily is used.

Excel variable name options:

duplistid(string) datelisted(string)
datefixed(string) correct(string)
drop(string) newid(string)
initials(string) notes(string)
listofdiffs(string)
allow the user to set a unique name for each default variable names (e.g. duplistid, datelisted, etc.) in the Excel report spreadsheet.
This is meant to be used when the variable name already exists in the dataset. To avoid error, the command offers a way to modify the variable name in the Excel Report spreadsheet.

The Excel Report

A report of duplicates will be created in the file path specified by using
if any duplicates in ID_varname were found. The command will create a subfolder called Daily
in the folder where this report is saved. Daily back-ups of the report will be saved in the Daily
subfolder. If a report is backed up already that day, that report will not be overwritten. In case
the command is run more than once in a day, and creates different reports, a time-stamped report for
the same date will be saved instead.

All duplicates in a group of duplicates must have a correction indicated. If
one or more duplicates are indicated as correct in correct or assigned a new
ID in newid, then all other duplicates with the same value in ID_varname must
be explicitly indicated for deletion. This requirement may (but probably
shouldn't) be disabled by option droprest.

Columns in Excel Report filled in automatically:

duplistid stores an auto incremented duplicate list ID that is used
to maintain the sort order in the Excel Report regardless of how the data in memory
is sorted at the time ieduplicates is executed.

datelisted stores the date the duplicate was first identified.

datefixed stores the date a valid correction was imported the first
time for that duplicate.

listofdiffs stores a list with the names of the variables that are
different in two different observations. This list is truncated at 250 characters
and is only stores when there are exactly two duplicates. For full list or cases
where there are more then two duplicates, iecompdup should be used.

Columns in Excel Report to be filled in manually by a user:

correct is used to indicate that the duplicate should be kept. The only
valid value is "correct" to reduce the risk of unintended entries ("yes" is also
allowed for backward compatibility). The values are not sensitive to case. If correct
is indicated then both drop and newid must be left empty.

drop is used to indicate that the duplicate should be deleted. The only
valid value is "drop" to reduce the risk of unintended entries ("yes" is also
allowed for backward compatibility). The values are not sensitive to case. If drop
is indicated then both correct and newid must be left empty.

newid is used to assign a new ID values to a duplicate. If ID_varname
is a string then all values are valid for newid. If ID_varname is numeric then
only digits are valid, unless the option tostringok is specified.
If tostringok is specified and newid is non-numeric, then ID_varname
is recasted to a string variable. If newid is indicated then both correct and drop must be
left empty.

initials allows the team working with this data to keep track on who
decided on corrections.

notes allows the team working with this data to document the reason
for the duplicates and the why one type of correction was chosen over the others.

Columns in Excel Report with data from the data set:

The columns above are followed by the values in uniquevars(varlist)
and in keepvars(varlist). These column keeps the name the
variables have in the data set. These variables can help the team to identify
which correction should be applied to which duplicate.

Stored results

ieduplicates stores the following results in r():

Scalars

r(numDup) number of unresolved duplicates

r(numDup) is intended to allow for the option to pause Stata in case unresolved duplicates
are found. See example 4 below for a code example on how to use r(numDup). See pause for instructions
on how to resume the execution of the code if Stata is paused.

Examples

Example 1.

ieduplicates HHID "C:\myImpactEvaluation\baseline\data\DuplicatesReport.xls" uniquevars(KEY)

Specified like this ieduplicates start by looking for the file
"C:\myImpactEvaluation\baseline\data\DuplicatesReport.xls". If there is a report
with corrections, those corrections are applied to the data set. Then the command looks for
unresolved duplicates in HHID and exports a new report if any duplicates were found. The data
set is returned without any of the unresolved duplicates. The variable KEY is used to separate
observations that are duplication in the ID var.

Example 2.

ieduplicates HHID using "C:\myImpactEvaluation\baseline\data\DuplicatesReport.xls", keepvars(enumerator) uniquevars(KEY)

Similar to the example above, but it also includes the variable enumerator in the Excel
report which is most likely helpful if the data set is collected through a household survey.

Example 3. Using r(numDup) to pause the execution of the code if
unresolved duplicates were found

ieduplicates HHID using "C:\myImpactEvaluation\baseline\data\DuplicatesReport.xls", uniquevars(KEY)

if (r(numDup) != 0) {

pause

}

Example 4. Using the Excel file. The table below could be the report generated in Example 2 above. Make the viewer window wider and reload the page if the table below does not display properly!

+
+
| HHID duplistid datelisted datefixed correct drop newid initials notes KEY enumerator listofdiffs |
+
+
| 4321 1 27Dec2015 02Jan2016 correct KB double submission uniquevalue keepvarvalue varlist |
| 4321 2 27Dec2015 02Jan2016 drop KB double submission uniquevalue keepvarvalue varlist |
| 7365 3 03Jan2016 uniquevalue keepvarvalue varlist |
| 7365 4 03Jan2016 uniquevalue keepvarvalue varlist |
| 1145 5 03Jan2016 11Jan2016 1245 IB incorrect id uniquevalue keepvarvalue varlist |
| 1145 6 03Jan2016 11Jan2016 correct IB correct id uniquevalue keepvarvalue varlist |
| 9834 7 11Jan2016 uniquevalue keepvarvalue varlist |
| 9834 8 11Jan2016 uniquevalue keepvarvalue varlist |
+
+

The table above shows an example of an Excel report with 4 duplicates groups with
two duplicates in each groups. The duplicates in 4321 and in 1145 have both been corrected
but 7365 and 9834 are still unresolved. Before any observation was corrected, all observations had
datefixed, correct, drop, newid, initials and note empty just like the observations for ID 7365 and 9834. datefixed
is not updated by the user, the command adds this date the first time the correction is made.

Observation with duplistid == 5 was found to have been
assigned the incorrect ID while the data was collected. This observation is assigned the correct ID in newid
and observation duplistid == 6 is indicated to be correct. Someone with initials IB made this
correction and made a note. This note can and should be more descriptive but is kept short in this example.

Observations with duplistid == 1 and duplistid == 2 were identified as a duplicate submissions of the same
observation. One is kept and one is dropped, usually it does not matter which you keep and which you drop, but that should be confirmed.

Both corrections described in the example would have been easily identified using this command's sister command iecompdup.

Example 5. ieduplicates HHIDusing "C:\myImpactEvaluation\baseline\data\DuplicatesReport.xls", uniquevars(KEY) drop(out) notes(notes_enumerators)

+
+
| HHID duplistid datelisted datefixed correct out newid initials notes_enumerators KEY listofdiffs |
+
+
| 4321 1 27Dec2015 02Jan2016 correct KB double submission uniquevalue varlist |
| 4321 2 27Dec2015 02Jan2016 drop KB double submission uniquevalue varlist |
| 7365 3 03Jan2016 uniquevalue varlist |
| 7365 4 03Jan2016 uniquevalue varlist |
| 1145 5 03Jan2016 11Jan2016 1245 IB incorrect id uniquevalue varlist |
| 1145 6 03Jan2016 11Jan2016 correct IB correct id uniquevalue varlist |
| 9834 7 11Jan2016 uniquevalue varlist |
| 9834 8 11Jan2016 uniquevalue varlist |
+
+

The variable names in Excel Report is now changed to the user speficied. If the user changed any of the variable names in the Excel Report, when importing the Excel file back to apply the decisions, run exactly the same code:

ieduplicates HHIDusing "C:\myImpactEvaluation\baseline\data\DuplicatesReport.xls", uniquevars(KEY) drop(out) notes(notes_enumerators)

Acknowledgements

We would like to acknowledge the help in testing and proofreading we received in relation to this command and help file from (in alphabetic order):

Mehrab Ali
Michell Dong
Paula Gonzalez
Seungmin Lee

Author

All commands in ietoolkit is developed by DIME Analytics at DECIE, The World Bank's unit for Development Impact Evaluations.

Main author: Kristoffer Bjarkefur, DIME Analytics, The World Bank Group

Please send bug-reports, suggestions and requests for clarifications
writing "ietoolkit ieduplicates" in the subject line to:

dimeanalytics@worldbank.org

You can also see the code, make comments to the code, see the version
history of the code, and submit additions or edits to the code through the GitHub repository of ietoolkit.