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!

2 REPLIES 2
Kurt_Bremser
Super User

What do you get in SAS when you do

libname xx ... /* connection to the DB */;

data abc;
set xx.db;
keep id dt tm;
run;

What types and formats do dt and tm have, and please show some values.

LinusH
Tourmaline | Level 20
Since it sems that you have problems with Hadoop/Impala syntax, it would make sense to reach out to such a forum instead.
Data never sleeps

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
  • 2 replies
  • 1759 views
  • 0 likes
  • 3 in conversation