Specifies DBMS-specific syntax to add to the end of the CREATE TABLE statement.
| Valid in: | DATA and PROC steps (when accessing DBMS data using SAS/ACCESS software) |
|---|---|
| Category: | Data Set Control |
| Alias: | POST_STMT_OPTS= |
| Default: | LIBNAME option value |
| Restriction: | Hadoop: The value that you specify for this option cannot contain a PARTITIONED BY clause. |
| Data source: | Amazon Redshift, Aster, DB2 under UNIX and PC Hosts, DB2 under z/OS, Google BigQuery, Greenplum, Hadoop, HAWQ, Impala, Informix, JDBC, Microsoft SQL Server, MySQL, Netezza, ODBC, OLE DB, Oracle, PostgreSQL, SAP ASE, SAP HANA, SAP IQ, Snowflake, Teradata, Vertica, Yellowbrick |
| Note: | Support for Yellowbrick was added in SAS 9.4M7. |
| Tip: | If you want all output tables to be in the default (non-TEXTFILE) format, use the LIBNAME option. (See the LIBNAME option for examples.) |
| See: | DBCREATE_TABLE_EXTERNAL= LIBNAME option, DBCREATE_TABLE_EXTERNAL= data set option, DBCREATE_TABLE_LOCATION= data set option, DBCREATE_TABLE_OPTS= LIBNAME option, DBTYPE= data set option, POST_STMT_OPTS= data set option, POST_TABLE_OPTS= data set option, PRE_STMT_OPTS= data set option, PRE_TABLE_OPTS= data set option |
Table of Contents
specifies one or more DBMS-specific clauses that can be appended to the end of an SQL CREATE TABLE statement.
You can use this option to add DBMS-specific clauses at the end of the SQL CREATE TABLE statement. The SAS/ACCESS engine passes the SQL CREATE TABLE statement and its clauses to the DBMS. The DBMS then executes the statement and creates the DBMS table. This option applies only when you are creating a DBMS table by specifying a libref associated with DBMS data.
If you need to add an option in a location other than at the end of your CREATE TABLE statement, use one of these data set options: POST_TABLE_OPTS=, PRE_STMT_OPTS=, and PRE_TABLE_OPTS=. For example, for Greenplum, a WITH clause should appear after the table name but before a DISTRIBUTED RANDOMLY clause in a CREATE TABLE statement. You should therefore specify a WITH clause using the POST_TABLE_OPTS= data set option.
In this example, the DB2 table TEMP is created with the value of the DBCREATE_TABLE_OPTS= option appended to the CREATE TABLE statement.
libname mydblib db2 user=myusr1 pwd=mypwd1 dsn=sample;
data mydblib.temp (DBCREATE_TABLE_OPTS='PARTITIONING KEY (X) USING HASHING');
x=1; output;
x=2; output;
run;When you use this data set option to create the DB2 table, the SAS/ACCESS Interface to DB2 passes this DB2 SQL statement:
CREATE TABLE TEMP (X DOUBLE) PARTITIONING KEY (X) USING HASHING
In this example, a Hive table PART is created with the value of the DBCREATE_TABLE_OPTS= option appended to the CREATE TABLE statement.
options sastrace=',,,d' sastraceloc=saslog;
libname x HADOOP server=XXXX user=XXXXX pwd=XXXXXX ;
data x.part (DBCREATE_TABLE_OPTS="PARTITIONED BY(I INT)");
i=1; output;
j=2; output;
run;When you use this data set option to create this table, the Hadoop interface generates a CREATE TABLE statement similar to this one.
HADOOP_8: Executed: on connection 2
CREATE TABLE 'PART' ('j' DOUBLE) PARTITIONED BY(I INT)
TBLPROPERTIES ('SAS OS Name'='W32_7PRO', 'SAS Version'='9.04.01M3D04152015')