Making data files

Creating Data Files

In 1994, this is how I began a review of of the program Epi Info version 5.01 for Chance magazine:

One semester, the data sets I wanted to use in class were already in machine-readable form. I placed them on the [then mainframe!] computer for my students as a matter of convenience to spare them the chore of data entry. Around the middle of the term, this prompted a student to ask, "Where do data files come from?"
Where do data files come from? With any luck, they will come from someone else, but eventually we all end up helping others deal with putting data files together.
Methods for recording data often appear to be selected as afterthoughts, and they often use tools designed for other purposes.

In 2000, this note began

Things haven't changed much over the last six year. Data continue to arrive from a variety of sources, entered in ways suggesting they weren't necessarily recorded with an eye toward future analysis.

Today, it's fair to say that things are different. The world needed a standard, so it chose the Excel spreadsheet. With Microsoft's dominance of the spreadsheet market, data are often collected directly into Excel spreadsheets. However, this is only part of the reason why Excel has become the standard. Users demand the ability to transfer data between programs. Software companies cannot ignore this and remain in business. Every program has its "Save As" options. Different programs may provide different options, but Excel is always on the list because it can be found on so many computers. Those who design program that import data, such as statistical program packages, recognize this reality and see to it that their programs can read Excel spreadsheets.

Excel has become analogous to an airline's hub. Just as getting from point A to point B involves passing through the hub, getting data from program A to program B often involves passing through Excel. There are still many cases where one program can read another program's data files directly, but if I were asked today "Where do data files come from?" my response would be, "Probably from someone's Excel spreadsheet."

Still, it is not good enough merely that data be entered into a spreadsheet. There are ways to enter data so that they are nearly unusable. Many spreadsheets require considerable massaging before they are suitable for analysis. This note provides some guidelines to minimize the work of importing Excel spreadsheets into standard statistical packages.

The standard data structure is a table of numbers in which each row corresponds to an individual subject and each column corresponds to a different variable or measurement. If for example, a study recorded the identification number, age, sex, height, and weight of 10 subjects, The resulting dataset would be composed of 10 rows and 5 columns.

Some recent programs use a different structure for repeated measurements on the same individual. The number of rows is determined by the total number of measurements rather than the number of subjects. Consider a study where 5 weekly blood pressure readings are made on each of 20 subjects. Until recently, the dataset would be composed of 20 rows and 6 columns (5 blood pressures and an id). Today, some programs want the data entered as 100 rows (5 rows for each of the 20 subjects) of 3 columns (id, week number(=1,2,3,4,5), blood pressure). The process of converting a dataset from one format to the other should be straightforward. The details will depend on the statistical program package.

Once the data structure is understood, the following rules should be observed to achieve a smooth transfer between a spreadsheet and a statistical program package.

The first row of the spreadsheet should contain only legal variable names. The definition of legal will vary with the target program. All programs will accept names that are no more than eight characters long, are composed only of letters, numbers, and underscores, and begin with a letter. The limit of 8 characters has being increased in the latest round of program updates. This is helpful in cases with large numbers of variables because it is difficult to come up with a set of hundreds of names that are easily distinguished from each other and are easily understood. However, when variable names can be as long as 32 characters, it is easy to get carried away and end up with names that are tedious to type and manage.
All rows but the first should contain only data. There should be no embedded formulas. The statistical programs may not be able to handle them. There are two ways to deal with formulas. One is to rewrite the formulas in the target package so the statistics package can generate the values. The other is to use the spreadsheet's cut and special paste capabilities to store the derived values as actual data values in the spreadsheet.
No text should be entered in a column intended for numbers. This includes notations such as "missing", "lost", "N/A", and my personal favorite, "<20". If character strings are present, the statistical package may consider all of the data to be character strings rather than numbers. Numerical data may be mistakenly identified as character strings when one or more spaces are typed into an otherwise empty cell.
When a study will generate multiple data files

Every record in every data file must contain a subject identifier that is consistent across files. This variable should have the same name in each data file. SUBJ, ID, STUDYID, and their variants are often used.
Data files that are likely to be merged should not use the same variable names (other than for the common ID varible). For example, if files of baseline and followup data both contain total cholesterol values, it would be better to call them BCHOL in the baseline file and FCHOL in the followup file rather than CHOL in both so that they will be distinct in the merged file. If the same variable name is used in both files, it will be necessary to rename the variables as part of the file merging process. Otherwise, only a single column of values will appear in the merged dataset.
In order to protect subjects' privacy and confidentiality, only id numbers assigned for the study should be used as identifiers. Names and social security numbers should not be linked directly to raw data. While there are some arguments for allowing files to contain subjects' names as long as access is restricted to investigators and files never leave a research center, I find it more trouble than it's worth, if only because all printout containing names or social security numbers must be shredded.

The importance of including subject identifiers cannot be overstated. There are lots of ways to get into trouble very easily.

In some cases, files might contain no identifiers because investigators thought a study would generate only one file.
In other studies, records might be identified inconsistently. For example, subjects' names might be used with the spelling or capitalization varying from file to file.
In still other studies, some files might use subjects' names as identifiers while others might use sample or recruitment id numbers.

Inadequate identifiers make it difficult to merge files for analysis. It is highly probable that errors will result due to mismatching. The mismatching can be of two forms--either data from two subjects is combined or one subject's data gets split into multiple incomplete records. Regardless of the cause or the form of the mismatching, weeks, months, or even years of otherwise solid work will be compromised.