SPSS TUTORIALS
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.
Example
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).
begin data
'' '1' '1a' '2' '2a' '3' '3a' '4' '4a' '5' '5a'
end data.
Result
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)
/print alteredvalues.
Result
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.
Perhaps the /PRINT ALTEREDVALUES subcommand was introduced in version 23 or 24 but the command syntax reference does not mention anything about it.
Thanks for reading!
THIS TUTORIAL HAS 6 COMMENTS:
By Jon Peck on July 12th, 2017
I only have access to 23 and later. I get a table with no rows in 23 with your original data, but values like a1 are correctly reported in both versions. Since the error is an edge case, I suspect that it was not covered in the testbed. The data conversions, though, are correct. Note also that the list of altered values is limited to the first 25