BookmarkSubscribeRSS Feed
AJ16
Calcite | Level 5

Hello Experts,

 

I'm querying a DB in Hadoop and need to combine the date and time fields within the query itself. I will then use the combined field to join to another Hadoop table but I'm not able to use cast and concat correctly:

 

Data available in DB:

Dt(string): 2024-06-14

Tm (string): 040507669747

 

Output that I'm trying to create:

Dat_time: 20240614040507669747

 

My query:

proc sql;

connect to impala (dsn=abc user="&sysuserid." pw="&password.");

create table abc as (

select id, dt, tm,

concat(cast(date_format(dt,'yyyymmdd') as string), tm) as dat_time  /*I tried creating this from what I've learned so far but it's giving error*/

from DB);

disconnect from impala;

quit;

 

Error: [Cloudera][Impala ODBC (370) Query analysis error occurred during query execution:  [HY000]: AnalysisException: default.date_format() unknown for database default. Currently this db has 0 functions.

 

I'm unable to figure the correct syntax to make this happen. Would appreciate any guidance.

 

Thank you!

 

1 REPLY 1
SASKiwi
PROC Star

I'd suggest doing the date and time conversion in SAS not Impala. Start with this which should handle the date:

proc sql;
connect to impala (dsn=abc user="&sysuserid." pw="&password.");
create table abc as 
select  *
       ,input(dt, yymmdd10.) as SAS_Date format = date9.
from connection to impala 
(select id, dt, tm,
 from DB
);
disconnect from impala;
quit;

What does your time string represent? Is it hh:mm:ss like 04:05:07.669747 or something else?

sas-innovate-white.png

Missed SAS Innovate in Orlando?

Catch the best of SAS Innovate 2025 — anytime, anywhere. Stream powerful keynotes, real-world demos, and game-changing insights from the world’s leading data and AI minds.

 

Register now

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 1 reply
  • 542 views
  • 0 likes
  • 2 in conversation