BookmarkSubscribeRSS Feed
SAS42
Calcite | Level 5

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.

7 REPLIES 7
LinusH
Tourmaline | Level 20
How did it fail and what did you try?
Pls show the code/log.
Data never sleeps
SAS42
Calcite | Level 5

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(

 

Ksharp
Super User
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.);


? 

Kurt_Bremser
Super User

@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
Saranya_Sub
Obsidian | Level 7
Hi SAS42 ,

Did you find the soultiong for this, am facing the same issue now.can you please help
Swagat711
Fluorite | Level 6
input(char_column,anydtdtm.) then apply the any date format as per your requirement

SAS INNOVATE 2024

Innovate_SAS_Blue.png

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. 

Register now!

What is Bayesian Analysis?

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.

Get the $99 certification deal.jpg

 

 

Back in the Classroom!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 11540 views
  • 0 likes
  • 6 in conversation