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.
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
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.