This document explains how to use Excel files in SAS on Winstat. Over the years, there have been many versions of Excel, many versions of SAS, and many versions of the operating systems on which they are used. As a result the details of reading and writing Excel files in SAS have changed often. Instructions you find elsewhere may or may not work on Winstat.
Some unsolicited advice: if you have the option, working with files in comma-separated value format (csv) is much more straightforward than working with Excel files. The relative simplicity of the csv format means that methods of reading and writing these files are very stable, and it will be possible to reproduce your work easily regardless of how Excel, SAS, and Windows may change in the future.
There are two methods we generally use for reading and writing Excel files using SAS on Winstat: PROC IMPORT (and PROC EXPORT), and libnames. PROC IMPORT is what you use when you use the Import Wizard, and is excellent for one-time file conversions. Libnames allow you to write data steps that access Excel files directly.
PROC IMPORT (and PROC EXPORT)
PROC IMPORT (and PROC EXPORT) can be used either by writing your own procedure code or by calling on the Import Wizard.
Procedure Code
The key to getting PROC IMPORT and PROC EXPORT to work properly on Winstat is to choose the correct DBMS= option. The preferred DBMS is EXCELCS. For example:
PROC IMPORT OUT= class
DATAFILE= "myfile.xls"
DBMS=EXCELCS REPLACE;
RANGE="Data$";
SCANTEXT=YES;
USEDATE=YES;
SCANTIME=YES;
RUN;
myfile.xls should be replaced by the actual name of your Excel file, most likely including its location (e.g. u:\myfolder\myfile.xls). This works for both xls and xlsx files.
Specifications that do NOT work in current (64-bit) SAS are: EXCEL, EXCEL97, EXCEL2000, EXCEL2002, EXCEL2003, EXCEL2007, or EXCEL2010. Two other specifications that work, but have always had limited functionality, are: XLS, and XLSX.
For more information, click Help, SAS Help and Documentation and go to the Contents tab. Then expand SAS Products, SAS/Access 9.3, SAS/Access 9.3 Interface to PC Files: Reference, and Import and Export Wizards and Procedures.
Import Wizard
To use the Import wizard, click File, Import Data. The key to using it successfully, just like when writing procedure code, is to choose the correct data source option. Although the default option is Microsoft Excel Workbook (*.xls, *.xlsb, *.xlsm, *.xlsx), this does NOT work on Winstat. Instead, choose Microsoft Excel Workbook on PC Files Server.
LIBNAMES
Using libnames to access data in Excel spreadsheets is a relatively new feature of SAS. The libname specification that works in SAS on Winstat is:
LIBNAME myxls PCFILES PATH='myfile.xls';
myfile.xls should be replaced by the actual name of your Excel file, most likely including its location (e.g. u:\myfolder\myfile.xls). This works for both xls and xlsx files. myxls can be replaced by any name you choose.
Accessing an Excel file via a libname treats the file as if it were a folder or directory, and then allows you to address individual worksheets or named ranges within the file as if they were SAS data sets.
There is one further detail to which you need to pay attention. Worksheets in Excel are identified to SAS with a dollar sign in their names, e.g. Data$. In order for SAS to interpret data sets names containing characters it does not normally allow, you need to enclose the worksheet name in quotes and then add a trailing n, like this:
data temp;
set myxls.'Data$'n;
bmi=(weight/height**2)*703;
run;
To read a named range, address it as if it were a SAS data set (no quotes needed), like this:
data temp;
set myxls.range1;
bmi=(weight/height**2)*703;
run;
When writing data sets to an Excel file, both a spreadsheet and a named range are created, regardless of which you specify.
data myxls.'test'n;
set temp;
run;
In the SAS explorer, worksheets and named ranges appear as SAS data sets.
One final thing to note is that you will not be able to open the Excel file outside of SAS until you clear the LIBNAME.
libname myxls clear;
For more information, click Help, SAS Help and Documentation and go to the Contents tab. Then expand SAS Products, SAS/Access 9.3, SAS/Access 9.3 Interface to PC Files: Reference and LIBNAME PCFILES Engine and PC Files Server on Microsoft Windows.
Last Revised: 3/4/2013