Skip to content
Tutorials Center
My learning

Topics

Lesson 13 of 39 ยท Data Preparation

SPSS Sort, Merge, Aggregate and Restructure Data (Wide to Long)

Sort cases, add cases and add variables with Merge Files, aggregate to group summaries, and restructure data between wide and long layouts using VARSTOCASES and CASESTOVARS.

Data rarely sits in one tidy file. A survey might come in two waves, customer details live in one file and orders in another, and a repeated-measures study might have one column per time point where a procedure wants one row per measurement. SPSS has a group of commands in the Data menu to shape files: Sort Cases, Merge Files, Aggregate and Restructure.

Sort Cases

Data > Sort Cases... orders the rows by one or more variables, ascending or descending. Sorting is required before some operations (matching files by a key, SPLIT FILE) and helps you inspect extremes.

TEXT
SORT CASES BY region (A) income (D).

(A) is ascending, (D) descending. Missing values sort first when ascending. There is also Data > Sort Variables..., which reorders the columns (by name, type, label) without touching the cases.

Add Cases: stack files with the same variables

Use Add Cases when two files hold different cases but the same kind of variables: January and February customers, or two interviewers' files.

  1. Open the first dataset (active).
  2. Data > Merge Files > Add Cases...
  3. Pick the other open dataset or an external .sav file, Continue.
  4. The dialog lists Unpaired Variables (names that appear in only one file, or that differ in type) and Variables in New Active Dataset (matching pairs). Rename or pair variables by hand if names differ, and drop the ones you do not need.
  5. Tick Indicate case source as variable to create source01 (0 = active dataset, 1 = other file).
TEXT
ADD FILES FILE=* /FILE='feb.sav' /IN=from_feb.
EXECUTE.

Problems arise when the same variable has a different type (numeric vs string) or string widths. Fix them in Variable View before adding.

Python Stand-in: Add Cases stacks rows

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.

Add Variables: match files on a key

Use Add Variables when two files hold the same cases but different variables: demographics in one file, test scores in another, joined by id.

  1. Sort both files by the key (id, ascending).
  2. Data > Merge Files > Add Variables..., choose the second file.
  3. Check Match cases on key variables, move id into Key Variables.
  4. Choose how the match is done:
    • Both files provide cases (a full outer join),
    • Non-active dataset is keyed table or Active dataset is keyed table (a lookup: the keyed file provides extra variables and adds no cases).
  5. Resolve Excluded Variables (same name in both files, for example gender: rename one).
TEXT
SORT CASES BY id.
MATCH FILES /FILE=* /FILE='scores.sav' /BY id.
EXECUTE.

For a lookup table (a file mapping product code to product name), use /TABLE: MATCH FILES /FILE=* /TABLE='products.sav' /BY productcode..

Without a key, Add Variables pairs rows by their position: row 1 with row 1. That is only right if both files have the same cases in the same order, which is rare. Always use a key, and check that it is unique in each file (see cleaning and validating data for finding duplicates).

Python Stand-in: match files by key (outer join and lookup)

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.

Notice that id 3 exists only in scores, so in the outer join it gets a missing age, and a lookup would not create that case at all.

Aggregate

Data > Aggregate... summarizes cases into group statistics.

  1. Move the grouping variable(s) into Break Variable(s).
  2. Move the variables to summarize into Summaries of Variable(s) and click Function... to choose Mean, Median, Sum, Count (N), First, Last, Minimum, Maximum, Standard deviation, percentages above or below a value, etc.
  3. Under Save, choose:
    • Add aggregated variables to active dataset (each case receives its group's value, handy for computing deviations from the group mean),
    • Create a new dataset containing only the aggregated variables (one row per group),
    • Write a new data file...
  4. Tick Number of cases in break group to get a count variable.
TEXT
AGGREGATE
  /OUTFILE=* MODE=ADDVARIABLES
  /BREAK=region
  /mean_income=MEAN(income)
  /n_region=N.
COMPUTE income_dev = income - mean_income.

MODE=ADDVARIABLES adds columns to every case; /OUTFILE=* with MODE=REPLACE would replace the file by one row per group, and /OUTFILE='file.sav' writes a new file.

Python Stand-in: AGGREGATE with a break 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.

Restructure: wide and long data

Repeated measurements can be stored two ways.

Layout Looks like Used by
Wide One row per subject, one column per time point (t1, t2, t3) Paired tests, repeated-measures ANOVA
Long One row per measurement (id, time, score) Mixed models, grouped charts

Data > Restructure... opens the Restructure Data Wizard with three paths.

  1. Restructure selected variables into cases (wide to long, VARSTOCASES).
  2. Restructure selected cases into variables (long to wide, CASESTOVARS).
  3. Transpose all data (rows become columns, FLIP).
flowchart LR
    W["Wide<br/>id, t1, t2, t3"] -- "VARSTOCASES" --> L["Long<br/>id, time, score"]
    L -- "CASESTOVARS" --> W

The same data in two shapes.

TEXT
VARSTOCASES
  /MAKE score FROM t1 t2 t3
  /INDEX=time(3)
  /KEEP=id
  /NULL=KEEP.

CASESTOVARS
  /ID=id
  /INDEX=time
  /GROUPBY=VARIABLE.

In the wizard you identify how many variable groups to restructure (one group if only score is measured repeatedly), name the target variable and the index variable (time), and decide what to do with the other fixed variables (keep, drop). For long to wide you identify the case group (id) and the index variable, and SPSS makes score.1, score.2, ....

Python Stand-in: wide to long and back

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.

Good practice

  • Keep the key variable clean: same type and format in both files, no duplicates or stray spaces.
  • Make sure variable names, types and labels agree before combining.
  • After combining, check N, run Frequencies on the source variable and spot-check a few cases.
  • Do each step in syntax and save the result under a new name.

Common mistakes

  • Merging by position instead of by key.
  • A key stored as a string in one file and a number in the other, so nothing matches.
  • Forgetting to sort both files by the key before MATCH FILES.
  • Aggregating into the active dataset and then overwriting it by accident.
  • Analyzing long data as if each row were a separate person (inflating the sample size).

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which Merge Files option do you use to join two files with the same variables but different cases?

    Add Cases stacks the rows of the files. Add Variables joins columns for the same cases.
  2. 2. What must you do before MATCH FILES with /BY id?

    Matching by key requires both files to be sorted by the key variable.
  3. 3. Which command turns t1, t2, t3 columns into one score column with a time index?

    VARSTOCASES converts wide data to long data.
  4. 4. What does AGGREGATE MODE=ADDVARIABLES do?

    Each case receives the summary value for its break group.

Summary

  • Sort Cases orders rows; sort by key before matching files or splitting.
  • Add Cases stacks rows; Add Variables joins columns, using a key variable.
  • Aggregate makes group summaries as new columns or a new file.
  • Restructure converts between wide and long layouts (VARSTOCASES, CASESTOVARS, FLIP).
  • Verify N, keys and variable types after every combination.

Next: Cleaning and Validating Data