In my job role I often work with other people's datasets, non-experts bring me clinical data and I help them to summarise it and perform statistical tests.

The problem I am having is that the datasets I am brought are almost always riddled with typos, inconsistencies, and all sorts of other problems. I am interested to know if other people have standard tests which they do to try to check any datasets that come in.

I used to draw histograms of each variable just to have a look but I now realise there are lots of horrible errors that can survive this test. For example, I had a repeated measures dataset the other day where, for some individuals, the repeated measure was identical at Time 2 as it was at Time 1. This was subsequently proved to be incorrect, as you would expect. Another dataset had an individual who went from being very severely disordered (represented by a high score) to being problem-free, represented by 0's across the board. This is just impossible, although I couldn't prove it definitively.

So what basic tests can I run on each dataset to make sure that they don't have typos and they don't contain impossible values?

Thanks in advance! 

It helps to understand how the data were recorded.
Let me share a story.  Once, long ago, many datasets were stored only in fading hardcopy.  In those dark days I contracted with an organization (of great pedigree and size; many of you probably own its stock) to computerize about 10^5 records of environmental monitoring data at one of its manufacturing plants.  To do this, I personally marked up a shelf of laboratory reports (to show where the data were), created data entry forms, and contracted with a temp agency for literate workers to type the data into the forms.  (Yes, you had to pay extra for people who could read.)  Due to the value and sensitivity of the data, I conducted this process in parallel with two workers at a time (who usually changed from day to day).  It took a couple of weeks.  I wrote software to compare the two sets of entries, systematically identifying and correcting all the errors that showed up.
Boy were there errors!  What can go wrong?  A good way to describe and measure errors is at the level of the basic record, which in this situation was a description of a single analytical result (the concentration of some chemical, often) for a particular sample obtained at a given monitoring point on a given date.  In comparing the two datasets, I found:

Errors of omission: one dataset would include a record, another would not.  This usually happened because either (a) a line or two would be overlooked at the bottom of a page or (b) an entire page would be skipped.

Apparent errors of omission that were really data-entry mistakes.  A record is identified by a monitoring point name, a date, and the "analyte" (usually a chemical name).  If any of these has a typographical error, it will not be matched to the other records with which it is related.  In effect, the correct record disappears and an incorrect record appears.

Fake duplication.  The same results can appear in multiple sources, be transcribed multiple times, and seem to be true repeated measures when they are not.  Duplicates are straightforward to detect, but deciding whether they are erroneous depends on knowing whether duplicates should even appear in the dataset.  Sometimes you just can't know.

Frank data-entry errors.  The "good" ones are easy to catch because they change the type of the datum: using the letter "O" for the digit "0", for instance, turns a number into a non-number.  Other good errors change the value so much it can readily be detected with statistical tests.  (In one case, the leading digit in "1,000,010 mg/Kg" was cut off, leaving a value of 10.  That's an enormous change when you're talking about a pesticide concentration!) The bad errors are hard to catch because they change a value into one that fits (sort of) with the rest of the data, such as typing "80" for "50".  (This kind of mistake happens with OCR software all the time.)

Transpositions.  The right values can be entered but associated with the wrong record keys.  This is insidious, because the global statistical characteristics of the dataset might remain unaltered, but spurious differences can be created between groups.  Probably only a mechanism like double-entry is even capable of detecting these errors.


Once you are aware of these errors and know, or have a theory, of how they occur, you can write scripts to troll your datasets for the possible presence of such errors and flag them for further attention.  You cannot always resolve them, but at least you can include a "comment" or "quality flag" field to accompany the data throughout their later analysis.
Since that time I have paid attention to data quality issues and have had many more opportunities to make comprehensive checks of large statistical datasets.  None is perfect; they all benefit from quality checks.  Some of the principles I have developed over the years for doing this include

Whenever possible, create redundancy in data entry and data transcription procedures: checksums, totals, repeated entries: anything to support automatic internal checks of consistency.

If possible, create and exploit another database which describes what the data should look like: that is, computer-readable metadata.  For instance, in a drug experiment you might know in advance that every patient will be seen three times.  This enables you to create a database with all the correct records and their identifiers with the values just waiting to be filled in.  Fill them in with the data given you and then check for duplicates, omissions, and unexpected data.

Always normalize your data (specifically, get them into at least fourth normal form), regardless of how you plan to format the dataset for analysis.  This forces you to create tables of every conceptually distinct entity you are modeling.  (In the environmental case, this would include tables of monitoring locations, samples, chemicals (properties, typical ranges, etc.), tests of those samples (a test usually covers a suite of chemicals), and the individual results of those tests.  In so doing you create many effective checks of data quality and consistency and identify many potentially missing or duplicate or inconsistent values.
This effort (which requires good data processing skills but is straightforward) is astonishingly effective.  If you aspire to analyze large or complex datasets and do not have good working knowledge of relational databases and their theory, add that to your list of things to be learned as soon as possible.  It will pay dividends throughout your career.

Always perform as many "stupid" checks as you possibly can.  These are automated verification of obvious things such that dates fall into their expected periods, the counts of patients (or chemicals or whatever) always add up correctly, that values are always reasonable (e.g., a pH must be between 0 and 14 and maybe in a much narrower range for, say, blood pH readings), etc.  This is where domain expertise can be the most help: the statistician can fearlessly ask stupid questions of the experts and exploit the answers to check the data.


Much more can be said of course--the subject is worth a book--but this should be enough to stimulate ideas.
