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

hackathon24-white-horiz.png

The 2025 SAS Hackathon Kicks Off on June 11!

Watch the live Hackathon Kickoff to get all the essential information about the SAS Hackathon—including how to join, how to participate, and expert tips for success.

YouTube LinkedIn

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
  • 1840 views
  • 0 likes
  • 3 in conversation