SPSS TUTORIALS
“I've a string variable in my data holding dates formatted as ‘01JAN2016’ without separators between the day, month and year components. To make things worse, the month abbreviations are in Dutch and 3 of those differ from their English counterparts. How can I change this string into an SPSS date variable?”
Step 1: Add Separators
The data are in stringdates.sav. Now, generally, ALTER TYPE is the way to go for this job but the day-month-year separators missing poses a problem here. The solution is to add those by combining CONCAT with CHAR.SUBSTR . We'll do so in a new string variable because this allows us to inspect if the conversion succeeded.
Syntax 1
string newdate(a11).
*Add day, month, year from old string to new string and separate these components with dashes.
compute newdate = concat(
char.substr(mydate,1,2), /*start at character 1, extract 2 characters (day)
'-',
char.substr(mydate,3,3), /*start at character 3, extract 3 characters (month)
'-',
char.substr(mydate,6,4) /*start at character 6, extract 4 characters (year)
).
*Check if result is as desired.
execute.
*Convert stringdate with dashes to SPSS date variable.
alter type newdate (date11).
Step 2: Inspect Results
A huge flaw in ALTER TYPE is that it may result in system missing values without throwing any error or warning if it can't convert one or more values. Failing to detect this -not unlikely in larger datasets- may result in severely biased results. We'll therefore flag cases -if any- which have a system missing value on our new date variable but not an empty string value on our input variable.
Syntax 2
compute flag = (missing(newdate) & mydate <> '').
*Move flagged cases -if any- to top of dataset.
sort cases by flag(d).
Result
Step 3: Replace Some Month Abbreviations
Note that we flagged some conversion failures but one case with an empty string value on our input variable is not one of them. The Dutch month abbreviations (such as “MRT” instead of “MAR”) are the reason for this. Fortunately, only 3 month abbreviations differ between English and Dutch.
We'll now convert our outcome variable back to string and recompute it. After doing so, we'll REPLACE the three deviant abbreviations in a DO REPEAT loop. After doing just that, we'll successfully convert our new variable into a date variable.
Syntax 3
delete variables flag.
*Change newdate back to string.
alter type newdate(a11).
*Recompute newdate as previously.
compute newdate = concat(
char.substr(mydate,1,2), /*start at character 1, extract 2 characters (day)
'-',
char.substr(mydate,3,3), /*start at character 3, extract 3 characters (month)
'-',
char.substr(mydate,6,4) /*start at character 6, extract 4 characters (year)
).
*Replace 3 Dutch abbreviations with their English counterparts.
do repeat #old = 'MRT' 'MEI' 'OKT' /#new = 'MAR' 'MAY' 'OCT'.
compute newdate = replace(newdate,#old,#new).
end repeat.
*Check if result is as desired.
execute.
*Convert to SPSS date variable - second attempt.
alter type newdate(date11).
Result
As we readily see, our conversion has now fully succeeded. In a larger dataset, you might want to inspect the result more carefully by flagging conversion failures like we did previously.
We really enjoyed writing this little tutorial. It nicely shows how combining the right building blocks gets a seemingly complicated job done with minimal effort and perfect precision. Hope you liked it too!
THIS TUTORIAL HAS 1 COMMENT:
By Jon Peck on November 17th, 2016
Regarding conversion error reporting, there are many conversions that can partially or totally fail, because the value is not compatible with the format. For this reason, ALTER TYPE syntax includes /PRINT ALTERED VALUES so that the user can see the effect of partially or totally failing conversions.