topic Re: Keep and where statement in SAS Hadoop passthrough in SAS Programming

Keep and where statement in SAS Hadoop passthrough

Adnan2 — Fri, 23 Jun 2023 16:04:48 GMT

Hi All,

just wondering if you can put a keep and where statement with a from statement within a hadoop pass through. See example below - thank you in advance Adnan

proc sql; 
connect to hadoop ( &connect. );
    create table work.test as
    select * from connection to hadoop
    (
select
acct_id, billing_date, balance  FROM datab.acc_history (keep = acct_id, billing_date, balance where acct_id= "123456")
);
disconnect from hadoop;

Re: Keep and where statement in SAS Hadoop passthrough

sbxkoenk — Fri, 23 Jun 2023 16:14:55 GMT

Hello,

Did the LOG say anything strange when you tried this out?

Maybe @FrederikV can help you out?

BR,

Koen

Re: Keep and where statement in SAS Hadoop passthrough

Tom — Fri, 23 Jun 2023 16:25:10 GMT

The code being passed to Hadoop has to be Hadoop code. Not SAS code.

(
select acct_id, billing_date, balance
FROM datab.acc_history 
where acct_id= '123456'
)

So no dataset options. Also most other SQL implementations require single quotes for string constants. Double quotes are used around object (variables or datasets or schemas) names.

Re: Keep and where statement in SAS Hadoop passthrough

LinusH — Mon, 26 Jun 2023 13:30:03 GMT

To be correct, you have used data set options, not statements (KEEP and WHERE).

I see no reason to use them at all, KEEP= does exactly the same as the SELECT statement.

WHERE works equally good as a data set options and as a SQL statement.

In fact, you should be able to write implicit pass through with this logic with the same performance, reducing the complexity of our code, and make it less RDBMS dependent (requires a hadoop libname):

proc sql; 
  create table work.test as
   select acct_id, billing_date, balance  
    FROM datab.acc_history 
    where acct_id= "123456";
quit;