For converting string variables to date variables, ALTER TYPE is the way to go. This tutorial demonstrates how to do this and points out a couple of caveats. For those who can't use
ALTER TYPE, we'll propose some alternatives.
SPSS Alter Type
The syntax below first creates a mini dataset holding 8 string variables. They demonstrate the 8 most common date formats. Next, each string variable is converted to a date variable by using
SPSS String to Date Syntax
data list free/d1 to d8(8a20).
31-dec-99 31.dec.1999 12/31/99 12-31-1999 31.12.99 31/12/1999 99-12-31 1999.12.31
*2. Convert all strings to dates.
alter type d1(date9).
alter type d2(date11).
alter type d3(adate8).
alter type d4(adate10).
alter type d5(edate8).
alter type d6(edate10).
alter type d7(sdate8).
alter type d8(sdate10).
String to Date Syntax Notes
- The date format in
ALTER TYPEtells SPSS which component is the year, month and day. So if
1/2/3means 1 February 2003, use
EDATE8(dd-mm-yy). If it means January 2nd, 2003, use
ADATE8(mm-dd-yy) and so on.
- In the original string variable, the year, month and day may be separated by a dash (-), a slash (/), a period (.) or even a combination of these. It doesn't matter which one is used.
- If two digits are used for years, numbers 0 through 44 are interpreted as years 2000 through 2044. Numbers 45 through 99 are interpreted as 1945 through 1999. For more on this, see Two Digit Year in String - Cautionary Note.
ALTER TYPEoverwrites existing values and is not reversible. It's usually no big problem if things go wrong here as long as you stick to sound practices such as working from syntax. Additionally, you can use SPSS Clone Variables Tool before
SPSS String to Date without Alter Type
ALTER TYPE can only be used for a limited number of date formats. Some more exotic formats may require a more flexible approach. Second, those on SPSS versions 15 and below don't have
ALTER TYPE since it was introduced in version 16.
For both scenarios, we'll usually extract the year, month and day by using SUBSTR, often combined with INDEX and RINDEX. We'll then convert these into an SPSS date variable by using the
DATE.DMY function. Finally, we'll display the number of seconds it holds as a more readable date by using
SPSS String to Date Syntax
data list free/s1 s2(2a20).
1.1.1999 1-jan-99 2.28.1999 2-feb-99 3.31.1999 3-mar-99 4.30.1999 4-apr-99 5.31.1999 5-may-99 6.30.1999 6-jun-99
7.31.1999 7-jul-99 8.31.1999 8-aug-99 9.30.1999 9-sep-99 10.31.1999 10-oct-99 11.30.1999 11-nov-99 12.31.1999 12-dec-99
*2. Extract day, month and year from string.
compute day = number(char.substr(s1,char.index(s1,'.') + 1,char.rindex(s1,'.') - char.index(s1,'.')),f2.0).
compute month = number(char.substr(s1,1,char.index(s1,'.') - 1),f2.0).
compute year = number(char.substr(s1,char.rindex(s1,'.') +1),f4.0).
*3. Compute date variable.
compute d1 = date.dmy(day,month,year).
*4. Display as date.
Dealing with Months as Letters
The previous example converted the first string variable but the second is slightly harder. This is because
DATE.DMY requires three numbers but months are now shown as letters (e.g.
FEB and so on). An easy way to fix this is to REPLACE the months by numbers (
12) using DO REPEAT as shown below. After doing so, one can proceed as in the previous example.
do repeat s = 'jan' 'feb' 'mar' 'apr' 'may' 'jun' 'jul' 'aug' 'sep' 'oct' 'nov' 'dec' / n = 1 to 12.
compute s2 = replace(s2,s,string(n,n2)).