Specifies whether to ignore or include columns whose data types are read-only when generating an SQL statement for inserts or updates.
| Valid in: | DATA and PROC steps (when accessing DBMS data using SAS/ACCESS software) |
|---|---|
| Category: | Data Set Control |
| Alias: | IGNORE_READONLY= [Greenplum, HAWQ, SAP IQ] |
| Default: | NO |
| Data source: | Amazon Redshift, Aster, DB2 under UNIX and PC Hosts, Greenplum, HAWQ, Impala, Microsoft SQL Server, Netezza, ODBC, OLE DB, PostgreSQL, SAP HANA, SAP IQ, Vertica, Yellowbrick |
| Note: | Support for Yellowbrick was added in SAS 9.4M7. |
| See: | IGNORE_READ_ONLY_COLUMNS= |
Table of Contents
specifies that the SAS/ACCESS engine ignores columns whose data types are read-only when you are generating insert and update SQL statements
specifies that the SAS/ACCESS engine does not ignore columns whose data types are read-only when you are generating insert and update SQL statements
Several databases include data types that can be read-only, such as the Microsoft SQL Server timestamp data type. Several databases also have properties that allow certain data types to be read-only, such as the Microsoft SQL Server identity property.
When IGNORE_READ_ONLY_COLUMNS=NO (the default) and a DBMS table contains a column that is read-only, an error is returned that the data could not be modified for that column.
For this example, a database that contains the table Products is created with two columns: ID and PRODUCT_NAME. The ID column is specified by a read-only data type and PRODUCT_NAME is a character column.
CREATE TABLE products (id int IDENTITY PRIMARY KEY, product_name varchar(40))If you have a SAS data set that contains the name of your products, you can insert the data from the SAS data set into the Products table.
data work.products;
id=1;
product_name='screwdriver';
output;
id=2;
product_name='hammer';
output;
id=3;
product_name='saw';
output;
id=4;
product_name='shovel';
output;
run;When IGNORE_READ_ONLY_COLUMNS=NO (the default), the database returns an error because the ID column cannot be updated. However, if you set the option to YES and execute a PROC APPEND, the append succeeds and the generated SQL statement does not contain the ID column.
libname x odbc uid=myusr1 pwd=mypwd1 dsn=lupinss
ignore_read_only_columns=yes;
options sastrace=',,,d' sastraceloc=saslog nostsuffix;
proc append base=x.PRODUCTS data=work.products;
run;