SPSS TUTORIALS
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
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 TYPEtells SPSS which component is the year, month and day. So if1/2/3means 1 February 2003, useEDATE8(dd-mm-yy). If it means January 2nd, 2003, useADATE8(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 beforeALTER 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
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).
exe.
*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.
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.
exe.
THIS TUTORIAL HAS 4 COMMENTS:
By Ron on May 29th, 2017
The info displayed looks informative, but I couldn't adapt it to my application. Your examples worked but I couldn't get it to work in my situation, where date was A8 and stored as 20170529.
By Ruben Geert van den Berg on May 29th, 2017
Hi Ron!
You can't readily convert that format into an actual SPSS date variable. You'll need to add separators with CONCAT and CHAR.SUBSTRING. For an example, please consult Convert String Date to SPSS Date Variable.
Although the title is quite similar to what you already read, this is a more challenging case study that comes closer to your situation.
Hope that helps!
By Luis on May 12th, 2020
compute month = number(char.substr(s1,1,char.index(s1,'.') - 1),f2.0).
is indeed for the day!
compute day = number(char.substr(s1,1,char.index(s1,'.') - 1),f2.0).
compute day = number(char.substr(s1,char.index(s1,'.') + 1,char.rindex(s1,'.') - char.index(s1,'.')),f2.0).
Is for the syntax for the month!
By Ruben Geert van den Berg on May 12th, 2020
First off, this is an old tutorial and the example is kinda confusing. However, it's totally correct nevertheless.
Please note that there's 2 string dates s1 and s2 and this example converts s1. Also note that the first character(s) indicate the month for this variable. If you rerun the entire example, you'll see that the day/month/year are all extracted correctly.
Hope that helps!
SPSS tutorials