topic Re: Converting an OLE OADate format variable to a SAS datetime format in New SAS User

Converting an OLE OADate format variable to a SAS datetime format

epiala1 — Wed, 12 Jun 2019 16:04:27 GMT

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

Re: Converting an OLE OADate format variable to a SAS datetime format

Reeza — Wed, 12 Jun 2019 16:09:46 GMT

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

Re: Converting an OLE OADate format variable to a SAS datetime format

epiala1 — Wed, 12 Jun 2019 16:26:47 GMT

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!

Re: Converting an OLE OADate format variable to a SAS datetime format

Reeza — Wed, 12 Jun 2019 17:13:43 GMT

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!

Re: Converting an OLE OADate format variable to a SAS datetime format

epiala1 — Thu, 13 Jun 2019 16:59:50 GMT

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.

Re: Converting an OLE OADate format variable to a SAS datetime format

Reeza — Thu, 13 Jun 2019 17:16:15 GMT

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.

Re: Converting an OLE OADate format variable to a SAS datetime format

epiala1 — Thu, 13 Jun 2019 19:03:50 GMT

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

Re: Converting an OLE OADate format variable to a SAS datetime format

Reeza — Thu, 13 Jun 2019 19:32:44 GMT

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;

Re: Converting an OLE OADate format variable to a SAS datetime format

epiala1 — Thu, 13 Jun 2019 19:56:04 GMT

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

Thanks again Reeza!

Liz