

































5

Graduate Student Journal of Psychology
2022, Vol. 19

Copyright 2022 by the Department of Counseling and Clinical Psychology
Teachers College, Columbia University

Merging Multiple and/or Divergent Datasets using 
SPSS: A Method Review and Tutorial

Lillian M. Audette, Katherine A. Johnson, Marie S. Hammond, Jenna S. Lehmann, and Michael Oyeteju,
Department of Psychological Sciences & Counseling, Tennessee State University

 Many researchers face problems merging multiple 
datasets with divergent or mismatched cases and/or 
variables to form a more complete dataset. This is par-
ticularly true when conducting research with humans 
in which data is obtained from multiple sources, such 
as self-report questionnaires, institutional databases, 
and/or collateral contacts. Challenges that occur in 
ȣȠǩǹǩΦǩǿǠ�ǏƺȠƺ�ǟȖȅǾ�ǏǩΚǓȖǠǓǿȠ�șȅȣȖǉǓș�ǩǿǉǹȣǏǓ�ǏǩАǓȖǓǿȠ�
șȠȖȣǉȠȣȖǓșॹ�ǏǩАǓȖǓǿȠ�ǟȅȖǾƺȠșॹ�ȅȖ�ȠǦƺȠ�ǏƺȠƺ�ǩș�ǩǿǉȅǾȒǹǓȠǓ�
when compared to the original dataset. 
 The present article focuses on merging multiple 
datasets either generated longitudinally or containing 
ǏǩАǓȖǓǿȠ�ΚƺȖǩƺǈǹǓșঀ��ΠƺǾȒǹǓș�ȅǟ�ȠǦǓ�ǏƺȠƺșǓȠș�Ƞȅ�ΛǦǩǉǦ�
we refer include pre-/post-testing for intervention 
research, longitudinal research utilizing two or more 
waves of data, or instances in which data from multi-
ple sources related to the same case. These datasets are 
likely to be mismatched in that they may not contain 
the same variables (in the instance of adding cases) or 
may not include the same cases (in the instance of add-
ing variables). Other relevant datasets might be consid-
ǓȖǓǏ� ৚ǉȅǾȒǹǓΠ৛� ǩǿ� ȠǦƺȠ� ȠǦǓ�ǏƺȠƺșǓȠș� ǉȅǿȠƺǩǿ�ǏǩАǓȖǓǿȠ�
ΚƺȖǩƺǈǹǓș�ƺǿǏ�ǦƺΚǓ�ǏǩАǓȖǓǿǉǓș�ǩǿ�ǉƺșǓșঀ�'ȅȖ�ǓΠƺǾȒǹǓॹ�ǩȠ�
may be that graduate students working as a part of a 
larger research team are tasked with managing the data 
and will need to understand the process and steps to 
ǓГǉǩǓǿȠǹΡ�ǉȅǾǈǩǿǓ�ȠǦǓșǓ�ǏƺȠƺșǓȠșঀ�
 It should be noted that in order to align with cur-
rent terminology, rather than describing these datasets 
ƺș� ৚ǉȅǾȒǹǓΠॹ৛� ΛǦǩǉǦ� Ǧƺș� ƺ� șȒǓǉǩЙǉ� ǾǓƺǿǩǿǠ� ΛǩȠǦǩǿ�
the social sciences (Guha et al., 2009), we will use the 
term “divergent” to refer to datasets that do not match 
variable for variable, time frame by time frame, and/or 
case for case. Further, the term “merge” will be used in 

this article rather than “combining” in that combin-
ing implies a common data structure that facilitates 
bringing the datasets together, while the term “merg-
ǩǿǠ৛�ȠΡȒǩǉƺǹǹΡ�ȖǓǹǩǓș�ȅǿ�ƺ�৚ǷǓΡ৛�ȅȖ�৚ǩǏǓǿȠǩЙǓȖ৛�ΚƺȖǩƺǈǹǓ�
that provides the common link between the datasets. 
This is distinct from data fusion, which occurs when 
multiple data sources are integrated without full pres-
ervation of all data (Haghighat et al., 2016). The goal 
of the process to be described is that the datasets are 
merged in such a way that all data is retained from its 
ȅȖǩǠǩǿƺǹ�ǏƺȠƺșǓȠ�ǩǿ�ȠǦǓ�Йǿƺǹ�ȒȖȅǏȣǉȠঀ�
 When it comes to merging two or more datasets 
that contain mismatching and/or missing cases, sever-
al complications could occur – resulting in compro-
mised analyses. For instance, mismatched data may in-
dicate that data were added to the dataset incorrectly. 
Additionally, missing data can result in decreased anal-
ysis power or, depending on the analysis required, may 
prevent an analysis from being completed entirely. 
Accurate dataset merging is vital to prevent potential 
errors from being introduced, prevent data loss, and 
allow researchers to have more reliable and valid data-
sets with which to conduct analyses. Thus, the accu-
rate merging of discrete datasets allows researchers to 
conduct analyses on a more accurate dataset, therefore 
producing results that have greater reliability, are more 
generalizable, and are more replicable. For the purpos-
es of this article, the focus will stay on mismatched or 
divergent dataset merging using SPSS software.

Literature Review
 To assess the need for an informative guide regard-
ing how to merge mismatched datasets, the authors 
chose two separate approaches to conducting the lit

'ȅȖ�ǾƺǿΡ�ȖǓƺșȅǿșॹ�ȖǓșǓƺȖǉǦǓȖș�ȒǹƺǉǓ�ȠǦǓǩȖ�ǏƺȠƺ�ǩǿ�ǾȣǹȠǩȒǹǓ�ǓǹǓǉȠȖȅǿǩǉ�ǏƺȠƺșǓȠș�ƺǿǏ�ǹƺȠǓȖ�ЙǿǏ�ȠǦƺȠ�ȠǦǓΡ�ΛǩșǦ�Ƞȅ�ǉȅǾǈǩǿǓ�ȠǦȅșǓ�ǏƺȠƺșǓȠș�
for a particular analysis. This article reports on an analysis of the extant literature on combining multiple and/or divergent datasets 
ƺǿǏ�ȒȖȅΚǩǏǓș�ǈȅȠǦ�ƺ�ȠȣȠȅȖǩƺǹ�ƺǿǏ�ȖǓǹƺȠǓǏ�șΡǿȠƺΠ�ȠǦƺȠ�ǉȅǾǈǩǿǓș�ǿȅǿেǾƺȠǉǦǩǿǠ�ǏƺȠƺșǓȠș�শǩঀǓঀॹ�ǟȖȅǾ�ǏǩАǓȖǓǿȠ�șȅȣȖǉǓșষ�ǩǿ�șȣǉǦ�ƺ�ΛƺΡ�
ȠǦƺȠ�ƺǹǹ�ǏƺȠƺ�ǩș�ǉȅǿȠƺǩǿǓǏ�ǩǿ�ȠǦǓ�Йǿƺǹ�ǉȅǾǈǩǿǓǏ�ǏƺȠƺșǓȠ�ΛǩȠǦ�ǩǏǓǿȠǩЙǉƺȠǩȅǿ�ȅǟ�ǏƺȠƺ�șȅȣȖǉǓঀ�eǦǩș�șΡǿȠƺΠ�ȖǓȒȖǓșǓǿȠș�ƺǿ�ǩǾȒȖȅΚǓǾǓǿȠ�
over the existing SPSS (v23-v27) combining dataset routines in terms of 1) expanding the types of datasets that can be combined, 2) 
ǉȅǾȒǹǓȠǓǿǓșș�ȅǟ�ȠǦǓ�ȖǓșȣǹȠǩǿǠ�ǏƺȠƺșǓȠॹ�ƺǿǏࢴ�ষ�ǟƺǉǩǹǩȠƺȠǩǿǠ�ǾƺǿƺǠǓǾǓǿȠ�ȅǟ�ǾȣǹȠǩȒǹǓ�ƺǿǏইȅȖ�ǏǩΚǓȖǠǓǿȠ�ǏƺȠƺ�șǓȠșঀ��ǿ�ƺǏǏǩȠǩȅǿƺǹ�ǈǓǿǓЙȠ�
of using this process is the incorporation of a method to test the accuracy of the merged data and thus verify the data quality. The 
SPSS syntax along with instructions and examples are reviewed in the article.
Keywords: SPSS, syntax, datasets, merging, longitudinal data



6

AUDETTE ET AL. 

erature searches. First, a literature search using the re-
search databases EBSCOHost (which is an umbrella 
dataset encompassing approximately 70 individual 
publisher databases), Web of Science, Sage Premier, 
Science Direct, and Google Scholar was conducted. 
The Boolean search phrase utilized for this search was 
as follows: SPSS AND dataset AND (merg* OR com-
bin* OR concatenat* OR mung* OR wrangl*). For 
databases that do not use the Boolean system exactly 
(e.g., Google Scholar), these search terms were added 
manually to the advanced search settings of each data-
base. The terms mung and wrangle were incorporated 
once we discovered that these terms are used outside 
of psychology to describe the data-cleaning process, 
which includes dataset merging (Braun et al., 2018; 
Endel & Piringer, 2015; Rattenbury et al., 2017). The 
above search phrase and its individual search terms 
were used to identify articles containing these terms 
in their abstracts. The resulting list from EBSCOHost 
provided 27 peer-reviewed articles, while the Web of 
Science search produced 24. None of these results ac-
knowledged the problem of merging mismatched or 
divergent datasets. A second literature search was con-
ducted using Google and Google Scholar. Since Goo-
gle Scholar uses a limited form of Boolean, the search 
phrase was changed to the following: SPSS AND data-
set AND (merge OR merged OR merging OR com-
bine OR combination OR combining OR combined 
OR mung OR munging OR munged OR concate-
nate OR concatenated OR concatenating OR wrangle 
OR wrangled OR wrangling). Other combinations of 
these search terms were also used (e.g., data wrangling, 
data munging, merging datasets). The results of this 
search found examples of both peer-reviewed articles 
and books that address dataset merging, but neither 
focused on mismatched dataset merging nor used 
SPSS for this purpose.
 Previous research literature on merging data-
sets for analyses of psychological data primarily used 
the Statistical Analysis System (SAS) software (Foley, 
1998; Foley, 2005) for this process, rather than SPSS. 
Parenthetically, research literature reviewed the steps 
Ƞȅ�ȅǈȠƺǩǿ�ƺ�ǾǓȖǠǓǏ�ǏƺȠƺșǓȠ�ΛǩȠǦ�ȠǦǓ�șȒǓǉǩЙǉ�ΚƺȖǩƺǈǹǓș�
and cases of interest. Again, however, no mention was 
made of merging mismatched or divergent datasets. 
Scholarly works and textbooks were reviewed to iden-
tify potentially helpful instruction in this area. Both 
works that did focus on the use of SPSS and works 

that did not specify which program they recommend-
ed using covered only basic dataset merging practices. 
These sources either did not provide information on 
ȠǦǓ� ǏǩГǉȣǹȠǩǓș� ǹǩǷǓǹΡ� Ƞȅ� ǈǓ� ǓǿǉȅȣǿȠǓȖǓǏ� ǩǿ� ǾǓȖǠǩǿǠ�
mismatched or divergent datasets (Stehlik-Barry & 
Babinex, 2017) or they only emphasized the impor-
tance of considering these challenges without provid-
ing guidance or details on its technicalities (Braun et 
al., 2018; Endel & Pringer, 2015; Rattenbury et al., 
2017). Guides on merging datasets for non-psycholog-
ical research purposes have also not focused on SPSS, 
but rather on software programs such as R and Python 
(Ojeda et al., 2014). 
 There does, however, appear to be plenty of gray 
literature (articles not formally published by commer-
cial academic publishers) surrounding this topic in 
SPSS (Haddaway et al., 2015, p. 1). Examples of this 
gray literature include 1) websites that host questions, 
discussions, and video tutorials related to the common 
practices for merging datasets (Coyer, 2013; Truong, 
2016), 2) academic websites with instructions (Cole-
man, n.d.; Glynn, 2002), 3) basic commands given by 
International Business Machine (IBM; IBM, n.d.); and 
4) other SPSS tutorial websites (Spss-tutorials.com, 
n.d.). However, most of the gray literature provides 
limited basic commands such as MERGE DATASETS 
or COMPARE DATASETS, which do not adequately 
ƺǏǏȖǓșș�șȒǓǉǩЙǉ�ǏǩΚǓȖǠǓǿȠ�ȅȖ�ǾǩșǾƺȠǉǦǓǏ�ǏƺȠƺșǓȠ�ǾǓȖǠ-
ing issues encountered by researchers.

Dataset Merging Methods Requirements
To address the above-mentioned issues, the extant lit-
ǓȖƺȠȣȖǓ� ȒȖǓΚǩȅȣșǹΡ� ǩǏǓǿȠǩЙǓǏ�Λƺș� ȖǓΚǩǓΛǓǏ� Ƞȅ� șȣȖǟƺǉǓ�
existing guidelines and/or requirements. This type of 
information was found primarily in the gray literature 
(e.g., Coleman, n.d.; Coyer, 2013; DeCator, 2015; 
2�Eॹ� ǿঀǏঀআ� eȖȣȅǿǠॹ� ǿঀǏঀষঀ� ^ǩΠ� ȖǓȕȣǩȖǓǾǓǿȠș� ȅǟ� ǓАǓǉ-
tive dataset merging methods to ensure the accuracy 
ȅǟ�ǏƺȠƺ�ΛǓȖǓ� ǩǏǓǿȠǩЙǓǏ� ǟȖȅǾ� ȠǦǩș� ǹǩȠǓȖƺȠȣȖǓঀ�eǦǓ�ЙȖșȠ�
three requirements are basic expectations of any data-
set merging method (Coleman, n.d.; Coyer, 2013). 
The next three requirements are more complex and are 
șȒǓǉǩЙǉ� Ƞȅ�ǾǓȖǠǩǿǠ�ǏƺȠƺșǓȠș� ȠǦƺȠ�ǉȅǿȠƺǩǿ�ȅΚǓȖǹƺȒȒǩǿǠ�
variables as well as overlapping cases, and whose cases 
sometimes contain mismatched data (DeCator, 2015; 
IBM, n.d.; Truong, n.d.). These six requirements have 
implications for the structure and variables contained 
ǩǿ�ȠǦǓ�Йǿƺǹ�ǉȖǓƺȠǓǏ�ǏƺȠƺǈƺșǓॹ�ƺș�Λǩǹǹ�ǈǓ�ǏǩșǉȣșșǓǏ�ǈǓǹȅΛঀ



7

MERGING MULTIPLE AND/OR DIVERGENT DATASETS IN SPSS

Requirement 1: Include All Cases
eǦǓ�ǾǓȖǠǓǏ�ǏƺȠƺǈƺșǓ� শЙǿǩșǦǓǏ�ȒȖȅǏȣǉȠষ� șǦȅȣǹǏ�ǉȅǿ-
tain all the cases from Dataset 1 and all the cases from 
Dataset 2 (and any additional datasets). The case data 
from each respective dataset should be faithfully repli-
cated in the merged database.
Requirement 2: Include All Variables
The merged database should hold all the variables 
present in all datasets, as well as all variables present in 
only one of the datasets. The width, number of dec-
ǩǾƺǹ�ȒǹƺǉǓșॹ� ǹƺǈǓǹșॹ�ΚƺǹȣǓ� ǹƺǈǓǹșॹ�ǾǩșșǩǿǠ� șȒǓǉǩЙǉƺȠǩȅǿॹ�
ǉȅǹȣǾǿ�ΛǩǏȠǦॹ�ƺǹǩǠǿǾǓǿȠॹ�ǾǓƺșȣȖǓ�șȒǓǉǩЙǉƺȠǩȅǿॹ�ƺǿǏ�
role of each variable should be faithfully replicated in 
the merged database. 
Requirement 3: Variable Settings Fidelity
The merging method integrates variables without 
changing the variable regardless of the type (numeric, 
string) and regardless of measure (scale, nominal, or-
dinal).
 In addition to the three basic requirements listed 
above (Coleman, n.d.; Coyer, 2013), the merged data-
base should provide researchers with three additional 
pieces of information. The following requirements are 
more complex, intending to assist researchers when 
the datasets to be merged are suspected of containing 
unique, overlapping, or mismatched cases, all of which 
șǦȅȣǹǏ�ǈǓ�ǩǿǉǹȣǏǓǏ�ǩǿ�ȠǦǓ�Йǿƺǹ�ǏƺȠƺǈƺșǓঀ�
Requirement 4: Indication of Unique Cases by 
Dataset 
A merged database should provide information as 
to when a case is unique to Dataset 1 or Dataset 2 
(rather than being present in both original datasets), 
and which dataset it originates from. For example, a 
merged dataset should tell us if a case with ID 13 exists 
uniquely in Dataset 1 or Dataset 2. 
Requirement 5: Indication of Overlapping Cases
A merged database should indicate when a case in 
Dataset 1 is also a case in Dataset 2. For example, a 
merged database should tell us if a case with ID 16 
originated in Dataset 1 and whether it also exists in 
Dataset 2. 
Requirement 6: Indication of Mismatched Data by 
Case
A merged database should tell us when a case in Data-
set 1 and a case in Dataset 2 have a matching ID but 
contain mismatching data while retaining both in-
stances of data in the merged database. Detecting mis-
matching data is a crucial requirement when merging 

datasets whose data, cases, or variables do not perfect-
ly match using SPSS. For example, a merged database 
should tell us if a case with ID 16 in Dataset 1 has 
“Brief Cognitive Behavioral Therapy” entered for the 
“Treatment” variable, but a case with ID 16 in Dataset 
2 has “Brief Object Relations Therapy” entered for the 
“Treatment” variable. In addition, both instances of 
data should be preserved within the merged database. 
For example, we should see both forms of cases with 
ID 16 in the merged database, one with “Brief Cogni-
tive Behavioral Therapy” and one with “Brief Object 
Relations Therapy” entered for “Treatment.” Thus, 
an indicator of match/mismatch and the retention of 
both instances of the data should appear in the merged 
database. 
 To clarify - several of the six requirements (dis-
cussed above) should be represented by one or more 
variables within the merged dataset. New variables 
within the dataset should be generated to represent at 
least one, if not more, of the six requirements. Each of 
the six requirements should be encoded into a variable 
ΛǩȠǦǩǿ� ȠǦǓ� ǏƺȠƺșǓȠঀ� eǦǓ� Йǿƺǹ�ǾǓȖǠǓǏ� ǏƺȠƺșǓȠ� șǦȅȣǹǏ�
ǩǿǉǹȣǏǓॹ� ǩǿ�ƺ� șȒǓǉǩЙǉ�ΚƺȖǩƺǈǹǓॹ� ȠǦǓ�ǏƺȠƺșǓȠ�ǿƺǾǓ�ǟȖȅǾ�
which each case was taken. This must be a unique 
variable, separate from the other variables. For exam-
ple, for one of the authors’ research projects, data was 
ǠƺȠǦǓȖǓǏ� ǟȖȅǾ�ȠǦȖǓǓ�ǏǩАǓȖǓǿȠ� ǩǿșȠǩȠȣȠǩȅǿșঀ� 2ǿ� ȠǦǩș� ǩǿ-
stance, in merging the data a new variable was created 
ΛǦǩǉǦ�ǩǏǓǿȠǩЙǓǏ�ȠǦǓ�ǏƺȠƺșǓȠইǩǿșȠǩȠȣȠǩȅǿ�ȅǟ�ȅȖǩǠǩǿঀ�eǦǩș�
variable, included in the merged dataset, provides in-
formation about the origin of the data and thus met 
this requirement. Thus, the dataset of origin variable 
should not rely on non-encoded methods (such as case 
origin based upon the cases’ ordering - i.e., SPSS row 
number) within the dataset. If case origin is denoted 
ǈΡ�ǉƺșǓ�ȅȖǏǓȖॹ�ƺǿǏ�ȠǦǓ�Йǿƺǹ�ǾǓȖǠǓǏ�ǏƺȠƺșǓȠ�ǩș�ǓΚǓȖ�ȖǓেȅȖ-
dered, the information regarding case origin could be 
lost. To summarize, each of the six dataset merging re-
ȕȣǩȖǓǾǓǿȠș�ǾȣșȠ�ǈǓ�ǟȣǹЙǹǹǓǏ�ǈΡ�ǈǓǩǿǠ�ǓǿǉȅǏǓǏ�ƺș�șǓȒ-
arate variables within the dataset or in such a way that 
the information cannot be easily lost.

Conducting the Dataset Merge
 Currently, SPSS does not include a built-in func-
tion that would perform a dataset merge that ensures 
all six requirements (above) are met. For example, the 
ADD FILES command does not identify cases that are 
unique to one of a researcher’s original datasets, 



8

nor is it able to identify duplicate cases that have mis-
matched data, thus, not meeting requirements 4 and 
6. The MERGE DATASETS command does not meet 
requirements 4, 5, or 6. In order to meet requirement 
1 using MERGE DATASETS, additional syntax is 
needed. Similarly, the MATCH FILES command does 
not meet requirements 1, 4, 5, or 6. Finally, the COM-
PARE DATASETS command does not meet require-
ments 1, 2, 3, or 4. In determining which syntax best 
meets all six requirements, a literature review was con-
ǏȣǉȠǓǏ�ΛǦǩǉǦ�ΡǩǓǹǏǓǏ�ЙΚǓ�শࢶষ�ǷǿȅΛǿ�ȒȅșșǩǈǹǓ�ǾǓȠǦȅǏș�
for combining datasets in SPSS. A total of 12 exper-
ǩǾǓǿȠș�ΛǓȖǓ� ǉȅǿǏȣǉȠǓǏ�ȣșǩǿǠ� ȠǦǓ�ЙΚǓ�ǾǓȠǦȅǏșॹ�ΛǩȠǦ�
ǓƺǉǦ�ǾǓȠǦȅǏ�ЙȖșȠ�ǈǓǩǿǠ�ȠǓșȠǓǏ�ǩǿ�ǩȠș�șǩǾȒǹǓșȠ�ǟȅȖǾ�ƺǿǏ�
then tested again in more complex forms. A summary 
of the experiments conducted, and their outcomes can 
be found in Table 1.
� eǦǓ�Йǿƺǹ�ȒȖȅǏȣǉȠ� শǈƺșǓǏ�ȅǿ� ȠǦǓ�ȅȣȠǉȅǾǓ�ȅǟ� ǓΠ-
periment 12) was a set of SPSS syntax to successful-
ly overcome the challenges associated with merging 
mismatched datasets using the least number of steps. 
It should be noted that the following steps were de-
veloped using SPSS v23 (IBM Corp., 2015) and tested 
using SPSS v25 and v27 (IBM Corp., 2017, 2020). It 
should be noted that conducting the merge using this 
syntax works across all versions including SPSS v28 
(IBM Corp., 2021); however, changes made to v28 in 
the point-and-click options may create a mismatch be-
tween the instructions provided herein. 

SPSS Syntax
  The following instructions on how to use the syn-
tax are applicable when researchers have two datasets 
that they wish to merge. It can also be used repeatedly 
if more than two datasets must be merged. Please re-
fer to Appendix A for the syntax related to each step. 
2Ƞ� ǩș�ƺǹșȅ� ǩǾȒȅȖȠƺǿȠ�Ƞȅ�ǿȅȠǓ�ȠǦƺȠ� ȠǦǓ�ЙǠȣȖǓș�ȒȖȅΚǩǏǓǏ�
ȠǦȖȅȣǠǦȅȣȠ�ȠǦǩș�șǓǉȠǩȅǿ�ƺȖǓ�șǩǾȒǹǩЙǓǏ�ǠȖƺȒǦǩǉș�ȅǟ�ȠǦǓ�
analysis for demonstration only and were not con-
ǏȣǉȠǓǏ�ȣșǩǿǠ�ȠǦǓ�șȣȒȒǹǓǾǓǿȠƺǹ�ЙǹǓșঀ��eǦǓ�șȣȒȒǹǓǾǓǿ-
Ƞƺǹ�ЙǹǓș�ƺȖǓ�ȒȖȅΚǩǏǓǏ�ǟȅȖ�ȠǦǓ�ȒȣȖȒȅșǓ�ȅǟ�ȒȖƺǉȠǩǉǩǿǠ�ȠǦǓ�
procedure with a more realistic dataset.

Original Datasets 
For variables shared by the two original datasets, the 
variable information should match perfectly (type, 
width, label, values, etc.). The following steps will er-
roneously identify matching variables as separate vari-

ables when they do not have identical characteristics 
(type, width, etc.). Before beginning the next steps, 
identify which dataset will be your “Dataset 1” and 
open both original datasets. See Figure 1 for an exam-
ple of two original datasets to be merged. It is also nec-
essary to clean the data from each of the datasets to 
be merged to the best of one’s ability before beginning 
the dataset merging process.

Syntax 
Step 1: Comparing Cases Between Datasets
The purpose of Step 1 is to create a new variable that 
encodes the number of unique cases present in Dataset 
1, the number of cases present in both original data-
sets that are perfectly matched, and the number of cas-
es present in both original datasets that contain mis-
matching data. The output will identify the location 
of the mismatched data in the dataset. Please note that 
the datasets will remain separate. Step 1 uses point-
and-click to run the COMPARE DATASETS com-
mand (which can be found under the DATA menu). 
Before running the command, make the following 
point-and-click changes. In the “Compare” tab, put 
the ID variable in Case IDs, and put all “Matched 
Fields” into “Fields to Compare.” In the “Attributes” 
tab, select “Do not compare the data dictionaries.” 
2ǿ�ȠǦǓ�৚KȣȠȒȣȠ৛�Ƞƺǈ�șǓǹǓǉȠ�৚МƺǠ�ǾǩșǾƺȠǉǦǓș�ǩǿ�ƺ�ǿǓΛ�
ЙǓǹǏ৛�ƺǿǏ�ǿƺǾǓ�ȠǦǓ�ǿǓΛ�ΚƺȖǩƺǈǹǓ�ΛǦƺȠ�Ρȅȣ�ΛǩșǦ�শΛǓ�
used “Mismatches”). Also, in the “Output” tab, unse-
lect “Limit the case-by-case table.” Click “paste” and 
run the resulting syntax on Dataset 1. See Figures 2A 
and 2B for example Datasets 1 and 2 after Step 1 as well 
as an example output after Step 1.

AUDETTE ET AL. 

Figure 1:  Simple Datasets Pre-Combination. This is a simple 
example of two separate, mismatched datasets.



9

Step 2: Merging Variables and Cases into One 
Dataset and Identifying Cases by Dataset
Step 2 merges all variables and cases into Dataset 1. 
From Dataset 1, select MERGE FILES then ADD 
CASES command. This is also found in the DATA 
menu. Before running the command, make the fol-
lowing point-and-click changes. Put all “Unpaired 
Variables” in “Variables in New Active Dataset.” As 
needed, manually pair variables from the two original 
datasets. Select “Indicate case source as variable” and 
name the new variable that you wish to use (we used 
“SourceDataset2”). Click “paste” and run the result-
ing syntax on Dataset 1. This step will add all variables 
and all cases to Dataset 1. It will also identify which 
cases come from Dataset 1 or Dataset 2 in the newly 
created variable. See Figure 3 for the example Dataset 1 
after Step 2. After completing this step, it may be help-
ful to label the values of this new variable, the syntax 
for which can be found in Appendix A. A value of 1 
indicates that a case originates from Dataset2 while a 
value of 0 indicates that a case originates from Dataset 
1.

Step 3: Identifying Mismatches, Matches, and 

Unique Cases Within Dataset 1
Step 3 duplicates the variable created in Step 1, which 
ǩǏǓǿȠǩЙǓș� ǾǩșǾƺȠǉǦǩǿǠॹ� ǾƺȠǉǦǩǿǠॹ� ƺǿǏ� ȣǿǩȕȣǓ� ǉƺșǓș�
present in Dataset 1. The duplicated variable is the one 
that will be manipulated in Steps 4 and 5. Step 3 can be 
done with the syntax (provided in Appendix A) using 
your preferred variable names inserted (we used “Cas-
esCompared”). The resulting duplicate variable only 
encodes information about matches, mismatches, and 
unique Dataset 1 cases for those cases from Dataset 1. 
The duplicated variable contains no information for 
cases added from Dataset 2, a problem that will be 
addressed in Step 4 and Step 5. See Figure 4 for the 
example Dataset 1 after Step 3. Once again, we suggest 
labeling the values of this new variable. A value of 1 in

MERGING MULTIPLE AND/OR DIVERGENT DATASETS IN SPSS

Figure 2A:  Simple Mismatched Datasets in SPSS after Step 1. 
This is an example of what the new variable created via Step 1 
would look like once added.

Figure 2B: Example Step 1 Output. This is an example of the out-
put that would be generated after completing Step 1 which pro-
vides a summary of case comparisons between the two datasets.

Figure 3: SPSS Data View After Step 2. This is an example of the 
variables added after step 2 which includes all variables existing 
in Dataset 2 that do not exist in Dataset 1 and a new variable that 
ǩǏǓǿȠǩЙǓș�ȠǦǓ�șȅȣȖǉǓ�ȅǟ�ǓƺǉǦ�ǉƺșǓঀ



10

dicates a mismatch between both datasets, 0 represents 
a match in both datasets, and -1 indicates that a case is 
unique to Dataset1. The syntax for creating these la-
bels can be found in Appendix A.

Step 4: Labeling Matches and Mismatches from 
Dataset 2
Step 4 uses the syntax we developed (utilizing the LAG 
function), and the variables created in Steps 2 and 3. 
Utilizing the LAG function and the “SourceDataset2” 
variable, Step 4 encodes for all cases within the new-
ly created “CasesCompared” variable (from Step 3). 
Step 4 encodes information as to whether the case is a 
match between datasets, a mismatch between datasets, 
or unique to Dataset 1. This step is important because 
without it only cases from Dataset 2 would have in-
formation encoded in the “CasesCompared” variable. 
Again, the syntax is found in Appendix A. When 
running the syntax, be careful to change the variable 
names to your chosen variable names, and to run the 
șȅȖȠǩǿǠ�șΡǿȠƺΠ�ЙȖșȠ�শΛǓ�ǩǿǉǹȣǏǓǏ�ǓΠƺǾȒǹǓș�Ƞȅ�ǟƺǉǩǹǩȠƺȠǓ�
understanding). Your ID variable must be in numer-
ical descending order (e.g. 1, 1, 2, 3, 4, 4), with cases 
from Dataset 1 listed or appearing before cases from 
Dataset 2. This syntax solves the problem from Step 3, 
however, the variable “CasesCompared” still does not 
encode when a case is unique to Dataset 2. See Figure 
5 for the example Dataset 1 after Step 4.

Step 5: Identifying Cases Unique to Dataset 2
Step 5 encodes in the “CasesCompared” variable when 
cases are unique to Dataset 2. The syntax can be found 

in Appendix A. Since the only cases with no data in 
“CasesCompared” are those which are unique to 
�ƺȠƺșǓȠࢳ�ॹ�ȠǦǩș�șΡǿȠƺΠ�ǩǏǓǿȠǩЙǓș�ǓǾȒȠΡ�ǏƺȠƺ�ǩǿ�৚�ƺșǓș-
Compared” and encodes them as unique to Dataset 2. 
The syntax also includes a method of labeling this new 
value. See Figure 6 for the example Dataset 1 after Step 
5.

Discussion
� eǦǓȖǓ� ǩș� șǩǠǿǩЙǉƺǿȠ� ǹǩȠǓȖƺȠȣȖǓ� ȠǦƺȠ� ȒȖȅΚǩǏǓș� Ƞȣ-
ȠȅȖǩƺǹș� ƺǿǏ� ǩǿșȠȖȣǉȠǩȅǿș�ȅǿ� ȠǦǓ�ǈƺșǩǉ�ǾǓȖǠǩǿǠ�ȅǟ�ЙǹǓș�
ΛǩȠǦ�ǩǏǓǿȠǩǉƺǹ�ΚƺȖǩƺǈǹǓș�ƺǿǏইȅȖ�ǉƺșǓșঀ�^ǩǠǿǩЙǉƺǿȠ� ǹǩȠǓȖ-
ature discussing these basic merging techniques can 
be found for programs such as R, SPSS, and STATA. 
What has not been discussed in the literature but may 
be of use to students and researchers using small- to 
medium-sized datasets, is a procedure that reliably 
merges datasets with missing or mismatched cases 
and/or variables within SPSS. While numerous soft-
ware programs are used to conduct research, one of 
the more frequently used software programs to teach 
statistical analysis, particularly within the social sci-
ences, is SPSS. Oftentimes students are introduced to 
data analysis using the drop-down menus within SPSS 
rather than syntax. This article has provided informa-
tion on both the use of drop-down menus and syntax 
to conduct data merging. Depending on the individu-
al’s comfort level and the version of SPSS being used, 
ǓǩȠǦǓȖ� ȅǟ� ȠǦǓșǓ� ȒȖȅǉǓǏȣȖǓș�ǾƺΡ� ǈǓ�ǾȅȖǓ� ǓАǓǉȠǩΚǓঀ� 2ǿ�
the case of an individual using version 27 or earlier, 
the drop-down menus provide point-and-click ease of 
conducting the analysis. While for those with greater 
comfort and/or facility using syntax, merging using 
the syntax is likely to be more comfortable regardless 
of the version used. 
 There are several advantages to using this proce-
ǏȣȖǓ�Ƞȅ�ǾǓȖǠǓ�ǏǩΚǓȖǠǓǿȠ�ǏƺȠƺșǓȠșঀ�eǦǓ�ЙȖșȠ�ƺǏΚƺǿȠƺǠǓ�
is that it allows the researcher to store all data within 
a single dataset. This facilitates data analysis for most 
procedures. The second advantage is that by having all 
data in one dataset, analyses accounting for missing

AUDETTE ET AL. 

Figure 4: SPSS Data View After the Creation of a Variable that 
2ǏǓǿȠǩЙǓș�EǩșǾƺȠǉǦǓșॹ�EƺȠǉǦǓșॹ�ƺǿǏ�hǿǩȕȣǓ��ƺșǓș�[ǓǠƺȖǏǩǿǠ�
Cases Originating From Dataset 1 (Step 3).

Figure 5: ^X^^��ƺȠƺ�tǩǓΛ��ǟȠǓȖ�^ȠǓȒࢵ��ΛǦǩǉǦ�2ǏǓǿȠǩЙǓș�Eǩș-
matches and Matches in Cases Originating from Dataset 2.

Figure 6: ^X^^��ƺȠƺ�tǩǓΛ��ǟȠǓȖ�^ȠǓȒࢶ��ΛǦǩǉǦ�2ǏǓǿȠǩЙǓș��ƺșǓș�
Unique to Dataset 2.



11

data are more easily conducted from within one data-
set rather than across multiple datasets. The third ad-
vantage is that this procedure allows one to conduct 
analyses that can only be performed within a single 
dataset. The fourth advantage is that embedded with-
in the syntax is a data quality check which increases 
the likelihood that the merged data is accurate and 
represents the population of interest.
 An additional consideration is related to the speed 
ȅȖ�ǓГǉǩǓǿǉΡ�ȅǟ�ȠǦǓ�ȒȖȅǉǓǏȣȖǓঀ�2Ƞ�șǦȅȣǹǏ�ǈǓ�ǿȅȠǓǏ�ȠǦƺȠ�
ȠǦǓ�șȒǓǓǏॹ�ǓГǉǩǓǿǉΡॹ�ƺǿǏ�ƺǾȅȣǿȠ�ȅǟ�șȠȅȖƺǠǓ�ȖǓȕȣǩȖǓǏ�
Ƞȅ�ǉȅǿȠƺǩǿ�ȠǦǓ�ǏƺȠƺșǓȠ�Λǩǹǹ�ǈǓ�ƺАǓǉȠǓǏ�ǈΡ�ȠǦǓ�șǩΦǓș�ȅǟ�ȠǦǓ�
datasets involved. Large datasets (such as those found 
in the National Center for Education Statistics, etc.), 
will require greater computing power and storage ca-
pacity than smaller datasets. 

Conclusion
 Merging datasets accurately is vital to ensuring 
that no data is altered or lost and that researchers can 
easily understand the sources of all their data. Present-
ly, there is no consensus on best practices for merg-
ing SPSS datasets with overlapping and potentially 
mismatched cases. The present article introduces one 
method to handle such a situation. The recommended 
method results in a merged dataset that includes all cas-
es and variables, in their original form, stemming from 
ȠΛȅ�ȅȖ�ǾȅȖǓ�ȅȖǩǠǩǿƺǹ�ǏƺȠƺșǓȠșঀ�2Ƞ�ǉȖǓƺȠǓș�ƺǿ�ȅȣȠȒȣȠ�ЙǹǓ�
ȠǦƺȠ�ǩǏǓǿȠǩЙǓș�ǓƺǉǦ�ǩǿșȠƺǿǉǓ�ȅǟ�ǉƺșǓ�ǾǩșǾƺȠǉǦ�ƺǿǏ�ȠǦǓ�
location of each mismatch within the dataset. It also 
results in a single variable that encoded whether a case 
was 1) a perfect match between datasets 2) a mismatch 
between datasets with non-matching data 3) unique 
to one dataset and which original dataset contains that 
unique case. Alternative strategies are recommended 
when the datasets to be merged contain overlapping 
cases but no or few shared variables.

References
Braun, M. T., Kuljanin, G., & DeShon, R. P. (2018). 

Special considerations for the acquisition and 
wrangling of big data. Organizational Re-
search Methods, 21(3), 633-659. https://doi.
org/10.1177/1094428117690235

Coleman, M. (n.d.). Merging datasets in SPSS. Re-
trieved December 12, 2017, from http://ww-
w.d.umn.edu/~mcoleman/tutorials/spss/merge.
html 

Coyer, L. (2013, April 20). /Ȥ͡�ǨǙȞ�ͧȤɂ�ȝǲȵǿǲ�ȿ͡Ȥ�ϫȘǲȸ�
in SPSS when the cases are not perfectly the same? 
[Online forum post]. ResearchGate. Retrieved 
December 12, 2017, from https://www.research-
ǠƺȠǓঀǿǓȠইȒȅșȠই/ȅΛউǉƺǿউΡȅȣউǾǓȖǠǓউȠΛȅউЙǹǓșউ
in_SPSS_when_the_cases_are_not_perfectly_
the_same. 

DeCator, D. D. (2015, February 8). SPSS Syntax 
Part 4: Double Entry Comparison. Retrieved 
October 12, 2017, from https://www.ddecator.
com/blog/2015/2/spss-syntax-part-4-double-en-
try-comparison

Endel, F., & Piringer, H. (2015). Data wrangling: 
Making data useful again. IFAC-PapersOnLine, 
48(1), 111-112. https://doi.org/10.1016/j.if-
acol.2015.05.197

Foley, M. J. (1998). Match-merging: 20 some traps 
and how to avoid them. In the Proceedings of the 
23rd Annual SAS Users Group International Con-
ference (pp. 277-286). Retrieved from https://
support.sas.com/resources/papers/proceedings/
proceedings/sugi23/Advtutor/P47.pdf 

Foley, M. J. (2005). Merging vs. joining: Comparing 
the DATA step with SQL. In the Proceedings of 
the 30th Annual SAS Users Group International 
Conference (pp. 184–200). Retrieved from http://
www.scsug.org/SCSUGProceedings/2005/Fo-
ley_Merging vs Joining - 184.pdf 

Glynn, P. (2002). SPSS for Social Science Research - 
Using Syntax. Retrieved December 12, 2017 from 
ǦȠȠȒॸইইșȠƺАঀΛƺșǦǩǿǠȠȅǿঀǓǏȣইǠǹΡǿǿইșȒșșǉǹƺșই�

Guha, S., Kidwell, P., Hafen, R.P., & Cleveland W.S. 
(2009). Visualization databases for the analysis of 
large complex datasets.  Proceedings of the 12th 
2ǿȠǓȖǿƺȠǩȅǿƺǹ� �ȅǿǟǓȖǓǿǉǓ� ȅǿ� �ȖȠǩЙǉǩƺǹ� 2ǿȠǓǹǹǩ-
gence and Statistics (AISTATS), USA, 193-200.  
Retrieved October 20, 2022 from https://pro-
ceedings.mlr.press/v5/guillory09a.html.

Haddaway, N. R., Collins, A. M., Coughlin, D., & 
Kirk, S. (2015). The role of Google Scholar in 
evidence reviews and its applicability to grey lit-
erature searching. PLOS ONE, 10(9). https://doi.
org/10.1371/journal.pone.0138237 

Haghighat, M., Abdel-Mottaleb, M., & Alhalabi, W. 
(2016). Discriminant correlation analysis: Re-
al-time feature level fusion for multimodal bio-
metric recognition. IEEE Transactions on

MERGING MULTIPLE AND/OR DIVERGENT DATASETS IN SPSS



12

AUDETTE ET AL. 

Information Forensics and Security, 11(9), 1984–1996. 
https://doi.org/10.1109/TIFS.2016.2569061

IBM Corp. (2015). IBM SPSS Statistics for Windows 
(Version 23.0) [Computer software]. IBM Corp.

IBM Corp. (2017). IBM SPSS Statistics for Windows 
(Version 25.0) [Computer software]. IBM Corp.

IBM Corp. (2020). IBM SPSS Statistics for Windows 
(Version 27.0) [Computer software]. IBM Corp.

IBM Corp. (2021). IBM SPSS Statistics for Windows 
(Version 28.0) [Computer software]. IBM Corp.

IBM Corp. (n.d.). Merging Data Files. Retrieved De-
cember 12, 2017 from https://www.ibm.com/
support/knowledgecenter/en/SSLVMB_23.0.0/
spss/base/idh_idd_add _gating.html 

Ojeda, T., Dasgupta, A., Bengfort, B., & Murphy, S. P. 
(2014). Practical data science cookbook. Packt Pub-
lishing.

Rattenbury, T., Hellerstein, J. M., Heer, J., Kandel, 
S., & Carreras, C. (2017). Principles of data wran-
gling: Practical techniques for data preparation. 
O’Reilly Media.

Spss-tutorials.com. (n.d.). Merging Data Files in SPSS. 
Retrieved December 12, 2017 from https://www.
șȒșșেȠȣȠȅȖǩƺǹșঀǉȅǾইǾǓȖǠǩǿǠেǏƺȠƺেЙǹǓșই�

Stehlik-Barry, K. & Babinec, A. J. (2017). Data ana-
lytics with IBM SPSS statistics. Packt Publishing.

Truong, D. [Dothang Truong]. (2016, July 21). SPSS 
missing values. [Video]. YouTube. https://www.
youtube.com/watch?v=DNThjjqLz9Q



13

MERGING MULTIPLE AND/OR DIVERGENT DATASETS IN SPSS



14

AUDETTE ET AL.

Step 1:
***Step 1***
*compare datasets 
DATASET ACTIVATE DataSet1. 
SORT CASES BY ID. 
COMPARE DATASETS 
     /COMPDATASET = DataSet2 
     /VARIABLES ALL 
     /CASEID ID 
     /SAVE FLAGMISMATCHES=YES VARNAME=Mis-
matches MATCHDATASET=NO 
MISMATCHDATASET=NO 
     /OUTPUT VARPROPERTIES=NONE CASE-
TABLE=YES TABLELIMIT=600.

Step 2: 
***Step 2*** 
*merging all variables and cases into DataSet1 
ADD FILES /FILE=* 
     /FILE=’DataSet2’ 
     /IN=SourceDataset2. 
VARIABLE LABELS SourceDataset2 
     ‘Case source is DataSet2’. 
EXECUTE. 

ADD Value Labels 
SourceDataset2 
1 ‘from Dataset2’ 
0 ‘from Dataset1’. 
Execute. 

Step 3: 
***Step 3*** 
*creating new mismatches variable called CasesCompared 
compute CasesCompared = Mismatches. 
Execute. 
ADD Value Labels 
CasesCompared 
1 ‘Mismatch between Both Datasets’ 
0 ‘Match in Both Datasets’ 
-1 ‘Unique to Dataset1’. 
Execute.

Step 4: 
***Step 4*** 
*duplicating CasesCompared encoded information for 
cases from Dataset2 
SORT CASES BY ID(A) SourceDataset2(A) Mismatches-
(A). 
Execute. 
IF ((ID = lag(ID)) AND (sysmis(CasesCompared))) 
CasesCompared=lag(CasesCompared). 
Execute. 

Step 5: 
***Step 5*** 
*encoding in CasesCompared those cases which are unique 
to Dataset2 
IF (sysmis(CasesCompared)) 
CasesCompared=-2. 
Execute. 
ADD Value Labels 
CasesCompared 
-2 ‘Unique to Dataset2’. 
Execute.

Appendix A
Syntax for Steps One Through Five


