Date: Wed, 16 Apr 2003 13:07:19 -0600
Reply-To: Jack Hamilton <JackHamilton@FIRSTHEALTH.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Jack Hamilton <JackHamilton@FIRSTHEALTH.COM>
Subject: 12 Ways to get data from SAS to Excel
Content-Type: text/plain; charset=us-ascii
1) SAS Access to PC File Formats
An extra-cost add-on, this lets you read an Excel file using PROC
IMPORT or a LIBNAME statement (in version 9.1).
2) Enterprise Guide
Lets you open an Excel file and a worksheet as a SAS file. I think
this requires SAS and SAS Access to PC File Formats on the same
machine.
3) SYLK tagset
Using ODS, you can write a file which can be read by most or all
versions of Excel, and by many other spreadsheet programs. Can include
formating, formulas, and range names, but creating thee can be
laborious. Simple data are straightforward. See the link near the
bottom of <http://support.sas.com/rnd/base/topics/odstagsets/>. This
can run on any platform to create a file which can be read later under
Windows.
4) DDE tagset
Also using ODS, creates DDE commands which are sent to Excel. See the
link near the bottom of
<http://support.sas.com/rnd/base/topics/odstagsets/>. This runs
under Windows only.
5) DDE from data step
Communicates with Excel using DDE from a data step. Several SUGI
papers have been presented on this topic. Runs under Windows only.
6) HTML
Using ODS or data step programming, create an HTML file. Excel 2000 and
later (and possibly Excel 95 or 97, but not Excel 5) can read an Excel
table into rows and columns of a spreadsheet. There are Microsoft CSS
extensions to provide a high level of control over appearance and
behavior. OpenOffice.org can read HTML into a spreadsheet, but does not
recognize the Microsoft extensions.
With Excel 2000 and later, you can create multiple-sheet workbooks. I
have gotten this to work, but it gives warning messages. If anyone has
gotten this to work without warning messages, please let me know.
7) SAS Access to PC File Formats for Unix
A new extra-cost product in SAS 9.1, this lets you read Excel workbooks
from Unix via a Windows server.
8) Business Intelligence Suite
A new extra-cost product in SAS 9.1, this lets you run predefined
procedures and return the results to Excel. With some improvements,
this might be a killer app for large corporations.
9) Custom programming with SAS/Share*Net
You can download libraries which can be used to read SAS/Share
libraries from non-SAS programs. The SAS/Intrnet component htmSQL uses
these libraries, so I know it's theoretically possible, but I don't know
of anyone actually doing it outside SAS Institute.
10) DBMS/Copy
This is a standalone Windows program, formerly independent but now
controlled by SAS Institute. It can convert between many different
database formats, including SAS and Excel. StatTransfer may be another
possibility, but I haven't used it.
11) Copy and paste from the SAS System Viewer
This is a free Windows program from SAS Institute. You can open a SAS
data set created on almost any platform (except our primary platform,
alas), click on Select All and Copy, then paste the data into your Excel
spreadsheet. At one time there were size restrictions, and there may be
still. Supports FTP and HTTP access, so you don't have to do a separate
file transfer or download if the SAS file is not on a Windows drive.
12) IT
The IT product, I've been told, provides an API which can be used to
read and write SAS data sets, and also to execute SAS code. IT is used
by Enterprise Guide, so you should be able to do anything that
Enterprise Guide can do. IT is a fairly expensive product, ut provides
a great deal of power.
Anything I've left out?
--
JackHamilton@FirstHealth.com
Manager, Technical Development
Metrics Department, First Health
West Sacramento, California USA
