The DBLOAD Procedure

PROC DBLOAD Statement

The DBLOAD procedure loads data to PCs and creates PC files.

PROC DBLOADDATA=input data DBMS=data-base-identifier;
PATH='path and filename.PC file-extension' |'filename' | fileref;
LOAD;

ACCDESC=<'libref.access-descriptor'>
DELETE=<variable-identifier-1...variable-identifier-n>
ERRLIMIT=<error-limit>
FMTLIB=<LIBNAME.member>
LABEL
LIMIT=<load-limit>
LIST< ALL|COLUMNS|FIELDS|variable-identifier>
QUIT
RENAME<='variable-identifier-1',column-name-1...'variable-identifier-n'column-name-n>
RESET=<variable-identifier-1...variable-identifier-n >
WHERE<SAS where-expression>;

Task	Option
Specify the input data	DATA
Specify the PC file format.	DBMS
Create an access description.	ACCDESC
Delete variables from the new data set.	DELETE
Limit loading obs after n number of errors.	ERRLIMIT
Specifies a format library.	FMTLIB
Use SAS labels as default column names.	LABEL
Limit number of observations loaded into new file.	LIMIT
List information about SAS variables to be loaded into new file.	LIST
Create a files and trasnfer data to it ffrom an input SAS data set.	LOAD
List information about SAS variables to be loaded into the new file.	LIST
Indicate the path and name of the PC file you create and load.	PATH
Exit the procedure without further processing.	QUIT
Rename the PC File columns assiciated with the listed SAS variables.	RENAME
Reset column names,data types, and enable setting of null values.	RESET
Load a subset of observations into the PC file.	WHERE

Required Arguments

DATA=input data

specifies the input data. The input data can be retrieved from a SAS data set, an SQL view, a DATA step view, a SAS/ACCESS view descriptor, or and Microsoft Excel file

Default:	the last SAS data set that was created.
Restriction:	If the data set is permanent, specify its two-level name libref.SAS data-set.

DBMS=pc-file-format

specifies the PC file format that you want to access.

File Type	Statement
DBF	DBMS=DBF
DIF	DBMS=DIF
EXCEL 97-2003	DBMS=EXCEL
WKn	DBMS=WK1
WKn	DBMS=WK3
WKn	DBMS=WK4
MDB	DBMS=MDB
XLS	DBMS=XLS

LOAD

causes the interface view engine to create a file and transfer data to it from the input data set, after the DBLOAD procedure is submitted. This statement is required to create and load a new file.

When you create and load a file, you must place statements or groups of statements in a certain order after the PROC DBLOAD statement and its options, as follows:

Database-description statements: PATH and your PC file specific statements.
Editing statements: ACCDESC, DELETE, ERRLIMIT, LABEL, LIMIT , LIST, RENAME, RESET, and WHERE. The order within this group usually does not matter.

See the individual statements for more information. QUIT is also an editing statement but using it immediately terminates PROC DBLOAD.
When creating and loading statement, LOAD must appear last before RUN in order to create and load the new table.
The RUN statement is used to process the DBLOAD procedure.

PATH

Featured in: indicates the path and name of the PC file that want to create and load. The length of the filename can vary with the operating environment.

Arguments:	The PATH Statement can take one or more of these arguments: path and filename.PC-file-extension specifies the fully qualified path and filename. Enclose the entire path and filename in quotation marks, including the appropriate PC file extension. If you omit the file extension, SAS/ACCESS supplies it for you. filename specifies the name of a file. The file must be located in your current (default) directory. If no extension is specified, the SAS/ACCESS interface supplies it for you. If the filename includes characters that are invalid in a SAS name, or if it begins with a number, enclose the filename in quotation marks. fileref specifies a fileref that references the path and name of the file.
Note:	Assigning a fileref with a FILENAME statement is described in Step-by-Step Programming with Base SAS Software.
Requirement:	This statement is required.
Restriction:	A file with the same name must not already exist. If one does exist, it is not overwritten. An error message is written to the SAS log, and the PC file that is specified in this statement is not loaded.
See:	SAS documentation for your operating environment for any restrictions.

Optional Statements

ACCDESC

creates an access descriptor based on the PC file that you are creating and loading. After the PC file is created and loaded, the access descriptor is automatically created. You must specify an access descriptor that does not already exist.

An editing statement, such as ACCDESC, must be specified after the database description statements when you create and load a file.

Aliases:

ACCESS and AD

DELETE

prevents variables from being loaded into the new PC file. The DELETE statement deletes the specified SAS variables from the PC file being created. The variable-identifier can be either the SAS variable name or the positional equivalent from a LIST statement. The positional equivalent is the number that represents the variable's place in the data set.

For example, if you want to delete the third variable, submit this statement:

DELETE 3;

You can delete as many variables as you want in one DELETE statement. If you delete more than one variable, separate the identifiers with spaces, not commas.

If you delete a variable from the list of variables, the positional equivalents of the variables do not change. For example, if you delete the second variable, the third variable is still referenced by the number 3, not 2.

Interacts with:	RENAME, RESET
Restriction:	An editing statement, such as DELETE, must be specified after the database-description statements when you create and load a file.

ERRLIMIT

stops loading observations after the specified number of errors has occurred while inserting rows into a file.

Default:	100
Note:	Specify ERRLIMIT=0 to allow an unlimited number of errors to occur.
Restriction:	ERRLIMIT must be a non-negative integer.
Restriction:	An editing statement, such as ERRLIMIT must be specified after the database-description statements when you create and load a file.
Valid for:	DBF, DIF, WK1, WK3, WK4, Excel 4, Excel 5, and Excel 95 file formats under Microsoft Windows operating environments

FMTLIB=<libref.>member

specifies the name of a format library to search.

LABEL

The LABEL statement causes the column names to default to the SAS variable labels when the new table is created. If a SAS variable has no label, the variable name is used. If the label is too long to be a valid column name, the label is truncated.

Default:	SAS labels.
Interacts with:	RENAME, RESET
Restriction:	For the LABEL statement to take effect, the RESET statement must be used after the LABEL statement.
Restriction:	An editing statement, such as LABEL, must be specified after the database-description statements when you create and load PC files.

LIMIT

limits the number of observations that can be loaded into the new file. The maximum number for the limit statement varies with each PC file.

Default:	5000
Note:	To load all observations from your input data set, specify LIMIT=0.
Restriction:	The load-limit must be a non-negative integer.
Restriction:	If you omit the LIMIT statement, a maximum of 5,000 observations are inserted.

LIST

The LIST statement lists information about all or some of the SAS variables to be loaded into the new file. Write the list to the SAS log.

You can specify LIST as many times as you want while creating a file; specify LIST before the LOAD statement to see the entire table. LIST must be specified after the database-description statements.

Default:

ALL

Valid values:

ALL|COLUMNS|FIELDS|variable-identifier

ALL

lists information about all variables in the input SAS data set, regardless of whether those variables are selected for the load.

COLUMNS

lists information only about the input SAS variables that are selected for loading. This argument does not apply to DBF files.

FIELDS

lists information only about the input SAS variables that are selected for the load.

variable-identifier

lists information only about the specified variable. The variable-identifier can be either the SAS variable name or the positional equivalent. The positional equivalent is the number that represents the variable's position in the data set.

For example, if you want to list information for the column associated with the third SAS variable, submit this statement.

LIST 3;

QUIT

exits the procedure without further processing.

Alias:

EXIT

RENAME

renames PC file columns that are associated with the listed SAS variables. The column-name must be a valid PC file column name. If the column name includes lowercase characters, special characters, or national characters, you must enclose the column name in quotation marks.

The variable-identifier can be a SAS variable name or the positional equivalent from the LIST statement. The positional equivalent is the number that represents where to place the variable in the data set.

You can rename as many variables as you want in one RENAME statement. The RENAME statement overrides the LABEL statement for columns that are renamed.

Alias:	COLUMN
Interacts with:	DELETE, LABEL, RESET
Note:	If you omit RENAME, column names default to the corresponding SAS variable names, unless you specify a LABEL statement.
Restriction:	An editing statement, such as RENAME, must be specified after the database-description statements when you create and load a PC file.

Example: The DELETE statement deletes the third variable. The RENAME statement, includes the third variable, assigns the default type, and assigns EMPNAME as the name.

DELETE 3;
RENAME 3='empname';

The RENAME statement enables you to include variables that you have previously deleted.

Example: This statement renames the column associated with the third SAS variable.

RENAME 3="employname";

RESET

resets column names and data types, and the ability to accept null values to their default values. If you specify ALL, all columns are reset to their default values. Additionally, any deleted columns are restored with their default values. An editing statement, such as RESET, must be specified after the database-description statements when you create and load a PC file.

The variable-identifier argument can be either the SAS variable name or the positional equivalent from the LIST statement. The positional equivalent is the number that represents the variable's place in the data set.

Interacts with:	DELETE, LABEL, RENAME
Note:	You can reset as many columns as you want in one RESET statement.
Restriction:	You must use the RESET statement after the LABEL statement for the LABEL statement to take effect.

Example: If you want to reset the column associated with the third SAS variable, submit this statement:

RESET 3;

WHERE

loads a subset of observations into the PC file. The SAS where-expression must be a valid statement that uses SAS variable names (not column names) as defined in the input data set.

Restriction:	An editing statement, such as WHERE, must be specified after the database-description statements when you create and load a PC File.
See:	SAS Language Reference: Dictionary

The following example loads only the observations where the SAS variable Country has the value Brazil.

 WHERE country='Brazil';

Top of Page