ACCESS Procedure: XLS Files

The ACCESS procedure for PC Files creates descriptor files that describe XLS data. The ACCESS Descriptors section provides XLS-specific syntax for the ACCESS procedure and describes ACCESS procedure data conversions. See The ACCESS Procedure for PC Files for additional information.

Access Descriptors

To create an access descriptor, use the ACCESS procedure with the DBMS=XLS option. There are six database-description statements:

GETNAMES
PATH
RANGE
SCANTYPE
SKIPROWS
WORKSHEET

These database-description statements supply XLS-specific information to SAS. The statements must immediately follow the CREATE statement. In addition to the database-description statements, editing statements can follow the database-description statements. In addition to the database-description statements, editing statements can follow the database-description statements.

Database-description statements are required only when you create access descriptors. Because the XLS information is stored in an access descriptor, you do not repeat the information when you create view descriptors.

Arguments

Use the Access procedure to define descriptors that identify spreadsheet data and the conversions necessary to use that data in SAS programs. The Microsoft Excel label data type is formatted as a SAS character type. The Microsoft Excel number data type is formatted as a SAS numeric type.

Fonts, attributes, and colors in the XLS files are not read into SAS data sets. The Access procedure supports most of the XLS number formats and automatically converts them to the corresponding SAS formats. XLS data strings that are longer than 200 characters are truncated during conversion to SAS data sets. XLS files that create SAS data sets can contain up to 256 variables and 16,384 observations.

w is based on Excel column width. The Excel format string controls .d.

If XLS files data falls outside of the valid SAS data ranges, you receive an error message in the SAS log when you try to access the data.

The SAS/ACCESS interface does not fully support the Microsoft Excel hidden and text formats. XLS data in hidden format is displayed in SAS data sets. You can drop the hidden column when you are creating the access descriptor. To display a formula in text format, add a space to indicate that the formula entry is a label. Otherwise, the results of the formula display.

You can change the default value from NO to YES by setting the SS_MIXED environment variable.

See Setting Environment Variables for XLS Files.

Set the SS_MIXED environment variable to YES and numeric values in XLS files are converted to character strings if the corresponding SAS variable type is character.

Syntax

PROC ACCESS DBMS= XLS | EXCEL;

CREATE libref.member-name. ACCESS | VIEW;

GETNAMES= YES | NO | Y | N;

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

RANGE= <'range-name'> | <'range-address'>;

SCANTYPE= YES | NO | Y | N | <number-of-rows>;

UPDATE libref.member-name.ACCESS | VIEW;
ASSIGN=YES | NO | Y | N;

DROP= <'column-identifier-1'> …<'column-identifier-n'>;

FORMAT=<'column-identifier-1'>=<'SAS format-name-1'>…<'column-identifier-n'>=
<'SAS format-name-n'>

LIST= ALL | VIEW | <column-identifier>

MIXED = YES | NO | Y | N

RENAME=<'column-identifier-1''SAS variable-name-1'>…<'column-identifier-n'>
<'SAS variable-name-n'>

RESET ALL | <'column-identifier-1'> ,...<'column-identifier-n'>

SELECT ALL | <'column-identifier-1'> ,…<'column-identifier-n'>

SKIPROWS= <number-of-rows-to-skip>

SUBSET <selection-criteria>

TYPE 'column-identifier-1'. C | N <'column-identifier-n'>,C | N

UNIQUE=YES | NO | Y | N

WORKSHEET=<'worksheet-name'> RUN;

SAS Formats: XLS Specifics

SAS Formats for XLS File Data

XLS File Data		SAS Variable Format
Data Type	XLS Format String	Type	Format
Char(footnote1)	@(footnote2)	Char	$w.
Numeric(footnote3)	General	Num	BEST
Numeric	0	Num	w.d
Numeric	0.00	Num	w.d
Numeric	#,##0	Num	COMMAw.d
Numeric	#,##0.00	Num	COMMAw.d
Numeric	#,##0_);(#,##0)	Num	NEGPARENw.d
Numeric	#,##0_);[Red](#,##0)	Num	NEGPARENw.d
Numeric	#,##0.00_);(#,##0.00)	Num	NEGPARENw.d
Numeric	#,##0.00_);[Red](#,##0.00)	Num	NEGPARENw.d
Numeric	$#,##0_);($#,##0)	Num	DOLLARw.d
Numeric	$#,##0_);[Red]($#,##0)	Num	DOLLARw.d
Numeric	($#,##0.00_);($#,##0.00)	Num	DOLLARw.d
Numeric	($#,##0.00_);[Red]($#,##0.00)	Num	DOLLARw.d
Numeric	_($#,##0_);_($(#,##0);_($*"-"_);_(@_)	Num	DOLLARw.d
Numeric	_(#,##0_);_((#,##0);_(*"-"_);_(@_)	Num	NEGPARENw.d
Numeric	_($#,##0.00_);_($(#,##0.00);_($*"-"??_);_(@_)	Num	DOLLARw.d
Numeric	_(#,##0.00_);_((#,##0.00);_(*"-"??_);_(@_)	Num	NEGPARENw.d
Numeric	0%	Num	PERCENTw.d
Numeric	0.00%	Num	PERCENTw.d
Numeric	0.00E+00	Num	Ew.d
Numeric	##0.0E+0	Num	Ew.d
Numeric	m/d/yy	Num	MMDDYYw.
Numeric	d-mmm-yy	Num	MMDDYYw.
Numeric	d-mmm	Num	DATEw.
Numeric	mmm-yy	Num	MONYYw.
Numeric	h:mm AM/PM	Num	TIMEw.
Numeric	h:mm:ss AM/PM	Num	TIMEw.
Numeric	h:mm	Num	TIMEw.
Numeric	hh:mm	Num	TIMEw.
Numeric	h:mm:ss	Num	TIMEw.
Numeric	hh:mm:ss	Num	TIMEw.
Numeric	m/d/yy h:mm	Num	DATETIMEw.
Numeric	ddmmmyy	Num	DATEw.
Numeric	ddmmmyyyy:hh:mm:ss	Num	DATETIMEw.
Numeric	dd	Num	DATEw.
Numeric	dd/mm/yy	Num	DDMMYYw.
Numeric	dddd	Num	DATEw.
Numeric	mm/dd/yy	Num	MMDDYYw.
Numeric	mm:ss	Num	MMSSw.
Numeric	mm yy	Num	MONYYw.
Numeric	mm yyyy	Num	MONYYw.
Numeric	mm:yy	Num	MONYYw.
Numeric	mm:yyyy	Num	MONYYw.
Numeric	mm-yy	Num	MONYYw.
Numeric	mm-yyyy	Num	MONYYw.
Numeric	mmyy	Num	MONYYw.
Numeric	mmyyyy	Num	MONYYw.
Numeric	mm.yy	Num	MONYYw.
Numeric	mm.yyyy	Num	MONYYw.
Numeric	mm/yy	Num	MONYYw.
Numeric	mm/yyyy	Num	MONYYw.
Numeric	mmmm	Num	MONYYw.
Numeric	m	Num	MONYYw.
Numeric	mmmyy	Num	MONYYw.
Numeric	mmmyyyy	Num	MONYYw.
Numeric	dddd, mmmm dd, yyyy	Num	MONYYw.
Numeric	dddd, dd mmmm yyyy	Num	MONYYw.
Numeric	mmmm dd, yyyy	Num	MONYYw.
Numeric	dd mmmm yyyy	Num	MONYYw.
Numeric	yy	Num	YYMMDDw.
Numeric	yyyy	Num	YYMMDDw.
Numeric	yy mm	Num	YYMMDDw.
Numeric	yyyy mm	Num	YYMMDDw.
Numeric	yy:mm	Num	YYMMDDw.
Numeric	yyyy:mm	Num	YYMMDDw.
Numeric	yy-mm	Num	YYMMDDw.
Numeric	yyyy-mm	Num	YYMMDDw.
Numeric	yymm	Num	YYMMDDw.
Numeric	yyyymm	Num	YYMMDDw.
Numeric	yy.mm	Num	YYMMDDw.
Numeric	yyyy.mm	Num	YYMMDDw.
Numeric	yy/mm	Num	YYMMDDw.
Numeric	yyyy/mm	Num	YYMMDDw.
Numeric	yy-mm-dd	Num	YYMMDDw.
Numeric	yymmm	Num	YYMMDDw.
Numeric	yyyymmm	Num	YYMMDDw.

SAS Formats: Customized for XLS Strings

SAS Variable Formats for Customized XLS Format Strings

XLS File Data		SAS Variable Format
Data Type	XLS Format String	Type	Format
Numeric	"$"	Num	DOLLARw.d
Numeric	"E"	Num	Ew.d
Numeric	"m, d and y"	Num	MMDDYYw.
Numeric	"m and h"	Num	TIMEw.d
Numeric	"m and s"	Num	TIMEw.d
Numeric	"m and y"	Num	MONYYw.
Numeric	"m"	Num	DATEw.
Numeric	"d"	Num	DATEw.
Numeric	"y"	Num	DATEw.
Numeric	"0.0"	Num	w.d
Numeric	Fraction values (#?/?)	Num	BESTw.d
Numeric	Percent values (0.0%)	Num	PERCENTw.d
Numeric	All others	Num	BESTw.d

FOOTNOTE 1:Label data.[return]

FOOTNOTE 2:The XLS character format for Excel Version 5.[return]

FOOTNOTE 3:Number, formula, or missing data.[return]