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!
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.
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
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.
Ready to level-up your skills? Choose your own adventure.