Allows additional database-specific options to be placed after the table name in a CREATE TABLE statement.
| Valid in: | DATA and PROC steps (when creating DBMS tables using SAS/ACCESS software). |
|---|---|
| Category: | Data Set Control |
| Default: | none |
| Interaction: | For Hadoop, if DBCREATE_TABLE_OPTS= or POST_STMT_OPTS= is specified in a LIBNAME statement, then the value for POST_TABLE_OPTS= data set option is ignored. |
| Data source: | Amazon Redshift, Aster, DB2 under UNIX and PC Hosts, DB2 under z/OS, Greenplum, Hadoop, HAWQ, Impala, Informix, JDBC, Microsoft SQL Server, Netezza, Oracle, PostgreSQL, SAP ASE, SAP IQ, Snowflake, Teradata, Vertica |
| See: | DBIDIRECTEXEC system option, POST_STMT_OPTS= data set option, PRE_STMT_OPTS= data set option, PRE_TABLE_OPTS= data set option |
Table of Contents
specifies additional database-specific options to be placed after the table name in a CREATE TABLE statement.
You can use the POST_TABLE_OPTS= data set option with these related data set options: PRE_STMT_OPTS, POST_STMT_OPTS=, and PRE_TABLE_OPTS=. For example, you can provide database options according to this template:
proc sql;
create table crtab1 ( POST_TABLE_OPTS= "/* post_table_hint */"
PRE_TABLE_OPTS= "/* pre_table_hint */"
POST_STMT_OPTS= "/* post_stmt_hint */"
PRE_STMT_OPTS= "/* pre_stmt_hint */"
) as
select * from rdtab;
<additional-clauses>
quit;The resulting code varies depending on whether the DBIDIRECTEXEC system option is enabled. When DBIDIRECTEXEC is specified, the code might result in this generated SQL code (assuming the columns C1, D1, and E1):
/* pre_stmt_hint */ CREATE /* pre_table_hint */ TABLE DBID20.crtab1
/* post_table_hint */ as ( select TXT_1."C1", TXT_1."D1", TXT_1."E1"
from DBID20.RDTAB TXT_1 ) WITH NO DATA IN UDBID20.USERDATA
/* post_stmt_hint */When you specify NODBIDIRECTEXEC, the code might result in this generated SQL code (assuming the columns C1, D1, and E1):
/* pre_stmt_hint */ CREATE /* pre_table_hint */ TABLE DBID20.CRTAB2
/* post_table_hint */ (C1 FLOAT, D1 CHAR(1), E1 CHAR(1)) IN UDBID20.USERDATA
/* post_stmt_hint */Another usage template that does not use the SQL procedure is shown in this code:
data mylib.crtab1 ( POST_TABLE_OPTS= "/* post_table_hint */"
PRE_TABLE_OPTS= "/* pre_table_hint */"
POST_STMT_OPTS= "/* post_stmt_hint */"
PRE_STMT_OPTS= "/* pre_stmt_hint */"
);
set work.localtable;
run;