One thing to note is that the HEADER= option applies to all worksheets in the
specified workbook. You cannot have some worksheets with headers and some
without in the same workbook, and have SAS handle these properly.
Since the spreadsheets are locked while you have the library reference in effect, it is
agood practice to clear the reference when you are finished. Use the LIBNAME
statement to do this.
libname inxls clear r ;
libname inxls2 clear r ;
As seen in these examples, SAS can read Excel files with either PROC IMPORT or the
LIBNAME statement, using very few statements.
Writing to Excel Spreadsheets
SAS can also write to MS Excel Spreadsheets. Three ways will be discussed here.
First, PROC EXPORT can be used to write directly to a spreadsheet through
SAS/Access to PC File Formats. Secondly, the EXCEL engine on the LIBNAME
statement allows you to write to Excel much as you would to any SAS data set.
These methods work very similar to what you have seen above in reading Excel
spreadsheets. The last method shown takes advantage of Output Delivery System
(ODS) features to create Excel files.
PROC EXPORT using the DBMS option can be used to write a SAS data set to an
Excel spreadsheet. This is basically the reverse of what you saw using PROC IMPORT
to read an Excel spreadsheet. The general syntax for PROC EXPORT is as follows.
PROC EXPORT DATA=<libref.>SAS-data-set <(SAS-data-set-options)>
OUTFILE="filename" | OUTTABLE="tablename"
The DATA= option specifies the SAS data set used as the source. For Excel you use
the OUTFILE=”filename” option to specify the Excel file to be written to. (The
OUTTABLE=”tablename” option would be applicable if you were writing to a database
such as Microsoft Access.) The DBMS= option identifies the type of file to be read. In
this case, you will use EXCEL2000 to write to an Excel spreadsheet. Finally, the
REPLACE option determines whether to replace the contents of the worksheet that is
created, if it already exists.
An important <data-source-statement(s);> is the SHEET statement. This allows you
to specify the sheet name within the workbook to which to write. The sheet may
already exist, or SAS will create it, if necessary. Again, there are other statements,
but please refer to your SAS documentation for these.
To illustrate writing to Excel spreadsheets, consider the simple data set TEST1 with
two variables and two observations.