SAS/ACCESS Software for Relational Databases: Reference

DBLOAD Procedure Syntax

The statements for your DBMS may differ from those listed here. See your DBMS chapter for details.

PROC DBLOAD <statement-options>;

Database Connection Statements

These statements are used to connect to your DBMS and vary depending on which SAS/ACCESS interface you are using. See your DBMS chapter for details. Examples include USER=, PASSWORD=, and DATABASE=.

Table Statement

TABLE= <'>table-name <'>;

Editing Statements

ACCDESC= <libref.>access-descriptor;

COMMIT= commit-frequency;

DELETE variable-identifier-1
<...variable-identifier-n>;

ERRLIMIT= error-limit;

LABEL;

LIMIT= load-limit;

LIST <ALL | COLUMN | variable-identifier>;

NULLS variable-identifier-1 = Y | N
<...variable-identifier-n = Y | N>;

QUIT;

RENAME variable-identifier-1 = <'>column-name-1<'>
<...variable-identifier-n = <'>column-name-n<'>>;

RESET ALL | variable-identifier-1<...variable-identifier-n>;

SQL DBMS-specific SQL-statement;

TYPE variable-identifier-1 = 'column-type-1' <...variable-identifier-n = 'column-type-n'>;

WHERE SAS-where-expression;

Creating and Loading Statement

LOAD;

RUN;

Details

The DBLOAD procedure enables you to create and load a DBMS table, append rows to an existing table, and submit non-query DBMS-specific SQL statements to the DBMS for processing without leaving your SAS session. The procedure constructs DBMS-specific SQL statements to create and load or append to a DBMS table by using any one of the following:

a SAS data file
a PROC SQL view or DATA step view
a view descriptor that was created with the SAS/ACCESS interface to your DBMS or with another SAS/ACCESS interface product
another DBMS table referenced by a SAS libref created with the Version 8 SAS/ACCESS LIBNAME statement.

The DBLOAD procedure associates each SAS variable with a DBMS column and assigns a default name and data type to each column. It also specifies whether each column accepts null values. You can use the default information or change it as necessary. When you are finished customizing the columns, the procedure creates the DBMS table and loads or appends the input data.

PROC DBLOAD Statement Options

The following options can be used in the PROC DBLOAD statement.

Options

DBMS=database-management-system

specifies which database management system you want to access. The DBMS= option is required. See your DBMS chapter for the value to enter for your DBMS.

DATA=<libref.>SAS-data-set

specifies the input data set. The input data can be retrieved from a SAS data file, a PROC SQL view, a DATA step view, a SAS/ACCESS view descriptor, or another DBMS table referenced by a SAS/ACCESS libref. If the SAS data set is permanent, you must use its two-level name, libref.SAS-data-set. If you omit the DATA= option, the default is the last SAS data set that was created.

APPEND

appends data to an existing DBMS table that you identify by using the TABLE= statement. When you specify APPEND, the input data specified with the DATA= option is inserted into the existing DBMS table. Your input data can be in the form of a SAS data set, PROC SQL view, or SAS/ACCESS view (view descriptor).

Note: When you use APPEND, you must ensure that your input data corresponds exactly to the columns in the DBMS table. If your input data does not include values for all columns in the DBMS table, you might corrupt your DBMS table by inserting data into the wrong columns. You can use the COMMIT, ERRLIMIT, and LIMIT statements to help safeguard against data corruption. The ERRLIMIT statement defaults to 10 when used with APPEND. [cautionend]

The DELETE and RENAME statements can be used with APPEND to drop and rename SAS input variables that do not have corresponding DBMS columns. The RENAME statement indicates the column name in the DBMS table for the SAS data set variable that you specify. For example, this statement loads data that is associated with the SAS variable COUNTRY into the DBMS column named ORIGIN:

rename country=origin;

All PROC DBLOAD statements and options can be used with APPEND, except for the NULLS and TYPE statements, which have no effect when used with APPEND. The LOAD statement is required.

The following example appends new employee data from the SAS data set NEWEMP to the DBMS table EMPLOYEES. The COMMIT statement causes a DBMS commit to be issued after every 100 rows are inserted. The ERRLIMIT statement causes processing to stop after 10 errors occur.

proc dbload dbms=oracle data=newemp append;
   user=testuser; 
   password=testpass; 
   path='myorapath';
   table=employees;
   commit=100;
   errlimit=5;
   load;
run;

Note: By omitting the APPEND option from the DBLOAD statement, you can use the PROC DBLOAD SQL statements to create a DBMS table and append to it in the same PROC DBLOAD step. [cautionend]

Procedure Statements

To invoke PROC DBLOAD, you use the options listed in Options along with certain statements. The statements that you choose are determined by your task and your database. These statements vary per DBMS and might be optional; see your DBMS chapter for more information.

Options and Statements Required for the DBLOAD Procedure summarizes the PROC DBLOAD options and statements.

Options and Statements Required for the DBLOAD Procedure
Tasks Options and Statements You Use

create and load a DBMS table

PROC DBLOAD
statement-options;
database-connection-options;

TABLE= <'>table-name<'>;

LOAD;

RUN;

submit a dynamic, non-query DBMS-SQL statement to DBMS (without creating a table)

PROC DBLOAD
statement-options;
database-connection-options;

SQL DBMS-specific-SQL-statements;

RUN;

The PROC DBLOAD statements are described in alphabetic order in the following sections.

Chapter Contents
Previous
Next
Top of Page