I would like to convert a character datetime column to SAS timestamp. The dataset is in hadoop and is pretty large.
I tried cast in FedSQL and input statement, they both failed. Can anyone help me with this issue?
Example output:
Character -----------> Timestamp
2001-01-01 00:00:00 --------------------> 01Jan2001 00:00:00
Thank you.
FedSQL can work on small dataset but it cannot run inDatabase so it failed for large dataset.
For input: I used (just try to convert time only for an example)
proc ds2;
data;
dcl time new_time having format time8.;
method run();
set &inputdata.;
new_tm=input(substr(char_datetime,1,2) !! ':' !! substr(char_datetime,4,2) !! ':' !! substr(char_datetime,7,2),time8.);
end;
enddata;
run;
quit;
Here is the log:
ERROR: Compilation error.
ERROR: Parse encountered INPUT when expecting one of: identifier constant expression.
ERROR: Line 30: Parse failed: new_tm= >>> input <<< (substr(
new_tm=input(substr(char_datetime,1,2) !! ':' !! substr(char_datetime,4,2) !! ':' !! substr(char_datetime,7,2),time8.); -----> new_tm=input(char_datetime,anydtdtm32.); ?
@SAS42 wrote:
I would like to convert a character datetime column to SAS timestamp. The dataset is in hadoop and is pretty large.
I tried cast in FedSQL and input statement, they both failed. Can anyone help me with this issue?
Example output:
Character -----------> Timestamp
2001-01-01 00:00:00 --------------------> 01Jan2001 00:00:00
Thank you.
Only needs a simple input function call with a format SAS introduced with version 9.2:
data _null_;
x1 = '2001-01-01 00:00:00';
x2 = input(x1,e8601dt19.);
format x2 datetime19.;
put x2=;
run;
Log:
16 data _null_; 17 x1 = '2001-01-01 00:00:00'; 18 x2 = input(x1,e8601dt19.); 19 format x2 datetime19.; 20 put x2=; 21 run; x2=01JAN2001:00:00:00
Post your question in a new thread. The OP has not been here since 2016, and nobody will see your post but the few who participated in this thread.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.