1. Introduction
When you have two data files, you can combine them
by merging them side by side, matching up observations based on an identifier. For
example, below we have a file containing dads and we have a file
containing faminc. We would like to match merge the files together
so we have the dads observation on the same line with the faminc
observation based on the key variable famid.
dads famid name inc 2 Art 22000 1 Bill 30000 3 Paul 25000 faminc famid faminc96 faminc97 faminc98 3 75000 76000 77000 1 40000 40500 41000 2 45000 45400 45800
After match merging the dads and faminc,
the data would look like this.
famid name inc faminc96 faminc97 faminc98 1 Bill 30000 40000 40500 41000 2 Art 22000 45000 45400 45800 3 Paul 25000 75000 76000 77000
2. One-to-one merge
Let’s start by creating the files that we will be
merging. Below we create the files dads.sav and faminc.sav.
DATA LIST LIST / famid * name (A4) inc. BEGIN DATA. 2 Art 22000 1 Bill 30000 3 Paul 25000 END DATA. LIST. SAVE OUTFILE = "dads.sav". DATA LIST LIST / famid faminc96 faminc97 faminc98. BEGIN DATA. 3 75000 76000 77000 1 40000 40500 41000 2 45000 45400 45800 END DATA. LIST. SAVE OUTFILE = "faminc.sav".
The output of these statements is shown below,
confirming that we have read the data properly.
FAMID NAME INC 2.00 Art 22000.00 1.00 Bill 30000.00 3.00 Paul 25000.00 FAMID FAMINC96 FAMINC97 FAMINC98 3.00 75000.00 76000.00 77000.00 1.00 40000.00 40500.00 41000.00 2.00 45000.00 45400.00 45800.00
There are three steps to match merge dads.sav
with faminc.sav. (Note that this is a one to one
merge because there is a one to one correspondence between the dads and
faminc
records.) These three steps are illustrated below.
- Use SORT CASES to sort dads on
famid and save that file (we will call it dads2.sav)- Use SORT CASES to sort faminc
on famid and save that file (we will call it faminc2.sav)- Use MATCH FILES to merge the dads2.sav
and faminc2.sav files based on famid
Below we show the commands for performing the merge.
GET FILE="dads.sav". SORT CASES BY famid. SAVE OUTFILE="dads2.sav". GET FILE="faminc.sav". SORT CASES BY famid. SAVE OUTFILE="faminc2.sav". MATCH FILES FILE="dads2.sav" /FILE="faminc2.sav" /BY famid. LIST.
The output below shows that the match merge worked properly.
FAMID NAME INC FAMINC96 FAMINC97 FAMINC98 1.00 Bill 30000.00 40000.00 40500.00 41000.00 2.00 Art 22000.00 45000.00 45400.00 45800.00 3.00 Paul 25000.00 75000.00 76000.00 77000.00
3. One-to-many merge
The next example considers a one to many
merge where one observation in one file may have multiple matching records in another
file. Imagine that we had a file with dads like we saw in the
previous example, and we had a file with kids where a dad could have more
than one kid. You see why this is called a one to many merge since
you are matching one dad observation to one or more (many) kids
observations. Remember that the dads file is the file with one
observation, and the kids file is the one with many
observations. Below, we create the data file for the dads and for
the kids.
DATA LIST LIST / famid * name (A4) inc . BEGIN DATA. 2 Art 22000 1 Bill 30000 3 Paul 25000 END DATA. LIST. SAVE OUTFILE = "dads.sav". DATA LIST LIST / famid * kidname (A4) birth age wt * sex (A1). BEGIN DATA. 1 Beth 1 9 60 f 1 Bob 2 6 40 m 1 Barb 3 3 20 f 2 Andy 1 8 80 m 2 Al 2 6 50 m 2 Ann 3 2 20 f 3 Pete 1 6 60 m 3 Pam 2 4 40 f 3 Phil 3 2 20 m END DATA. LIST. SAVE OUTFILE="kids.sav".
As you see below, the steps for doing a one to
many merge is similar to the one to one merge that we saw above.
- Use SORT CASES BY to sort dads on
famid and save that file (we will call it dads2)- Use SORT CASES BY to sort kids
on famid and save that file (we will call it kids2)- Use MATCH FILES to merge the dads2
and kids2 files. However, since the dads file is
the file with one observation, use /TABLE="dads2.sav",
not /FILE="dads2.sav" to specify the dads
file.GET FILE="dads.sav". SORT CASES BY famid. SAVE OUTFILE="dads2.sav". GET FILE="kids.sav". SORT CASES BY famid. SAVE OUTFILE="kids2.sav". MATCH FILES FILE="kids2.sav" /TABLE="dads2.sav" /BY famid. LIST.
The output below shows that this merge worked as we
hoped.
FAMID KIDNAME BIRTH AGE WT SEX NAME INC 1.00 Beth 1.00 9.00 60.00 f Bill 30000.00 1.00 Bob 2.00 6.00 40.00 m Bill 30000.00 1.00 Barb 3.00 3.00 20.00 f Bill 30000.00 2.00 Andy 1.00 8.00 80.00 m Art 22000.00 2.00 Al 2.00 6.00 50.00 m Art 22000.00 2.00 Ann 3.00 2.00 20.00 f Art 22000.00 3.00 Pete 1.00 6.00 60.00 m Paul 25000.00 3.00 Pam 2.00 4.00 40.00 f Paul 25000.00 3.00 Phil 3.00 2.00 20.00 m Paul 25000.00
The key difference between a one to one
merge and a one to many merge is that you need to use /TABLE="dads2.sav"
instead of /FILE="dads2.sav". For your data, when you do
a one to many merge, ask yourself which file plays the role of one
(in one to many). For that file, use /TABLE=
instead of /FILE=.
Let’s intentionally make an error and use /FILE="dads2.sav"and
see what SPSS does.
MATCH FILES /FILE="kids2.sav" /FILE="dads2.sav" /BY famid. LIST.
The first thing we notice is that SPSS gives us the
warning shown below. This is telling us that there are multiple kids
for a given dad.
Warning # 5132 Duplicate key in a file. The BY variables do not uniquely identify each case on the indicated file. Please check the results carefully.
As SPSS advises, we will inspect the results carefully.
Indeed, we see the results are not what we desired. When there were multiple
kids
per dad, it only merged the dad with the first kid,
and then the following kids with the same dads were assigned missing
values for the dads information (name and inc).
When we used the /TABLE= subcommand in the previous example, SPSS
carried the dads information across all of the kids.
FAMID KIDNAME BIRTH AGE WT SEX NAME INC 1.00 Beth 1.00 9.00 60.00 f Bill 30000.00 1.00 Bob 2.00 6.00 40.00 m . 1.00 Barb 3.00 3.00 20.00 f . 2.00 Andy 1.00 8.00 80.00 m Art 22000.00 2.00 Al 2.00 6.00 50.00 m . 2.00 Ann 3.00 2.00 20.00 f . 3.00 Pete 1.00 6.00 60.00 m Paul 25000.00 3.00 Pam 2.00 4.00 40.00 f . 3.00 Phil 3.00 2.00 20.00 m .
4. Ordering the variables in the new file
You can use the /MAP subcommand with the ADD
FILES command to see the order of the variables in the new file, as
illustrated below. If you would like to rearrange the order of the variables
in the new file, you can also add the /KEEP subcommand to the ADD
FILES command. The variables will be ordered in the new file in the order
that you list them on the /KEEP subcommand. If you do not list all of the
variables on the /KEEP subcommand, the variables not listed will not be
present in the new file. Also note that you can list the first few variables
if they are the only ones that need to be reordered, and then use the keyword
ALL
to have the rest of the variables included in the new file. The variables not
specified on the /KEEP subcommand will remain the order in which they
are in the original files.
DATA LIST LIST / famid * name (A4) inc. BEGIN DATA. 2 Art 22000 1 Bill 30000 3 Paul 25000 END DATA. SAVE OUTFILE = "dads.sav". DATA LIST LIST / famid * name (A4) inc. BEGIN DATA. 1 Bess 15000 3 Pat 50000 2 Amy 18000 END DATA. SAVE OUTFILE = "moms.sav".ADD FILES FILE="dads.sav" /FILE="moms.sav" /KEEP = name ALL /MAP. EXECUTE.Map of the result file Result Input1 Input2------ ------ ------NAME NAME NAMEFAMID FAMID FAMIDINC INC INC
As you can see, the variables in the new file are now
in the order name, famid inc.
5. Problems to look out for
5.1 Mismatching records in one-to-one merge
The two data files have may have records that do not
match. Below we illustrate this by including an extra dad (Karl in
famid
4) who does not have a corresponding family, and there are two extra families (5
and 6) in the family file that do not have a corresponding dad.
DATA LIST LIST / famid * name (A4) inc. BEGIN DATA. 2 Art 22000 1 Bill 30000 3 Paul 25000 4 Karl 95000 END DATA. SORT CASES BY famid. SAVE OUTFILE = "dads.sav". DATA LIST LIST / famid faminc96 faminc97 faminc98. BEGIN DATA. 3 75000 76000 77000 1 40000 40500 41000 2 45000 45400 45800 5 55000 65000 70000 6 22000 24000 28000 END DATA. SORT CASES BY famid. SAVE OUTFILE = "faminc.sav". MATCH FILES FILE="dads.sav" /IN=fromdad /FILE="faminc.sav" /IN=fromfam /BY famid. LIST. CROSSTABS /TABLES= fromdad BY fromfam.
As you see above, we use /IN=fromdad to
create a 0/1 variable that indicates whether the resulting file contains a record with
data from the dads file. Likewise, we use /IN=fromfam
to indicate if the resulting file has a record from the faminc file.
The LIST and CROSSTABS then show us about the
mismatching records.
The output from the LIST command shows
us that when there were mismatching records. For famid 4, the value
of fromdad is 1 and fromfam is 0, as we would
expect since there was data from dads for famid 4, but
no data from faminc. Also, as we expect, this record has valid data
for the variables from the dads file (name and inc)
and missing data for the variables from faminc (faminc96 faminc97
and faminc98). We see the reverse pattern for famid
5 and 6.
FAMID NAME INC FAMINC96 FAMINC97 FAMINC98 FROMDAD FROMFAM 1.00 Bill 30000.00 40000.00 40500.00 41000.00 1 1 2.00 Art 22000.00 45000.00 45400.00 45800.00 1 1 3.00 Paul 25000.00 75000.00 76000.00 77000.00 1 1 4.00 Karl 95000.00 . . . 1 0 5.00 . 55000.00 65000.00 70000.00 0 1 6.00 . 22000.00 24000.00 28000.00 0 1
If we look at the fromdad and fromfam
variables, we can see that there are three records that have matching data,
one that has data from the dads only, and two records that have data from the
faminc
file only. The crosstab below shows us the same results, and is an easier way of
tallying the matching than manually tallying the matching.
FROMDAD by FROMFAM
FROMFAM Page 1 of 1
Count |
|
| Row
| 0| 1| Total
FROMDAD --------+------+------+
0 | | 2| 2
| | | 33.3
+------+------+
1 | 1| 3| 4
| | | 66.7
+------+------+
Column 1 5 6
Total 16.7 83.3 100.0
When matching files, we suggest that you use this
strategy to check the matching of the two files. If there are unexpected mismatched
records, then you should investigate to understand the cause of the mismatched records.
You can use SELECT IF to eliminate some
of the non-matching records. For example, if you wanted to keep just the records
where the dads matched with the family information, you could type
SELECT IF fromdad AND fromfam. LIST.
The results are shown below, including just the three matching records.
FAMID NAME INC FAMINC96 FAMINC97 FAMINC98 FROMDAD FROMFAM 1.00 Bill 30000.00 40000.00 40500.00 41000.00 1 1 2.00 Art 22000.00 45000.00 45400.00 45800.00 1 1 3.00 Paul 25000.00 75000.00 76000.00 77000.00 1 1
5.2 Mismatching records in one-to-many merge
SPSS handles the inclusion of mismatched records in a
one to-many merge differently than a one-to-one merge.
Remember that in a one-to-many merge, there is a file that has one
observation that matches to many observations in the other file; let us refer to these
as the one file and the many file. If there are
observations in the one file that do not match to the many
file, then these observations will not appear in the merged file at all. If there
are observations in the many file that do not match the one
file, those records will appear in the merged file. If this is what you
desire, then you can merge the files as illustrated in Section 3, and use the
/IN=
as illustrated in the prior section to track the matching. However, if you would
like mismatched records from the one and many file to
both appear in the merged file, then you can use the matching strategy outlined below.
Below we use our example to merge dads
with kids, and in this example we have mismatched records in both files.
Below we match the files to include all mismatched records in the merged file.
The parts that are different are indicated in
red.
DATA LIST LIST / famid * name (A4) inc. BEGIN DATA. 2 Art 22000 1 Bill 30000 3 Paul 25000 4 Karl 95000 END DATA. SAVE OUTFILE = "dads.sav". DATA LIST LIST / famid * kidname (A4) birth age wt * sex (A1). BEGIN DATA. 1 Beth 1 9 60 f 1 Bob 2 6 40 m 1 Barb 3 3 20 f 2 Andy 1 8 80 m 2 Al 2 6 50 m 2 Ann 3 2 20 f 3 Pete 1 6 60 m 3 Pam 2 4 40 f 3 Phil 3 2 20 m 5 Ted 1 4 35 m 5 Tess 2 2 18 f END DATA. SAVE OUTFILE="kids.sav". GET FILE="kids.sav". SORT CASES BY famid. SAVE OUTFILE="kids2.sav". GET FILE="dads.sav". SORT CASES BY famid. SAVE OUTFILE="dads2.sav". SAVE OUTFILE="dadid.sav" /KEEP=famid. MATCH FILES FILE="kids2.sav" /IN=inkid /FILE="dadid.sav" /BY famid . LIST. SAVE OUTFILE="temp.sav". MATCH FILES FILE="temp.sav" /TABLE="dads2.sav" /IN=dads /BY famid. LIST.
The section in red adds an extra step to the matching.
The purpose of this step is to add any values of famid that are
only in the dads file to the kids file. It does by
doing a one-to-one merge between dadid and the kids and
saves that file as temp. Since dadid just the famid
of all of the dads, this merge basically adds observations for any famid
that is in the dads file but not in the kids file, and
saves this as temp. Then, we can then merge temp with dads2
and temp will have a famid for every observation in the dads2
file. This assures that the resulting file will include all observations from the
dads
file, even if they do not have a matching record in the kids file.
The result is shown below. Indeed, the file contains the observation
for the
dad Karl who does not have any matching kids. If we omitted the
extra code in this step, that record would not have been included in this file.
FAMID KIDNAME BIRTH AGE WT SEX INKID NAME INC DADS 1.00 Beth 1.00 9.00 60.00 f 1 Bill 30000.00 1 1.00 Bob 2.00 6.00 40.00 m 1 Bill 30000.00 1 1.00 Barb 3.00 3.00 20.00 f 1 Bill 30000.00 1 2.00 Andy 1.00 8.00 80.00 m 1 Art 22000.00 1 2.00 Al 2.00 6.00 50.00 m 1 Art 22000.00 1 2.00 Ann 3.00 2.00 20.00 f 1 Art 22000.00 1 3.00 Pete 1.00 6.00 60.00 m 1 Paul 25000.00 1 3.00 Pam 2.00 4.00 40.00 f 1 Paul 25000.00 1 3.00 Phil 3.00 2.00 20.00 m 1 Paul 25000.00 1 4.00 . . . 0 Karl 95000.00 1 5.00 Ted 1.00 4.00 35.00 m 1 . 0 5.00 Tess 2.00 2.00 18.00 f 1 . 0
5.3 Variables with the same name, but different information
Below we have the files with the information about the
dads and family, but look more closely at the names of the variables. In the
dads
file, there is a variable called inc98, and in the family file there are
variables inc96, inc97 and inc98. Let’s go ahead
and merge these files and see what SPSS does.
DATA LIST LIST / famid * name (A4) inc98. BEGIN DATA. 2 Art 22000 1 Bill 30000 3 Paul 25000 END DATA. SAVE OUTFILE = "dads.sav". DATA LIST LIST / famid inc96 inc97 inc98. BEGIN DATA. 3 75000 76000 77000 1 40000 40500 41000 2 45000 45400 45800 END DATA. SAVE OUTFILE = "faminc.sav". GET FILE="dads.sav". SORT CASES BY famid. SAVE OUTFILE="dads2.sav". GET FILE="faminc.sav". SORT CASES BY famid. SAVE OUTFILE="faminc2.sav". MATCH FILES FILE="dads2.sav" /FILE="faminc2.sav" /BY famid. LIST.
The results are shown below. As you see, the variable inc98
has the data from the dads file, the file that appeared first in the MATCH
FILES command. When you match files that have the same variable, SPSS will
use the values from the file that appears earliest in the MATCH FILES
command.
FAMID NAME INC98 INC96 INC97 1.00 Bill 30000.00 40000.00 40500.00 2.00 Art 22000.00 45000.00 45400.00 3.00 Paul 25000.00 75000.00 76000.00
There are a couple of ways you can solve this problem.
Solution #1. The most obvious solution
is to choose variable names in the original files that will not conflict with each other.
However, you may receive files where the names have already been chosen.
Solution #2. You can rename the
variables in the MATCH FILES command (which renames the variables before
doing the matching). This allows you to select variable names that do not conflict
with each other, as illustrated below.
GET FILE="dads.sav". SORT CASES BY famid. SAVE OUTFILE="dads2.sav". GET FILE="faminc.sav". SORT CASES BY famid. SAVE OUTFILE="faminc2.sav". MATCH FILES FILE="dads2.sav" /RENAME=(inc98 = dadinc98) /FILE="faminc2.sav" /RENAME=(inc96 inc97 inc98 = faminc96 faminc97 faminc98) /BY famid. LIST.
As you can see below, the variables were renamed as we
specified.
FAMID NAME DADINC98 FAMINC96 FAMINC97 FAMINC98 1.00 Bill 30000.00 40000.00 40500.00 41000.00 2.00 Art 22000.00 45000.00 45400.00 45800.00 3.00 Paul 25000.00 75000.00 76000.00 77000.00
5.4 The same variables with different dictionary information
This problem is similar to the one outlined above. In
this example, we have two variables with the same name and the same
information, but with different dictionary information associated with them.
This dictionary information could include value labels and/or variable labels.
As with the example above, SPSS will take the information from the file listed
first in the MATCH FILES command. No error or warning message will be
issued to let you know that the information from the variable in the later
file has been lost. The solution to this problem is to list first in the MATCH
FILES command the file with the dictionary information that you want in
the resulting file.
5.5 You have run the ADD FILES command, and nothing happened
If you run just the ADD FILES command, as
shown below, SPSS will not do anything. However, you will see a note in
the lower right corner of the data editor saying "transformation
pending".
ADD FILES FILE="dads.sav" /FILE="moms.sav"
Solution: The solution is to add either the execute
command or a procedure command that will force the execution of the
transformation, such as the list command or the crosstab
command.
ADD FILES FILE="dads.sav" /FILE="moms.sav" execute.
6. For more information
- For more information about Match Merging data files, see
the MATCH FILES command in SPSS Syntax Reference Guide. -
For information on concatenating data
files, see the SPSS Learning Module on Concatenating Data Files in SPSS