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.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

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

View all other training opportunities.

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