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);
sas.Open();
OleDbCommand sasCommand = sas.CreateCommand();
sasCommand.CommandType = CommandType.TableDirect;
sasCommand.CommandText = sasDataSet;
OleDbDataReader sasRead = sasCommand.ExecuteReader();
dt.Load(sasRead);
endTime = DateTime.Now;
return dt;
}

3. Download the Aspose Excel .NET model. It can be found at www.aspose.com . While this ultimately will cost around $400, it is worth every penny if you do this a lot.

4. Write your .NET code. There are loads of examples on the Aspose site.


This approach is simply fast, fast, fast and is very powerful. You have full formatting control over the sheet, random access to anywhere on the sheet, and it is simple to code once you get started. Depending on the level of formatting, sheets should be created in less than 1ms plus Excel is not needed to create the sheets.

While learning 2 languages and keeping up on them has its complexities, I think you will find the above to be worth the time. What you will get out is a powerful tool to create Excel worksheets from your SAS data.