SAS Pass through syntax for HADOOP syntax for SAS Buffer errors

[buddha_d]

I have the following code to fix the Buffer Errors in HADOOP sql:

set tez.am.resource.memory.mb=12288;
set hive.tez.container.size=12288;
set tez.runtime.io.sort.mb=1638;
set hive.tez.java.opts=-Xmx8192M;

I don't know how to use it in SAS code in PROC SQL pass through query. 

I tried different options like 
1.

PROC SQL;

connect to hadoop( );
create table WANT as select * from connection to hadoop
(

set tez.am.resource.memory.mb=12288;
set hive.tez.container.size=12288;
set tez.runtime.io.sort.mb=1638;
set hive.tez.java.opts=-Xmx8192M;

select * 

from datasetname
);

disconnect from hadoop;

quit; 

2.

data _null_ ;

set tez.am.resource.memory.mb=12288;
set hive.tez.container.size=12288;
set tez.runtime.io.sort.mb=1638;
set hive.tez.java.opts=-Xmx8192M;

run;

I am getting this error:

ERROR: The libraries for both data sets of data set list (.tez-WORK.Xmx8192M) must be the same.
ERROR 211-185: Invalid data set name.

ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, (, -, :, ;, CUROBS, END, INDSNAME, KEY,
KEYRESET, KEYS, NOBS, OPEN, POINT, _DATA_, _LAST_, _NULL_.

ERROR 76-322: Syntax error, statement will be ignored.

ERROR 200-322: The symbol is not recognized and will be ignored.

Thanks in advance!!!

[yabwon]

Did you tried execute?

PROC SQL;

connect to hadoop( );

execute
(
set tez.am.resource.memory.mb=12288;
set hive.tez.container.size=12288;
set tez.runtime.io.sort.mb=1638;
set hive.tez.java.opts=-Xmx8192M;
) by hadoop;

/* create table query goes here */

disconnect from hadoop;

quit; 

Doc:

https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/sqlproc/n0tpd3yaqvep53n1g8wahav3hgco.htm

[buddha_d]

yabwon,

It didn't work as you suggested. I am getting the following error when I try your code: 

ERROR: Prepare error: [Cloudera][HiveJDBCDriver](500051) ERROR processing query/statement. Error Code: 1, SQL state: Error while
compiling statement: FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.tez.TezTask, Query: select

[yabwon · Accepted solution]

1) Show us more of your log

2) Try execute each set in separate statement, and without the semicolon inside, like:

execute
(
set tez.am.resource.memory.mb=12288
) by hadoop;

Bart

[buddha_d]

This works for me. Giving each statement is the way to go. Thanks a ton yabwon !!!

You Rock. I appreciate it.