SPSS – Convert String into Date Variable

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 ALTER TYPE.

SPSS String to Date Syntax

*1. Create mini dataset.

data list free/d1 to d8(8a20).
begin data
31-dec-99 31.dec.1999 12/31/99 12-31-1999 31.12.99 31/12/1999 99-12-31 1999.12.31
end data.

*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

SPSS String to Date without Alter Type

Note that 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 FORMATS.

SPSS String to Date Syntax

*1. Create mini dataset.

data list free/s1 s2(2a20).
begin data
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
end data.

*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.

formats d1(adate10).

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. JAN, FEB and so on). An easy way to fix this is to REPLACE the months by numbers (00 through 12) using DO REPEAT as shown below. After doing so, one can proceed as in the previous example.

*Replace month letters by month numbers in string.

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)).
end repeat.

Tell us what you think!

*Required field. Your comment will show up after approval from a moderator.