Showing posts from January, 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)
ProsEasily writtenWorks pretty fastExcel not required on machine
ConsCreates enormous files. Have to open and then save as
old Excel using COM to reduce file sizeHard 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)
ProsBuilt-in support in Visual Studio (ie easy editing)Microsoft 'direction'Does not require Excel
ConsCOM based (slow, 1 instance only)Requires Visual StudioRequires coding in a different language
(VB.NET or C#)Only supported on Windows

VBA in Excel
ProsWell-documented. Fairly easy to use.
ConsVBA will be deprecated, probably in Office 12
coming next yearCOM based (see above)R…

SAS and Processes

Most SAS folks that I have dealt think in terms of the language and how to do things easier or 'niftier' using traditional SAS. No issues there. What has intrigued me the most, though, over the past few years is to think in terms of the processes to write SAS code and whether those processes can be made easier. Also, whether the processes can be done outside of SAS and then incorporated. Well, why go out of SAS is the question. Well, oftentimes it is easier to build a GUI or to write an easy to maintain program in another language. For example, build a SAS program that reads in XML using XPath or loop through the process threads on a system looking for a file name. Well, not going to happen easily hence other languages and approaches are needed either before or after it gets into SAS.

On SAS-L today was a debate over macros vs whatever else or whether a user should learn macros early, late, never, sometime. I have nothing against macros but I do have an issue with the idea that…

Approach to Excel and SAS

Oftentimes on SAS-L, the topic of how to integrate with Excel comes up. Most SAS users take very traditional approaches to this and use DDE, ODS, Access engines, etc. I feel, though, that is is best approached from the Microsoft side of the equation. The reason is that you achieve far more control and power over the resulting sheet.

The approach I typically advocate is:

1. Use SAS to get your data in shape.

2. Write a .NET program and use the SAS OleDB provider to read in the dataset. This sample code will turn your SAS dataset into a .NET datatable.

internal DataTable LoadSasDataSet(string sasLibrary, string sasDataSet)
DataTable dt = new DataTable();
OleDbConnection sas = new OleDbConnection("Provider=sas.LocalProvider; Data Source=" + sasLibrary);
OleDbCommand sasCommand = sas.CreateCommand();
sasCommand.CommandType = CommandType.TableDirect;

Visual Studio Tools for Office (VSTO) and SAS Integration

Software Used

Microsoft Office 2003 SP1
Visual Studios 2005
SAS version 9.13

Excel Project

1. Start Visual Studios 2005 and create a new Office project, specifying Excel
2. Enable VS2005 to allow access to the VBA engine (security)

3. On the right hand side of the screen, right-click ThisWorkbook.cs, select View Code

Here is the complete code listing. Replace the existing code in ThisWorkbook.cs with the following:

using System;
using System.Data;
using System.Data.OleDb;
using System.Drawing;
using System.Windows.Forms;
using Microsoft.VisualStudio.Tools.Applications.Runtime;
using Excel = Microsoft.Office.Interop.Excel;
using Office = Microsoft.Office.Core;

namespace SasToExcel
public partial class ThisWorkbook
private Excel.Worksheet xlSheet = null;
private Excel.Chart xlChart = null;

//The @ sign in front of a string means ignore special characters
string sasLibrary = @"C:\Program Files\SAS\SAS 9.1\core\sashelp";
string sasDataSet = "retail";

private void ThisWorkbook_Startup(object…

Creating Excel spreadsheets using Visual Studio Express for C# and SAS datasets

These steps show you how to make an Excel sheet using the Excel COM object, SAS, and C#. There are other ways to do this but it is a good way to get started.

Here are the steps needed to create the sheets:

Download Visual Studios Express for C# from Microsoft’s website: it is free.

Download and install the OLEDB Providers for SAS from the SAS website:

Create a directory c:\temp. Copy the shoes dataset from the sashelp files (this is for testing only) and place it in temp. You can find it where sas is installed under core\sashelp.

Once installed, create a new project (Fileà Newà Project). Select console type for now. After you become familiar with the steps, you can build a windows app.

You need to add in a reference to Excel. This basically is telling the project that the Excel classes will be available to your code. Look at the Solution Explo…