12 Ways to get data from SAS to Excel

shiyiming

Hi friends,

Some users ask this question, I'm glad to forwards a post here. Hope it helps for some of you.

As I think, different way for different users under different condition if meeting certain assumption.

In fact, you may list more ways.

Zibao

========================================
<!-- m --><a class="postlink" href="http://groups.google.com/groups?q=SAS+to+EXCEL+group:comp.soft-sys.sas&amp;hl=en&amp;lr=&amp;ie=UTF-8&amp;group=comp.soft-sys.sas&amp;safe=off&amp;selm=916067ABEE7DF34EA4DFE700B1A94A1F71D3BF%40Remailnt3&amp;rnum=2">http://groups.google.com/groups?q=SAS+t ... nt3&amp;rnum=2</a><!-- m -->

From: Jack Hamilton [mailto:JackHamilton@FIRSTHEALTH.COM]
Sent: Wednesday, April 16, 2003 3:07 PM
To: <!-- e --><a href="mailto:SAS-L@LISTSERV.UGA.EDU">mailto:SAS-L@LISTSERV.UGA.EDU</a><!-- e -->
Subject: 12 Ways to get data from SAS to Excel


[color=blue:4d19c]1) SAS Access to PC File Formats[/color:4d19c]

An extra-cost add-on, this lets you read an Excel file using PROC
IMPORT or a LIBNAME statement (in version 9.1).

[color=blue:4d19c]2) Enterprise Guide[/color:4d19c]

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.


[color=blue:4d19c]3) SYLK tagset[/color:4d19c]

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 &lt;http://support.sas.com/rnd/base/topics/odstagsets/&gt;.  This
can run on any platform to create a file which can be read later under
Windows.


[color=blue:4d19c]4) DDE tagset[/color:4d19c]

Also using ODS, creates DDE commands which are sent to Excel.  See the
link near the bottom of
&lt;http://support.sas.com/rnd/base/topics/odstagsets/&gt;.  This runs
under Windows only.


[color=blue:4d19c]5) DDE from data step[/color:4d19c]

Communicates with Excel using DDE from a data step.  Several SUGI
papers have been presented on this topic.  Runs under Windows only.


[color=blue:4d19c]6) HTML[/color:4d19c]

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.


[color=blue:4d19c]7) SAS Access to PC File Formats for Unix[/color:4d19c]

A new extra-cost product in SAS 9.1, this lets you read Excel workbooks
from Unix via a Windows server.


[color=blue:4d19c]8) Business Intelligence Suite[/color:4d19c]

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.


[color=blue:4d19c]9) Custom programming with SAS/Share*Net[/color:4d19c]

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.


[color=blue:4d19c]10) DBMS/Copy[/color:4d19c]

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.


[color=blue:4d19c]11) Copy and paste from the SAS System Viewer[/color:4d19c]

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.


[color=blue:4d19c]12) IT[/color:4d19c]

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.