We urge that data be submitted on Excel worksheets, since 1) use of a spreadsheet program facilitates data entry, editing and display, 2) our statistical packages can read Excel data directly if our format specifications are followed, 3) you probably have access to Excel. If you cannot supply data on Excel worksheets, or if your data format does not conform to the requirements below, see the Additional Information section at the end of this document.

If your data are for a clinical protocol, please see the Additional Information section for discussion of additional data that should be provided.

In addition to the data, we require a Data Dictionary that defines the variables.

Please keep in mind that, although we do consistency checks on data, we expect data submitted to us for analysis to be accurate and ready for analysis.

Data Format

An Excel dataset is a matrix of cells arranged in horizontal rows and vertical columns. We require that every row have the same number of cells. Examples 1 and 2 show two variants of the required data format; example 3 shows some common data formatting errors.

Columns: Each column must contain the values of a single variable; one column must contain the values of a subject identifier. The dataset may be augmented with any number of columns containing descriptive text, but only if the text is not required for analysis.

Row #1: The first row (row #1) must contain variable names.

Rows: Each row except row #1 must contain values of variables for a single subject, and an entry must be made for each variable listed in row #1. A variable may be repeatedly measured for each subject. If the number of measurements varies from subject to subject, it is best to use example 2 as a template; if not, you can use either example 1 or 2.

Cells: A cell must contain the value of a single variable or a variable name; cells should not be left empty. Formats for specific kinds of data follow:

Variable names: The first row of cells must contain variable names. We prefer a single word or string of characters without spaces, but can accommodate almost anything. There can be no duplicate names
subject identifiers: enter a subject identifier as a number or as a character string. If the data are for patients, the number should be the medical record number; this will permit us to link to the CRS database. We do not want patient names or initials.
Numbers: enter a number in integer (1,2,3,.…) or decimal (3.1, 5.2, 8.6,….). format. If an entry is known only to be greater than some bound (>5, >10, >100,….), or less than some bound (<5, <10, <100,….), enter a numeric code that could not possibly be mistaken for data; there should be a different numeric code for each bound. Include a description of the codes in the data dictionary.
categories: enter a value for a categorical variable (sex, race,….) as an integer (0,1,…), or as a short (and, hopefully, descriptive) character string without spaces. A single character (e.g., M, F for sex) is preferred when unambiguous. When using characters, always use either upper case or lower case; do not mix cases.
dates: enter a date as mm/dd/yyyy (11/22/2001, 1/5/2002).
missing data: if data for any cell are missing, enter a period (.). If data can be missing for several reasons, and it is important in the analysis to know why the data are missing, enter a unique numeric code for each reason. A code may be also used to indicate when data are missing because a variable is not applicable for some subjects (e.g., age at first pregnancy for men, age at first pregnancy for women who were never pregnant). Use codes that could not possibly be mistaken for data.
Empty cells: there should be none.

Data Dictionary

We need a “data dictionary” to define:

the variables and the units in which they are measured.
the codes used for:

a) numbers, when known only to be greater than, or less than some value.

b) categorical variables.

c) missing data, if not a period.

We suggest that, if you use Excel, the data dictionary be included with the data on a separate Excel worksheet in the same workbook. Any additional information about the variables that you believe might be useful to us can be included in the data dictionary. Examples of data dictionaries are provided below; however, we do not require that you use the specific format.

Examples

Example 1: the “short-fat” format

Example 2: the “long-skinny” format

Example 3: This example shows some common data formatting errors:

Example 3, Corrected, and augmented with a third patient:

Additional Information

Alternative Formats: Contact us if your data are not in Excel worksheets. We can analyze data from most spreadsheet and database programs, and from most statistical packages; however data should be formatted according to the rules described above. We also accept properly formatted text files. Data embedded in Word or WordPerfect documents would generally need to be re-formatted.

For large datasets with repeated measurements, it is often more efficient to record baseline data in one file, and the repeatedly measured data in another file. However, both files must have identical patient identifiers. See example 4.

Use of Text for Values of Categorical Variables: We discourage use of text for the values of a categorical variable; our experience is that text is much more subject to various data entry errors than numeric or character codes. However, if your dataset already contains text entries, we can analyze it under the following conditions: a) there is no evidence of data entry errors, and b) each value of a categorical variable is identified by a single unique text string.

Alternative Date Formats: We can handle most date formats, but a single format should be used for an entire dataset. We prefer mm/dd/yyyy since it is less prone to data entry errors than most other formats. The year must be indicated with 4 digits.

Numeric Formats: Scientific notation (1.3E+10, 5.6E-5, 2.3E+1,….) is an acceptable format, but may be more prone to data entry errors than decimal or integer formats. If you use Excel, you may mix the three numeric formats in a dataset, even for a single variable.

Missing Data Codes: Missing data codes should generally be used to identify the reason why data are missing, unless that information is of no interest or would be redundant. For example, a woman’s age at first pregnancy could be missing either because the age is unknown, or because the woman was never pregnant. (In this case, one should use two numbers that could not possibly be a woman’s age at first pregnancy to code the missing values, e.g., 98 = age unknown and 99 = never pregnant.) If the number of children were also provided for all women, a missing data code to distinguish between these two cases would be redundant. However, if the age is unknown, it may still be useful to distinguish between the following two situations: 1) the age is expected to be determined in future follow-up, and 2) the age is not expected to be determined.

Additional Data that You Should Provide for a Clinical Protocol:

1. There should be at least one data file that includes (subject, patient, etc.) entered into the study, regardless of whether complete data was obtained for that experimental unit. (For example, even patients who drop out of the study just after randomization should be included in at least one of the data files.)

2. When the protocol calls for a measurement to be taken in a certain week or month of the study (say, at baseline or 1 month after surgery), include this information (e.g., coded as: 0 = baseline, 1 = 1 month) as well as the actual calendar date on which the visit occurred.

3. When information on patient survival, duration of response, or time to disease progression is to be analyzed, the date of last contact (last follow-up) must be provided for all patients. (This should be in a separate column.)

Example 4: Same data as in example 2, but separated into 2 files.

Home • Services • Personnel • Publications • Design Resources • Data Submission • Links
UPCI Biostatistics Facility • 201 N.Craig St • Suite 325 • Pgh, PA • 15213
Phone 412.383.1537 • Fax 412.383.1535