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 the file that contains duplicate IDs
- From the Data Sets tree by hovering over the ID variable > Plus (+) > Insert Variable(s) > Custom Code > JavaScript - Text
- Tick the box Access all data rows.
- 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 Publish > Export Data > Download as SPSS File (.SAV) 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