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.

SAS throwing RPC error

If you are doing code in C#  and get this error when creating a LanguageService: The RPC server is unavailable. (Exception from HRESULT:...