BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Anna_nag
Obsidian | Level 7

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.

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Anna_nag
Obsidian | Level 7

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

6 REPLIES 6
Kurt_Bremser
Super User

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.

ballardw
Super User

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.

Anna_nag
Obsidian | Level 7
Yes, It is coming from excel. I tried playing around with formats in excel but in excel its resolving to #### once applied format.
Kurt_Bremser
Super User

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.

ballardw
Super User

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".

 

Anna_nag
Obsidian | Level 7

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"

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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