SPSS TUTORIALS
MATCH FILES is an SPSS command mostly used for merging data holding similar cases but different variables. For different cases but similar variables, use ADD FILES.
MATCH FILES is also the way to go for a table lookup similar to VLOOKUP in Excel.
Merging two datasets by id, which is a unique case identifier.
SPSS Match Files - Basic Use
- The most common scenario for
MATCH FILESare two data files or datasets holding different variables on similar cases. - Each case has a unique id (identifier) in each data source. This id tells SPSS which case from one data source corresponds to which case from the other. Corresponding cases become a single case in the merged data.
- The syntax below demonstrates a very basic
MATCH FILEScommand. If you're not comfortable working with multiple datasets, have a look at SPSS Datasets Tutorial 1 - Basics.
SPSS Match Files Syntax Example 1
*1. Create test data 1.
data list free/id test_1.
begin data
3 8 4 5 6 6
end data.
dataset name test_1.
*2. Create test data 2.
data list free/id test_2.
begin data
1 4 3 9 4 8
end data.
dataset name test_2.
*3. Match test_1 and test_2.
match files file = test_1 / file = test_2
/by id.
execute.
*4. Close all but merged dataset.
dataset close test_1.
dataset close test_2.
data list free/id test_1.
begin data
3 8 4 5 6 6
end data.
dataset name test_1.
*2. Create test data 2.
data list free/id test_2.
begin data
1 4 3 9 4 8
end data.
dataset name test_2.
*3. Match test_1 and test_2.
match files file = test_1 / file = test_2
/by id.
execute.
*4. Close all but merged dataset.
dataset close test_1.
dataset close test_2.
SPSS Match Files - Table
- A second common scenario is having a file with respondents and their zip codes. Note that there are probably duplicate zip codes in the respondents file.
- If we also have a table with the city (or region) indicated by each zip code, we can merge these into the respondent data. In this case we can use
MATCH FILESwith oneFILE(with duplicates) and oneTABLE(without duplicates). - The syntax below demonstrates how to do this. Note that
*refers to the active dataset.
SPSS Match Files Syntax Example 2
*1. Table holding zip codes and cities.
data list free/zip_code (f3.0) city(a20).
begin data
123 'Amsterdam' 456 'Haarlem' 789 "'s Hertogenbosch"
end data.
dataset name cities.
*2. Mini data holding respondents and their zip codes.
data list free /id zip_code.
begin data
1 123 2 123 3 123 4 456 5 456 6 456 7 789 8 789 9 789
end data.
*3. Add cities to active dataset using zip_code.
match files file * / table cities
/by zip_code.
execute.
*4. Close all but merged data.
dataset close cities.
data list free/zip_code (f3.0) city(a20).
begin data
123 'Amsterdam' 456 'Haarlem' 789 "'s Hertogenbosch"
end data.
dataset name cities.
*2. Mini data holding respondents and their zip codes.
data list free /id zip_code.
begin data
1 123 2 123 3 123 4 456 5 456 6 456 7 789 8 789 9 789
end data.
*3. Add cities to active dataset using zip_code.
match files file * / table cities
/by zip_code.
execute.
*4. Close all but merged data.
dataset close cities.
SPSS Match Files - One Data Source
- Match files can also be used with a single data source. This is often used for reordering variables and/or dropping variables..
- One option here is using the
KEEPsubcommand. It basically means “drop all variables except ...”. - Alternatively, the
DROPsubcommand means “keep all variables except ...”.Note that these subcommands can be used in a similar way in aGET FILE,SAVEandADD FILEScommand. - The TO and ALL keywords are convenient here. However, in this case
ALLmeans “all variables that haven't been addressed yet” rather than simply all variables.
SPSS Match Files Syntax Example 3
*1. Single case test data with wrong variable order.
data list free / v1 to v3 v5 v6 v7 v8 v4.
begin data
0 0 0 0 0 0 0 0
end data.
* 2. Reorder variables. Note the TO and ALL keywords here.
match files file * / keep v1 to v3 v4 all.
execute.
data list free / v1 to v3 v5 v6 v7 v8 v4.
begin data
0 0 0 0 0 0 0 0
end data.
* 2. Reorder variables. Note the TO and ALL keywords here.
match files file * / keep v1 to v3 v4 all.
execute.
SPSS Match Files - Rules
- Instead of merging two data sources, you may specify up to 50 data sources in one
MATCH FILEScommand. - More than one variable may be used to uniquely identify cases. We'll hereafter refer to these as the
BYvariables since they're used on theBYsubcommand. An common example are respondents having ahousehold_idand amember_idindicating the nth member of each household. Both variables will probably have many duplicates but their combination should uniquely identify each respondent. - All data must be sorted on the
BYvariable(s) ascendingly. In case of doubt, runSORT CASESbefore proceeding. - The order of the merged variables is the order in which they're encountered. This implies that the order in which data sources are specified matters for the end result. For a demo, run the first syntax example once with
file = test_1 / file = test_2and then again withfile = test_2 / file = test_1. - Make sure there's no duplicate variable names across data sources. In this case, values on duplicate variables that are first encountered overwrite those that are encountered later. Annoyingly, SPSS does not throw a warning if this happens.
THIS TUTORIAL HAS 18 COMMENTS:
By Yaser Pourdavar on March 5th, 2016
Hi,
When I use "Match File" syntax, it says:
"There is no working file to restore. You must define a working file before proceeding with your analysis. "
I don't know what the problem is.
I opened my two files but it didn't solve anything.
By Ruben Geert van den Berg on March 5th, 2016
Hi Yaser!
It sounds as if your MATCH FILES command doesn't properly address your datasets. Did you run
DATASET NAME ...commands right after opening either file?This is usually a good idea as illustrated in our syntax examples. Then use the two dataset names you chose in your MATCH FILES command.
By Yaser Pourdavar on March 5th, 2016
Thanks Ruben.
I found the answer: I should use the names in the brackets.
By Ruben Geert van den Berg on March 6th, 2016
Hi Yaser!
Right! "The name in the brackets" is known as the DATASET NAME in SPSS. You can set it yourself by running something like
*Open data file.get file 'd:/data.sav'.
*Set dataset name.dataset name maindata.
You'll now see "maindata" in square brackets and you can use it to refer to these data in commands such as MATCH FILES and AGGREGATE.
If you open data without syntax (bad idea), SPSS will set a DATASET NAME for you such as "Dataset1".
However, when working on a single data file, we often prefer not setting any DATASET NAME at all because we can close the data more easily like so.
Hope that helps!
By Hadi on March 15th, 2016
Hi there,
I am a novice at SPSS.
I have 2 data sets for two different studies with the same variables and of course different cases. Both are in xls format. I prepared the first one for SPSS where I did a lot of recoding and defined variable labels and missing values. Now, I'm thinking that there might be a way to tell SPSS to do the same things for the second file as well and save time. Any idea?