In SPSS, why were the results of my merge with MATCH FILES scrambled?

Username:
Password:

If the results of your merge using the MATCH FILES command in SPSS are scrambled, follow the example below to fix your data. If you wish to use SPSS graphical interface to fix the problem, see SPSS graphical interface below.

The MATCH FILES command in SPSS merges two datasets to add or update variables. Without a /BY or /TABLE subcommand, the command sometimes produces odd results by simply putting two data sets together. The problem becomes worse when the index variable is not a unique identification variable.

Consider the following datasets and the MATCH FILES command:

ID V1 V2 ID V3 V4 C:\spss\DATA1.sav 1 1 1 c:\spss\DATA2.sav 1 5 5 1 2 2 2 6 6 2 3 3 2 4 4 MATCH FILES /FILE='c:\spss\DATA1.sav' /FILE='c:\spss\DATA2.sav' /BY ID. EXECUTE.

Since ID, the index variable, has duplicate cases, SPSS gets confused and gives you the warning message, "Warning # 5132 Duplicate key in a file...", producing the following result. Notice that when SPSS found a duplicate index, it automatically set the variable from DATA2 as missing and then went on to the next unique case.

ID V1 V2 V3 V4 1 1 1 5 5 1 2 2 . . 2 3 3 6 6 2 4 4 . .

To avoid this, use the /TABLE subcommand to tell SPSS that one file will be used as a lookup table for the other dataset. SPSS will then look through the file you defined with the /TABLE subcommand and use it to match data to each field duplicated in the other file, for example:

MATCH FILES /FILE='c:\spss\DATA1.sav' /TABLE='c:\spss\DATA2.sav' /BY ID. EXECUTE.

The command with /TABLE will produce the following dataset:

ID V1 V2 V3 V4 1 1 1 5 5 1 2 2 5 5 2 3 3 6 6 2 4 4 6 6

SPSS graphical interface

To merge data using SPSS graphical interface:

Open the data file Data1.sav.
From the Data menu, select Merge Files and then Add Variables... .
Select the file to merge (e.g., Data2.sav), and then click Continue.
In the Add variables dialog box, select Match cases on key variables in sorted files and check Non-active dataset is keyed table. Under "Key Variables:", select ID. Click OK.
This will produce the same results as the example using the MATCH FILES command above. It is important to sort both the data files by key variable(s) before you merge them.

For more about statistical and mathematical software, email the UITS Stat/Math Center, visit the center's web page, or phone 812-855-4724 (IUB) or 317-278-4740 (IUPUI). The center is located in Bloomington at 410 N. Park Avenue, and is open for consultation by appointment Monday-Friday 9am-5pm.

This is document afit in domain all.
Last modified on May 11, 2011.