At first we have to go to menu bar and select. “Data”→”Merge file”→”Add variables”. Then there open a new box named “Add variables to data set1” and we have to select “An external spss data file” and click on “Browse” and select the second data set which we want to combine with “data set1″. Multiple SPSS datasets can be merged at a time. Not all variables from the datasets to be merged need to be included in the new, merged dataset. An in keyword or subcommand is available to create a new variable in the merged data file that indicates from which file each case came. How to combine two datasets in SPSS.

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 FILES are 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 FILES command. 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.

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 FILES with one FILE (with duplicates) and one TABLE (without duplicates).
  • The syntax below demonstrates how to do this. Note that * refers to the active dataset.
Merge Data Spss

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.

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 KEEP subcommand. It basically means “drop all variables except ...”.
  • Alternatively, the DROP subcommand means “keep all variables except ...”.Note that these subcommands can be used in a similar way in a GET FILE, SAVE and ADD FILES command.
  • The TO and ALL keywords are convenient here. However, in this case ALL means “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.

SPSS Match Files - Rules

  • Instead of merging two data sources, you may specify up to 50 data sources in one MATCH FILES command.
  • More than one variable may be used to uniquely identify cases. We'll hereafter refer to these as the BY variables since they're used on the BY subcommand. An common example are respondents having a household_id and a member_id indicating 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 BY variable(s) ascendingly. In case of doubt, run SORT CASES before 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_2 and then again with file = 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.

1. Introduction

When you have two data files, you may want to combine them by stacking them one on top of the other (referred to as concatenating files). Below we have a file called dads and a file containing moms.

Below we have stacked (concatenated) these files creating a file we called momdad. These examples will show how to concatenate files in SPSS.

2. Concatenating the moms and dads

The SPSS program below creates an SPSS data file called dads.sav and then creates a file called moms.sav.

Below we see the output of the SPSS commands above showing that the data was read correctly.

We can combine the files using the add filescommand as shown below.

The output of these commands are shown below.

The output from this program shows that the files were combined properly. The dads and moms are stacked together in one file. But, there is a little problem. We can’t tell the dads from the moms. Let’s try doing this again but in such a way that we can tell which observations are the moms and which are the dads.

3. Concatenating the moms and dads, a better example

In order to tell the dads from the moms, let’s ask SPSS to create a variable called dad that will be 1 for observations from dads.sav, and a variable called mom that will be 1 for observations from moms.sav . As you see below, this is accomplished using /in=dad and /in=mom option. We then create a variable called momdad in the dads and moms data files that will contain 'dad' for the dads data file and 'mom' for the moms data file. When we combine the two files together the momdad variable will tell us who the moms and dads are.

The output of these commands is shown below.

Here we get a more desirable result, because we can tell the dads from the moms by looking at the variable momdad. In this example, we could have skipped the step of creating the momdad variable and just referred either to the mom dummy variable or the dad dummy variable. We wanted to illustrate the strategy of creating momdad if you ever combined three or more files, in which case the dummy variables would not be as useful.

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.

As you can see, the variables in the new file are now in the order name, famid and inc.

5. Problems to look out for

These above examples cover situations where there are no complications. However, look out for these complications.

5.1 The two data files have different variable names for the same thing

For example, income is called dadinc and in the dads file and called mominc in the moms file, as shown below.

You can see the problem illustrated below.

Solution #1. The most obvious solution is to choose appropriate variable names for the original files (i.e., name the variable inc in both the moms and dads file). This solution is not always possible since you might be concatenating files that you did not originally create. To save space, we omit illustrating this solution.

Solution #2. If solution #1 is not possible, we can fix this problem after we combine the files as shown below. We use the momdad variable to assign dadinc to inc for the dads, and mominc to inc for the moms.

The results are shown below, where inc now has the income for both the moms and dads.

Solution 3. Another way you can fix this is by using the rename subcommand on the add files command to rename the variables just before the files are combined.

You can see the results below. This solution is more elegant than renaming the variables after the fact.

5.2 The two data files have variables with the same name but different types

In the dads data set below, famid is a numeric variable. However, in the moms data set, it is a string variable. If we try to concatenate these files, we will get the error message shown below.

Variable(s) with conflicting type:

Result Type Type
FAMID FAMID NUM FAMID S1
Codes: NUM = Numeric, SN = String of length N.

>Error # 5127
>Mismatched variable types on the input files.
>This command not executed.

The solution is to change one of the variables to be the same type as the other. In most cases, you will want to change the string variable to be numeric, as shown below.

5.3 The two data files have string variables with the same name but different lengths

In all of the examples above, the variable name was input with the format A4 indicating name is an alphabetic (string) variable with a length of 4. What would happen if name in the dads file was A3 and name in the moms file was A4. This is illustrated below.

When we combine these files, SPSS gives us the error message shown below.

As you can see, SPSS considered this a serious error and did not merge the files.

Solution #1. Define the variables to have the same length in the original files (i.e., use the A4 format for both the moms and dads file). This is the simplest solution if you are creating the files yourself. We will omit illustrating this solution to save space.

Solution #2. You may not have created the original raw data files, so solution #1 may not be possible for you. In that case, you can create a new variable in each file that has the same length and will be compatible when you merge the files. Below we illustrate this strategy.

For the dads file, a new variable called name2 is created with a length of A4 the value of name is copied to name2. Finally, we save the file as dads2 and drop the variable name. The same is done for the moms file. Then, the files dads2 and moms2 can be combined now that name2 is compatible between the two files.

The results are shown below.

Note that if the variable is a numeric variable, then SPSS will still concatenate the file even if the lengths of the two numeric variables is not the same. The length of the variable in the first file listed in the command will be the length used in the concatenated file.

5.4 The two data files have variables with the same name but different codes

This problem is similar to the problem above, except that it has an additional wrinkle, illustrated below. In the dads file there is a variable called fulltime that is coded 1 if the dad is working full time, 0 if he is not. The moms file also has a variable called fulltime that is coded Y is she is working full time, ad N if she is not. Not only are these variables of different types (character and numeric), but they are coded differently as well.

We forego trying to combine these files since we already know that this will not work (based on the prior example). How can we solve this?

Solution #1. Code the variables in the two files in the same way. For example, code fulltime using 0/1 for both files with 1 indicating working fulltime. This is the simplest solution if you are creating the files yourself. We will omit illustrating this solution to save space.

Solution #2. You may not have created the original raw data files, so solution #1 may not be possible for you. In that case, you can create a new variable in each file that has the same coding and will be compatible when you merge the files. Below we illustrate this strategy.

For the dads file, we make a variable called full that is the same as fulltime, and save the file as dads2, dropping fulltime. For the moms, we create full by recoding fulltime, and save the file as moms2, also dropping fulltime. The files dads2 and moms2 both have the variable full coded the same way (0/1 where 1=works full time) so we can combine those files together.

The results are shown below.

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'.

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.

6. For more information

Merge Data Sets In Spss

  • For more information about concatenating data files, see the add files command in the SPSS Syntax Reference Guide.
  • For information on match merging data files, see the SPSS Learning Module on Match Merging SPSS Data Files.

Coments are closed

Most Viewed Posts

  • Tor For Chrome Os
  • Prime Video Friends
  • What's My Address

Scroll to top