topic Re: How to convert timestamp to "yyyy-mm-dd hh:mm:ss.sss" without T in SAS Data Science

How to convert timestamp to "yyyy-mm-dd hh:mm:ss.sss" without T

zdk — Fri, 21 Feb 2020 08:14:18 GMT

Hello everyone,

I want to export SAS data to Hive.

Tpye timestamp in hive is "yyyy-mm-dd hh:mm:ss.sss",but when I used format E8601DTw.d in SAS,it appeared as

"yyyy-mm-ddT hh:mm:ss.sss",how can I deal with it without "T",or do you have other better solutions?

I really appreciate for your help.

Re: How to convert timestamp to "yyyy-mm-dd hh:mm:ss.sss" without T

LinusH — Fri, 21 Feb 2020 08:36:04 GMT

It sounds like you are using a STRING column in Hive.

If you are on .12 or higher, this is not the recommended approach.

Use a TIMESTAMP data type instead, and SAS/ACCESS should be able to map it, thus no need to specify a SAS ISO datetime format.

"Highlighting New Hive .12 Data Types

This chapter presents considerations for sharing data between Hive and SAS. For example, you need to know how to identify the Hive STRING columns that contain DATE, TIME, or TIMESTAMP information. You also need to know how to minimize the length of SAS character columns that are created from Hive STRING columns. Where you use the VARCHAR, DATE, and TIMESTAMP data types that are available in Hive .12 and later, you optimize sharing data between Hive and SAS. For example, the SASFMT table properties that are described later are not needed for Hive columns that are declared VARCHAR, DATE, and TIMESTAMP."

Re: How to convert timestamp to "yyyy-mm-dd hh:mm:ss.sss" without T

ed_sas_member — Fri, 21 Feb 2020 10:40:20 GMT

Hi @zdk

Maybe you could try to use a picture format:

proc format;
picture myfmt (default = 50) low-high='%Y-%0m-%0d %0H:%0M:%0S' (datatype=datetime);
run;

Re: How to convert timestamp to "yyyy-mm-dd hh:mm:ss.sss" without T

yabwon — Fri, 21 Feb 2020 10:36:56 GMT

Hi,

Maybe your own format:

proc format; 
  picture myTimestamp (default=19)
          other='%Y-%0m-%0d %0H:%0M:%0S' (datatype=datetime);
run;


data test;
  a = datetime();
  format a myTimestamp.;
run;
proc print;
run;

All the best

Bart