converting string date to numeric

[leah rubin]

Hi,

Would someone be able to help me with the proper syntax for converting a string date to a numeric date.
The string dates are along the lines of:
21106
101106
etc

Thus, you can see that for any month prior to October (10) there is one less number in the string.
The initial code I wrote only works for months after Sept. The code below does not work for strings such as 21106.

Thanks in advance,
Leah

COMPUTE DATE_R=
  DATE.MDY( NUMBER(SUBSTR(DATE,1,2),f2.0),
            NUMBER(SUBSTR(DATE,3,2),f2.0),
            NUMBER(SUBSTR(DATE,5,2),f2.0) ).
FORMATS DATE_R (ADATE).
EXECUTE.

[Bruce Weaver]

Reading between the lines, I infer that your string date variable is formatted MMDDYY, except that when the month is < 10, it is MDDYY.  If that is correct, see if this works:

* Read in some sample data.
data list list / stringdate (a6).
begin data
21106
101106
end data.

if length(rtrim(stringdate)) EQ 5 stringdate = concat("0",stringdate).
numeric newdate (date11).
compute #M = NUMBER(SUBSTR(stringdate,1,2),f2.0).
compute #D = NUMBER(SUBSTR(stringdate,3,2),f2.0).
compute #Y = NUMBER(SUBSTR(stringdate,5,2),f2.0).
compute newdate = date.MDY(#M,#D,#Y).
list.

HTH.

leah rubin wrote

Hi,

Would someone be able to help me with the proper syntax for converting a
string date to a numeric date.
The string dates are along the lines of:
21106
101106
etc

Thus, you can see that for any month prior to October (10) there is one less
number in the string.
The initial code I wrote only works for months after Sept. The code below
does not work for strings such as 21106.

Thanks in advance,
Leah

COMPUTE DATE_R=
  DATE.MDY( NUMBER(SUBSTR(DATE,1,2),f2.0),
            NUMBER(SUBSTR(DATE,3,2),f2.0),
            NUMBER(SUBSTR(DATE,5,2),f2.0) ).
FORMATS DATE_R (ADATE).
EXECUTE.

[ariel barak]

Hi Bruce,

I was looking at this post before seeing your solution and assumed that if the string dates Leah had allowed for month to be M or MM, then it probably allowed for day to be D or DD. This means that the dates could be:

a) MDYY
b) MMDYY
c) MDDYY

I don't think your solution accounts for possibility "a" above, right? If the data that Leah has does indeed always have day as DD, then you've got it.

-Ariel

On Tue, Feb 1, 2011 at 10:15 AM, Bruce Weaver <[hidden email]> wrote:

Reading between the lines, I infer that your string date variable is
formatted MMDDYY, except that when the month is < 10, it is MDDYY.  If that
is correct, see if this works:

* Read in some sample data.
data list list / stringdate (a6).
begin data
21106
101106
end data.

if length(rtrim(stringdate)) EQ 5 stringdate = concat("0",stringdate).
numeric newdate (date11).
compute #M = NUMBER(SUBSTR(stringdate,1,2),f2.0).
compute #D = NUMBER(SUBSTR(stringdate,3,2),f2.0).
compute #Y = NUMBER(SUBSTR(stringdate,5,2),f2.0).
compute newdate = date.MDY(#M,#D,#Y).
list.

HTH.

leah rubin wrote:
>
> Hi,
>
> Would someone be able to help me with the proper syntax for converting a
> string date to a numeric date.
> The string dates are along the lines of:
> 21106
> 101106
> etc
>
> Thus, you can see that for any month prior to October (10) there is one
> less
> number in the string.
> The initial code I wrote only works for months after Sept. The code below
> does not work for strings such as 21106.
>
> Thanks in advance,
> Leah
>
> COMPUTE DATE_R=
>   DATE.MDY( NUMBER(SUBSTR(DATE,1,2),f2.0),
>             NUMBER(SUBSTR(DATE,3,2),f2.0),
>             NUMBER(SUBSTR(DATE,5,2),f2.0) ).
> FORMATS DATE_R (ADATE).
> EXECUTE.
>
>

-----
--
Bruce Weaver
[hidden email]
http://sites.google.com/a/lakeheadu.ca/bweaver/

"When all else fails, RTFM."

NOTE: My Hotmail account is not monitored regularly.
To send me an e-mail, please use the address shown above.

--
View this message in context: http://spssx-discussion.1045642.n5.nabble.com/converting-string-date-to-numeric-tp3366453p3366499.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

[Mario Giesel]

Leah, the easiest way is maybe to insert this syntax as a first step:

 

IF (LENGTH(RTRIM(DATE)) < 6) DATE = CONCAT(SUBSTR(DATE,1,2),"0",SUBSTR(DATE,3)).

If the length of the date field is smaller than 6 then a "0" is inserted, then.

 

Good luck,

 Mario

---

Von: leah rubin <[hidden email]>
An: [hidden email]
Gesendet: Dienstag, den 1. Februar 2011, 16:55:13 Uhr
Betreff: converting string date to numeric

Hi,

Would someone be able to help me with the proper syntax for converting a string date to a numeric date.
The string dates are along the lines of:
21106
101106
etc

Thus, you can see that for any month prior to October (10) there is one less number in the string.
The initial code I wrote only works for months after Sept. The code below does not work for strings such as 21106.

Thanks in advance,
Leah

COMPUTE DATE_R=
  DATE.MDY( NUMBER(SUBSTR(DATE,1,2),f2.0),
            NUMBER(SUBSTR(DATE,3,2),f2.0),
            NUMBER(SUBSTR(DATE,5,2),f2.0) ).
FORMATS DATE_R (ADATE).
EXECUTE.

[Bruce Weaver]

If b and c below are both possible, then there is no way of knowing if 11106 is 1-Nov-2006 or 11-Jan-2006.   So I hope that's not the case.

ariel barak wrote

Hi Bruce,

I was looking at this post before seeing your solution and assumed that if
the string dates Leah had allowed for month to be M or MM, then it probably
allowed for day to be D or DD. This means that the dates could be:

a) MDYY
b) MMDYY
c) MDDYY

I don't think your solution accounts for possibility "a" above, right? If
the data that Leah has does indeed always have day as DD, then you've got
it.

-Ariel

On Tue, Feb 1, 2011 at 10:15 AM, Bruce Weaver <[e-mail]>wrote:

> Reading between the lines, I infer that your string date variable is
> formatted MMDDYY, except that when the month is < 10, it is MDDYY.  If that
> is correct, see if this works:
>
> * Read in some sample data.
> data list list / stringdate (a6).
> begin data
> 21106
> 101106
> end data.
>
> if length(rtrim(stringdate)) EQ 5 stringdate = concat("0",stringdate).
> numeric newdate (date11).
> compute #M = NUMBER(SUBSTR(stringdate,1,2),f2.0).
> compute #D = NUMBER(SUBSTR(stringdate,3,2),f2.0).
> compute #Y = NUMBER(SUBSTR(stringdate,5,2),f2.0).
> compute newdate = date.MDY(#M,#D,#Y).
> list.
>
> HTH.
>
>
>
> leah rubin wrote:
> >
> > Hi,
> >
> > Would someone be able to help me with the proper syntax for converting a
> > string date to a numeric date.
> > The string dates are along the lines of:
> > 21106
> > 101106
> > etc
> >
> > Thus, you can see that for any month prior to October (10) there is one
> > less
> > number in the string.
> > The initial code I wrote only works for months after Sept. The code below
> > does not work for strings such as 21106.
> >
> > Thanks in advance,
> > Leah
> >
> > COMPUTE DATE_R=
> >   DATE.MDY( NUMBER(SUBSTR(DATE,1,2),f2.0),
> >             NUMBER(SUBSTR(DATE,3,2),f2.0),
> >             NUMBER(SUBSTR(DATE,5,2),f2.0) ).
> > FORMATS DATE_R (ADATE).
> > EXECUTE.
> >
> >
>
>
> -----
> --
> Bruce Weaver
> [e-mail]
> http://sites.google.com/a/lakeheadu.ca/bweaver/
>
> "When all else fails, RTFM."
>
> NOTE: My Hotmail account is not monitored regularly.
> To send me an e-mail, please use the address shown above.
>
> --
> View this message in context:
> http://spssx-discussion.1045642.n5.nabble.com/converting-string-date-to-numeric-tp3366453p3366499.html
> Sent from the SPSSX Discussion mailing list archive at Nabble.com.
>
> =====================
> To manage your subscription to SPSSX-L, send a message to
> [e-mail] (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
>