SAS/ACCESS Software for PC File Formats: Reference

How the SAS/ACCESS Interface Works

The SAS/ACCESS interface accesses data in the Microsoft Excel XLS files directly. It enables you to create SAS data sets from XLS files or directly read the XLS file data without creating SAS data sets. The interface does not allow you to update, add, or delete data in XLS files.

Accessing the Data

To access the data, the interface accesses a range in a worksheet as a table. If the range is not specified, the interface accesses the entire worksheet as a table. By default, the interface uses the Microsoft Excel formats of columns in the first row of the range to determine the formats of variables in SAS/ACCESS descriptors.

However, you can manipulate where the interface begins to read data and what format the interface generates by using the SKIPROWS and SCANTYPE statements in the ACCESS procedure. SKIPROWS skips a specified number of rows before reading data. SCANTYPE finds the most common data type and format from among a specified number of rows within an XLS range (after skipping the number of rows specified in SKIPROWS) and uses it to generate the default data type and format for SAS variables.

The ACCESS procedure enables you to create access descriptors and view descriptors for XLS files. You then can use the view descriptors as SAS data sets.

You can retrieve a subset of data using the WHERE statement .

To sort XLS file data, you must first extract the data from an XLS file and place them in a SAS data file, unless you are using the SQL procedure. (The SQL procedure enables you to present output data in a sorted order using the ORDER BY clause of the SELECT statement.) You can extract and sort XLS file data in one step with the OUT= option in the SORT procedure, using a view to the XLS file as input to PROC SORT.

Creating and Loading the Data

When you use PROC DBLOAD to create and load XLS files, the procedure translates the SAS data set into an XLS file. The file is stored in the location specified by the PATH= statement. Only one SAS data set can be loaded into an XLS file at one time. The loaded XLS file can contain only one worksheet. Microsoft Excel then reads data from the loaded XLS file directly.

In the DBLOAD procedure, you can specify the PUTNAMES statement to place the SAS variable names in the first row of the spreadsheet and the first observation in the second row, and so on. If PUTNAMES is not specified, the first observation is placed in the first row, the second observation is placed in the second row, and so on. Columns do not have names. The formats for SAS variables are automatically converted to the closest corresponding Microsoft Excel data types and formats. See the descriptions of individual statements for more information on how the data and columns are read.

Chapter Contents
Previous
Next
Top of Page