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
- /
- BI
- /
- Enterprise Guide
- /
- SAS EG: Numeric to timestamp

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

06-07-2016 03:46 AM

Good day,

I am using the query builder in SAS EG 4.1.

I am trying to convert a numeric to a timestamp and am not successful.

Example: Numeric: 20091016.11415700

Timestamp: 16OCT2009:11:41:57:000000

I have attached pictures of the numeric and the timestamp format I require the data in.

I have tried using the DHMS function with an informat of Datetime25.6

Accepted Solutions

Solution

06-08-2016
02:28 AM

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

Posted in reply to TomKari

06-07-2016 02:21 PM

And for the somewhat obscure code of the week:

datetime = input(translate(put(x,f15.6),'T','.'), b8601dt.);

All Replies

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

Posted in reply to ahtinuS

06-07-2016 04:15 AM

Before you click yourself to death in the query builder, just write some code:

```
data have;
input _numeric;
cards;
20091016.114157
;
run;
data want (keep=_numeric _timestamp);
set have;
_year = int(_numeric/10000);
_month = int(_numeric/100) - _year * 100;
_day = int(_numeric) - _year * 10000 - _month * 100;
_date = mdy(_month,_day,_year);
_time = int((_numeric - int(_numeric)) * 1000000);
_hours = int(_time/10000);
_minutes = int(_time/100) - _hours * 100;
_seconds = int(_time) - _hours * 10000 - _minutes * 100;
_timestamp = dhms(_date,_hours,_minutes,_seconds);
format _timestamp datetime26.6;
run;
```

Note that fractions of seconds cannot be used with your initial numeric notation, as they would exceed the maximum precision of SAS.

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

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 KurtBremser

06-07-2016 06:13 AM

Thank you for this.

I would still like to know how to do this in SAS EG.

I would still like to know how to do this in SAS EG.

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

Posted in reply to ahtinuS

06-07-2016 06:27 AM

ahtinuS wrote:

Thank you for this.

I would still like to know how to do this in SAS EG.

In a Code window.

You see, since EG is nothing but a code generator, and everything in SAS is done through code, you WILL learn to work with code sooner or later.

So why not start sooner?

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

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 KurtBremser

06-08-2016 02:34 AM

Here is the code that was generated:

**proc** **sql**;

create table res_table as

select t1.cfty_mfts

/* calculation */

(input(translate(put(t1.cfty_mfts,f15.6),'t','.'), b8601dt.)

) format=datetime25.6 as calculation,

from my_table;

**quit**;

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

Posted in reply to ahtinuS

06-07-2016 01:38 PM

Well, here it is. Put in a new computed column, and then copy and paste this formula:

dhms(mdy(int(ts/**100**) - (int(ts/**10000**)* **100**),

int(ts) - (int(ts/**100**) * **100**),

int(ts/**10000**)),

int((ts - int(ts)) * **100**),

int((ts * **100** - int(ts * **100**)) * **100**),

(ts * **10000** - int(ts * **10000**))***100**)

Note that I usually charge at least one beer for this amount of effort!

Very important to note, as @kurtbremser does, that 8 byte floating point can't hold enough digits for you to get fractional seconds.

Tom

Solution

06-08-2016
02:28 AM

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

Posted in reply to TomKari

06-07-2016 02:21 PM

And for the somewhat obscure code of the week:

datetime = input(translate(put(x,f15.6),'T','.'), b8601dt.);

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

Posted in reply to ballardw

06-07-2016 02:34 PM - edited 06-08-2016 08:04 AM

ballardw wrote:

And for the somewhat obscure code of the week:

datetime = input(translate(put(x,f15.6),'T','.'), b8601dt.);

Simple and Brilliant. Wish I caught that.

Actually, I don't think it is obscure at all.

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

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 ballardw

06-08-2016 02:32 AM

And for those who like the code:

**proc** **sql**;

create table res_table as

select t1.cfty_mfts

/* calculation */

(input(translate(put(t1.cfty_mfts,f15.6),'t','.'), b8601dt.)

) format=datetime25.6 as calculation,

from my_table;

**quit**;

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

Posted in reply to ballardw

06-08-2016 08:04 AM

Wow! I love it!!

I never even thought to look at formats and informats for this one.

Tom