SPSS – Convert String into Date Variable

For converting string variables to date variablesALTER 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

  • The date format in ALTER TYPE tells SPSS which component is the year, month and day. So if 1/2/3 means 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 TYPE overwrites 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 ALTER TYPE.

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).
exe.*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. JANFEB 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.