turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- Base SAS Programming
- /
- Convert numeric "1506950864323.23" to datetime

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-30-2017 10:05 AM

Hi,

I have a data which has a date in numeric for example "1506950864323.23".

I am having a hard time converting this to datetime. I have tried datepart, input, put function etc. but getting either missing or asterisk as output.

Please suggest how to convert "1506950864323.23" numeric to DateTime.

Accepted Solutions

Solution

10-30-2017
11:22 AM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to ballardw

10-30-2017 11:22 AM

I think I got it working.

I have used following to get the data in right DateTime format:

new=(start_time***0.001**)+**"01jan1970 0:0:0"dt** ;

Format new datetime23.3;

which gives me "02OCT2017:13:27:44.323" as a output for "1506950864323.23"

All Replies

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Anna_nag

10-30-2017 10:13 AM

What does this value count? Timestamp values are usually a count of seconds, but this would mean that the time in question would be sometime in the year 49685 (for a zero-point of 1900). Unlikely, and too great a value for SAS.

Look into the documentation of the originating software how timestamps/datetime values are stored there.

---------------------------------------------------------------------------------------------

Maxims of Maximally Efficient SAS Programmers

Maxims of Maximally Efficient SAS Programmers

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Anna_nag

10-30-2017 10:16 AM

You might want to start by explaining where that value came from.

It has no obvious date components, year, month or day, and the value itself is way to large to be a SAS datetime value.

The value 150695086432, which is much smaller than yours, if a datetime value would be in the year 6735.

If this number originated in Excel then tell us so. You may need to manipulate it in Excel before bringing it into SAS, and possibly change how you brought it into SAS.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to ballardw

10-30-2017 10:20 AM

Yes, It is coming from excel. I tried playing around with formats in excel but in excel its resolving to #### once applied format.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Anna_nag

10-30-2017 10:28 AM

Then it is NOT a Excel datetime value, as that are counts of seconds from 1899-12-31. Which would translate to a time I already mentioned in my first post, and explains why Excel itself can't display it. Ask the originator what that value should represent.

---------------------------------------------------------------------------------------------

Maxims of Maximally Efficient SAS Programmers

Maxims of Maximally Efficient SAS Programmers

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Anna_nag

10-30-2017 10:38 AM

The ### in Excel usually means that you column width is too short and you should be able to see value when you make the column wider. But again I think your "value" is too large for the display format.

Excel "datetime" values are numbers of days since 1Jan1900 and the decimal portion is the time of day (.5=half a day=12 hours).

31Dec19999 (yes just before year 20,000) is day in 6588970 in SAS date terms or 6610884 in Excel (but Excel doesn't want to recognize that either). You value is WAY bigger than that. So I'm going to suspect an errant calculation in Excel that someone thinks is a datetime value or possibly reading some logger that reports in hundredths or thousandths of a second or possibly some completely different unit of "time".

Solution

10-30-2017
11:22 AM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to ballardw

10-30-2017 11:22 AM

I think I got it working.

I have used following to get the data in right DateTime format:

new=(start_time***0.001**)+**"01jan1970 0:0:0"dt** ;

Format new datetime23.3;

which gives me "02OCT2017:13:27:44.323" as a output for "1506950864323.23"