Data Set Options for Relational Databases

IGNORE_ READ_ONLY_COLUMNS= Data Set Option

Specifies whether to ignore or include columns whose data types are read-only when generating an SQL statement for inserts or updates.

Default value:	NO
Valid in:	DATA and PROC steps (when accessing DBMS data using SAS/ACCESS software)
DBMS support:	Aster nCluster, DB2 under UNIX and PC Hosts, Greenplum, HP Neoview, Microsoft SQL Server, Netezza, ODBC, OLE DB, Sybase IQ

Syntax
Syntax Description
Details
Examples
See Also

Syntax

IGNORE_READ_ONLY_COLUMNS=YES | NO

Syntax Description

YES: specifies that the SAS/ACCESS engine ignores columns whose data types are read-only when you are generating insert and update SQL statements
NO: 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

Details

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.

Examples

For this example, a database that contains the table Products is created with two columns: ID and PRODUCT_NAME. The ID column is defined 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=dbitest pwd=dbigrp1 dsn=lupinss
               ignore_read_only_columns=yes;
options sastrace=',,,d' sastraceloc=saslog nostsuffix;
proc append base=x.PRODUCTS data=work.products;
run;