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)

Pros

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


Cons

  • 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)

Pros

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

Cons

  • 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

Pros

  • Well-documented. Fairly easy to use.

Cons

  • 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)

Pros

  • 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

Cons

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

Next up: web services and SAS data.

7 comments:

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=http://skkbgako.com/aswh/mrkt.html]My homepage[/url] | [url=http://bjzlvuxw.com/sftn/zerr.html]Cool site[/url]

Anonymous said...

Nice site!
My homepage | Please visit

Anonymous said...

Thank you!
http://skkbgako.com/aswh/mrkt.html | http://loldpspw.com/osnb/iijh.html

MalaysiaEconomic said...

There is two software you can convert your pdf to excel, check it out at:
1. www.investintech.com
2. www.cogniview.com

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.

CTRL+Z does not generate EOF in Windows 10

In Windows 10, when I was trying to generate an EOF for a Java program, the CTRL+Z did not work. After doing some research (and help from f...