Monday, January 23, 2006

Convert SAS datasets to Excel

I've recently posted code samples of using VSTO and some other means of getting SAS data into Excel. I thought I would compile a list of various techniques to move data from SAS to Excel.

These are the means (outside of SAS) that I am aware of using as well as pros and cons:

XML (for Office 2000 and better)


  • Easily written
  • Works pretty fast
  • Excel not required on machine


  • Creates enormous files. Have to open and then save as
    old Excel using COM to reduce file size
  • Hard to work with the XML model due to its top to right
    formatting. No real random cell access that I can find.

VSTO (Visual Studio Tools for Office)


  • Built-in support in Visual Studio (ie easy editing)
  • Microsoft 'direction'
  • Does not require Excel


  • COM based (slow, 1 instance only)
  • Requires Visual Studio
  • Requires coding in a different language
    (VB.NET or C#)
  • Only supported on Windows

VBA in Excel


  • Well-documented. Fairly easy to use.


  • VBA will be deprecated, probably in Office 12
    coming next year
  • COM based (see above)
  • Requires Excel to run and build
  • Possibly opens up security concerns
  • Only supported on Windows

.NET using 3rd party tools (Aspose is an example)


  • Fastest generation method seen. Faster than
    COM by probably 1000x
  • Able to run simultaneous threads
  • Easy to code and edit in Visual Studios due to
    intellisense support
  • Object model is simple and easy to use
  • Random cell access


  • Requires 3rd party product ($400) plus
    Visual Studios
  • Requires a non-SAS language
  • Only supported on Windows

Next up: web services and SAS data.


Anonymous said...

Two more advantages of XML

- Can incorporate non-SAS generated XML easily.

- SAS generated XML can be transformed into Office, HTML etc without having to alter the SAS code.

Anonymous said...

Nice site!
[url=]My homepage[/url] | [url=]Cool site[/url]

Anonymous said...

Nice site!
My homepage | Please visit

Anonymous said...

Thank you! |

MalaysiaEconomic said...

There is two software you can convert your pdf to excel, check it out at:

Dean said...

greetings to all.
I would first like to thank the writers of this blog by sharing information, a few years ago I read a book called guanacaste costa rica in this book deal with questions like this one.

niz said...

Hello .. firstly I would like to send greetings to all readers. After this, I recognize the content so interesting about this article. For me personally I liked all the information. I would like to know of cases like this more often. In my personal experience I might mention a book called Generic Viagra in this book that I mentioned have very interesting topics, and also you have much to do with the main theme of this article.

For setting SAS Options in code, make sure you have the following dlls: Interop.SAS SASObjectManager SASWorkspaceManager For...