Reading Date & Time from CSV File

[cumminpa]

This is my first time posting, so I apologize in advance if the solution to my problem is already posted; I looked and could not find it.  My SPSS file was created from a csv file with dates formatted at MM/DD/YYYY, but in SPSS it comes ups as 5 digits ranging from 17623 to 18633.  The dates should be from about 04/01/2008 to 09/30/2010.  I went to the SPSS website and the posted suggestion was:

COMPUTE NEWDATE = DATE.MDY(1,1,1900) +( (excdate - 2) * 24 * 60 * 60).
FORMATS NEWDATE (DATE14).
EXECUTE.

where "excdate" is the current date variable.  This did convert to the date format, but it is about 60 years earlier that it should be (i.e., 1948-1950).  The SPSS suggested solution is for an excel file import and by file was in csv.  I don't know enough about imports to know if that could be the problem.

Any and all suggestions and help would be appreciated!

PAC

[Maguin, Eugene]

PAC,

MM/DD/YYYY corresponds to what spss calls an ADate date/time format (see
page 63 of the v18 reference). So you can do this.

Compute newdate=number(execdate,adate10).
Format newdate(adate10).


The number function is quite useful to be familiar with.

Gene Maguin


-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of
cumminpa
Sent: Thursday, May 05, 2011 9:26 AM
To: [hidden email]
Subject: Re: Reading Date & Time from CSV File

This is my first time posting, so I apologize in advance if the solution to
my problem is already posted; I looked and could not find it.  My SPSS file
was created from a csv file with dates formatted at MM/DD/YYYY, but in SPSS
it comes ups as 5 digits ranging from 17623 to 18633.  The dates should be
from about 04/01/2008 to 09/30/2010.  I went to the SPSS website and the
posted suggestion was:

COMPUTE NEWDATE = DATE.MDY(1,1,1900) +( (excdate - 2) * 24 * 60 * 60).
FORMATS NEWDATE (DATE14).
EXECUTE.

where "excdate" is the current date variable.  This did convert to the date
format, but it is about 60 years earlier that it should be (i.e.,
1948-1950).  The SPSS suggested solution is for an excel file import and by
file was in csv.  I don't know enough about imports to know if that could be
the problem.

Any and all suggestions and help would be appreciated!

PAC

--
View this message in context:
http://spssx-discussion.1045642.n5.nabble.com/Reading-Date-Time-from-CSV-Fil
e-tp3381430p4372785.html
Sent from the SPSSX Discussion mailing list archive at Nabble.com.

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

[cumminpa]

I still can't make it work.  When I used the syntax you provided, all that showed up in my new variable were missing values.  I've searched for the v18 reference you mentioned and can't find anything useful; I found various reference guides on the IBM website, but none had anything relevant on page 63.  

[David Marso]

Please post some samples of your CSV date field, the expected value for these samples and the exact syntax you are using along with the resulting values.
AFAICT, you are not working from an EXCEL based date reference.  If you can provide this info, chances are that someone can determine what it is.

[Marta Garcia-Granero]

Hi PAC:

I created (using Excel) a short CSV file with the following data:

id;mydate
1;04/01/2008
2;09/30/2010

(the two example dates you mentioned in your  message) and read it into
SPSS (15) using:

GET DATA  /TYPE = TXT
  /FILE = 'C:\Documents and Settings\MartaGG\Escritorio\Datasample.csv'
  /DELCASE = LINE
  /DELIMITERS = ";"
  /ARRANGEMENT = DELIMITED
  /FIRSTCASE = 2
  /IMPORTCASE = ALL
  /VARIABLES = id F1.0  mydate ADATE10.
CACHE.
EXECUTE.

And the dates were OK. Have you taken a look at your CSV file using a
text editor?

HTH,
MArta GG

El 05/05/2011 15:26, cumminpa escribió:

> This is my first time posting, so I apologize in advance if the solution to
> my problem is already posted; I looked and could not find it.  My SPSS file
> was created from a csv file with dates formatted at MM/DD/YYYY, but in SPSS
> it comes ups as 5 digits ranging from 17623 to 18633.  The dates should be
> from about 04/01/2008 to 09/30/2010.  I went to the SPSS website and the
> posted suggestion was:
>
> COMPUTE NEWDATE = DATE.MDY(1,1,1900) +( (excdate - 2) * 24 * 60 * 60).
> FORMATS NEWDATE (DATE14).
> EXECUTE.
>
> where "excdate" is the current date variable.  This did convert to the date
> format, but it is about 60 years earlier that it should be (i.e.,
> 1948-1950).  The SPSS suggested solution is for an excel file import and by
> file was in csv.  I don't know enough about imports to know if that could be
> the problem.
>
> Any and all suggestions and help would be appreciated!
>
> PAC
>
> --
> View this message in context: http://spssx-discussion.1045642.n5.nabble.com/Reading-Date-Time-from-CSV-File-tp3381430p4372785.html
> Sent from the SPSSX Discussion mailing list archive at Nabble.com.
>
> =====================
> To manage your subscription to SPSSX-L, send a message to
> [hidden email] (not to SPSSX-L), with no body text except the
> command. To leave the list, send the command
> SIGNOFF SPSSX-L
> For a list of commands to manage subscriptions, send the command
> INFO REFCARD
>

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

[Garry Gelade]

Phyllis

How are you opening the csv file in SPSS? Are you going via Excel?

If you open the csv as a text file, using the text import wizard, you can
specify the relevant field as ADATE10.

The following syntax (pasted from the wizard works) with a csv file containg
dates in the format mm/dd/yyyy.

GET DATA
  /TYPE=TXT
  /FILE="C:\dates.csv"
  /FIXCASE=1  /ARRANGEMENT=FIXED  /FIRSTCASE=1 /IMPORTCASE=ALL   /VARIABLES=
/1 mydatevar 0-9 ADATE10.
CACHE.
EXECUTE.
DATASET NAME DataSet1 WINDOW=FRONT.

HTH

Garry

-----Original Message-----
From: Cummins, Phyllis Ann [mailto:[hidden email]]
Sent: 05 May 2011 18:04
To: Garry Gelade
Subject: RE: Reading Date & Time from CSV File

Garry,
This is how dates are formatted in the csv file:

,07/14/2010,

The command below is what I found on the SPSS website, but the result is 60
years off from where the dates should be.  When I changed the "1900" to
"1960" the date ranges where more in line with what I expected with the
exception of a few outliers.  I want to check the dates on a few cases to
see if the recoded date (using 1960) in SPSS matches the csv date. The
original data file has over 5 million cases, but with the sorting I have
done so I'm just looking at the cases of interest, the file is about
300,000.

COMPUTE NEWDATE = DATE.MDY(1,1,1900) +( (excdate - 2) * 24 * 60 * 60).
FORMATS NEWDATE (DATE14).
EXECUTE.

PAC

-----Original Message-----
From: Garry Gelade [mailto:[hidden email]]
Sent: Thursday, May 05, 2011 12:11 PM
To: Cummins, Phyllis Ann
Subject: RE: Reading Date & Time from CSV File

What you need to do depends on how the date is formatted within the csv. To
see this, you will need to inspect the raw csv, for example by opening it in
Notepad.  Do not rely on the formatting you see when you open the file in
Excel.


Garry Gelade

-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of
cumminpa
Sent: 05 May 2011 16:32
To: [hidden email]
Subject: Re: Reading Date & Time from CSV File

I still can't make it work.  When I used the syntax you provided, all that
showed up in my new variable were missing values.  I've searched for the v18
reference you mentioned and can't find anything useful; I found various
reference guides on the IBM website, but none had anything relevant on page
63.

--
View this message in context:
http://spssx-discussion.1045642.n5.nabble.com/Reading-Date-Time-from-CSV-Fil
e-tp3381430p4373107.html
Sent from the SPSSX Discussion mailing list archive at Nabble.com.

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

[Richard Ristow]

At 08:26 AM 5/5/2011, Cummins, Phyllis Ann wrote:

>My SPSS file was created from a csv file with
>dates formatted at MM/DD/YYYY; the dates should
>be from about 04/01/2008 to 09/30/2010. In SPSS
>they come up as 5 digits ranging from 17623 to 18633.

The 5-digit numbers look like 'offset' numbers,
i.e. number of days from some reference date;
however, the two dates you give are 912 days
apart, and the two numbers differ by 1,010, so
maybe they aren't number of days after all. Could
you give us a few exact correspondences between
dates in the CSV file and the numbers they become in SPSS?

At 10:53 AM 5/5/2011, Marta García-Granero wrote:
>Have you taken a look at your CSV file using a text editor?

That's a very good idea. Sometimes SPSS fails to
import a column correctly just because of one or
two strange values in it -- for example, integer or character instead of date.

You wrote,
>At the SPSS website, and the posted suggestion was:
>
>COMPUTE NEWDATE = DATE.MDY(1,1,1900) +( (excdate - 2) * 24 * 60 * 60).
>FORMATS NEWDATE (DATE14).
>
>where "excdate" is the current date
>variable.  This did convert to the date format,
>but it is about 60 years earlier that it should be (i.e.,
>1948-1950).

Right. That's for Excel dates, for which
09/30/2010 is day 40,451; you don't have those.
You may have something similar (though I'm not
sure what, or how); if so, some 'before and after' values would help.

-Best of luck,
  Richard Ristow

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

[David Marso]

OK!!!
This thread is ALL OVER THE PLACE and I don't believe it is progressing too far and we are having to make assumptions about what you really have and the exact syntax you are using on what data and what is resulting from this syntax!!!
If you REALLY have a text file with elements of the form 07/14/1990 ...
then reading it as ADATE **WILL WORK**!!
If you are getting system missing values then your data are screwy and DON'T LOOK LIKE mm/dd/yyyy .
OTOH, if you re applying the
COMPUTE NEWDATE = DATE.MDY(1,1,1900) +( (excdate - 2) * 24 * 60 * 60).
FORMATS NEWDATE (DATE14).
to an already formatted DATE variable then you will get totally screwy stuff.
AND if you are applying this formula to a numeric field and it is off by 60 years (sort of but not really ...GACK...) Then you need to figure out where the data came from.  What is a correct value for at least 2 of the data elements and then make some assumptions stronger than (1960 as the starting date).
Linear regression might be useful for this or the documentation on the source software which created the file.
So.... Please help us help you and provide the necessary details...

[cumminpa]

Sorry not to be providing more useful information.  I received the data on a CD from the Department of Labor and the file had to be set up with the syntax and data file provided.  The file was so large (more than 5 million cases) that I was unable to do it on my home pc (with 8gb of memory) and one of my professors set it up for me.  Unfortunately, I can’t ask him to help me with this.

 

The variable in question is names “exit_dt” and I substituted it for “excdate” in the syntax shown below.  What shows up in that variable (exit_dt) before the recode are numbers ranging from 17623 to 18633.   After the recode using the syntax sent previously, the dates range from 03/31/1948 to  01/05/1951 (97 of 382076 cases were past 9/30/1950; I have filtered out many cases because they did not meet my age criteria).  The dates should range from 04/01/2008 to 9/30/2010, although there could be some dates entered incorrectly which might cause some outliers.  When I changed the “1900” in the syntax to “1960” the date range was 03/30/2008 – 01/04/2011.

 

I asked someone who has used this data file previously, but in SAS, how to solve the problem and this was his response:

 

“In the csv file the dates are MM/DD/YYYY.  However, if you read the dates into SAS they are stored in SAS’s internal date format, which give numbers like the ones you indicate (ie., 17623-18633).  You should look at the SAS documentation on how to deal with dates.  If you use the format yyq6. for a frequency, you will get calendar quarters.”

 

I found the syntax I used on the SPSS (IBM) website and tried to get help from IBM before posting, and have had no response at all from IBM.

 

I hope this provides useful information.

 

From: David Marso [via SPSSX Discussion] [mailto:[hidden email]]
Sent: Thursday, May 05, 2011 3:55 PM
To: Cummins, Phyllis Ann
Subject: Re: Reading Date & Time from CSV File

 

OK!!!
This thread is ALL OVER THE PLACE and I don't believe it is progressing too far and we are having to make assumptions about what you really have and the exact syntax you are using on what data and what is resulting from this syntax!!!
If you REALLY have a text file with elements of the form 07/14/1990 ...
then reading it as ADATE **WILL WORK**!!
If you are getting system missing values then your data are screwy and DON'T LOOK LIKE mm/dd/yyyy .
OTOH, if you re applying the
COMPUTE NEWDATE = DATE.MDY(1,1,1900) +( (excdate - 2) * 24 * 60 * 60).
FORMATS NEWDATE (DATE14).
to an already formatted DATE variable then you will get totally screwy stuff.
AND if you are applying this formula to a numeric field and it is off by 60 years (sort of but not really ...GACK...) Then you need to figure out where the data came from.  What is a correct value for at least 2 of the data elements and then make some assumptions stronger than (1960 as the starting date).
Linear regression might be useful for this or the documentation on the source software which created the file.
So.... Please help us help you and provide the necessary details...

---

If you reply to this email, your message will be added to the discussion below:

http://spssx-discussion.1045642.n5.nabble.com/Reading-Date-Time-from-CSV-File-tp3381430p4373942.html

To unsubscribe from Reading Date & Time from CSV File, click here.

[David Marso]

Perhaps this is useful:
"From its inception, the SAS System has stored date values as an offset in days from January 1, 1960. Leap years, century, and fourth-century adjustments are made automatically. Leap seconds are ignored, and the SAS System does not adjust for daylight saving time".... Blah blah blah...
FROM: http://www.pauldickman.com/teaching/sas/dates.php
.....
Is it possible that "professor" read the file into SAS and dumped a nonformatted text file from SAS?
Maybe the formula (adapting the code to use 1960 rather than 1900 is correct) and your stated minimum and maximum dates are WRONG.
OTOH:  It would be REALLY nice if you would post a before processing and after processing version of some of your date values.
WHAT DOES THE FIRST 10 lines of the text file actually look like.
WHAT SYNTAX are you using to read the file?
The amount of memory (8GB) should be fine for reading this file.  
OTOH it will take a LARGE amount of disk space.
"one of my professors set it up for me. "...
Can you at least get the professor to document what he did in setting this up?
--
HTH, OTOH:YMMV given I am making assumptions here and you know what that can do (make an A$$ out of you and me)....

cumminpa wrote

Sorry not to be providing more useful information.  I received the data on a CD from the Department of Labor and the file had to be set up with the syntax and data file provided.  The file was so large (more than 5 million cases) that I was unable to do it on my home pc (with 8gb of memory) and one of my professors set it up for me.  Unfortunately, I can't ask him to help me with this.

The variable in question is names "exit_dt" and I substituted it for "excdate" in the syntax shown below.  What shows up in that variable (exit_dt) before the recode are numbers ranging from 17623 to 18633.   After the recode using the syntax sent previously, the dates range from 03/31/1948 to  01/05/1951 (97 of 382076 cases were past 9/30/1950; I have filtered out many cases because they did not meet my age criteria).  The dates should range from 04/01/2008 to 9/30/2010, although there could be some dates entered incorrectly which might cause some outliers.  When I changed the "1900" in the syntax to "1960" the date range was 03/30/2008 - 01/04/2011.

I asked someone who has used this data file previously, but in SAS, how to solve the problem and this was his response:

"In the csv file the dates are MM/DD/YYYY.  However, if you read the dates into SAS they are stored in SAS's internal date format, which give numbers like the ones you indicate (ie., 17623-18633).  You should look at the SAS documentation on how to deal with dates.  If you use the format yyq6. for a frequency, you will get calendar quarters."

I found the syntax I used on the SPSS (IBM) website and tried to get help from IBM before posting, and have had no response at all from IBM.

I hope this provides useful information.

From: David Marso [via SPSSX Discussion] [mailto:[hidden email]]
Sent: Thursday, May 05, 2011 3:55 PM
To: Cummins, Phyllis Ann
Subject: Re: Reading Date & Time from CSV File

OK!!!
This thread is ALL OVER THE PLACE and I don't believe it is progressing too far and we are having to make assumptions about what you really have and the exact syntax you are using on what data and what is resulting from this syntax!!!
If you REALLY have a text file with elements of the form 07/14/1990 ...
then reading it as ADATE **WILL WORK**!!
If you are getting system missing values then your data are screwy and DON'T LOOK LIKE mm/dd/yyyy .
OTOH, if you re applying the
COMPUTE NEWDATE = DATE.MDY(1,1,1900) +( (excdate - 2) * 24 * 60 * 60).
FORMATS NEWDATE (DATE14).
to an already formatted DATE variable then you will get totally screwy stuff.
AND if you are applying this formula to a numeric field and it is off by 60 years (sort of but not really ...GACK...) Then you need to figure out where the data came from.  What is a correct value for at least 2 of the data elements and then make some assumptions stronger than (1960 as the starting date).
Linear regression might be useful for this or the documentation on the source software which created the file.
So.... Please help us help you and provide the necessary details...
________________________________
If you reply to this email, your message will be added to the discussion below:
http://spssx-discussion.1045642.n5.nabble.com/Reading-Date-Time-from-CSV-File-tp3381430p4373942.html
To unsubscribe from Reading Date & Time from CSV File, click here<http://spssx-discussion.1045642.n5.nabble.com/template/NamlServlet.jtp?macro=unsubscribe_by_code&node=3381430&code=Y3VtbWlucGFAbXVvaGlvLmVkdXwzMzgxNDMwfDE5NTU1MjMwOTM=>.

[cumminpa]

I am almost certain that my professor set up the original file in SAS.  The syntax we received was SAS syntax, so he must have set it up in SAS first, then save/converted to SPSS and also to Stata.  That would explain why using 1960 gave me the data I expected.

From: "David Marso [via SPSSX Discussion]" <[hidden email]>
Date: Thu, 5 May 2011 18:18:17 -0400
To: PHYLLIS CUMMINS <[hidden email]>
Subject: RE: Reading Date & Time from CSV File

Perhaps this is useful:
"From its inception, the SAS System has stored date values as an offset in days from January 1, 1960. Leap years, century, and fourth-century adjustments are made automatically. Leap seconds are ignored, and the SAS System does not adjust for daylight saving time".... Blah blah blah...
FROM: http://www.pauldickman.com/teaching/sas/dates.php
.....
Is it possible that "professor" read the file into SAS and dumped a nonformatted text file from SAS?
Maybe the formula (adapting the code to use 1960 rather than 1900 is correct) and your stated minimum and maximum dates are WRONG.
OTOH:  It would be REALLY nice if you would post a before processing and after processing version of some of your date values.
WHAT DOES THE FIRST 10 lines of the text file actually look like.
WHAT SYNTAX are you using to read the file?
The amount of memory (8GB) should be fine for reading this file.  
OTOH it will take a LARGE amount of disk space.
"one of my professors set it up for me. "...
Can you at least get the professor to document what he did in setting this up?
--
HTH, OTOH:YMMV given I am making assumptions here and you know what that can do (make an A$$ out of you and me)....

cumminpa wrote:

Sorry not to be providing more useful information.  I received the data on a CD from the Department of Labor and the file had to be set up with the syntax and data file provided.  The file was so large (more than 5 million cases) that I was unable to do it on my home pc (with 8gb of memory) and one of my professors set it up for me.  Unfortunately, I can't ask him to help me with this.

The variable in question is names "exit_dt" and I substituted it for "excdate" in the syntax shown below.  What shows up in that variable (exit_dt) before the recode are numbers ranging from 17623 to 18633.   After the recode using the syntax sent previously, the dates range from 03/31/1948 to  01/05/1951 (97 of 382076 cases were past 9/30/1950; I have filtered out many cases because they did not meet my age criteria).  The dates should range from 04/01/2008 to 9/30/2010, although there could be some dates entered incorrectly which might cause some outliers.  When I changed the "1900" in the syntax to "1960" the date range was 03/30/2008 - 01/04/2011.

I asked someone who has used this data file previously, but in SAS, how to solve the problem and this was his response:

"In the csv file the dates are MM/DD/YYYY.  However, if you read the dates into SAS they are stored in SAS's internal date format, which give numbers like the ones you indicate (ie., 17623-18633).  You should look at the SAS documentation on how to deal with dates.  If you use the format yyq6. for a frequency, you will get calendar quarters."

I found the syntax I used on the SPSS (IBM) website and tried to get help from IBM before posting, and have had no response at all from IBM.

I hope this provides useful information.

From: David Marso [via SPSSX Discussion] [mailto:[hidden email]]
Sent: Thursday, May 05, 2011 3:55 PM
To: Cummins, Phyllis Ann
Subject: Re: Reading Date & Time from CSV File

OK!!!
This thread is ALL OVER THE PLACE and I don't believe it is progressing too far and we are having to make assumptions about what you really have and the exact syntax you are using on what data and what is resulting from this syntax!!!
If you REALLY have a text file with elements of the form 07/14/1990 ...
then reading it as ADATE **WILL WORK**!!
If you are getting system missing values then your data are screwy and DON'T LOOK LIKE mm/dd/yyyy .
OTOH, if you re applying the
COMPUTE NEWDATE = DATE.MDY(1,1,1900) +( (excdate - 2) * 24 * 60 * 60).
FORMATS NEWDATE (DATE14).
to an already formatted DATE variable then you will get totally screwy stuff.
AND if you are applying this formula to a numeric field and it is off by 60 years (sort of but not really ...GACK...) Then you need to figure out where the data came from.  What is a correct value for at least 2 of the data elements and then make some assumptions stronger than (1960 as the starting date).
Linear regression might be useful for this or the documentation on the source software which created the file.
So.... Please help us help you and provide the necessary details...
________________________________
If you reply to this email, your message will be added to the discussion below:
http://spssx-discussion.1045642.n5.nabble.com/Reading-Date-Time-from-CSV-File-tp3381430p4373942.html
To unsubscribe from Reading Date & Time from CSV File, click here<

---

If you reply to this email, your message will be added to the discussion below:

http://spssx-discussion.1045642.n5.nabble.com/Reading-Date-Time-from-CSV-File-tp3381430p4374440.html

To unsubscribe from Reading Date & Time from CSV File, click here.

[thara vardhan-2]

Hi Pac

I had faced the same problem earlier on with my data file both in excel and .csv format. Eventually the problem turned put to be that i had the word 'unknown' for the records where no info on the date of birth was available.

SPSS list members were so kind and offered several solutions. All fo them worked for me.

YES I HAVE SAVED A COPY OF THESE AS A SPSS SYNTAX FILE. I ALSO SENT THIS EMAIL TO ALL LIST MEMBERS AND I AM SURE IT IS STORED SOMEWHERE.

Please see the options provided below.

Marta's suggestion is very correct. Please read it in a .txt file and you will know if the date variable has any strings or characters in it.  

cheers
Thara Vardhan
Senior Statistician
Performance Improvement & Planning


Problem:

1. I have a variable pbirthdate in excel which is a date variable. However there are also text  values 'unknown'  in 58 records. ( Sorry - usually I do check but this time I missed it).The variable is a "date" but the cell format is *14/03/2001.

2. When I tried to read the file in SPSS version 17.0 -  'pbirthdate'   05/06/1954 comes into SPSS as a string variable 19849.

3.Solution 1: Offered by Jon Peck  

1.Off list we have determined that missing values in the date column are being represented with text values in Excel.  Fixing that lets SPSS import the date values in the expected way.

2. BTW, a useful trick for diagnosing odd behaviour with Excel imports is to create a column of values in Excel like =TYPE(A1), say, if A is the column that imports oddly.
This produces a column of type codes.  You can then easily scan or tabulate this column to see if the cell type varies.

In general, it's better to fix the Excel sheet rather than patch things up after importing, although both approaches can work.

Jon Peck

3.Creating a column 'Type' in excel gave me values as 1 for a date and 2 for a 'unknown'. There were 58 records with   a value of 2.

4.I Removed the 'unknowns' from the pbirthdate in the excel file.SPSS read them as missing values.  

Solution 2: Offered by Richard Ristow - to fix the problem in SPSS after importing the excel file.

Try, then, using the NUMBER function in SPSS:

COMPUTE  SPSSDate = DATE.MDY(01,01,1900)
                            + TIME.DAYS(NUMBER(Pbirthdate,F5))
                            - TIME.DAYS(1).

Richard Ristow


COMPUTE  
                SPSSEDate = DATE.MDY(01,01,1900)
              + TIME.DAYS(NUMBER(EventReportedDate,F5))
               - TIME.DAYS(1).


This works perfectly but gives output as a numeric in spssdate column.
The unknowns are treated as missing values.
Go to the variable and change the variable type to date in SPSS.
Then check the values with the original in excel file. It matches.

Finally Albert-Jan the syntax suggested by you works fine - but the value in excel file is 14/03/2001- does not have an asterisk - the cell format is *14/03/2001.
Moreover it comes into SPSS as a string variable '19849'  

data list free/funnydate (a12).
begin data
*14/03/2001
end data.
numeric spssdate (edate30).
compute spssdate = date.dmy(
number(substr(funnydate, 2, 2), n2),
number(substr(funnydate, 5, 2), n2),
number(substr(funnydate, 8, 4), n4)).
exe.

Cheers!!
Albert-Jan

thanks
regards
Thara Vardhan
Senior Statistician
Performance Improvement & Planning


Eero Olli wrote:

I am repeatedly importing excel files containing badly formatted dates,
[...]
>There are 3 different types of dates in my funnydates:
>- no date (= zero length) this should be missing, thus no compute is
>needed for the new variable.
>- 39741       (excel date)
>- 19.09.2006  (looks like proper date)
>
>Thanks to Albert-Jan and Richard for providing very readable code.
>By adding a few IF statements one can do them both.

Code for the real-date form can be simplified slightly:

* the real dates.
IF LENGHT(RTRIM(funnydate)) > 6
   spssdate = NUMBER(funnydate,EDATE12).
* excel date format.
IF LENGHT(RTRIM(funnydate)) = 5
   spssdate =  DATE.MDY(01,01,1900)
               + TIME.DAYS(NUMBER(funnydate,F5))
               - TIME.DAYS(1).
FORMATS spssdate(EDATE10).

LIST.
List

Thanks for the kind words! and a good job on your code.

Richard Ristow

_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

The information contained in this email is intended for the named recipient(s) 
only. It may contain private, confidential, copyright or legally privileged 
information.  If you are not the intended recipient or you have received this 
email by mistake, please reply to the author and delete this email immediately. 
You must not copy, print, forward or distribute this email, nor place reliance 
on its contents. This email and any attachment have been virus scanned. However, 
you are requested to conduct a virus scan as well.  No liability is accepted 
for any loss or damage resulting from a computer virus, or resulting from a delay
or defect in transmission of this email or any attached file. This email does not 
constitute a representation by the NSW Police Force unless the author is legally 
entitled to do so.

[Richard Ristow]

At 02:56 PM 5/5/2011, Cummins, Phyllis Ann wrote:

>Here are a few of the combinations:
>
>18289  01/25/2010
>18264  12/31/2009
>18238  12/05/2009
>18078  6/28/2009
>
>The MM/DD/YYYY are using this syntax:
>
>COMPUTE NEWDATE = DATE.MDY(1,1,1960) +( (excdate - 2) * 24 * 60 * 60).
>FORMATS NEWDATE (DATE14).
>EXECUTE.
>
>I've gone through a small sample of cases in the excel file and the
>dates (which are in MM/DD/YYYY format) are 2 days off from what I
>came up with creating the new variable using this syntax in SPSS.

As for the two days off -- see the "excdate-2" in the formula? That's
to correct an oddity in how Excel represents its dates. SAS doesn't
have the same oddity.

Now that you know it's from SAS, your estimate that it's days since
1960 is confirmed. (And, by the way, it also confirms that it's
helpful to give exact pairs of input and output values, not just rough ranges.)

You may want to clean up the syntax, so it doesn't have the two-day
error and things like "24*60*60".  Like this (but not tested):

COMPUTE NEWDATE = DATE.MDY(1,1,1960) + TIME.DAYS(excdate).
FORMATS NEWDATE (DATE14).

Please skip the EXECUTE statement. I think I age a month whenever
somebody posts an EXECUTE statement to the list. They're needed in a
few specific cases; otherwise, they slow your processing and give no benefit.

And I wonder, how did the dates come to be mm/dd/yyyy in the .csv
file? Have you looked at the .csv file itself, rather than, say, a
printout from SAS or how it's displayed in Excel?

I'm glad it's going well, and best wishes to you,
Richard

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

[Marta Garcia-Granero]

El 05/05/2011 21:56, Cummins, Phyllis Ann escribió:

> Here are a few of the combinations:
>
> 18289  01/25/2010
> 18264  12/31/2009
> 18238  12/05/2009
> 18078  6/28/2009
>
> The MM/DD/YYYY are using this syntax:
>
> COMPUTE NEWDATE = DATE.MDY(1,1,1960) +( (excdate - 2) * 24 * 60 * 60).
> FORMATS NEWDATE (DATE14).
> EXECUTE.
>
>
> I've gone through a small sample of cases in the excel file and the dates (which are in MM/DD/YYYY format) are 2 days off from what I came up with creating the new variable using this syntax in SPSS.  I will be looking at data on a quarterly basis, so the exact date is not necessarily relevant.

Does your file come from a Mac? I seem to recall that here is a 2 day
difference in the way dates are stored internally in Mac Excel and PC Excel.

HTH,
Marta GG

> Phyllis Cummins
>
> -----Original Message-----
> From: Richard Ristow [mailto:[hidden email]]
> Sent: Thursday, May 05, 2011 3:21 PM
> To: [hidden email]; Cummins, Phyllis Ann; [hidden email]
> Cc: Marta García-Granero
> Subject: Re: Reading Date&  Time from CSV File
>
> At 08:26 AM 5/5/2011, Cummins, Phyllis Ann wrote:
>
>> My SPSS file was created from a csv file with
>> dates formatted at MM/DD/YYYY; the dates should
>> be from about 04/01/2008 to 09/30/2010. In SPSS
>> they come up as 5 digits ranging from 17623 to 18633.
> The 5-digit numbers look like 'offset' numbers,
> i.e. number of days from some reference date;
> however, the two dates you give are 912 days
> apart, and the two numbers differ by 1,010, so
> maybe they aren't number of days after all. Could
> you give us a few exact correspondences between
> dates in the CSV file and the numbers they become in SPSS?
>
> At 10:53 AM 5/5/2011, Marta García-Granero wrote:
>> Have you taken a look at your CSV file using a text editor?
> That's a very good idea. Sometimes SPSS fails to
> import a column correctly just because of one or
> two strange values in it -- for example, integer or character instead of date.
>
> You wrote,
>> At the SPSS website, and the posted suggestion was:
>>
>> COMPUTE NEWDATE = DATE.MDY(1,1,1900) +( (excdate - 2) * 24 * 60 * 60).
>> FORMATS NEWDATE (DATE14).
>>
>> where "excdate" is the current date
>> variable.  This did convert to the date format,
>> but it is about 60 years earlier that it should be (i.e.,
>> 1948-1950).
> Right. That's for Excel dates, for which
> 09/30/2010 is day 40,451; you don't have those.
> You may have something similar (though I'm not
> sure what, or how); if so, some 'before and after' values would help.
>
> -Best of luck,
>    Richard Ristow
>
>

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

[David Marso]

In retrospect.  Do you NOT believe that the file being created in SAS might have been relevant at this point of the discussion?  It seems that many of us spent much time thinking you had a text file and were reading it using SPSS and the date field went FUBAR for no reason... or that it came from EXCEL...and then you mentioned it had 5 million cases so that puts the EXCEL idea to rest.  In the future please be more forthcoming with regard to WTF you really have and what happened in the middle to make what you ended up with.  It was only by accident that my ESP kicked in and I realized that your file had been molested by a SAS user prior to ending up in SPSS.  So, to quote Homer Simpson...DOH!!!!
Seriously, you are in my ignore file!
---------

cumminpa wrote

This is my first time posting, so I apologize in advance if the solution to my problem is already posted; I looked and could not find it.  My SPSS file was created from a csv file with dates formatted at MM/DD/YYYY, but in SPSS it comes ups as 5 digits ranging from 17623 to 18633.  The dates should be from about 04/01/2008 to 09/30/2010.  I went to the SPSS website and the posted suggestion was:

COMPUTE NEWDATE = DATE.MDY(1,1,1900) +( (excdate - 2) * 24 * 60 * 60).
FORMATS NEWDATE (DATE14).
EXECUTE.

where "excdate" is the current date variable.  This did convert to the date format, but it is about 60 years earlier that it should be (i.e., 1948-1950).  The SPSS suggested solution is for an excel file import and by file was in csv.  I don't know enough about imports to know if that could be the problem.

Any and all suggestions and help would be appreciated!

PAC

[cumminpa]

I apologize.  I am not as familiar with the various softwares as I should be and was not knowledgeable enough to know that the file having been created in SAS then converted to SPSS would have been the issue. And I do have the entire data set in an excel file.  I just can't open the whole file; my own computers don't have enough memory.  It had to be opened on the mainframe at school in order to create the SAS file.  It was in a zip file on the CD I received from the DOL. I won't post again.

Marta, thanks for your help.

PAC

From: "David Marso [via SPSSX Discussion]" <[hidden email]>
Date: Fri, 6 May 2011 02:42:06 -0400
To: PHYLLIS CUMMINS <[hidden email]>
Subject: Re: Reading Date & Time from CSV File

In retrospect.  Do you NOT believe that the file being created in SAS might have been relevant at this point of the discussion?  It seems that many of us spent much time thinking you had a text file and were reading it using SPSS and the date field went FUBAR for no reason... or that it came from EXCEL...and then you mentioned it had 5 million cases so that puts the EXCEL idea to rest.  In the future please be more forthcoming with regard to WTF you really have and what happened in the middle to make what you ended up with.  It was only by accident that my ESP kicked in and I realized that your file had been molested by a SAS user prior to ending up in SPSS.  So, to quote Homer Simpson...DOH!!!!
Seriously, you are in my ignore file!
---------

cumminpa wrote:

This is my first time posting, so I apologize in advance if the solution to my problem is already posted; I looked and could not find it.  My SPSS file was created from a csv file with dates formatted at MM/DD/YYYY, but in SPSS it comes ups as 5 digits ranging from 17623 to 18633.  The dates should be from about 04/01/2008 to 09/30/2010.  I went to the SPSS website and the posted suggestion was:

COMPUTE NEWDATE = DATE.MDY(1,1,1900) +( (excdate - 2) * 24 * 60 * 60).
FORMATS NEWDATE (DATE14).
EXECUTE.

where "excdate" is the current date variable.  This did convert to the date format, but it is about 60 years earlier that it should be (i.e., 1948-1950).  The SPSS suggested solution is for an excel file import and by file was in csv.  I don't know enough about imports to know if that could be the problem.

Any and all suggestions and help would be appreciated!

PAC

---

If you reply to this email, your message will be added to the discussion below:

http://spssx-discussion.1045642.n5.nabble.com/Reading-Date-Time-from-CSV-File-tp3381430p4375142.html

To unsubscribe from Reading Date & Time from CSV File, click here.

[Richard Ristow]

At 03:24 AM 5/6/2011, cumminpa wrote:

>I am not as familiar with the various softwares as I should be and
>was not knowledgeable enough to know that the file having been
>created in SAS then converted to SPSS would have been the issue.

Certainly, the more we know about a problem, the better our chances
of diagnosing it. I join in encouraging questioners to describe
problems fully, recognizing that you may not spot what details are
relevant. But it's impossible to give 'everything' about a problem,
and nobody's always right about what is significant to say. If you're
newer to SPSS your judgement about significant details will be less
reliable; but you're just whom we hope to be helping.

I hope we can accept that, with the best will in the world,
questioners won't always judge rightly what's relevant. I hope that
both questioners and regular participants accept that solving a tough
problem may take several exchanges to request additional information.

>I won't post again.

I will be most sorry if you, or anyone else, feels discouraged from doing so.

-With best wishes,
  Richard

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

[Francisco Araújo]

Please, see
https://www.ibm.com/support/knowledgecenter/en/SSLVMB_20.0.0/com.ibm.spss.statistics.help/syn_date_and_time_date_time_formats.htm

* Encoding: UTF-8.
*
**********************************************************************************************************************.
* PRG_01_GERAR_SORTEIOS.
* By Prof. Francisco Araújo.
* BRAZIL, 26/10/2017.
*
**********************************************************************************************************************.
DATASET NAME Dados WINDOW=FRONT.
DATASET ACTIVATE Dados.

* Importar Dados de POPULAÇÃO.TXT.
GET DATA  /TYPE=TXT
  /FILE="C:\SORTEIOS.txt"
  /ENCODING='Locale'
  /DELCASE=LINE
  /DELIMITERS=";"
  /ARRANGEMENT=DELIMITED
  /FIRSTCASE=1
  /IMPORTCASE=ALL
  /VARIABLES=
  CONCURSO F8.0  
* Here is the solution!!!!!!!!!!!!!!!!
  DATASORTEIO EDATE10
  N1 F2.0
  N2 F2.0
  N3 F2.0
  N4 F2.0
  N5 F2.0
  N6 F2.0.
CACHE.
EXECUTE.

* Rótulos.
   VARIABLE LABELS
           CONCURSO 'Concurso'  
           DATASORTEIO 'Data Sorteio'
           N1 'Numero 1'
           N2 'Numero 2'
           N3 'Numero 3'
           N4 'Numero 4'
           N5 'Numero 5'
           N6 'Numero 6'.

* Medidas (ORDINAL, NOMINAL ou SCALE).
   VARIABLE LEVEL CONCURSO (SCALE) DATASORTEIO (SCALE) N1 TO N6 (SCALE).

* Alinhamento.
   VARIABLE ALIGNMENT CONCURSO (LEFT)
                                         DATASORTEIO (RIGHT)
                                         N1 N2 N3 N4 N5 N6 (CENTER).

* Largura.
  VARIABLE WIDTH CONCURSO (12) DATASORTEIO (12) N1 N2 N3 N4 N5 N6 (4).

* Salvar o arquivo.
   SAVE OUTFILE='C:\SORTEIOS.sav'
             /COMPRESSED.

DATASET CLOSE ALL.
*
**********************************************************************************************************************.



--
Sent from: http://spssx-discussion.1045642.n5.nabble.com/

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD