## SAS EG: Numeric to timestamp

Solved
Occasional Contributor
Posts: 12

# SAS EG: Numeric to timestamp

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
Super User
Posts: 13,889

## Re: SAS EG: Numeric to timestamp

And for the somewhat obscure code of the week:

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

All Replies
Super User
Posts: 10,530

## Re: SAS EG: Numeric to timestamp

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
How to convert datasets to data steps
How to post code
Occasional Contributor
Posts: 12

## Re: SAS EG: Numeric to timestamp

Thank you for this.
I would still like to know how to do this in SAS EG.
Super User
Posts: 10,530

## Re: SAS EG: Numeric to timestamp

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
How to convert datasets to data steps
How to post code
Occasional Contributor
Posts: 12

## Re: SAS EG: Numeric to timestamp

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;

PROC Star
Posts: 1,333

## Re: SAS EG: Numeric to timestamp

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
Super User
Posts: 13,889

## Re: SAS EG: Numeric to timestamp

And for the somewhat obscure code of the week:

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

Super User
Posts: 10,530

## Re: SAS EG: Numeric to timestamp

[ Edited ]

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
How to convert datasets to data steps
How to post code
Occasional Contributor
Posts: 12

## Re: SAS EG: Numeric to timestamp

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;

PROC Star
Posts: 1,333

## Re: SAS EG: Numeric to timestamp

Wow! I love it!!

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

Tom

🔒 This topic is solved and locked.