How To Create a Unique ID from an ID Variable That Contains Duplicates

Introduction

This article explains how to use JavaScript to create a unique ID variable from an ID variable that contains some repeated values. In some cases, the data file may be missing a variable that identifies each respondent uniquely, because some respondents have data in more than one row of the file, but there is still a need to have a variable that identifies the cases uniquely (for example to set the Case IDs in the Data tab).

Here, we make a new variable where each occurrence of an ID value after the first has a number appended to it. For example, if the ID 100355 appears twice, the new variable will have values of 100355 and 100355_1 in the corresponding rows.

Note

This process requires you to make a new copy of your SPSS data file. A new data file is necessary because in order to set a variable as a Case ID, the variable must be in the raw data, rather than a constructed variable. Creating a new copy of the data file hard codes the new variable into the raw data.

Method

Select File > New Project.
Select File > Data Sets > Add to Project > From File.
In the Data Import Window:
1. Select Use original data file structure.
2. Untick Tidy Up Variable Labels and Strip HTML from Labels.
3. Click OK.
Go to the Variables and Questions tab.
Right-click the ID variable and select Insert Variables > JavaScript Formula > Text.
Tick the box Access all data rows (advanced).
Paste the code below into the Expression.
Change CustomerID in the first line to the Variable Name of the ID variable you are using.
Change the Name to NewID and Label to New ID, and click OK.
Use Tools > Save as SPSS/CSV File and save a new SPSS file. This saves the data with the new variable included.

var _old_ids = CustomerID;

var _new_ids = _old_ids.map(function (x) { return x.toString(); });

_new_ids.forEach(function (_i, _ind) {
    if (_new_ids.indexOf(_i) < _ind) {
        var _counter = 1;
        while(_new_ids.indexOf(_i + "_" + _counter.toString()) < _ind && _new_ids.indexOf(_i + "_" + _counter.toString()) > -1)
            _counter ++;
        _new_ids[_ind] = _i + "_" + _counter;
    }
});
                
_new_ids

How to Handle Duplicate Cases in Survey Data

How To Delete Duplicate Cases in a .sav

How to De-duplicate Raw Data Using R

How to Identify Duplicates in Q Using Code