CODEWITHSUNDEEP
databasespssdata-cleaning
Rozo
Rozo

Reputation: 37

Convert Nonstandard Date Formats to Dates in SPSS

I feel like an idiot for not being able to figure this out on my own, but hours of futile effort I realize I really need some help:

I have date of birth data imported from Excel into SPSS in these formats:

2004 *assume year of birth
12-Sep-14

They were imported as a string variable to SPSS. When I try to change the variable type to Date they disappear unfortunately meaning I have been trying to code it in. Coming across answers online I believe the answer will look similar to this:

COMPUTE year=number(SUBSTR(string(v1,F8),7,2),F2).
COMPUTE day=number(SUBSTR(string(v1,F8),5,2),F2).
COMPUTE month=number(SUBSTR(string(v1,F8),3,2),F2).
COMPUTE date=DATE.MDY(month,day,year).
FORMAT date(ADATE8).
LIST.
EXECUTE.

But going through the Syntax manual for the different commands I haven't been able to figure out what I need to change for my desired result. I got to the following but I am obviously missing something.

COMPUTE Year = NUMBER(SUBSTR(STRING(Date,F8),1,4),F2).
EXECUTE.

Theoretically it's pretty easy, but the syntax of it is where I am hitting a wall. Please help!

Upvotes: 1

Views: 644

Answers (2)

JKP
JKP

Reputation: 5417

I am not clear on whether that year row is actually part of the same variable, but to just convert string date values in the day-month-year format, you can use ALTER TYPE like this.

alter type v1(date(a9).

If there are also year-only rows, you could convert them into a new variable and then combine the two.

Upvotes: 1

eli-k
eli-k

Reputation: 11350

  1. The syntax STRING(Date,F8) is used to turn a number into a string, but Date is already a string, so you should get rid of that.
  2. You have two different situations, and you have to work differently in each one - I'd use do if.

Try this:

do if char.index(Date,"-")=0. 
   COMPUTE Year = NUMBER(char.SUBSTR(Date,3,2),F2).
else.
   COMPUTE Year = NUMBER(char.SUBSTR(Date,8,2),F2).
end if.

You can continue with the month and day after the else .

But a different approach would be to change the four digit years so they can be read along with the full dates in one command. In the following syntax I change the four digit years into the first of January (you can change that of course) of that year, and then read all of the Dates in the same format:

string Date2 (a10).
compute Date2=Date.
* since I need to change the variable, I do it in a copy of the variable so
  I don't lose my original data.
if char.index(Date2,"-")=0  Date2=concat("1-1-",char.SUBSTR(Date2,3,2)).
compute DateFull=number(Date2, DATE9).
formats DateFull (DATE9).
exe.

Upvotes: 0

Related Questions