help for iecodebook

Title

iecodebook -- performs common data cleaning tasks using dataset definitions (codebooks) written in Excel files.

For more detailed instructions, please refer to the iecodebook DIME Wiki entry.

Description

iecodebook is designed to automate repetitive data cleaning tasks in two situations:
apply, where a large number of variables need to have arbitrary rename, recode, or label commands applied to them;
and append, where two or more datasets need to be harmonized to have the same variable names, labels, and value labels ("choices") in order to be appended together.
iecodebook also provides an export utility so that a human-readable record of the variables and their labels in a dataset
can be instantly created at any time.

The purpose of iecodebook is therefore to:

(1) reduce the Stata coding required for an arbitrary number of commands, usually to a single command; and

(2) to leave a human-readable record of the adjustments that were made to each dataset in Excel.

For the apply and append syntaxes, iecodebook provides a template command
that will correctly set up the appropriate codebook or harmonization template.
In both cases, you then need to manually complete the template in order to tell the command the exact adjustments that you want to be made in the dataset.
This completed template becomes a more readable record of the changes made in data cleaning than a dofile typically is.

Functions

iecodebook template
creates an Excel codebook template that describes the current or targeted dataset(s),
with empty columns for you to specify the changes or harmonizations for the other iecodebook commands.

iecodebook apply
reads an Excel codebook that specifies
renames, recodes, variable labels, and value labels, and applies them to the current dataset.

iecodebook append
reads an Excel codebook that specifies how variables should be harmonized across
two or more datasets - rename, recode, variable labels, and value labels - applies the harmonization, and appends the datasets.

iecodebook export
creates an Excel or plaintext codebook that describes the current dataset,
optionally creates or verifies a datasignature for record-keeping, optionally re-saves the dataset in the specified location,
and optionally reduces the dataset to only the variables used in a set of specified dofiles.

Syntax

Note that the correct command is always created by replacing

"apply" or "append" with "template" when creating the template,

and changing it back to use the completed codebook. It's that easy!

Apply: Setting up and using a codebook to alter current data

iecodebook template using "/path/to/codebook.xlsx" , [replace]

iecodebook apply using "/path/to/codebook.xlsx"

, [drop] [missingvalues(# "label" [# "label" ...])]

Note: This function operates on the dataset that is open in the current Stata session.

Append: Setting up and using a codebook to harmonize and append multiple datasets

iecodebook template

"/path/to/survey1.dta" "/path/to/survey2.dta" [...]

using "/path/to/codebook.xlsx"

, surveys(Survey1Name Survey2Name [...])

[match] [replace]

iecodebook append

"/path/to/survey1.dta" "/path/to/survey2.dta" [...]

using "/path/to/codebook.xlsx"

, clear surveys(Survey1Name Survey2Name [...])

[generate(varname) missingvalues(# "label" [# "label" ...])]

[report] [replace] [keepall]

Export: Creating codebooks and signatures for datasets

iecodebook export ["/path/to/data"]

using "/path/to/codebook.xlsx"

, [replace] [save] [verify]

[signature] [reset]

[plaintext(compact | detailed)] [noexcel]

[trim("/path/to/dofile1.do" ["/path/to/dofile2.do"] [...])] [trimkeep(varlist)]

Options

Apply Options

drop Requests that iecodebook drop all variables which have no entry in the "name" column in the codebook.
The default behavior is to retain all variables. Alternatively, to drop variables (or remove value labels from variables) one-by-one, write .
(a single period) in the "name" (or "choices") column of the codebook.
Unused value labels will always be removed from the datset by iecodebook,
but existing value labels will remain attached to variables by default.
Removing value labels explicitly with . is therefore recommended
when you wish to remove value label information from the dataset.

missingvalues() This option specifies standardized "extended missing values" to add to every value label definition in the "choices" column.
For example, specifying missingvalues(.d "Don't Know" .r "Refused" .n "Not Applicable") will add those codes to every coded answer.

Append Options

clear This option is required, as iecodebook append will clear the data in memory.

surveys() This option is always required in append. When creating a template or reading a codebook from "/path/to/codebook.xlsx",
iecodebook will use this list of names to identify each survey in the codebook.
These must be exactly one word for each survey, and they must come in the same order as the filepaths.
Names must have no spaces or special characters.
When importing, this will also be used to create a variable identifying the source of each observation.

match This option can be used to "auto-align" the template command when preparing for iecodebook append.
If specified, it will cause any variables in later datasets with the same original name as a variable in the first dataset
to appear in the same row of the Excel sheet.

generate() This option names the variable identifying the source of each observation. If left blank, the default is "survey".

missingvalues() This option specifies standardized "extended missing values" to add to every value label definition in the "choices" column.
For example, specifying missingvalues(.d "Don't Know" .r "Refused" .n "Not Applicable") will add those codes to every value-labeled answer.

report This option writes a codebook in the standard export format describing the appended dataset.
It will be placed in the same folder as the append codeboook, with the same name, with "_report" added to the filename.

replace This option is required to overwrite a previous report.

keepall By default, iecodebook append will only retain those variables with a new name explicitly written in the codebook to signify manual review for harmonization.
Specifying this option will keep all variables from all datasets. Use carefully!
Forcibly appending data, especially of different types, can result in loss of information.
For example, appending a same-named string variable to a numeric variable may cause data deletion.
(This is common when one dataset has all missing values for a given variable.)

Export Options

replace This option allows iecodebook export to overwrite an existing codebook or dataset.

save This option requests that a the data be saved at the same location as the codebook,
with the same name as the codebook.

saveas() This option requests that a the data be saved at the specified location,
overwriting the codebook name.

verify This option orders iecodebook export to confirm that the current data precisely matches an existing codebook.
It will break with an error and describe all changes if there are any differences between the two.
A new codebook will not be written in this case.

plaintext(compact | detailed) This option requests that the codebook be created as a plaintext file.
This file contains the default output of codebook if argument detailed} is used,
and the compact output of codebook if argument compact is used.
Only one of the arguments can be used}.

noexcel This option requests that the codebook be created as a plaintext file.
It can only be used alongside option plaintext() and cannot be combined with verify.

signature This option requests that a datasignature be verified
in the same destination folder as the codebook and/or data are saved,
and will return an error if a datasignature file is not found or is different,
guaranteeing data has not changed since the last reset of the signature.

reset Specified with signature,
this option allows iecodebook export to place a new datasignature
or overwrite an existing datasignature.

trim() This option takes one or more dofiles as inputs, and trims the current dataset to only include variables used in those dofiles,
before executing any of the other export tasks requested.

trimkeep() This option adjusts trim() to retain additional
variables (such as ID variables) that are desired, but not used in code.

Examples

Apply: Create and prepare a codebook to clean current data

Step 1: Use the template function to create a codebook template for the current dataset.

sysuse auto.dta , clear
iecodebook template using "codebook.xlsx"

Step 2: Fill out some instructions on the "survey" sheet.

The "name" column renames variables and the "label" column applies labels.

The "choices" column applies value labels (defined on the "choices" sheet in Step 3):

+
+
| name label choices name:current label:current choices:current recode:current |
+
+
| _template (Ignore this placeholder, but do not delete it.) |
| |
| car Name make Make and Model |
| | | value recode |
| rename label labels Current names, labels, types, & value labels commands |
| | | | | |
| dom Domestic? yesno foreign Car type origin (0=1)(1=0) |
+
+

Step 3: Use the "choices" sheet to define variable labels according to the following syntax.

+
+
| list_name value label |
+
+
| yesno 0 No |
| yesno 1 Yes |
| |
| Each individual label |
| gets an entry, grouped |
| by the "list_name" |
| corresponding to "choices" |
| on the "survey" sheet. |
+
+

Step 4: Use the apply function to read the completed codebook.

Note that the correct command is created by replacing

"template" with "apply" after creating the template.

sysuse auto.dta , clear
iecodebook apply using "codebook.xlsx"
tab dom

Append: Harmonize and combine multiple datasets using a codebook

Step 0: Create two dummy datasets for testing iecodebook append.

sysuse auto.dta , clear
save data1.dta , replace
rename (price foreign mpg)(cost origin car_mpg)
save data2.dta , replace

Step 1: Create a harmonization template for iecodebook append.
Note that this clears current data.

iecodebook template

"data1.dta" "data2.dta"

using "codebook.xlsx"

, surveys(First Second)

(Run)

Step 2: Fill out some instructions on the "survey" sheet.

The survey sheet is designed to be rearranged so that stacked variables are placed in the same row.

There will also be one extra "choices" sheet per survey with existing value labels for your reference.

+
+
| name label choices name:First recode:First name:Second recode:Second |
+
+
| survey Data Source (do not edit this row) |
| |
| cost Cost price cost |<- align old
| | | value | names
| rename label labels Original names, labels, types, & value labels for reference | and new
| | | | | recode
| dom Domestic? yesno foreign (0=1)(1=0) origin (0=1)(1=0) |<- commands
+
+

Note: When aligning the old variable names in the same row, cut and paste the whole variable entry.

Don't just copy the names, leaving the old names in the original place.

Step 3: Read and apply the harmonization template.

Note that the correct command is created by replacing

"template" with "append" after creating the template.

iecodebook append

"data1.dta" "data2.dta"

using "codebook.xlsx"

, clear surveys(First Second)

(Run)

Export: Creating a simple codebook

sysuse auto.dta , clear

iecodebook export using "codebook.xlsx"

Acknowledgements

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

Kristoffer Bjarkefur
Luiza Cardoso De Andrade
Saori Iwamoto
Maria Ruth Jones

...and all DIME Research Assistants and Field Coordinators

Authors

Benjamin Daniels, The World Bank, DEC

Please send bug reports, suggestions and requests for clarifications
writing "iefieldkit: iecodebook" in the subject line to the email address
found here.

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 for iefieldkit:

https://github.com/worldbank/iefieldkit