Monday, October 30, 2006

Inserting records into SQL Server

99+% of the time, I read records from SQL Server into SAS. I typically use C# and do it direct in code. However, I recently needed to write records into SQL Server from the SAS side.

Attempt #1 was to use PROC APPEND. This failed with the following:

"ERROR: During insert: Data was not set for one or more columns."

This was failing on the identity column.

Attempt #2 was to try a SQL Server insert:

proc sql ;
insert into SqlSrvr.Test
select * from newdata
;
quit;

ERROR: Attempt to insert fewer columns than specified after the INSERT table name.
ERROR: Value 1 on the SELECT clause does not match the data type of the corresponding column
listed after the INSERT table name.
ERROR: Value 2 on the SELECT clause does not match the data type of the corresponding column
listed after the INSERT table name.
ERROR: Value 17 on the SELECT clause does not match the data type of the corresponding column
listed after the INSERT table name.

Hmmmm, could it be a problem wit hthe identity column and me using the new XML filed type?

A little bit of sleep and attempt #3 worked:

libname SQLSrvr oledb provider=sqloledb init_string='Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=MyCustomer;Data Source=SERVER01' schema=dbo ;

data NewData;
attrib platform length=$200
periodicity length=$200
level0-level10 length=$200
image length=$1024
data length=$1024
help length=$1024
;
DateTime = DateTime() ;
Platform = "MVS" ;
Periodicity = "Daily" ;
Level0 = "CPU Utilization" ;
Image = "c:\temp\myimage.jpg" ;
Data = "" ;
Help = "c:\temp\myhelp.doc" ;
run;

proc sql ;
insert into SqlSrvr.Test
select * from newdata
;
quit;

proc sql ;
insert into SqlSrvr.Test (datetime,platform, periodicity, level0, image, data, help)
select datetime, platform, periodicity, level0, image, data, help from newdata
;
quit;

Tuesday, October 03, 2006

SAS and AJAX

SAS has no built in support for AJAX at this time. However, you can hack up some of it by using enabling technologies such as ASP.NET or just code it yourself in JavaScript.

However, I have coded AJAX bits in JavaScript using SAS/IntrNet and found the experience less than desirable. A better way to make this happen, IMO, is to use the new Atlas framework from Microsoft:

http://atlas.asp.net/Default.aspx?tabid=47

It's quick and easy and makes coding AJAX much easier. JavaScript is god-awful due to lack of true debugging support but it works. Make it easier though by focusing on doing the AJAX piece in Atlas and let them handle the JavaScript bits. My $0.01.

Alan

Thursday, August 31, 2006

Weird VS2005 Error

I'm posting this in case others hit the same issue.

When trying to doa ClickOnce deployment, we hit the following error:

"Cannot publish because a project failed to build."
"SignTool reported an error. "The parameter is incorrect."

We switched from VB to C# and it worked fine. I'll leave this blog posting out on the net so it can help someone else out doing ClickOnce deployments.

Sunday, August 06, 2006

EG Tasks not displaying

Installed EG4.1 and no task were displayed. Here's how I fixed it (based upon an old 2.0 TS post):

Go to:

Tools > SAS Enterprise Guide Explorer >

In Enterprise Guide Explorer

Tools > Options > Uncheck Enable Task Administration

SAS EG and .NET 2.0

Ok, so the official word is no .NET 2.0 apps in EG. I understand this position 100% and I agree with the position. Regardless, .NET 2.0 costs me 25-50% less effort than 1.1 so my goal was to see if I could hack out something that would allow me to post a 2.0 app in EG 4.1.

It is a hack, it's not official, it's limited, etc. but I successfully got my 2.0 app to run under EG and had it post my code to an EG task. Here's how I did it but it is simplistic and not pretty. I share it in case you need something similar.

First, create a 2.0 app. Make it a WinForm and have fun on layout, generics, etc.

Then change parts of your program.cs to something like the following:

MainForm frm = new MainForm();
Application.Run(frm);
Console.WriteLine(frm.SasCode);

All Winform apps can write to a console but this output goes to a standard out.

Then change your EG add-in to support it:

public SAS.Shared.AddIns.ShowResult Show(System.Windows.Forms.IWin32Window Owner)
{
Process proc ;
proc = new Process() ;
proc.StartInfo.UseShellExecute = false ;
proc.StartInfo.RedirectStandardOutput = true ;
proc.StartInfo.RedirectStandardError = true ;
proc.StartInfo.CreateNoWindow = true ;
proc.StartInfo.FileName = "AnalystToolkit.exe";
proc.Start() ;
proc.WaitForExit() ;
sasCode = proc.StandardOutput.ReadToEnd() ;
return SAS.Shared.AddIns.ShowResult.RunLater;
}

I could have done a lot more with standard out (and I probably will) but this shows you a quick and easy way to hack up a solution that works. From this standard out, you should be able to make out a way to do anything you need.


From out in left field and having fun,

Alan

Tuesday, June 13, 2006

Excel 2007 and SAS Programmers

SAS-L is always having questions on Excel (and sometimes Word and PowerPoint). Office 2007 has now gone to beta 2 and should go production by the end of the year.

There are some areas that might be of relevance to SAS programmers who have to interface with the new Excel 2007.

- Color coding of cells by value is now very, very easy for users to do. So easy in fact that it is 1 click for the entire sheet.
- Cell formatting, coloring, etc. is now wide-open and very, very easy to do.
- Color choices now expand to 16M
- Rows go to 1M, columns to 65K
- Pivot tables no longer expand into other columns but remain fixed in the 1 column
- The file format for Excel files will be changing. Currently, it is binary. The new file format will be XML based, may contain multiple files, and all of them will be zipped up into a single package
- Etc., etc.

The reason I mention this is because a) it’s on my mind, b) I'm at TechEd and just saw mor eof this stuff, and c) I think it may have significant impact on SAS programmers. BTW, the beta 2 of Office may be one of the largest download activities ever seen by Microsoft (500K+ in 2 days).

I can see users using Excel a lot more for data storage. Also, expect a lot more data to be displayed by color-coding rather than value-based.

Why should SAS users care?

It will break a lot of existing paradigms for reading Excel and from what we expect from users. Data will now be stored a lot more in unstructured forms and there will be a lot more data. Also, users will expect Excel sheets to be done nicely and not just be a data dump.

The move of SAS programmers away from using DDE is long overdue. As difficult as it may be for many, the use of Excel object models will becoome more and more of a job necessity IMO.

For what it's worth...

Alan

Sunday, April 30, 2006

MHTML, AOL, C#, and the new WebBrowser

Recently, I had a customer who used AOL as their primary email account. This was a CEO so it was important to accomodate their desired email vendor. Well, AOL doesn't support MIME HTML (MHTML). Since the data being reporting was coming out of SQL Server Reporting Services, the choices for output were limited. The CEO liked the layout of the MHTML, but there was a hex dump at the end of the emails. He wanted to keep the look of the emails but without the hex dump.

The solution to this problem, and I hope this helps someone else, was to write a custom C# program to rip out the meta tags in the emails. There were several tricks that needed to be applied so I will list them here for someone else.

1. Switch the program.cs code to instantiate a form but don't run it directly. Make the application run a null value. This keeps the form from popping:

static void Main(string[] args)
{
Application.EnableVisualStyles();
Application.SetCompatibleTextRenderingDefault(false);
Main main = new Main(args);
Application.Run();
}

2. Use the new webbrowser control in .NET 2.0. You can then grab the DocumentText from the browser control AFTER it finishes rendering:

class Main
{
string report;
string reportName;
string distributionList;
static string server = "mail.savian.net";
WebBrowser browser = new WebBrowser();

public Main(string[] args)
{
if (args.Length < 2)
MessageBox.Show("Too few arguments to process");
else
{
distributionList = args[1];
ConvertReportToHtml(args[0]);
reportName = args[0].Substring(args[0].LastIndexOf('\\') + 1).Replace(".mhtml", "");
}
}

private void ConvertReportToHtml(string p)
{
browser.Navigate(p);
browser.DocumentCompleted += new WebBrowserDocumentCompletedEventHandler(browser_DocumentCompleted);
}

void browser_DocumentCompleted(object sender, WebBrowserDocumentCompletedEventArgs e)
{
report = browser.DocumentText;
StripMhtmlSection();
SendReport();
}

private void StripMhtmlSection()
{
Regex regex = new Regex(@"");
regex.Replace(report, "");
}

private void SendReport()
{
try
{
SmtpClient client = new SmtpClient(server);
MailAddress from = new MailAddress("CCLeadManagementSystem@savian.net");
MailAddress to = new MailAddress(distributionList);
MailMessage message = new MailMessage(from, to);
message.IsBodyHtml = true;
message.Subject = reportName;
message.Body = report;
client.Send(message);
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
Application.Exit();
}
}


I hope this helps someone. It took a lot of time to figure this all out. I tried FileWebRequest as well to no avail.


Alan

Thursday, March 02, 2006

Web Services

When setting up web services using Visual Studios and ASP.NET, you will notice that the test page does not contain a launch button by default for testing. This is solved by going into the web.config file for the site and entering the following:

<webservices>
<protocols>
<add name="HttpPost">
<add name="HttpGet">
</protocols>
</webservices>

That should get you running.

Monday, February 27, 2006

RoboHelp and IIS 6.0

Ok, so for some reason, flash wasn't working on my Windows Server 2003 running IIS 6.0. This impacted me since I wanted to run RoboHelp Flash. After a lot of tinkering and searching, turns out that IIS wouldn't allow the flashhelp_default.fhs file to run because IIS wouldn't allow the MIME type of fhs to run.This is a simple fix. Go into the properties of the web server and add in the mime type of fhs with a desscription of text/xml. Voila!! life is happy again.

I hope this helps someone else in this same boat.

Alan

Thursday, February 09, 2006

Web Services and SAS

The traditional field of Application Integration (you know, creating a SAS program on Unix that creates Excel spreadsheets) has been a muddied water for a long time. People tend to stick with the tried and true and just use API-type of mentatlity for delivering information. This is problematic for a lot of reasons:

  • An API has to be created and maintained over time
  • Neither application plays to its strengths but instead each one 'dumbs' down to the lowest common denominator of both applications
  • You always have to search for the specific API to use to get anything done
  • There is no central way of managing these APIs and you get a lot of code bloat

To solve these issues, web services were born. Now web services may sound like voodoo or black magic but they are very, very simple. Everyone, and I mean everyone (including SAS), agreed to have a common means (XML) of transporting information between each other. I won't go into the ugly details of web services because the web is rife with them but let's talk about how they could be used to solve the typical SAS scenario I mentioned above.

You have SAS running on a Unix server and all of your users want it in Excel.

Old solution: Use SAS\Access to create an Excel file, use ODS to create a very bloated Excel file, write out to CSV, etc. Run the code, ftp the file to a server for delivery, have the end users read in the data and parse it themselves, or again use ODS and figure out the coding needed to create an Excel report.

New solution: Create a web service on Unix that reads in the SAS data using OleDB, ODBC, etc. and just expose a service (let's call it GetSasData("ExcelData"). Next have Excel consume that service and parse the data into the exact Excel report you need.

Ok. You may say that the new solution doesn't sound much better than the old. But wait!! Your boss now comes along and says can you please make that same data available to Lotus 1-2-3 and StarOffice. AHHHHH!!!! Under the old method you need to rewrite all of that ODS or just create an entirely new package. The boss then asks you to also support SAP and Oracle. AHHH!!

Ok, web services wouldn't require any changes because Oracle, SAP, etc. support web services. They can just consume that service the same as Excel. But, you claim, CSV is supported by everyone as well.

Actually, it isn't. Take the following CSV file:

Name Age Gender

Bill 29 M

Mary 32 F

Now is name a numeric, string, what does Name mean? CSV doesn't tell you and never will. Web services have support for typing information so you know what it is.

This blog merely scratches the surface. Don Henderson and I have put up some sample web services for people to play with over here:

http://demo.savian.net/SasWebServicesDemo/Service.asmx

I urge you to check them out and see what the possibilities are.

Out in left field and loving it,

Alan

Web Services and SAS

The traditional field of Application Integration (you know, creating a SAS program on Unix that creates Excel spreadsheets) has been a muddied water for a long time. People tend to stick with the tried and true and just use API-type of mentatlity for delivering information. This is problematic for a lot of reasons:

  • An API has to be created and maintained over time
  • Neither application plays to its strengths but instead each one 'dumbs' down to the lowest common denominator of both applications
  • You always have to search for the specific API to use to get anything done
  • There is no central way of managing these APIs and you get a lot of code bloat

To solve these issues, web services were born. Now web services may sound like voodoo or black magic but they are very, very simple. Everyone, and I mean everyone (including SAS), agreed to have a common means (XML) of transporting information between each other. I won't go into the ugly details of web services because the web is rife with them but let's talk about how they could be used to solve the typical SAS scenario I mentioned above.

You have SAS running on a Unix server and all of your users want it in Excel.

Old solution: Use SAS\Access to create an Excel file, use ODS to create a very bloated Excel file, write out to CSV, etc. Run the code, ftp the file to a server for delivery, have the end users read in the data and parse it themselves, or again use ODS and figure out the coding needed to create an Excel report.

New solution: Create a web service on Unix that reads in the SAS data using OleDB, ODBC, etc. and just expose a service (let's call it GetSasData("ExcelData"). Next have Excel consume that service and parse the data into the exact Excel report you need.

Ok. You may say that the new solution doesn't sound much better than the old. But wait!! Your boss now comes along and says can you please make that same data available to Lotus 1-2-3 and StarOffice. AHHHHH!!!! Under the old method you need to rewrite all of that ODS or just create an entirely new package. The boss then asks you to also support SAP and Oracle. AHHH!!

Ok, web services wouldn't require any changes because Oracle, SAP, etc. support web services. They can just consume that service the same as Excel. But, you claim, CSV is supported by everyone as well.

Actually, it isn't. Take the following CSV file:

Name Age Gender

Bill 29 M

Mary 32 F

Now is name a numeric, string, what does Name mean? CSV doesn't tell you and never will. Web services have support for typing information so you know what it is.

This blog merely scratches the surface. Don Henderson and I have put up some sample web services for people to play with over here:

http://demo.savian.net/SasWebServicesDemo/Service.asmx

I urge you to check them out and see what the possibilities are.

Out in left field and loving it,

Alan

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.

Friday, January 13, 2006

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 SAS code generation is exclusively the domain of macros and SCL. I also don't think users should ever be held back on what they should or should not learn. No bounds in programming is my motto and the point of this blog entry.

Awhile back I wrote a program that was entirely in C# and just generated SAS format code. This provided the users with a great little graphical utility that generated valid format statements from various data sources (http://savian.net/utilities). Why? To do this in SAS would require Access engines, would not be graphically rich, and wouldn't allow the drag and drop stuff that I like.

This is where I think the SAS community can benefit: asking "how do I do this today and is there a better way?". I'm not saying it's right but that it provides an alternative view for how to accomplish a given task. The more we know the more we will embrace macros, embrace perl, embrace C#, embrace wild stuff like LINQ. The more you play, the more you will be able to help the end users.

Ok, I'm heading back to left field now...

Tuesday, January 10, 2006

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.

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 sender, System.EventArgs e)
{
ResetWorkbook();
DataTable dt = LoadSasDataSet();
CreateWorksheet();
LoadAndFormatData(dt);
CreateChart();
}

private void ThisWorkbook_Shutdown(object sender, System.EventArgs e)
{
}

#region VSTO Designer generated code

///
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
///

private void InternalStartup()
{
this.Startup += new System.EventHandler(ThisWorkbook_Startup);
this.Shutdown += new System.EventHandler(ThisWorkbook_Shutdown);
}

#endregion

private void ResetWorkbook()
{
// Get rid of all but the original worksheet.

try
{
ThisApplication.DisplayAlerts = false;

foreach (Excel.Worksheet ws in Globals.ThisWorkbook.Worksheets)
if (ws != ThisApplication.ActiveSheet)
{
ws.Delete();
}
foreach (Excel.Chart cht in Globals.ThisWorkbook.Charts)
cht.Delete();
}
finally
{
ThisApplication.DisplayAlerts = true;
}
}

private DataTable LoadSasDataSet()
{
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);
return dt;
}

private void CreateWorksheet()
{
xlSheet = (Excel.Worksheet)Globals.ThisWorkbook.Worksheets.Add(Type.Missing, Globals.ThisWorkbook.ActiveSheet,
Type.Missing, Type.Missing);

xlSheet.Name = "Sas Retail Sample";

// Copy field names to Excel.
// Bold the column headings.
Excel.Range rng = (Excel.Range)xlSheet.Cells[1, 1];
rng.Formula = "Year";
rng.Font.Bold = true;

rng = (Excel.Range)xlSheet.Cells[1, 2];
rng.Formula = "Sales";
rng.Font.Bold = true;
}

private void LoadAndFormatData(DataTable dt)
{
int row;
// Copy the data in from the SqlDataReader.
// Start at row 2.
row = 1;
foreach (DataRow dr in dt.Rows)
{
row += 1;
xlSheet.Cells[row, 1] = dr["Year"];
xlSheet.Cells[row, 2] = dr["Sales"];
}

// Format the columns.
((Excel.Range)xlSheet.Columns[1, Type.Missing]).AutoFit();

Excel.Range rng = (Excel.Range)xlSheet.Columns[2, Type.Missing];
rng.NumberFormat = "0.00";
rng.AutoFit();
}

private void CreateChart()
{
// Now create the chart.
Excel.Chart xlChart = (Excel.Chart) Globals.ThisWorkbook.Charts.
Add(Type.Missing, xlSheet, Type.Missing, Type.Missing);

Excel.Range cellRange = (Excel.Range)xlSheet.Cells[1, 1];
xlChart.ChartWizard(cellRange.CurrentRegion,
Excel.Constants.xl3DBar, Type.Missing,
Excel.XlRowCol.xlColumns, 1, 2, false,
xlSheet.Name, Type.Missing, Type.Missing,
Type.Missing);

// Apply some formatting to the chart.
xlChart.Name = xlSheet.Name + " Chart";

Excel.ChartGroup grp = (Excel.ChartGroup)xlChart.ChartGroups(1);
grp.GapWidth = 20;
grp.VaryByCategories = true;
xlChart.ChartTitle.Font.Size = 16;
xlChart.ChartTitle.Shadow = true;
xlChart.ChartTitle.Border.LineStyle = Excel.Constants.xlSolid;
}

}
}

4. Press F5 to build and see the results

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.

http://lab.msdn.microsoft.com/express/vcsharp/default.aspx


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

http://www.sas.com/apps/demosdownloads/oledbproviders_PROD_9.1.3_sysdep.jsp?packageID=000366

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 Explorer on the right, right-click References and select Add Reference….Click on the COM tab at the top and then scroll down until you see Microsoft Excel 11.0 Object Library (you may have one slightly different such as 9.0, etc. Just select the Excel library you have).

Paste the following code into the default class that appears:

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Text;
// using Excel = Microsoft.Office.Interop.Excel;


namespace SASToExcel
{
class Program
{
static DataTable dt;
static Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
static Excel.Workbook wb;
static Excel.Worksheet ws;
static List regions = new List();

static void Main(string[] args)
{
app.DisplayAlerts = false;
dt = LoadSasDataSet(@"c:\temp", "shoes");
DetermineDistinctValuesForWorksheets();
CreateWorkbooks();
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
app = null;
}

public static void DetermineDistinctValuesForWorksheets()
{
Excel.Workbooks wbs = app.Workbooks;
Excel.Workbook wb = wbs.Add(Type.Missing);

foreach (DataRow dr in dt.Rows)
{
if (!regions.Contains(dr["region"].ToString()))
{
regions.Add(dr["region"].ToString()) ;
}
}
}

public static void CreateWorkbooks()
{
Excel.Workbooks wbs = app.Workbooks;
wb = wbs.Add(Type.Missing);

foreach (string region in regions)
{
wb.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
ws = (Excel.Worksheet)wb.ActiveSheet;
//Create the worksheet name but clean up invalid values at the same time
ws.Name = region.Replace("/", " ");
Console.WriteLine("Wroksheet added: " + region);
DataRow[] rows = GetSelectedRegion(region);
AddDataToSheets(rows, region);
}

wb.SaveAs(@"c:\temp\SASToExcelSample.xls", Excel.XlFileFormat.xlWorkbookNormal,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing);

wb.Close(Type.Missing, Type.Missing, Type.Missing);
wbs.Close();
}

static DataRow[] GetSelectedRegion(string region)
{
string selectQuery = "region='" + region + "'";
DataRow[] subset = dt.Select(selectQuery);
return subset;
}

public static void AddDataToSheets(DataRow[] rows, string region)
{
int row = 1 ;
//Write Header records
WriteRow(row, 1, "Product");
WriteRow(row, 2, "Subsidiary");
WriteRow(row, 3, "Stores");
WriteRow(row, 4, "Sales");
row++;
foreach(DataRow dr in rows)
{
WriteRow(row, 1, dr["product"].ToString());
WriteRow(row, 2, dr["subsidiary"].ToString());
WriteRow(row, 3, dr["stores"].ToString());
WriteRow(row, 4, dr["sales"].ToString());
row++;
}
}

public static void WriteRow(int row, int col, string value)
{
Excel.Range rng = (Excel.Range)ws.Cells[row, col];
rng.Value2 = value;
}

public static DataTable LoadSasDataSet(string sasLibrary, string sasDataSet)
{
Console.WriteLine("Loading SAS data at library " + sasLibrary + ", dataset " + 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);
Console.WriteLine("Records read from SAS: " + dt.Rows.Count.ToString());
return dt;
}

}
}

7. Press F5 to build and run the code. You will also have a new program called SasToExcel.exe in the project’s bin\debug folder

8. View your new workbook in the temp directory. It is called SasToExcelSample.xls

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:...