ERROR: Connection is missing required option SERVER - Proc sql execute statment connection to hadoop

[Koti]

Getting below error message while creating table in hadoop via proc sql execute statement.Is there any server options or setting do i need to set to work with execute statement? Note:This code working with out execute statment.

ERROR: Connection is missing required option SERVER.

Code:

proc   sql;
connect to hadoop as sashdp(server=servername port=10000 schema=schemaname subprotocol=hive2);
execute(create table test08_sas row format delimited fields terminated by '\001'
                 stored as textfile as select * from connection to sashdp ( select *,date_sub('2016-08-31',99) as date_sub_value,
case when col1>="2016-01-23 then "2016-08-30"
else "2016-07-31" end as new_col1
from  table1

limit 10
))by hadoop;
quit;

[DanielSantos]

Hmmm.

Is there really a server/schema named servername/schemaname in your system?

Because that's what you are providing for your connection

connect to hadoop as sashdp(server=servername port=10000 schema=schemaname subprotocol=hive2);

Daniel Santos @ www.cgd.pt

[DavidGhan]

The issue here is that you are trying to embed a SAS pass-through query within a SAS EXECUTE pass-through. Instead complete the Pass-through EXECUTE statement by putting the Hive select clause directly in the HiVE CREATE TABLE statement. Like so:

proc sql;

connect to hadoop as sashdp(server=servername port=10000 schema=schemaname subprotocol=hive2);

execute(create table test08_sas row format delimited fields terminated by '\001'

stored as textfile as select *,date_sub('2016-08-31',99) as date_sub_value,

case when col1>="2016-01-23 then "2016-08-30"

else "2016-07-31" end as new_col1

from table1

limit 10

)by hadoop;

quit;