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
And for the somewhat obscure code of the week:
datetime = input(translate(put(x,f15.6),'T','.'), b8601dt.);
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 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?
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;
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
And for the somewhat obscure code of the week:
datetime = input(translate(put(x,f15.6),'T','.'), b8601dt.);
@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.
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;
Wow! I love it!!
I never even thought to look at formats and informats for this one.
Tom
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!
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.
Ready to level-up your skills? Choose your own adventure.