SAS Programming

DATA Step, Macro, Functions and more
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

Special offer for SAS Communities members

Save $250 on SAS Innovate and get a free advance copy of the new SAS For Dummies book! Use the code "SASforDummies" to register. Don't miss out, May 6-9, in Orlando, Florida.

 

View the full agenda.

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
  • 524 views
  • 0 likes
  • 2 in conversation