DBLOAD Procedure: XLS Specifics

DBLOAD Procedure Statements and Options

The DBLOAD Procedure enables you to read data, format data, and set environment variables for XLS-specific data.

The QUIT statement is also available in the DBLOAD procedure. QUIT causes the procedure to terminate. QUIT is used most often in interactive line mode and batch mode to exit the procedure without exiting SAS.

The DBLOAD procedure chooses the default version of Excel depending on your operating environment. For Windows, DBLOAD uses Excel 5. Excel 5 files have the identical format to Excel 95 files.

The DBLOAD procedure does not support Excel 97 or later files. For information about accessing these files, see the Supported Data Sources and Environments.

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

option(s) can be one or more options.

FORMAT SAS variable-name-1 SAS format-1 …SAS variable-name-nSAS 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. Change the SAS format 12.1 to DOLLAR15.2. The format changes from a fixed numeric format, width 12, and one decimal, to a currency format, width of 15 and two decimals.

PUTNAMES = YES|NO|Y|N

writes column names to the first row of the XLS file. The column names default to SAS variables names unless you specify the LABEL statement. You can modify the column names using the RENAME statement.

The PUTNAMES statement is optional. Omit PUTNAMES and data is read beginning in the first row of the XLS file. No column names are written to the file.

You can change the default value to YES by setting the SS_NAMES environment variable. For more information, see Setting Environment Variables for XLS Files.

VERSION= Excel-product-number

specifies the version number of the Excel product that you are using, such as Excel 5. The Excel-product-number argument can be one of the values in the following table:

Excel Versions

Value	Description
3	Microsoft Excel Version 3
4	Microsoft Excel Version 4
5	Microsoft Excel Version 5
7	Microsoft Excel 95 (also called Microsoft Excel Version 7)

DBLOAD Procedure Data Conversions for XLS Files

This section explains how SAS data is read into Microsoft Excel data when a table is loaded. In this conversion, the SAS character data type is converted into the Microsoft Excel label type and the SAS numeric type is converted into the Microsoft Excel number type.

The SAS/ACCESS interface automatically converts SAS formats to the same or associated Microsoft Excel formats and column widths. You can temporarily assign other formats and column widths to SAS variables by using the FORMAT statement. The loaded XLS file columns have the formats that you want.

Note: The FORMAT statement in the DBLOAD procedure only changes the format of SAS variables while you are creating and loading the XLS files. When the procedure is completed, the formats of SAS variables return to their original settings.

The following table shows SAS variable types and formats and the XLS data types, formats, and column widths to which you can assign them. XLS date and time values are numeric data.

Converting SAS Variable Formats to XLS File Data

SAS Variable Format		XLS File Data
Type	Format	XLS Format String	Data Type
Char	" "	General	LABEL
Char	$CHAR	General	LABEL
Char	$	General	LABEL
Num	BESTw.d	General	NUMBER
Num	COMMAw.d	#,##0	NUMBER
Num	COMMAXw.d	#,##0	NUMBER
Num	DATEw.	ddmmmyy	NUMBER
Num	DATETIMEw.d	ddmmmyyyy:hh:mm:ss	NUMBER
Num	DAYw.	dd	NUMBER
Num	DDMMYYw.	dd/mm/yy	NUMBER
Num	DOLLARw.d	"$"#,##0_);("$"#,##0)	NUMBER
Num	DOLLARXw.d	"$"#,##0_);("$"#,##0)	NUMBER
Num	DOWNAMEw.d	dddd	NUMBER
Num	Ew.	0.00E+00	NUMBER
Num	HHMMw.d	h:mm	NUMBER
Num	HOURw.d	h:mm	NUMBER
Num	JULDAYw.	m/d/yy	NUMBER
Num	JULIANw.	m/d/yy	NUMBER
Num	MMDDYYw.	mm/dd/yy	NUMBER
Num	MMSSw.d	mm:ss	NUMBER
Num	MMYYxw.	mmyy	NUMBER
Num	MMYYC	mm:yy	NUMBER
Num	MMYYD	mm-yy	NUMBER
Num	MMYYN	mmyy	NUMBER
Num	MMYYP	mm.yy	NUMBER
Num	MMYYS	mm/yy	NUMBER
Num	MONNAMEw.	mmmm	NUMBER
Num	MONTHw.	m	NUMBER
Num	MONYYw.	mmmyy	NUMBER
Num	NEGPARENw.d	#,##0_);(#,##0)	NUMBER
Num	NENGOw.	m/d/yy	NUMBER
Num	PERCENTw.d	0%	NUMBER
Num	QTRw.	m/d/yy	NUMBER
Num	QTRRw.	m/d/yy	NUMBER
Num	SSNw.	000-00-0000	NUMBER
Num	TIMEw.d	h:mm:ss	NUMBER
Num	TODw.	h:mm:ss	NUMBER
Num	W	0	NUMBER
Num	WEEKDATEw.	dddd, mmmmdd, yyyy	NUMBER
Num	WEEKDATXw.	dddd, dd mmmmyyyy	NUMBER
Num	WEEKDAYw.	m/d/yy	NUMBER
Num	WORDDATEw.	mmmmdd, yyyy	NUMBER
Num	WORDDATXw.	ddmmmmyyyy	NUMBER
Num	YEARw.	yy or yyyy	NUMBER
Num	YYMM	yy mm	NUMBER
Num	YYMMC	yy:mm	NUMBER
Num	YYMMD	yy-mm	NUMBER
Num	YYMMN	yymm	NUMBER
Num	YYMMP	yy.mm	NUMBER
Num	YYMMS	yy/mm	NUMBER
Num	YYMMDDw.	yy-mm-dd	NUMBER
Num	YYMONw.	yymmm	NUMBER
Num	Zw.d	0w.d	NUMBER
Num	FRACTw.	# ?/?	NUMBER

Excel column widths are set to w and display in the column. If the data is larger than column width, it displays as pound signs (###). In that case, you can view it by adjusting the column width.

Setting Environment Variables for XLS Files

SS_MIXED SS_NAMES SS_SCAN

You can change the default behavior of the ACCESS procedure and the DBLOAD procedure by setting environment variables in your SAS configuration file. You can set three SAS/ACCESS environment variables: SS_MIXED, SS_NAMES, and SS_SCAN. Setting these variables in your SAS configuration file defines how the interface works. The configuration file omits these three environment variables. Their default values are NO.

SS_MIXED =YES|NO

YES allows both Microsoft Excel numeric and character data in a column to be displayed as SAS character data. The Microsoft Excel numeric data is converted to its character representation when its corresponding SAS variable type is defined as character.

NO does not convert Microsoft Excel numeric data in a column into SAS character data. Microsoft Excel numeric data is read in as SAS missing values when its corresponding SAS variable type is defined as character. NO is the default.

Setting the SS_MIXED environment variable changes the default value of the MIXED statement in the Access procedure.

SS_NAMES= YES|NO

YES allows the Access procedure to generates SAS variable names from column names. The first row of the worksheet or the specified range of the worksheet is used to generate SAS variable names. Data is then read from the second row.

YES in the DBLOAD procedure writes column names using SAS variable names. YES also writes SAS variable labels to the first row of the XLS file. SAS reads the data from the data set, and writes it to the XLS file beginning with the second row.

NO in the Access procedure generates the SAS variable names VAR0, VAR1, VAR2, and so on, and reads data from the first row of the worksheet or specified range.

NO in the DBLOAD procedure reads the data from the data set and writes it to the XLS file beginning with the first row. NO is the default.

Setting the SS_NAMES environment variable changes the default value of the GETNAMES= option in the Access procedure and the PUTNAMES= option in the DBLOAD procedure.

SS_SCAN= YES | NO | number-of-rows

YES scans the data type and format of rows in a worksheet or specified range after skipping the number of rows specified in the SKIPROWS statement. After scanning the rows, SS_SCAN finds the most common Microsoft Excel data type and format in order to generate the default SAS data type and format. If a number of rows is specified, SAS/ACCESS scans the data type and format only from these rows.

NO uses the type and format of the first row in a worksheet or specified range. If SKIPROWS= is specified, the first row is after skipping the number of rows specified. NO is the default.

Number-of-rows scans only the type and format of the specified number of rows. Setting the number of rows is more efficient because data is read from only the specified number of rows rather than the entire file.

Setting the SS_SCAN environment variable changes the default value of the SCANTYPE statement in the Access procedure.