DATA Step, Macro, Functions and more

Convert numeric "1506950864323.23" to datetime

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 14
Accepted Solution

Convert numeric "1506950864323.23" to datetime

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
3 weeks ago
Occasional Contributor
Posts: 14

Re: Convert numeric "1506950864323.23" to datetime

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"

View solution in original post


All Replies
Super User
Posts: 7,854

Re: Convert numeric "1506950864323.23" to datetime

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
Super User
Posts: 11,343

Re: Convert numeric "1506950864323.23" to datetime

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.

Occasional Contributor
Posts: 14

Re: Convert numeric "1506950864323.23" to datetime

Yes, It is coming from excel. I tried playing around with formats in excel but in excel its resolving to #### once applied format.
Super User
Posts: 7,854

Re: Convert numeric "1506950864323.23" to datetime

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
Super User
Posts: 11,343

Re: Convert numeric "1506950864323.23" to datetime

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
3 weeks ago
Occasional Contributor
Posts: 14

Re: Convert numeric "1506950864323.23" to datetime

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"

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 112 views
  • 0 likes
  • 3 in conversation