question about dates

[Aysha Abbas]

Hello,I have a few questions re:dates in SPSS

1. How do I convert five digit numbers to date that I get when I import date
field from an excel file into SPSS.

2. I have dates as string variable (like 1/16/2006 12:00:00 AM) to date in
SPSS

3. Can i break the date into three separate variables (dd, mm, yyyy)

Thanks for your assistance

AA

=====================
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

[ViAnn Beadle]

First of all, when you get an Excel file with a column formatted as a date
you should be getting that variable formatted as a date in SPSS. So why is
it coming in as a string? What is the format that Excel shows in the Format
Cell dialog? What is the format that SPSS shows in the variable view after
getting the Excel file? What SPSS commands are you using to read the Excel
file?


-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of
Aysha Abbas
Sent: Monday, December 03, 2007 5:35 PM
To: [hidden email]
Subject: question about dates

Hello,I have a few questions re:dates in SPSS

1. How do I convert five digit numbers to date that I get when I import date
field from an excel file into SPSS.

2. I have dates as string variable (like 1/16/2006 12:00:00 AM) to date in
SPSS

3. Can i break the date into three separate variables (dd, mm, yyyy)

Thanks for your assistance

AA

=====================
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

[Aysha Abbas]

Dates in Excel file are in date format (dd/mm/yy) and it shows as numeric in
the variable view. For reading the Excel file, I go to file and <file>,
<open> and then to <data> and select my data file in excel format.

On 12/3/07, ViAnn Beadle <[hidden email]> wrote:

>
> First of all, when you get an Excel file with a column formatted as a date
> you should be getting that variable formatted as a date in SPSS. So why is
> it coming in as a string? What is the format that Excel shows in the
> Format
> Cell dialog? What is the format that SPSS shows in the variable view after
> getting the Excel file? What SPSS commands are you using to read the Excel
> file?
>
>
> -----Original Message-----
> From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of
> Aysha Abbas
> Sent: Monday, December 03, 2007 5:35 PM
> To: [hidden email]
> Subject: question about dates
>
> Hello,I have a few questions re:dates in SPSS
>
> 1. How do I convert five digit numbers to date that I get when I import
> date
> field from an excel file into SPSS.
>
> 2. I have dates as string variable (like 1/16/2006 12:00:00 AM) to date in
> SPSS
>
> 3. Can i break the date into three separate variables (dd, mm, yyyy)
>
> Thanks for your assistance
>
> AA
>
> =====================
> 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

[ViAnn Beadle]

So what is the format in SPSS? That will determine how to extract parts of
the date.



From: Aysha Abbas [mailto:[hidden email]]
Sent: Tuesday, December 04, 2007 7:07 AM
To: ViAnn Beadle
Cc: [hidden email]
Subject: Re: question about dates



Dates in Excel file are in date format (dd/mm/yy) and it shows as numeric in
the variable view. For reading the Excel file, I go to file and <file>,
<open> and then to <data> and select my data file in excel format.

On 12/3/07, ViAnn Beadle <[hidden email]> wrote:

First of all, when you get an Excel file with a column formatted as a date
you should be getting that variable formatted as a date in SPSS. So why is
it coming in as a string? What is the format that Excel shows in the Format
Cell dialog? What is the format that SPSS shows in the variable view after
getting the Excel file? What SPSS commands are you using to read the Excel
file?


-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of
Aysha Abbas
Sent: Monday, December 03, 2007 5:35 PM
To: [hidden email]
Subject: question about dates

Hello,I have a few questions re:dates in SPSS

1. How do I convert five digit numbers to date that I get when I import date

field from an excel file into SPSS.

2. I have dates as string variable (like 1/16/2006 12:00:00 AM) to date in
SPSS

3. Can i break the date into three separate variables (dd, mm, yyyy)

Thanks for your assistance

AA

=====================
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

[ViAnn Beadle]

Oops, I missed the "numeric" in the variable view. First, change the Numeric
format to a date format to see if you have a date variable. If this is truly
a date variable, you can you use the date/time wizard to extract parts of
the date such as the year, month, and day. Go to Transform>Date and Time
Wizard and select the 5th radio button down. Proceed through the wizard and
paste the syntax to see how it does it.



From: Aysha Abbas [mailto:[hidden email]]
Sent: Tuesday, December 04, 2007 7:07 AM
To: ViAnn Beadle
Cc: [hidden email]
Subject: Re: question about dates



Dates in Excel file are in date format (dd/mm/yy) and it shows as numeric in
the variable view. For reading the Excel file, I go to file and <file>,
<open> and then to <data> and select my data file in excel format.

On 12/3/07, ViAnn Beadle <[hidden email]> wrote:

First of all, when you get an Excel file with a column formatted as a date
you should be getting that variable formatted as a date in SPSS. So why is
it coming in as a string? What is the format that Excel shows in the Format
Cell dialog? What is the format that SPSS shows in the variable view after
getting the Excel file? What SPSS commands are you using to read the Excel
file?


-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of
Aysha Abbas
Sent: Monday, December 03, 2007 5:35 PM
To: [hidden email]
Subject: question about dates

Hello,I have a few questions re:dates in SPSS

1. How do I convert five digit numbers to date that I get when I import date

field from an excel file into SPSS.

2. I have dates as string variable (like 1/16/2006 12:00:00 AM) to date in
SPSS

3. Can i break the date into three separate variables (dd, mm, yyyy)

Thanks for your assistance

AA

=====================
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

[Aysha Abbas]

When I change to numeric from date in the variable view, I loose all the
data for the date variable.

On 12/4/07, ViAnn Beadle <[hidden email]> wrote:

>
>  Oops, I missed the "numeric" in the variable view. First, change the
> Numeric format to a date format to see if you have a date variable. If this
> is truly a date variable, you can you use the date/time wizard to extract
> parts of the date such as the year, month, and day. Go to Transform>Date and
> Time Wizard and select the 5th radio button down. Proceed through the
> wizard and paste the syntax to see how it does it.
>
>
>
> *From:* Aysha Abbas [mailto:[hidden email]]
> *Sent:* Tuesday, December 04, 2007 7:07 AM
> *To:* ViAnn Beadle
> *Cc:* [hidden email]
> *Subject:* Re: question about dates
>
>
>
> Dates in Excel file are in date format (dd/mm/yy) and it shows as numeric
> in the variable view. For reading the Excel file, I go to file and <file>,
> <open> and then to <data> and select my data file in excel format.
>
> On 12/3/07, *ViAnn Beadle* <[hidden email]> wrote:
>
> First of all, when you get an Excel file with a column formatted as a date
> you should be getting that variable formatted as a date in SPSS. So why is
>
> it coming in as a string? What is the format that Excel shows in the
> Format
> Cell dialog? What is the format that SPSS shows in the variable view after
> getting the Excel file? What SPSS commands are you using to read the Excel
>
> file?
>
>
> -----Original Message-----
> From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of
> Aysha Abbas
> Sent: Monday, December 03, 2007 5:35 PM
> To: [hidden email]
> Subject: question about dates
>
> Hello,I have a few questions re:dates in SPSS
>
> 1. How do I convert five digit numbers to date that I get when I import
> date
> field from an excel file into SPSS.
>
> 2. I have dates as string variable (like 1/16/2006 12:00:00 AM) to date in
> SPSS
>
> 3. Can i break the date into three separate variables (dd, mm, yyyy)
>
> Thanks for your assistance
>
> AA
>
> =====================
> 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

[Maguin, Eugene]

All,

A question about the Any function. I want to test Temp for the presence of
any of a set of characters (example below). I am sure I have seen various
people on the list use Any in manner shown below even though the
documentation does not illustrate such an example. [According to the
documentation I should say

ANY(CHAR,"a","b","c","d","e","f","g","j","~","#","*","'")].

But, this I don't recall being used. Am I just not remembering correctly, is
there a secret trick, or what.




TEMP(A10)
Reso@ving
Safe Sch'o
S4hool saf
Four Year*
An In~egra
Preliminar
Conflict R
Public Hea
Youth acti
Preventing

STRING CHAR(A1) TEMP(A10).
VECTOR T(10,F1.0).
LOOP #I=1 TO 10.
+  COMPUTE CHAR=SUBSTR(TITLE,#I,1).
+  COMPUTE T(#I)=0.
+  IF (ANY(CHAR,"abcdefgj~#*'") EQ 1) T(#I)=1.
END LOOP.
EXECUTE.


Thanks, Gene Maguin

=====================
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 07:35 PM 12/3/2007, Aysha Abbas wrote:

>1. How do I convert five digit numbers to date that I get when I
>import date field from an excel file into SPSS.

Excel dates are supposed to be imported as SPSS date values, and they
usually are.

You write "five digit numbers", and that raises another possibility for me.

SOMETIMES (I don't know all the circumstances), the numeric value
underlying the Excel date is imported instead. If you have dates not
too far from the present, and your five-digit numbers are in the
30,000s, that may be what's happening to you.

There are things to do about it, but I won't start on those unless it
looks like this is your problem.

=====================
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

[Aysha Abbas]

The five digit numbers range from 37986 to 38832.
thanks


On 12/4/07, Richard Ristow <[hidden email]> wrote:

>
> At 07:35 PM 12/3/2007, Aysha Abbas wrote:
>
> >1. How do I convert five digit numbers to date that I get when I
> >import date field from an excel file into SPSS.
>
> Excel dates are supposed to be imported as SPSS date values, and they
> usually are.
>
> You write "five digit numbers", and that raises another possibility for
> me.
>
> SOMETIMES (I don't know all the circumstances), the numeric value
> underlying the Excel date is imported instead. If you have dates not
> too far from the present, and your five-digit numbers are in the
> 30,000s, that may be what's happening to you.
>
> There are things to do about it, but I won't start on those unless it
> looks like this is your problem.
>
>

=====================
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 10:06 AM 12/4/2007, Gene Maguin wrote:

>A question about the Any function. I want to test Temp for the
>presence of any of a set of characters (example below).  I am sure I
>have seen various people on the list use Any in manner shown below
>even though the documentation does not illustrate such an example.

No, it doesn't work the way you've used it ...

>[According to the documentation I should say
>
>ANY(CHAR,"a","b","c","d","e","f","g","j","~","#","*","'")].

Right; that's the way. The statement

+  IF (ANY(CHAR,"abcdefgj~#*'") EQ 1) T(#I)=1.

tests for the *whole* string "abcdefgj~#*'" occurring in CHAR - as,
of course it doesn't.

>Am I just not remembering correctly, is there a secret trick, or what.

There's a trick, but it uses INDEX, not ANY. I'm doing it the easy
way, which notes only the first occurrence on one of the characters.
Note that it doesn't need a LOOP. (And I'm fixing one error: using
TITLE in the test code, instead of TEMP, the test variable.)

STRING CHAR(A1).
VECTOR T(10,F1.0).
VECTOR T_B(10,F1.0).
LOOP #I=1 TO 10.
+  COMPUTE CHAR=SUBSTR(TEMP,#I,1).
+  COMPUTE T(#I)=0.
+  IF (ANY(CHAR,"abcdefgj~#*'") EQ 1) T(#I)=1.
+  COMPUTE T_B(#I)=0.
+  IF (ANY(CHAR,
            "a","b","c","d","e","f","g","j","~","#","*","'"))
            T_B(#I) = 1.
END LOOP.

*  The trick with "INDEX". Note numeric 3rd argument to INDEX.
NUMERIC IX(F3).
COMPUTE IX = INDEX(TEMP,"abcdefgj~#*'",1).

TEMPORARY.
STRING SPACE(A16).
LIST.

List
|-----------------------------|---------------------------|
|Output Created               |04-DEC-2007 12:39:42       |
|-----------------------------|---------------------------|
            C                     T T T
            H                   T _ _ _                   T_
            A T T T T T T T T T 1 B B B T_ T_ T_ T_ T_ T_ B1
TEMP       R 1 2 3 4 5 6 7 8 9 0 1 2 3 B4 B5 B6 B7 B8 B9 0   IX SPACE

Reso@ving    0 0 0 0 0 0 0 0 0 0 0 1 0  0  0  0  0  0  1  0   2
Safe         0 0 0 0 0 0 0 0 0 0 0 1 1  1  0  0  0  0  0  0   2
S4hool       0 0 0 0 0 0 0 0 0 0 0 0 0  0  0  0  0  0  0  0   0
Four         0 0 0 0 0 0 0 0 0 0 0 0 0  0  0  0  0  0  0  0   0
An           0 0 0 0 0 0 0 0 0 0 0 0 0  0  0  0  0  0  0  0   0
Preliminar r 0 0 0 0 0 0 0 0 0 0 0 0 1  0  0  0  0  0  1  0   3
Conflict     0 0 0 0 0 0 0 0 0 0 0 0 0  1  0  0  1  0  0  0   4
Public       0 0 0 0 0 0 0 0 0 0 0 0 1  0  0  1  0  0  0  0   3
Youth        0 0 0 0 0 0 0 0 0 0 0 0 0  0  0  0  0  0  0  0   0
Preventing g 0 0 0 0 0 0 0 0 0 0 0 0 1  0  1  0  0  0  0  1   3
===========================================
APPENDIX: Test data (from original posting)
===========================================
DATA LIST LIST
  /TEMP(A10).
BEGIN DATA
Reso@ving
Safe Sch'o
S4hool saf
Four Year*
An In~egra
Preliminar
Conflict R
Public Hea
Youth acti
Preventing
END DATA.

=====================
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

[Hashmi, Syed S]

Aysha:

Like ViAnn mentioned, if you're importing dates which are in the EXCEL
date format, they should get imported as dates in SPSS as well.

As for how to separate a date into three separate m, d and y variables,
the best way to learn that is to go through the "Date and Time Wizard"
in SPSS (Transform --> Date and time wizard), select the transformation
you want and paste the syntax to see how it works.  That being said, the
syntax to do what you're asking would be the following:



        /*  syntax to create a yyyy var from a date var */
        /*  date var name = fulldate                            */
        /*  yyyy var name = year                                */

        COMPUTE year = XDATE.YEAR(fulldate).
        VARIABLE LABEL year "Year part of fulldate".
        FORMATS year (F4.0).


To extract the day or the month, use XDATE.MDAY and XDATE.MONTH,
respectively, in the code above.

HTH.


- Shahrukh Hashmi








> -----Original Message-----
> From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf
Of
> Aysha Abbas
> Sent: Monday, December 03, 2007 6:35 PM
> To: [hidden email]
> Subject: question about dates
>
> Hello,I have a few questions re:dates in SPSS
>
> 1. How do I convert five digit numbers to date that I get when I
import
> date
> field from an excel file into SPSS.
>
> 2. I have dates as string variable (like 1/16/2006 12:00:00 AM) to
date in

> SPSS
>
> 3. Can i break the date into three separate variables (dd, mm, yyyy)
>
> Thanks for your assistance
>
> AA
>
> =====================
> 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]

Aha!

At 11:56 AM 12/4/2007, Aysha Abbas wrote:

>The five digit numbers range from 37986 to 38832.

OK. Excel dates are (normally) represented as a number of days,
starting with day 1=1 January 1900.

Try this conversion, if your input variable is MY_DATE. I'm giving
two versions. One creates new variable MyDateCv, which is safe but
having the two variables may be a nuisance. The other converts 'in
place', which is more convenient but more dangerous.

Code not tested. And LOOK AT THE RESULTS: with the best will in the
world, sometimes they come off one day off, and the code must be adjusted.

COMPUTE   MyDateCv = TIME.DAYS(MY_DATE)
                    + DATE.MDY(01,01,1900)
                    - TIME.DAYS(1).
or
COMPUTE   MY_DATE  = TIME.DAYS(MY_DATE)
                    + DATE.MDY(01,01,1900)
                    - TIME.DAYS(1).

=================
See,
Subject: Re: [SPSSX-L] Date question: I know this is quite simple...
Date: Wed, 19 Oct 2005 08:05:09 -0500
From: "Peck, Jon" <[hidden email]>
To: "Richard Ristow" <[hidden email]>

>Excel actually has two different systems of date values.  The
>default on Windows is [as above], but the Macintosh default, which
>is also available on Windows if you choose it, starts in 1904, so
>the meaning of the day count would be different.
>
>And if you have some excess brain cells that need to be killed off
>with some probably useless information, note this comment from
>http://www.cpearson.com/excel/datetime.htm
>
>The integer portion of the number, ddddd, represents the number of
>days since 1900-Jan-0.  For example, the date 29-Jan-2000 is stored
>as 36,544, since 36,544 days have passed since 1900-Jan-0.  The
>number 1 represents 1900-Jan-1.  It should be noted that the number
>0 does not represent 1899-Dec-31.  It does not. If you use the
>MONTH  function with the date 0, it will return January, not
>December.  Moreover, the YEAR function will return 1900, not 1899.
>
>Actually, this number is one greater than the actual number of
>days.  This is because Excel behaves as if the date 1900-Feb-29
>existed.  It did not.  The year 1900 was not a leap year (the year
>2000 is a leap year).  In Excel, the day after 1900-Feb-28 is
>1900-Feb-29.  In reality, the day after 1900-Feb-28 was 1900-Mar-1
>.  This is not a "bug".  Indeed, it is by design.  Excel works this
>way because it was truly a bug in Lotus 123.  When Excel was
>introduced, 123 had nearly the entire market for spreadsheet
>software.  Microsoft decided to continue Lotus' bug, in order to
>fully compatible.  Users who switched from 123 to Excel would not
>have to make any changes to their data.   As long as all your dates
>later than 1900-Mar-1, this should be of no concern.

=====================
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