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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.