Skip to content
Tutorials Center
My learning

Topics

Lesson 14 of 39 ยท Data Preparation

Cleaning and Validating Data in SPSS: Duplicates, Outliers, Range Checks

Clean data in SPSS: screen with Frequencies and Descriptives, find duplicate cases, run validation rules, spot outliers with boxplots and Explore, fix text values and document every change.

Statistical procedures do not know that an age of 199 is a typo or that "male" and "Male " are the same group. Cleaning is the unglamorous step that decides whether results can be trusted. The method is always the same: screen, diagnose, decide, document. SPSS has tools for each part.

A cleaning workflow

flowchart LR
    A["Screen<br/>frequencies, descriptives, charts"] --> B["Diagnose<br/>error, true outlier, or missing?"]
    B --> C["Decide<br/>correct, set missing, keep, remove"]
    C --> D["Document<br/>syntax and a change log"]
    D --> A

Cleaning is a loop: each fix can reveal the next problem.

Never correct a value without being able to answer: why do I believe it is wrong, and where is the evidence? When possible, go back to the source (the questionnaire, the sensor log). Keep the raw file unchanged and make every fix in syntax with a comment.

Step 1: Screen categorical variables

Run Analyze > Descriptive Statistics > Frequencies on every categorical variable. Look for:

  • Spelling variants: Female, female, F, Fem..
  • Codes that are not defined (a value 4 where only 1 to 3 exist).
  • Unexpectedly rare categories that suggest errors.
  • A suspicious number of missing cases.

Turn off Display frequency tables for variables with hundreds of categories, or use Analyze > Reports > Case Summaries for a listing.

Text cleanup with string functions:

TEXT
STRING gender_c (A10).
COMPUTE gender_c = UPCASE(LTRIM(RTRIM(gender))).
RECODE gender_c ('F', 'FEM', 'FEMALE' = 'FEMALE') ('M', 'MALE' = 'MALE').
EXECUTE.

AUTORECODE or numeric recodes then turn the cleaned text into labelled codes.

Step 2: Screen numeric variables

Use Descriptives (or Frequencies with statistics and no table) to read the Minimum and Maximum of each numeric variable. Compare with what is possible:

  • age 0 to 110; a Likert item 1 to 5; a percentage 0 to 100;
  • impossible: negative counts, dates in the future, heights of 20 cm.
  • Placeholder codes (999, -1, 9999) show up as the maximum or minimum. Declare them missing.

A histogram (Frequencies > Charts, or Graphs > Chart Builder) shows gaps and spikes: a spike at exactly 0 or 99 hints at a default or placeholder.

Python Stand-in: range checks for each variable

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

In SPSS you can do the same with Data > Validation > Define Validation Rules... (single-variable rules such as "age between 0 and 110, integers only") and Data > Validation > Validate Data... (applies the rules, reports violations per variable and per case, and can check for incomplete IDs and duplicate IDs). The Validation menu is part of the Data Preparation add-on in some editions; check your licence.

Step 3: Logical consistency checks

Single-variable ranges are not enough. Check relationships between variables with COMPUTE flags:

TEXT
COMPUTE err_job = (years_employed > age - 14).
COMPUTE err_dates = (end_date < start_date).
COMPUTE err_child = (n_children > 0 AND age < 14).
EXECUTE.
FREQUENCIES VARIABLES=err_job err_dates err_child.

Each flag is 1 when the rule is broken. Then list the culprits: Analyze > Reports > Case Summaries, limit to the flagged cases, show id and the relevant variables. You can also sort by the flag and look at the top rows in Data View.

Step 4: Find duplicate cases

Duplicate IDs (the same person entered twice) distort counts. Data > Identify Duplicate Cases...:

  1. Move the variables that define a duplicate (usually id, or a set such as name + birthdate) into Define matching cases by.
  2. Under Variables to Create, tick Indicator of primary cases (1 for the first case in each group of duplicates, 0 for the rest) and Sequential count of matching case in each group.
  3. Optionally Move matching cases to the top of the file and Display frequencies for created variables.

Then look at the cases flagged 0. Decide whether they are true duplicates (remove) or different people sharing an ID (fix the ID). Do not remove blindly; two records with the same ID may hold different answers.

TEXT
SORT CASES BY id.
MATCH FILES FILE=* /BY id /FIRST=primary_first /LAST=primary_last.
EXECUTE.
Python Stand-in: indicator of primary cases

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Note the two records for id 102 have different ages (29 vs 31): that needs a decision, not an automatic deletion.

Step 5: Outliers

An outlier is a value far from the others. It might be an error, or a genuine extreme case. Do not delete a value just because it is extreme.

Explore is the main outlier tool: Analyze > Descriptive Statistics > Explore..., move the variable to Dependent List, optionally a Factor List, and in Statistics... tick Outliers (lists the five highest and lowest values with case numbers) and in Plots... keep Boxplots.

The boxplot draws a box from the 25th to the 75th percentile (using Tukey's hinges), a line at the median, and whiskers to the most extreme values within 1.5 box lengths. Beyond that:

  • a circle (o) marks an outlier (1.5 to 3 box lengths from the box),
  • an asterisk (*) marks an extreme value (more than 3 box lengths).

Case numbers appear next to the symbols, so you can find them in Data View.

Another approach is z scores: in Descriptives, tick Save standardized values as variables, which creates Zage; values with |z| > 3 (some use 3.29) deserve a look. See descriptives and z-scores. For several variables at once, Data > Identify Unusual Cases... (Data Preparation) computes an anomaly index.

Python Stand-in: Tukey fences and z scores

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The z score of the extreme value is only about 3.1, barely over the usual cutoff, because the outlier itself inflates the mean and standard deviation. The boxplot fences are more robust and flag it clearly, which is why Explore is preferred for finding outliers.

Decide and document

For each problem choose one action:

  1. Correct it, if the true value is known.
  2. Set to missing (a recode to SYSMIS), if it is impossible but unfixable.
  3. Keep it, if it is a real extreme; analyze with and without it (a sensitivity check) and report both.
  4. Remove the case, only with a strong reason; state the number removed.
  5. Use robust methods (medians, nonparametric tests, transformations).

Write every fix in a commented syntax file:

TEXT
* Case 217: age 199 is impossible (questionnaire says 19). Corrected.
IF (id = 217) age = 19.
* Placeholder 999 is missing.
MISSING VALUES income (999).
EXECUTE.

Save the cleaned data under a new name and keep the old file.

Common mistakes

  • Deleting every outlier automatically.
  • "Fixing" data directly in Data View with no record.
  • Forgetting that the cleaning of one variable can change another (a dropped case alters all statistics).
  • Cleaning but not re-running the screening afterward.
  • Treating duplicate IDs as errors without reading the records.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. In an SPSS boxplot, what does an asterisk mean?

    Circles mark outliers (1.5 to 3 box lengths) and asterisks mark extreme values (more than 3).
  2. 2. What is the first step when you see an impossible value such as age 199?

    Diagnose before acting: a typo may be fixable, a placeholder should be declared missing, and a real extreme may be kept.
  3. 3. Which tool lists the five highest and lowest values with case numbers?

    Explore > Statistics > Outliers gives the five largest and smallest values.
  4. 4. Why not delete duplicates automatically?

    Two records with the same ID may be different people or conflicting entries that need investigation.

Summary

  • Clean in a loop: screen, diagnose, decide, document.
  • Screen categories with Frequencies and numbers with Descriptives, Explore and charts; add logical cross-checks with COMPUTE flags.
  • Identify Duplicate Cases flags primary and duplicate records for review.
  • Outliers are found with boxplots, Explore's Outliers list and z scores; extreme does not mean wrong.
  • Every fix belongs in commented syntax, applied to a copy of the raw data.

Next: Frequencies and Percentiles