SPSS Tutorials

BASICS REGRESSION T-TEST CHI-SQUARE TEST ANOVA

Convert String Date to SPSS Date Variable

SPSS String to Date Variable Conversion Example

“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

*Create new date variable as string.

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

*ALTER TYPE may result in system missing values without error or warning so check if that's the case here.

compute flag = (missing(newdate) & mydate <> '').

*Move flagged cases -if any- to top of dataset.

sort cases by flag(d).

Result

SPSS ALTER TYPE conversion failures

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

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

SPSS REPLACE Function Example

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!

Previous tutorial: How to Compute Age in SPSS?

Next tutorial: SPSS Time Variables Basics

Let me know what you think!

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

This tutorial has 1 comment!