dateteime variable matching/transformation problem

[Biblin, Janet, BHCS]

Hello helpful folk

I have to match records using a time stamp

Unfortunately, though they originate from the same dataset, the data pull technologies differ such that one set get’s to me as a data and the other as a string.

That part is not in my control, so how do I fix them after the fact? 

 

I do my work using syntax, but have hit the wall on this. I have not had luck with the wizard.

I have to keep the hundredths of the seconds for the key to work.

 

 

 

DATE)       1-Apr-2011 03:56:05:14

STRING)   2011-04-01 16:28:31:20

[Rick Oliver-3]

Not tested:

compute #index=char.index(stringvar, " ").
compute #date=number(substr(stringvar, 1, #index-1), sdate10).
compute #time=number(substr(stringvar, #index+1), time11.2).
compute newdate=#date+#time.
formats newdate (datetime23.2).

Or you could try the date and time wizard to see if it can convert the string.

Rick Oliver
Senior Information Developer
IBM Business Analytics (SPSS)
E-mail: [hidden email]
Phone: [phone] | T/L: 206-4922



From:        "Biblin, Janet, BHCS" <[hidden email]>
To:        [hidden email],
Date:        09/17/2012 12:42 PM
Subject:        dateteime variable matching/transformation problem
Sent by:        "SPSSX(r) Discussion" <[hidden email]>

---

Hello helpful folk
I have to match records using a time stamp
Unfortunately, though they originate from the same dataset, the data pull technologies differ such that one set get’s to me as a data and the other as a string.
That part is not in my control, so how do I fix them after the fact?  
 
I do my work using syntax, but have hit the wall on this. I have not had luck with the wizard.
I have to keep the hundredths of the seconds for the key to work.
 
 
 
DATE)       1-Apr-2011 03:56:05:14
STRING)   2011-04-01 16:28:31:20

[Maguin, Eugene]

Janet,

You could match strings or match numbers. Most importantly, I assume there is not a typo with respect to how hundredths of a second are coded. Correct? So that in the date example value you provide, the time part is 3 hours, 56 minutes, 5.14 seconds??  However, a colon (:) is not valid date-time format element for fractional seconds.  Please review the datetimew.d format to see what I mean.

 

If the fractional seconds weren’t present and you decided to match strings, you’d just use the xdate.xxx functions to extract the date and time components and then use the concat function to reassemble them after converting them to string using the string function.

Alternatively, if the fractional seconds weren’t present and you decided to match numbers, you would extract the date time components using the substr function and reassemble them using the date aggregate functions. With fractional seconds being present and important, I think the seconds component would be added to the sum of the date aggregation and time aggregation with seconds for the time aggregation function being specified as 0. But the presence of the colon instead of a period (or comma for Europe) changes everything.  Clarify this first.

 

Gene Maguin

 

 

 

 

From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Biblin, Janet, BHCS
Sent: Monday, September 17, 2012 1:30 PM
To: [hidden email]
Subject: dateteime variable matching/transformation problem

 

Hello helpful folk

I have to match records using a time stamp

Unfortunately, though they originate from the same dataset, the data pull technologies differ such that one set get’s to me as a data and the other as a string.

That part is not in my control, so how do I fix them after the fact? 

 

I do my work using syntax, but have hit the wall on this. I have not had luck with the wizard.

I have to keep the hundredths of the seconds for the key to work.

 

 

 

DATE)       1-Apr-2011 03:56:05:14

STRING)   2011-04-01 16:28:31:20

[David Marso]

TESTED!!
--
data list / dtSTRING (A22).
BEGIN DATA  
2011-04-01 16:28:31:20
END DATA.
COMPUTE SUBSTR(dtSTRING,20,1)="." .
COMPUTE SUBSTR(dtSTRING,1,10)
       =CONCAT(SUBSTR(dtSTRING,9,2),"-", SUBSTR(dtSTRING,6,2),"-",SUBSTR(dtSTRING,1,4) ).
COMPUTE dtNUM=NUMBER(dtSTRING,DATETIME40).
FORMATS dtNUM (DATETIME40).
COMPUTE dtCOPY=dtNUM .
FORMAT dtCOPY (F20.2).
LIST.


DTSTRING                                                  DTNUM               DTCOPY

01-04-2011 16:28:31.20                     01-APR-2011 16:28:31       13521054511.20


Number of cases read:  1    Number of cases listed:  1

Biblin, Janet, BHCS wrote

Hello helpful folk
I have to match records using a time stamp
Unfortunately, though they originate from the same dataset, the data pull technologies differ such that one set get's to me as a data and the other as a string.
That part is not in my control, so how do I fix them after the fact?

I do my work using syntax, but have hit the wall on this. I have not had luck with the wizard.
I have to keep the hundredths of the seconds for the key to work.



DATE)       1-Apr-2011 03:56:05:14
STRING)   2011-04-01 16:28:31:20