Converting an OLE OADate format variable to a SAS datetime format

[epiala1]

Hi all,

I am working with a secondary dataset that has datetimes stored in OADate format.  I would like to reformat these variables into a SAS datetime format.  I am working with SAS 9.4.  Is there a way to easily do this?

Thanks in advance for your help.

Liz

[Reeza]

What is an OADAte format? What does it look like and what is the type and format applied currently to that variable?

[epiala1]

Hello Reeza,

The OADate is an OLE automation date that is a floating-point number whose integral component is the number of days before or after midnight, 30 December 1889 and whose fractional component represents the time on that day divided by 24. 

In my dataset the OADate is a BEST12. numeric value in the form xxxxx.yyyyyy where a value of say 42634.000000 is 09/21/2016 at 00:00:00.   

I hope this helps!

[Reeza]

Ok, so SAS stores dates as number of days and datetimes as number of seconds using a base year of January 1, 1960.

So you can do the following to get what you need - testing it of course:

1. Figure out the number of days between January 1 1960 and December 30, 1889 

2. Add that to the integer portion of your number to get the date

3. For the 'time' component, take the decimal and mulitply by the number of seconds in a day (24hrs*60mins*60 secs) to get the number of seconds.

4. Use DHMS() to convert that to a date time value. 

offset = 0 - mdy(12, 30, 1889);

date_want = dhms(int(oadate) + offset, 0, 0, oadate-int(oadate)*24*60*60);

format date_want datetime22.;

You could try reading it in as a datetime and then using INTCK() and incrementing it by the offset as well. Not sure if that would work. 

---

@epiala1 wrote:

Hello Reeza,

The OADate is an OLE automation date that is a floating-point number whose integral component is the number of days before or after midnight, 30 December 1889 and whose fractional component represents the time on that day divided by 24. 

In my dataset the OADate is a BEST12. numeric value in the form xxxxx.yyyyyy where a value of say 42634.000000 is 09/21/2016 at 00:00:00.   

I hope this helps!

---

[epiala1]

Thanks Reeza but something is still not going right.  This is what I have - The number of days between 1889 and 1960 is 25567.

offset = 25567 - mdy(12, 30, 1889);
180 ERROR 180-322: Statement is not valid or it is used out of proper order.

data test; set ct.sample;
triageb = dhms(int(triagebegan) + offset, 0,0, triagebegan-int(triagebegan)*24*60*60);
36 format triageb datetime22.; run;

NOTE: Variable offset is uninitialized.
NOTE: Missing values were generated as a result of performing an operation on missing values.
Each place is given by: (Number of times) at (Line):(Column).
53 at 35:11 53 at 35:33
NOTE: There were 53 observations read from the data set CT.SAMPLE.
NOTE: The data set WORK.TEST has 53 observations and 9 variables.
NOTE: DATA statement used (Total process time):
real time 0.15 seconds
cpu time 0.01 seconds

Thanks.

[Reeza]

Looks your offset line isn't in the data step?
Move that line to after your set statement and before you try and use it.

[epiala1]

Yes, that removed the errors but I am still missing something.  I apologize!!!!

Here is my code:

data test; set ct.sample;
offset = 0 - mdy(12,30,1889);
triageb = dhms(int(triagebegan) + offset, 25567, 0, triagebegan-int(triagebegan)*24*60*60);

format triageb datetime22.; run;

Something is not working in the third line. 

When I run the code my value becomes 02Dec2022 and all the times are the same.

As an example:

43615.576082 = 02Dec2022:03:06:56

43615.583186 = 02Dec2022:03:06:56

Liz

[Reeza]

Sorry, we both made mistakes 🙂

Not sure where you got the second parameter for the DHMS function, that should be 0.

triageb = dhms(int(triagebegan) + offset, 25567, 0, triagebegan-int(triagebegan)*24*60*60);

Second portion - needs to be in brackets to get the decimal portion otherwise order of operations is wrong:

triageb = dhms(int(triagebegan) + offset, 25567, 0, (triagebegan-int(triagebegan))*24*60*60);

Changing things around a bit more this seems to work:

*make fake data;
data have;
	triagebegan=42634.000000;
	output;
	triagebegan=43615.576082;
	output;

data test;
	set have;
	triageb=dhms(int(triagebegan) + mdy(12, 30, 1889)-1, 0, 0, 
		(triagebegan-int(triagebegan))*24*60*60);
	format triageb datetime22.;
run;

proc print data=test;
run;

[epiala1]

Oh, I can't thank you enough. It worked beautifully!

Thanks again Reeza!

Liz