topic How to convert character datetime to SAS timestamp using proc ds2? in SAS Procedures

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

SAS42 — Wed, 14 Sep 2016 19:02:05 GMT

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.

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

LinusH — Wed, 14 Sep 2016 19:50:08 GMT

How did it fail and what did you try?
Pls show the code/log.

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

SAS42 — Wed, 14 Sep 2016 20:04:00 GMT

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(

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

Ksharp — Thu, 15 Sep 2016 02:40:59 GMT

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


?

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

Kurt_Bremser — Thu, 15 Sep 2016 06:59:11 GMT

@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

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

Saranya_Sub — Sun, 12 Aug 2018 04:13:44 GMT

Hi SAS42 ,

Did you find the soultiong for this, am facing the same issue now.can you please help

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

Kurt_Bremser — Sun, 12 Aug 2018 06:50:39 GMT

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.

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

Swagat711 — Tue, 14 Aug 2018 07:38:31 GMT

input(char_column,anydtdtm.) then apply the any date format as per your requirement