Allows additional database-specific options to be placed after the CREATE TABLE statement in generated SQL code.
| Valid in: | DATA and PROC steps (when creating DBMS tables using SAS/ACCESS software). |
|---|---|
| Category: | Data Set Control |
| Alias: | DBCREATE_TABLE_OPTS= |
| Default: | none |
| Restriction: | Hadoop: The value that you specify for this option cannot contain a PARTIONED BY clause. |
| 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: | DBCREATE_TABLE_OPTS= data set option, DBIDIRECTEXEC system option, POST_TABLE_OPTS= data set option, PRE_STMT_OPTS= data set option, PRE_TABLE_OPTS= data set option |
Table of Contents
specifies database-specific options to be placed after the CREATE TABLE statement. Enclose the options that you specify within single or double quotation marks.
You can use the POST_STMT_OPTS= data set option with these related data set options: PRE_STMT_OPTS, PRE_TABLE_OPTS=, and POST_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;