SAS/ACCESS Software for PC File Formats: Reference

DBLOAD Procedure: XLS Specifics

DBLOAD Procedure Reference describes the generic options and procedure statements that enable you to create a PC file format table and to insert data in it. The following section describes the file-specific statements you use in the SAS/ACCESS interface to XLS.

DBLOAD Procedure Statements for XLS

To create and load an XLS table, the SAS/ACCESS interface to XLS uses the following statements in batch mode:

PROC DBLOAD DBMS=XLS | EXCEL <DATA=<libref.>SAS-data-set>;

PATH='path-and-filename<.XLS>' | <'>filename<'> | fileref;

VERSION <=> EXCEL-product-number;

PUTNAMES <=> YES | NO | Y | N;

ACCDESC= <libref.>access-descriptor;

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

ERRLIMIT= error-limit;

FORMAT SAS-variable-name-1 SAS-format-1 <=>
<...SAS-variable-name-n SAS-format-n>;

LABEL;

LIMIT=load-limit;

LIST <ALL | COLUMNS | FIELDS | variable-identifier>;

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

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

WHERE SAS-where-expression ;

LOAD ;

RUN ;

The QUIT statement is also available in PROC DBLOAD. However, its use causes the procedure to terminate. QUIT is used most often in the interactive line and noninteractive modes to exit the procedure without exiting SAS.

FORMAT SAS-variable-name-1 SAS-format-1 <...SAS-variable-name-n SAS-format-n>;

assigns a temporary format to a SAS variable in the input SAS data set. This format temporarily overrides any other format for the variable. The assignment lasts only for the duration of the procedure. Assign formats to as many variables as you want in one FORMAT statement.

Use FORMAT when you want to change the format, column width, or the number of decimal digits for columns being loaded into the PC file. For example, if you change the SAS variable format 12.1 to DOLLAR15.2, the column format of the loaded data changes from a fixed numeric format with a column width of 12 and one decimal digit to a currency format with a column width of 15 and two decimal digits.

PUTNAMES <=> YES|NO|Y|N;

writes column names to the first row of the new XLS file. The column names can be default SAS variables names or, if you specify the LABEL statement, SAS variable labels. You can modify the column names using the RENAME statement.

The PUTNAMES statement is optional. If you omit PUTNAMES, data are read from the data set and written to the XLS file beginning in the first row of the XLS file, and no column names are written to the file.

You can change the default value to YES by setting the SS_NAMES environment variable. See Setting Environment Variables for more information on setting and changing environment variables.

VERSION <=> EXCEL-product-number;

specifies the version number of the Excel product you are using, such as Excel 5.0. The EXCEL-product-number argument can be one of the following values: 3, 4, 5, or 7.

The DBLOAD procedure chooses the default version of Excel depending on which operating environment you use. If you use Windows, DBLOAD uses Excel 5.0. Excel 5 files have the identical format to Excel 95 (Version 7) files. If you use OS/2, DBLOAD uses Excel 4.0 because OS/2 does not support OLE2.

PROC DBLOAD does not support Excel 97 (Version 8) files. For information about accessing these files, see Understanding XLS Essentials.

Specify VERSION before the TYPE statement in order to get the correct data types for your new .XLS table.

Chapter Contents
Previous
Next
Top of Page