• Print  |
  • Feedback  |

Knowledge Base


TS-607

PROC DBLOAD APPEND option versus PROC APPEND when appending to DBMS Tables

This document describes the append capabilities for PROC DBLOAD APPEND option versus the PROC APPEND procedure.

PROC DBLOAD APPEND option

The PROC DBLOAD APPEND option allows you to append 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 the APPEND option, the input data must correspond exactly to the columns in the DBMS table. This matching must be in name, data type and order of variables.

All PROC DBLOAD statements and options can be used with the APPEND option, except for the NULLS and TYPE statements, which have no effect because the input variable types and the DBMS column types must have a one-to-one match.

The DELETE and RENAME statements can be used with the APPEND option to drop and rename SAS input variables that do not have corresponding DBMS columns. The DELETE statement is used to drop any input variables that have no corresponding DBMS column. The RENAME statement is used if the names do not match. For example, this statement loads data that is associated with the SAS variable COUNTRY into the DBMS column named ORIGIN: 

   rename country=origin;
If there is not a one-to-one match between the input variable names and the DBMS column names, the APPEND operation will be rejected by the DBMS and you will receive the following error: 

   ERROR: No dbms column matches SAS variable name.
The DBLOAD procedure verifies that the names of the columns are valid and the data types match approximately (e.g., numeric to numeric, char to char ). Further validation is done by the database server. In general the server is responsible for catching any gross data type or column process errors.

The ERRLIMIT statement defaults to 10 when used with APPEND.

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=work.newemp append;
   user=testuser; 
   password=testpass; 
   path='myorapath,;
   table=employees;
   commit=100;
   errlimit=5;
   load;
   run;

PROC APPEND Procedure

You can append SAS dataset observations to other DBMS Tables by creating an access view of the other database table using SAS/ACCESS and then using PROC APPEND to append the SAS dataset to the view which will actually append the dataset to the DBMS table. Below is an example (base=ACCESS VIEW data=SAS DATASET): 

   proc append base=vlib.test data=dlib.test force;
   run;
If you are concatenating a SAS dataset to an ACCESS view that have different variables and different attributes, PROC APPEND works as follows:

If the DATA=data set contains variables that are not in the BASE=access view, you must use the FORCE option on the PROC APPEND statement. The extra variables from the DATA=data set are not included in the BASE=access view; you receive a message to warn you that variables are being dropped.

If the BASE=access view contains a variable that is not in the DATA=data set, PROC APPEND concatenates the tables, but the observations from the DATA=data set have a missing value for the variable that was not in the DATA=data set. The FORCE option is not needed in this case.

If a variable has different attributes in the BASE= access view than it does in the DATA= data set, the attributes in the BASE= access view prevail.

PERFORMANCE

You may encounter slower performance when appending data to an DBMS table using PROC APPEND. The PROC DBLOAD APPEND option accomplishes the task much faster.

PROC DBLOAD APPEND option requires that the input variable types and the DBMS column types must have a one-to-one match and the column must already exists and in the same order - just appends rows to the bottom.

PROC APPEND procedure allows different variables and different attributes and it reads every record then appends the data - only one commit is issued at the end of the PROC APPEND procedure.