Keep and where statement in SAS Hadoop passthrough

[Adnan2]

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;

[sbxkoenk]

Hello,

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

Maybe @FrederikV can help you out?

BR,

Koen

[Tom]

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.

[LinusH]

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;