*! version 1.0 7 Jun 2016

Title

codebook_ripper -- Rip metadata saved in a Excel workbook and
generate a do file with code for labels, value labels and notes.

Syntax

codebook_ripper
using Excel_workbook_path_and_name
[,
options]

Options

Mandatory
variable(name) The column title text in the Excel workbook specifying which column to use for variable names.

The column title text is in the first row on the sheet specified by the sheet option.

The column title text is transformed into a Stata variable name when the sheet is imported, eg spaces are removed.

THAT name is the one to specify.

label(name) The column title text in the Excel workbook specifying which column to use for variable label text.

The column title text is in the first row on the sheet specified by the sheet option.

The column title text is transformed into a Stata variable name when the sheet is imported, eg spaces are removed.

THAT name is the one to specify.

sheet(string) The name from where to read the columns containing variables, labels value labels and notes.

When the sheet is imported first row is used as a base for the Stata names to refer to.

Optional
valuelabels(name) The column title text in the Excel workbook specifying which column to use for value labels.

The column text is in the first row on the sheet specified by the sheet option.

The column text is transformed into a Stata variable name when the sheet is imported, eg spaces are removed.

THAT name is the one to specify.

Value label text syntax is specified the options delimiter and equal2 below.

nolowername Naming conventions here are to have names in lower
letters. If names are not to be forced to be lowered use this option.

rename(name) The column title text in the Excel workbook specifying which column to use as new variable names.

The column text is in the first row on the sheet specified by the sheet option.

The column text is transformed into a Stata variable name when the sheet is imported, eg spaces are removed.

THAT name is the one to specify.

delimiter(string) The delimiter separates each assignment of value
and value label in the column of value labels.

Default value is ";" (semicolon).

equal2(string) The equal2 separates value and value label for
each assignment of value and value label in the column of value labels.

Default value is "=" (equal to).

Extra spaces around to equal2 value are ignored.

notes(namelist) A set of additional column title names adding a set of notes to the variable in the order specified in option notes.

The column title texts in the Excel workbook are transformed into a Stata variable names when the sheet is imported, eg spaces are removed.

THESE name is the ones to specify.

Notes could eg be based on information on the original questions, units or formulas saved in the sheet.

dofile(string) Which do file to save the commands into.

Default value is the workbookname underscore the sheet name (eg workbook_sheet).

outpath(string) Path to save the do file into.

Default value is the workbookname underscore the sheet name (eg workbook_sheet).

replace Use option replace if it is ok to replace existing do file.

run If the dataset to use the do file upon is opened in Stata option run will run (command) the do file at the end.

This is only relevant if the dataset to use the do file upon is loaded up front.

do If the dataset to use the do file upon is opened in Stata option run will do (command) the do file at the end.

This is only relevant if the dataset to use the do file upon is loaded up front.

open This option opens the do file in the Do-file editor at the end.

Description

Metadata such as labels for variables, value labels and other information are
sometimes saved in sheets from a Excel workbook.

The command codebook_ripper can convert that information into a do file
template that adds this information to the dataset.

codebook_ripper imports the specified sheet table into Stata and from
there the necessary commands are generated and saved into the do file.

The dataset possibly present at Stata will be preserved in the process.

Examples

Assume metadata are in sheet metadata1 in a Excel workbook named metadata.

The flat file table consist of the columns named Variable names, Labels, Value
Labels, note1 and note2.

Hence delimiter and equal2 are by default set to "," and "=", respectively.

An example of a cell value in the column with name Value labels could be:
0 = Female, 1=Male.

Set the default working directory:

cd c:/mydata

Load the dataset which needs the labels etc from the sheet in the Excel workbook:

use "./dataset_with_no_labels/dataset.dta", clear

Now running the command below will generate the do file ./do/my_label_file that
the dataset dataset.dta containing the variables listed in column
Variablenames (note that space is removed).

These variables will get labels from the values in the column Labels.
Value labels will be generated from column Valuelabels.
Note1 and note2 follow the same recipe.

The do file will be replaced (option replace) if necessary and it will also
be run in the end of the command codebook_ripper (option do).
All in the command:

codebook_ripper using "metadata.xlsx", variable(Variablenames) label(Labels) valuelabels(Valuelabels) sheet(metadata1) dofile(./do/my_label_file) notes(note1 note2) replace do

Authors and support

Author:

Niels Henrik Bruun,

Section for General Practice,

Dept. Of Public Health,

Aarhus University

Support:

nhbr@ph.au.dk