SAS HADOOP Date Query Question

[JayS]

I'm fairly New to querying HADOOP data from SAS.
The below code returns 0 Obs, and I know there is a InsertDate of '01Aug2020'

Reading up, but have not found the solution as of yet.

I am using this driver... CLASS="com.cloudera.hive.jdbc.HS2Driver" 

Do I need to change the data type in order for HADOOP to resolve the string value.

TIA, Jay

proc sql;

*******************************************************************************

*** All the Connect To Hadoop Schema Table info works, I connect ***

*******************************************************************************

CREATE TABLE NewTable AS
SELECT *
FROM CONNECTION TO hadoop
/* file stored in HADOOP */
/*****************************/
( select * from HADOOPData
Where InsertDate = '01Aug2020' );
disconnect from hadoop;
quit;

[ballardw]

Try using '01Aug2020'd . The d immediately after the value in quotes tells SAS to treat it as a DATE literal, otherwise it would be a character value.

[jimbarbour · Accepted solution]

@JayS,

You're using explicit pass through SQL.  Whenever there are phrases like "FROM CONNECTION TO hadoop" or "disconnect from hadoop", explicit pass through SQL is being used.  Pardon me if you already know this but just in case:  When explicit pass through is used, you're basically telling SAS, just hand this over to the database as is.  In other words, SAS will perform no "translation" from SAS SQL to the SQL (or in this case HQL) of the database.  Therefore all statements must be coded using the syntax of the database not SAS syntax.

In your case, you need to code the date in the format that Hadoop uses, i.e. '2020-08-01'.

Jim

[JayS]

Thank you both for the quick response.

I'll give them a try and confirm my findings.

Thanks again.

Jay

[JayS]

Perfect, thanks Jim....

[jimbarbour]

You're welcome; glad to help out.

Jim