SAS EG: Numeric to timestamp

[ahtinuS]

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

[Kurt_Bremser]

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]

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

[Kurt_Bremser]

---

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

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]

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 · Accepted solution]

And for the somewhat obscure code of the week:

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

[Kurt_Bremser]

---

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

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]

Wow! I love it!!

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

Tom