In some cases, ALTER TYPE in SPSS version 24 seems to report incorrect altered values when converting a string to a numeric value. This results in messed up data while SPSS reports that everything is fine. Let's try and replicate the problem with the example below.
We imported some .csv data in which some numeric values were flagged with an “a”. This indicates that these were estimated rather than measured values. Due to these flags, some variables were imported as string variables. The syntax below recreates a microsample from these data.
data list free/s1 (a2).
'' '1' '1a' '2' '2a' '3' '3a' '4' '4a' '5' '5a'
Convert to Numeric with ALTER TYPE
We'll now convert our string to a numeric variable with the syntax below. Since our variable seems to hold only single digit integers, we chose to convert it into the f1 format. As an extra safety check, we'll inspect which original values are converted into which new values.
alter type s1(f1)
This table suggests that all variables have been converted as desired. It seems SPSS made the right guess that string value 1a should be changed to 1. Furthermore, only empty string values result in system missings after the conversion. Mission accomplised?
Data View after ALTER TYPE
At first glance, everything looks great. Ok, we do have quite some system missing values after the conversion but those were all empty string values. Right?
But let's take another quick look at our original data: our flagged values weren't converted as reported in the altered values table. They all result in system missing values.
With just 1 variable and 11 cases, we immediately see the problem. However, the actual data contained some 40,000 records and it was more or less by accident that I stumbled upon the issue.
Which SPSS Versions?
My students tried to replicate the issue in SPSS versions 18 and 22. Neither version reported incorrect values because the altered values tables were empty -for this example at least.
/PRINT ALTEREDVALUES subcommand was introduced in version 23 or 24 but the CSR does not mention anything about it.
Thanks for reading!