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

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


Numeric.pngTimestamp.png
1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

And for the somewhat obscure code of the week:

 

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

View solution in original post

9 REPLIES 9
Kurt_Bremser
Super User

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.

ahtinuS
Fluorite | Level 6
Thank you for this.
I would still like to know how to do this in SAS EG.
Kurt_Bremser
Super User

@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?

ahtinuS
Fluorite | Level 6

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;

TomKari
Onyx | Level 15

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

ballardw
Super User

And for the somewhat obscure code of the week:

 

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

Kurt_Bremser
Super User

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

ahtinuS
Fluorite | Level 6

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;

TomKari
Onyx | Level 15

Wow! I love it!!

 

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

 

Tom

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

Creating Custom Steps in SAS Studio

Check out this tutorial series to learn how to build your own steps in SAS Studio.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 9 replies
  • 5347 views
  • 4 likes
  • 4 in conversation