Help using Base SAS procedures

How to convert character datetime to SAS timestamp using proc ds2?

Reply
Occasional Contributor
Posts: 5

How to convert character datetime to SAS timestamp using proc ds2?

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.

Super User
Posts: 5,260

Re: How to convert character datetime to SAS timestamp using proc ds2?

How did it fail and what did you try?
Pls show the code/log.
Data never sleeps
Occasional Contributor
Posts: 5

Re: How to convert character datetime to SAS timestamp using proc ds2?

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(

 

Super User
Posts: 9,687

Re: How to convert character datetime to SAS timestamp using proc ds2?

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.);


? 

Super User
Posts: 6,963

Re: How to convert character datetime to SAS timestamp using proc ds2?


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
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Ask a Question
Discussion stats
  • 4 replies
  • 1687 views
  • 0 likes
  • 4 in conversation