topic Re: SAS EG: Numeric to timestamp in SAS Enterprise Guide

SAS EG: Numeric to timestamp

ahtinuS — Tue, 07 Jun 2016 07:46:12 GMT

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

Re: SAS EG: Numeric to timestamp

Kurt_Bremser — Tue, 07 Jun 2016 08:15:41 GMT

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.

Re: SAS EG: Numeric to timestamp

ahtinuS — Tue, 07 Jun 2016 10:13:06 GMT

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

Re: SAS EG: Numeric to timestamp

Kurt_Bremser — Tue, 07 Jun 2016 10:27:19 GMT

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

Re: SAS EG: Numeric to timestamp

TomKari — Tue, 07 Jun 2016 17:38:16 GMT

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

Re: SAS EG: Numeric to timestamp

ballardw — Tue, 07 Jun 2016 18:21:15 GMT

And for the somewhat obscure code of the week:

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

Re: SAS EG: Numeric to timestamp

Kurt_Bremser — Wed, 08 Jun 2016 12:04:26 GMT

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

Re: SAS EG: Numeric to timestamp

ahtinuS — Wed, 08 Jun 2016 06:32:30 GMT

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;

Re: SAS EG: Numeric to timestamp

ahtinuS — Wed, 08 Jun 2016 06:34:07 GMT

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;

Re: SAS EG: Numeric to timestamp

TomKari — Wed, 08 Jun 2016 12:04:06 GMT

Wow! I love it!!

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

Tom