Desktop productivity for business analysts and programmers

SAS EG: Numeric to timestamp

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 12
Accepted Solution

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


Numeric.pngTimestamp.png

Accepted Solutions
Solution
‎06-08-2016 02:28 AM
Super User
Posts: 11,114

Re: SAS EG: Numeric to timestamp

And for the somewhat obscure code of the week:

 

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

View solution in original post


All Replies
Super User
Posts: 7,405

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
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: 7,405

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
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,146

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: 11,114

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: 7,405

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
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,146

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.

Need further help from the community? Please ask a new question.

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