Data Set Options for Relational Databases |
Specifies
whether to force data truncation during insert processing.
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, DB2 under z/OS,
Greenplum, HP Neoview, Informix, Microsoft SQL Server, Netezza, ODBC, OLE
DB, Oracle, Sybase, Sybase IQ, Teradata
|
-
YES
-
specifies that rows that contain data values
that exceed the length of the DBMS column are inserted, and the data values
are truncated to fit the DBMS column length.
-
NO
-
specifies that the rows that contain data
values that exceed the DBMS column length are not inserted.
This option determines how the SAS/ACCESS engine
handles rows that contain data values that exceed the length of the DBMS column.
DBFORCE= works only when you create a DBMS table with the DBTYPE= data set
option--namely, you must specify both DBTYPE= and this option. DBFORCE=
does not work for inserts or updates. Therefore, to insert or update a DBMS
table, you cannot use the DBFORCE= option--you must instead specify the
options that are available with SAS procedures. For example, specify the
FORCE= data set option in SAS with PROC APPEND.
FORCE= overrides DBFORCE= when you use FORCE= with PROC
APPEND or the PROC SQL UPDATE statement. PROC SQL UPDATE does not warn you
before it truncates data.
In this example, two librefs are associated with Oracle
databases, and it does not specify databases and schemas because it uses the
defaults. In the DATA step, MYDBLIB.DEPT is created from the Oracle data that
MYORALIB.STAFF references. The LASTNAME variable is a character variable
of length 20 in MYORALIB.STAFF. When MYDBLIB.DEPT is created, the LASTNAME
variable is stored as a column of type character and length 10 by using DBFORCE=YES.
libname myoralib oracle user=tester1 password=tst1;
libname mydblib oracle user=lee password=dataman;
data mydblib.dept(dbtype=(lastname='char(10)')
dbforce=yes);
set myoralib.staff;
run;
DBNULL= Data Set Option
DBTYPE= Data Set Option
Copyright © 2010 by SAS Institute Inc., Cary, NC, USA. All rights reserved.