SAS from Out in Left Field

SAS Macro to Make Tiny URLs

2011-12-27T09:52:00.000-08:00

Someone on SAS-L wanted a piece of SAS code to convert a long url to a short one. Well, here you go:

filename in "x:\temp\in";
filename out "x:\temp\out.txt";

%macro MakeTiny(longUrl=);
 data _null_;
    file in lrecl=1028;
    put "url=&longUrl" ;
    run;

    proc http in=in out=out url="http://tinyurl.com/api-create.php"
      method="post"
      ct="application/x-www-form-urlencoded";
 run;

 data _null_ ;
    infile out;
    input tinyUrl :$1024. ;
    call symput('tinyUrl',tinyUrl);
    %global tinyUrl ;
    run;

%mend makeTiny;

%MakeTiny(longUrl=www.savian.net);

%put &tinyUrl ;

WCF and PROC SOAP

2011-07-21T13:26:00.000-07:00

Adam Bullock in SAS Tech Support has become a superstar in my book. I don't send him tickets directly but the area I am working in always seems to find him.

The latest issue was consuming a WCF service. This was different since Microsoft uses interfaces in WCF vs the old way of doing it. I didn't catch that but Adam did. Here is working SAS code for handling it:

FILENAME REQUEST 'C:\temp\REQUEST.xml';
FILENAME RESPONSE 'C:\temp\RESPONSE.xml';

data _null_;
file request;
input;
put _infile_;
datalines4;
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:tem="http://tempuri.org/">
   <soapenv:Header/>
   <soapenv:Body>
      <tem:DoWork>
         <!--Optional:-->
         <tem:p>Test</tem:p>
      </tem:DoWork>
   </soapenv:Body>
</soapenv:Envelope>
;;;;
run;

%let RESPONSE=RESPONSE;

proc soap in=REQUEST
out=&RESPONSE
url="http://prognos2.savian.net/SampleServices/cdmAlive.svc"
soapaction="http://tempuri.org/ICdmAlive/DoWork"
;
run;

Adam used SoapUI to track it down and was nice enough to send a picture of where he saw the interface call:

Wix and InStyler

2011-06-20T20:14:00.000-07:00

This isn't a SAS post even though SAS is on the periphery of this one. This post is designed to help other developers in a similar boat if they get a hit on the error message verbiage.

The standard MS Installer is going away (next year, I believe), and a lot of people are converting to WIX (Windows Installer XML). On my latest project, I needed a custom installer. Now anyone who has ever worked with custom installers should be able to tell you what an absolute pain it is, how hard it is to debug, hard to put in custom screens, etc.

This seemed like an opportune time to jump over to WIX, especially when I needed a lot more than what the custom dialogs could provide under the standard Windows installer technology inside of Visual Studios. InstallShield was NOT an option. They want way, way too much money for their product and I am not a fan from days of yore.

WIX is very flexible but it is also hard to work with. There are no GUIs, per se, for it which means a lot of manual coding. My current WIX file, code generated, is weighing in at 900+ lines of XML. That and looking up the GUIDs for products, etc. meant a lot of manual effort and lots of places for mistakes.

I found a product on the web called Instyler Setup. Not sure how well supported it is, and it has a number of bugs, but it mostly writes the WIX for you. Two of the most annoying bugs I wanted to describe so others can seee them on the web:

1. "Error 2834: The next pointers on the dialog ErrorPopup do not form a single loop"

...when running the installer is caused by the text label on the ErrorPopup dialog having a tabstop set to true. MSI is very, very picky about everything which is why it is a nightmare to debug.

2. "Error 1723. There is a problem with this Windows Installer package."

...make sure you reference the CA dll instead of teh normal dll on your custom action.

3. To make working with WIX a lot easier, download and install the DTF package:

http://wix.sourceforge.net/

4. Turn on MSI debugging in your local group policy. this makes like a lot easier to track things down.

I hope these get picked up in the search engines and helps some other dev out at some point.

IIS 7.5 and SAS DCOM: ACCESS DENIED, ACCESS DENIED, ACCESS DENIED

2011-06-17T23:24:00.000-07:00

And the joy of being a pathfinder continues ;-]

IIS 7.5 has now changed the game. No longer do we play with NETWORK SERVICES to get SAS operational. Now it is a new user IIS APPPOOL\DefaultAppPool. Security has now been taken from NETWORK SERVICES, which covered the website, to specific security by the AppPools. I am not a security expert but that is my understanding.

So I took my happy little web services project (WCF) that worked fine in Visual Studio 2010 (Cassini), built a Web Setup for it, and deployed it to localhost. Then spent the next 7 hours staring at the same error: ACCESS DENIED. This was occurring on CreateObjectByServer. ACCESS DENIED, ACCESS DENIED, ACCESS DENIED, over and over again. I spent so much time in DCOMCNFG even going so far as to enable the Administrator account as the launching account, open up everything I could, and still ACCESS DENIED.

This morning I spoke to Bubba in SAS Tech Support. Great guy. He didn’t know this area but diligently was tossing out terms on tech notes he saw. He finally tossed out “enable 32-bit apps” and I keyed in on that one immediately. Well, I did a bit of investigation and have now made it far enough along for a blog post:

In IIS 7.5, get your application in place under a Virtual Directory:

Set up whatever app pool you are using. In this case, it is DefaultAppPool. Click on the Application Pools under the directory. Now here is where you have a choice. You can use the DefaultAppPool for everything but that also means giving it access to every library location, file, whatever. I opted to instead set up a new account called ‘WebService”.

Now it needs to be associated with the access rights for each directory and file. This is standard Windows stuff. Click on a directory and add the new username:

In IIS, change the App Pool to use the new identity:

Here are the settings and the ones to be concerned with:

Enable 32-bit applications and set the identity as needed. By default, it will be the name of your app pool. We will use our newly created name of WebService.

Now, time to go into DCOMCNFG (Start --> Run --> DCOMCNFG and fix the DCOM entry for the SAS Workspace:

Finally, change your code to the following:


                ObjectFactory factory = new ObjectFactory();
                ServerDef server = new ServerDef();
                SasWorkSpace = (Workspace)factory.CreateObjectByServer("ws", true, server, "WebService", "MyPassword");
                var id = SasWorkSpace.UniqueIdentifier;
                SasLanguageService = SasWorkSpace.LanguageService;
                keeper = new ObjectKeeper();
                keeper.AddObject(1, "SASServer", SasWorkSpace);

No more ACCESS DENIED.

Thanks Bubba. I think we p*wned it.

…not really but it works good enough for me for now ;-)

SAS StoredProcessService events

2011-05-17T08:49:00.000-07:00

If you encounter the following error when using SAS's StoredProcessService events:

event invocation for COM objects requires event to be attributed with DispIdAttribute

Roll your project back from .NET 4.0 to .NET 3.5. This may only be applicable to the web services code.

Example code:

class Program
{
static void Main(string[] args)
{

Workspace ws = new Workspace();
SAS.LanguageService ls = ws.LanguageService;
ls.StepError += new CILanguageEvents_StepErrorEventHandler(ls_StepError); <---- ERROR OCCURS HERE
ls.Submit("data test; abort; run;");
}
static void ls_StepError()
{
Console.WriteLine("Bingo!");
Console.ReadLine();
}
}

Flush with excitement...errr, logs

2011-05-13T21:32:00.000-07:00

Well, it is Friday night, I am back from the doctor with antibiotics due to a possible spider bite (love those little guys), and I decide to track down some performance issues with the SAS StoredProcessService (or Microsoft's Cassini). Somewhere, someone is responsible. Set up my little test bed to see it all work:


SAS.Workspace ws = new Workspace();
LanguageService ls = ws.LanguageService;
StoredProcessService sp = ls.StoredProcessService;
sp.Repository = @"file:" + @"x:\temp";
sp.Execute("test.sas", string.Empty);

All is well and good. Life is happy, kids are playing, sunshine is everywhere.

Wait! We need to see if the code ran. let's check the log:


string log = ls.FlushLog(1000);

AHHHH!!!! It never comes back. Where is my string!?!?

Turns out that if the FlushLog int amount EXCEEDS the total number of chars in the log, you go to a happy place called infinity. Less than the total chars and all is well.

I decided to test this a few times and was able to get a picture of my CPU utilization:

This does not happen when using the LanguageService directly, only when using the StoredProcessService. Time to wrap up this blog, pull back from the black hole of infinite loops, and go issue a bug report with SAS R&D.

If any SAS R&D folks in this area read this blog, let me know if I missed something. Right now, it seems like I have been bitten twice today by bugs.

3-fer on StoredProcessService

2011-04-18T22:08:00.000-07:00

Ok, well the major issues have been tackled except 1. How do you handle spaces in your NameValuePairs? Well, without the single quotes below, it kept splitting the values between the Sample and the Data. The single quotes hold it together.

Check this out:

string[] parms = new string[2]
{
"outdata=WORK.ALAN_20110418_070053",
@"datalib='X:\Data\Prognos\DemandForecasting\Sample Data'",
};

Notice the single quotes around the value? It took a long time to track that one down (thanks ThotWave).

Now it is simple to submit to SAS:

string newParms = string.Empty;
if (parms != null)
{
newParms = String.Join(" ", parms);
}
StoredProcessService sp = Common.SasLanguageService.StoredProcessService;
sp.Repository = storedProcLibrary;
sp.Execute(storedProcedureName, newParms);

I would ask R&D a simple question which is why isn't there an option on the SPS to specify the delimiter or am I missing a flag somewhere?

Our journey with SAS's StoredProcessService object continues...

2011-04-18T21:29:00.000-07:00

Check out this code:

string newParms = "outdata=WORK.TEST";
Common.SasLanguageService.Async = true;
StoredProcessService sp = Common.SasLanguageService.StoredProcessService;
sp.Repository = storedProcLibrary;
sp.Execute(storedProcedureName, newParms);

Standard stuff when working with the stored process server through VB or C# (or any other means of hitting these dlls).

Funny thing is the SAS log shows that the macro assignment of newParms never takes place. Hence, &outdata is undefined:

SYMBOLGEN: Macro variable OUTDATA resolves to

Commenting out the Async makes this all work:

SYMBOLGEN: Macro variable OUTDATA resolves to WORK.ALAN_20110418_070053

Now, even wiring up the event handlers for SubmitComplete do not fix the issue. Seems like a bug but I'll let the guys in R&D figure it out. If you have to waste a lot of time on it, however, keep in mind the nuances here until SAS R&D dives in.

Well, back out to the far left field where I hang out. Time to work on the business problem.

This one is a real Keeper

2011-04-18T13:01:00.000-07:00

So, if you ever get this error while trying to work with OleDb and SAS IOM:

The object ... could not be found; make sure it was previously added to the object keeper

Make sure that you include the following lines (the lack of a Keeper is what causes this error):

ObjectFactory factory = new ObjectFactory();
ServerDef server = new ServerDef();
Workspace ws = (Workspace)factory.CreateObjectByServer("ws", true, server, "", "");
ObjectKeeper keeper = new ObjectKeeper();
keeper.AddObject(1, "SASServer", ws);

That whole ObjectKeeper is missing from a number of examples that I have seen and it is critical for everything to function. It becomes apparent when trying to get the data from an OleDb DataAdaptor.

Wufoo and REST API

2011-02-20T22:28:00.000-08:00

Wufoo (http://www.wufoo.com/) is a great service for creating user forms and getting input. Build a form interactively, let people fill it out, and Wufoo collects the results. If you haven't checked it out before, I highly recommend this great service.

I was simply parsing the emails when they were sent upon form completion but that is a real hassle due to HTML. However, Wufoo has a really good REST API so I started playing around with it. Here, in C#, is how I used their API to get my data. This can easily be converted into SAS as well:

The HttpGet:


internal static string HttpGet(string uri, string apiKey)
{
    var pairs = new StringBuilder();
    var req = WebRequest.Create(uri) as HttpWebRequest;
    req.Timeout = 300000;
    req.Credentials = new NetworkCredential(apiKey, string.Empty);
    req.UserAgent = "AlanC";
    req.ContentType = "text/html";
    req.Method = "GET";

    // Get response

    using (var response = req.GetResponse() as HttpWebResponse)
    {
        if (response != null)
        {
            WebResponse resp = req.GetResponse();
            if (resp != null)
            {
                var sr = new StreamReader(resp.GetResponseStream());
                return sr.ReadToEnd().Trim();
            }
            return null;
        }
    }
    return null;
}

The call to the method:

XDocument xd = XDocument.Parse(HttpGet(@"https://xxxxxxx.wufoo.com/api/v3/forms/xxxxx3/entries.xml", "XXXX-XXXX-XXXX-XXXX"));

The https://xxxxx would correspond to your domain on wufoo.
The xxxxx after forms in the uri would be the form id assigned by Wufoo.
The XXX-XXXX-etc. is the API Key assigned by Wufoo.

The above can be found on the Wufoo site. Just look at the Wufoo API information to find out how to obtain it.

SAS Transport Files and .NET

2011-02-17T09:32:00.000-08:00

Well, someone contacted me about the Data Management Utilities. they loved them (thank you) but wanted to know about reading SAS Transport Files. Well, after fiddling with the localProvider a bit and getting nowhere, I stumbled onto something cool:

Download and install the SAS Universal Viewer from the SAS support site
Create a new project in Visual Studio
Add a reference from the SAS Universal Viewer install files to the following 2 dlls
SAS.UV.Transport
SAS.UV.Utility
Use the following C# code (adapt as needed):

TransportFile tf = new TransportFile(@"x:\temp\sample.xpt");
var x = tf.Datasets;

Your dataset will be in variable x

Don't forget to add the using statement to the top of your code for the SAS.UV.

RegexBuddy and Automated Emails

2011-02-10T18:31:00.001-08:00

So, I am going to help out with a computer club at my kids middle school. Since a topic was needed to start it all off, I said 'hey! regular expressions are used everywhere, why not start there. Plus, it is very useful.'.

So, I sent JGSoft (the owner of RegexBuddy) an email and explained the situation. How they were kids, we needed a limited version or a free one for a certain time, the kids may purchase it after the class, etc.

What was the response?

"We indeed do not offer a free trial version of RegexBuddy for download on our web site. At RegexBuddy's low price point and with our solid 3-month money-back guarantee, you can buy the full version of RegexBuddy entirely risk-free."

All of the supportive messages for RegexBuddy on SAS-L and other places and the best they can do is some automated response saying all of the kids need to cough up the 40 dollars to use their product. This is a fine way to get a bad reputation IMO.

Now I need to go get a free regex editor to show the kids despite RegexBuddy being the premiere product on the market. I am trying to turn them into kids interested in the product so they can go home and dazzle mom and dad who will then pay the fee.

Ok, off of my soapbox.

SAS-X Blog Aggregator

2010-11-28T14:17:00.000-08:00

Tal Galili has started up a SAS blog aggregator. The more folks involved in SAS, the better, as far as I am concerned.

You can find it at:

SAS-X

Getting SAS/IntrNet Operational on IIS7

2010-11-07T09:27:00.000-08:00

IIS7 is a major change in IIS. Don Henderson contacted me and asked if I could help get it working. It really isn't hard but involves a few steps.

How do you get SAS/IntrNet operational.

1. Enable CGI Role on Server through the Server Manager.

2. Add in your website in IIS. Right-click the scripts or cgi-bin directory where
the broker.exe is located and select Convert to Application.

3. While still having the scripts/cgi-bin folder selected, double-click on the handler mappings icon on the right-hand side. There should already be a listing for *.cgi at the top. Double-click on it --> Request restrictions --> Mapping --> Invoke --> File or Folder, Access --> Execute.

4. Click on the machine name in IIS. Click on ISAPI and CGI restrictions. Click on Add..., specify the full path to the broker.exe.

5. Optional: set up a host header.

Click on the Default website or on the website name. On the far right-hand side of the screen, click on bndings. Specify a host header (demos.savian.net, for example).

See here for more details:

http://www.wrensoft.com/zoom/support/faq_cgi_iis.html

Good luck and contact me if you have issues.

Alan

Excel Pivot Tables and Filtering

2010-03-17T11:56:00.000-07:00

So I get a post this morning from my client saying everything looks great and they can't find any issues with the latest release. Happy moment, cigar time.

Well, at this point I am over on the project and am working on my own time. That's fine considering that there might be future business there. Ok, so they mention 2 minor items that are annoyances that would be perfect. No obligation to do them but I have a few minutes so I dive in.

5 hours later I have it working. What was the issue? Pivot Tables in Excel and setting their filter values. For those poor saps who have to deal with it in the future, here is some C# code to hopefully save you some time:


        private static void HandlePivotTableChanges(string p)
        {
            FindProcessInstances();
            Console.WriteLine("Starting Excel. Please be patient while it loads.");
            string format = "'Product_Container_Release_Metrics_Workbook.xls'!Format_Workbook";
            Application ExcelApp = new Microsoft.Office.Interop.Excel.Application();
            ExcelApp.DisplayAlerts = false;
            Console.WriteLine("Opening workbook.");
            Workbook wb = ExcelApp.Workbooks.Open(p, m, m, m, m, m, m, m, m, m, m, m, m, m, m);
            Console.WriteLine("Running macro.");
            ExcelApp.RunMacro(format);
            Worksheet ws = (Worksheet)wb.Worksheets["Pivot Tables"];
            string pivotTable = "Forecast to Go";
            PivotTable pt = (PivotTable)ws.PivotTables(pivotTable);
            pt.ManualUpdate = true;
            PivotField pf = (PivotField)pt.PivotFields("Release (A)");
            PivotItems items = (PivotItems)pf.PivotItems(m);

            foreach (PivotItem item in items)
            {
                    if (item.Value != string.Empty)  // <--- CRITICAL LINE. Filters must have at least 1 value selected. Exception otherwise.
                        ((PivotItem) items.Item(item.Value)).Visible = false;
            }

            pt.ManualUpdate = false;
            ExcelApp.Calculation = XlCalculation.xlCalculationAutomatic;
            Console.WriteLine("Saving workbook.");
            wb.Save();
            wb.Close(false, m, m);
            System.Runtime.InteropServices.Marshal.FinalReleaseComObject(wb);
            Console.WriteLine("Closing Excel.");
            ExcelApp.Quit();
            System.Runtime.InteropServices.Marshal.FinalReleaseComObject(ExcelApp);
        }

        private static void FindProcessInstances()
        {
            Console.WriteLine("Finding open processes.");
            KillProcesses("Excel");
            KillProcesses("PowerPnt");
        }

        private static void KillProcesses(string processName)
        {
            Process[] processes = Process.GetProcessesByName(processName);
            if (processes.Length > 0)
            {
                foreach (var p in processes)
                {
                    p.Kill();
                }
            }
        }

        public static void RunMacro(this Application app, string macro)
        {
            app.Run(macro,
                    m, m, m, m, m, m, m, m, m, m,
                    m, m, m, m, m, m, m, m, m, m,
                    m, m, m, m, m, m, m, m, m, m
                );
        }

SaviDataSet 1.0.0.5 Released

2010-03-03T13:22:00.000-08:00

I just posted the latest version of the SaviDataSet reader/writer. The latest version allows for its use from the command line for converting SAS datasets to delimited, XML, and Excel files (2003 & 2007).

Please see the Help File in the Savian--> SaviDataSet --> Help.txt folder for instructions.

I will be incorporating a write feature in at some point but it is more complex since variables need to be defined.

The latest release can be found here:

SaviDataSet 1.0.0.5

SaviDataSet Alpha 1.0 On the Web

2010-02-06T21:20:00.001-08:00

I am finally ready with my SAS dataset reader/writer for .NET. It is written in 100% managed code using .NET 3.5. The dlls can be found
here.

A sample .NET console application can be found in the program file entries after the installation.

Update 2/13/2010:

I am now testing this against a real-world project so I am finding little bugs here and there. These are being addressed for version 1.0.0.1. If you need a build sooner than it is released, let me know.

I have also found that you need at around 100+ observations for this to work correctly. I am investigating but keep that in mind while testing.

[LATE BREAKING] I fixed the obs issue so I went ahead and uploaded the fixed version. Watch for breaking changes which are in the Readme.txt file.

Update 2/29/2010

Based upon Chris' work, I added in a console application that exposed my existing work. It allows for export of the sas7bdat to Excel, delimited, and XML. I will add in import at some point and have designed the interface as such. For example, I put the parms in an XML file to provide enough flexibility to accomplish everything. That has pros and cons but I figured the pros outweighed the cons.

I am also doing something I have meant to do for a long time which is to build a data viewer. Hence, I don't want an interim release but will bundle it all together. I have spent some time this weekend starting that process. All of the components are here but I have to pull them together which I am hoping to do this week.

SaviCellsPro and Exciting Changes

2010-01-15T17:12:00.000-08:00

Well, it was hectic week. I went back to work again (on a cool project) and spent a lot of personal time on SaviCellsPro. Specifically on a new utility that I think is pretty darn exciting.

In the past, when you used SCP, you needed to write the XML for the product. Now, the XML is well-documented and I have provided help files and samples to make it easy but, let's face it, a lot of people are uncomfortable with XML.

A suggestion was made by a friend of mine at SAS to have a utility that would take an existing Excel spreadsheet and automatically generate the beginning XML so it doesn't have to be done from scratch. Well, I scratched my head and few times and decided, over the Christmas holiday, that this might be doable. Well, it was. SCP now has an XML creator that can take an existing workbook and describe it in XML. A SAS programmer can then use that as a basis and incorporate their data into the SCP XML. It makes it much easier to get started.

The utility supports cell values, styles (fonts, font sizes, borders, italics, bolds, etc.), formulas, graphs, images, print settings, and more. I will continue to add to it as demand is shown.

Expect a rollout next week on SAS-L, sasCommunity, and probably this blog. I have some testers looking at it first but I think this really rounds out SCP nicely. I will probably add in OLAP support and more robust graphing but for now, it is a cool utility that makes it easy to generate native Excel, PDF, and HTML files of your SAS data (or any other data for that matter). Since it uses XML, it can be run on any computer that produces text.

Look for my presentation at SGF on SaviCellsPro and how to use it. It is on Tues at 1:30.

.NET Coders and the sas7bdat dll

2010-01-04T21:04:00.000-08:00

I have been spending the last week working on how the interface to the sas dataset can be used by a .NET developer. It is of particular concern because once the initial dll is released, it will be hard to change it. Getting the interface 'mostly' correct is important.

After a lot of thought, here is what I have so far:

//Initiate logging

Savian.SaviDataSet.Logging.StartLog(@"c:\temp\SaviDataSetErrors.log");

//Create SAS dataset object

SasDataSet ds = new SasDataSet();

//Add 4 variables

ds.AddVariable("AA1", "Var1");
ds.AddVariable("AA2", "Var2");
ds.AddVariable("AA3", "Var3", 10, SasVariableType.Character,"$5.","$7.");
ds.AddVariable("AA4", "Var4", 10, SasVariableType.Character);

//Add observations
//Bulk insertion

for (int i = 0; i < 1000; i++)
{
object[] values = new object[] {1, 2, "Test_" + (i + 1).ToString("00#"), "Test2", "Test3", "Test4", "Test5"};
ds.AddObservation(i, values );
}

//Modify observation - Similar to a .NET dataset

ds.Observations[0]["AA1"].Value = "Test";

//Write dataset

ds.WriteDataSet("TEMP", @"c:\temp\test.sas7bdat");

//Stop logging

Savian.SaviDataSet.Logging.CloseLog();

The area that was a challenge was adding observations since they are really an array across existing variable metadata.

The other area I am focused on is keeping the wording the same as how SAS would refer to things. Hence, observation instead of row and variable instead of column.

I have also been working on validation so that invalid data does not make it into the dataset. I can't prevent everything, but I am making a good faith effort to minimize it. Also, formats/informats have to be checked, name lengths, length values, dataset name, etc. all have to be verified to make sure they comply. So far, so good but more checking is underway.

While working on this, you realize how much effort has been put into the dataset by SAS over the years and how much work they have to go through to make things compliant and workable.

Progress on the sas7bdat

2009-12-28T23:14:00.000-08:00

It is tough at times to be a consultant and get those side projects out of the way. That said, I finally had a chance to really work on the sas7bdat some more over the holidays. Not too much since my personal focus is my kids.

A really tough project interfered since the last time I played with it in September so I was looking forward to the holidays to pass another milestone.

Well, I just managed to create a 100 record dataset with 7 variables, char and numeric. My little Christmas gift to myself.

When I first started work on this project many moons back, I never expected that writing a dataset would be much more difficult than reading one. That was a major mistake. However, the writing has made me really discover a lot more about the structure and how it works plus I have had to step up my coding skills a bit to make it through.

Long road but finally happy to be here after so much time.

Happy New Year everyone.

Alan

Calculate a SAS Observation Length

2009-11-10T05:33:00.000-08:00

Well, I couldn't find this documented anywhere (someone correct me if I missed it) but the question for the day was how to calculate the SAS Observation Length. After some investigation, here is how it is done (pseudocode):

obsLength = Sum of all of your character lengths + Sum of all of your variable lengths;

if any numeric variables have a length of 8 then
round the obsLength to the nearest factor of 8.

Since the default for numeric variables is a length of 8, the rounding will almost always occur but it is not a guarantee.

I hope this helps others who may need to calculate this value for storage or performance reasons.

What next?

2009-08-27T19:23:00.000-07:00

As I can see that shimmer of light at the end of the tunnel I have to start thinking "what do I do with a binary SAS reader/writer?"

I originally started down this path with the goal of avoiding having to read/write SAS using XML Now that I am nearing the finish, I am trying to determine what comes after this (other than a quiet, non-working evening).

Where I am leaning right now is an Excel add-in or something in that space. I think Excel/SAS is one of the biggest areas of opportunity and an area that frankly does not get enough attention, IMO. SQL Server add-ins are another possible option so SAS can be supported in SSIS. It is early in this mulling period though so I would appreciate any thoughts people have on what they would like to see.

Created my first SAS dataset without using SAS

2009-07-26T19:06:00.001-07:00

Well, around 14 months ago, I started on a journey to understand the SAS dataset so I could read and write one independently. Originally, I needed the functionality for a client but it became an obsession to figure it out. Client work and personal matters interfered non-stop but I have finally written my first SAS dataset using C# and .NET.

It isn't pretty (no data is in it) but it is a dataset and that is what matters. Putting data in is minor so I am not concerned about that.

A lot of effort still remains to get it all worked out but I am happy to be at this position. After cleaning the code up and finishing it, I plan on making interoperability tools to simplify interfacing with SAS, expecially in areas where SAS needs help (i.e. SSIS, MS Office).

It is a good day but it has taken a brutal amount of hours to accomplish this feat. I would not suggest to others to spend this amount of time on this issue: it is simply mind-numbing.

[Update 07/27/2009]

I just wrote my first data into the dataset. It is only numerics right now but it is a start.

[Update 07/29/2009, 3AM]

I just created a dataset that contains text fields. I had an extraneous bug that made me think it was failing when it wasn't. Such is the life of a coder. Anyway, on to cleaning up code that I left hard-coded and making the system a bit more robust.

Let me be clear: I am tired of looking at hex code.
476F6F646E69676874

[Update 08/27/2009]

Progress continues. After I created my first dataset, the goal was to get rid of any hard-coded values and make it more dynamic. While doing that work, I hit a major logic issue and had to focus on figuring that out. I have made it through that and am now mopping up the remnants and tightening up the logic. Since the goal is to ship a .NET dll for developers, I also need to consider how a developer codes to the dll. Basically, I am in cleanup mode and testing additional variables, labels, etc. to see where things break or do not work as intended.

Stay tuned.

PM Stories

2009-07-15T09:53:00.000-07:00

These are stories I have collected over the years. i hope people enjoy.

Project Managers In Practice (PIMP…sorry, PMIP)

Our first story involves a young PM, new to the project and not very technical

Ok, so I (senior consultant guy) have a project and there are 3 changes requested by the team:

1. Change a label (minor)
2. Allow a user to save their selections made on dropdowns (these select what displays in a grid)
3. Allow a user to change a grid and submit the values to another program

That’s all fine and good. I bid 44 hours to make the changes.

Team now wants a user to not only make the grid changes (#3) but save those changes as well. So PM says change 2 & 3 are now merged. We need to save selections and grid entries.

I said, OK, slight increase in scope, 50 hours total.

PM says, wait, we merged 2 & 3 together so now we only have 2 changes not 3 so it should be a decrease in hours.

PM survival tip #1: Reduce scope by merging letters on the plan and hope no one notices.

Story 2 involves 4 experienced PMs

Project is in a major crisis. High-demanding customer, a lot of screaming, no time to deliver on time. Save team is called in to try and pull it out. Atmosphere is very tense.

Some of the project members (developer/consultants) have to work on all 4 segments of the overall project simultaneously. Each segment is given a PM. Customer wants to know what is happening all of the time so PMs demand status reports at least twice a day. For consultants working on all segments that is 8 status reports per day. When complaints are lodged, developers are told that it has to be that way and why aren’t we getting the work done.

Revolt of developers / consultants ensues after months of this activity.

PM survival tip #2: if you flood your customer with progress reports they will think you are doing your job. See tip #4 below.

Story 3 involves a fixed bid contract and some super savvy consultants

Two senior consultants figure out a clever way around a major stumbling block on a late project. They can change the process agreed upon slightly and save time, money, effort, AND bring in a late project on time. The customer also wants it done since the new process has a lot of compelling advantages.

Guess who says no? That’s right, our friendly neighborhood PM. It seems that the PM has decided that the original project, as scoped and laid out, is immutable for all time. No changes are allowed from the project plan WHATSOEVER.

PM Survival Tip #3: Minimize risk by making no changes. No changes, no new plans to build. Voila!

Story 4 involves a PM with a dangerous weapon…Outlook

A project, as usual, is in crisis. The PM decides that their main role is to send out emails…lots and lots of emails. On the day of ‘the incident’, the PM has sent close to 50 emails since 8am.

Then it happens…just before lunch. The PM sends an email saying that people are sending way too many emails.

Consultants laugh and break for lunch.

PM sends 10 more before they get back….

PM survival tip #4: Forward everything, comment on everything, make up stuff. The success of a PM is ultimately measured by the suffering of others.

Story 5 involves a PM with no sense what a PM does

So, the PM contacts the lead on a project and tells the lead that there are too many emails about the project and could the lead please summarize all of the open issues and assign priorities…

…isn’t this what a PM does?

Story 6 involves a PM…again…

The developer has been waiting on getting access to the client’s machine for almost a week. Technical issues, blah, blah, blah.

After the week is up, the PM wants to know timelines (this is good, they are doing their job). Developer says the development has been pushed by 3 days. The PM, who has been copied on every email between the client and the developer, says that they don’t understand why the project is delayed because of a lack of access…

Rule #1 in computers: it is hard to use them if you can’t access them.

Story 7 Guess who…

This story involves, SURPRISE, a PM who we will affectionately call Rum. Now Rum is a great PM because Rum thinks that EVERY piece of email and/or communication must flow through him. Rum insists that no direct communication happen between the technical people or the customer and that he will just forward on what is relevant.

I…AM…IRON MAN…DO, DO, DO DUH DO…

Story 8 Who wants it early?

So, the consultant finishes the application early (1 week) but they can’t test it because it isn’t scheduled to be deployed until the following week. They are told to wait until the project scheduled date so they meet the schedule.

Story 9 This is a loving one that I just title ‘Make the Sh*& up’

Well, the customer wants a project plan. Enter our superhero ‘The PM’. Rather than getting requirements or design done first, the PM decides ‘hey, let me have fun with little chain icons and links and just create a project plan! Oh boy!’.

So a project plan is created, with no document saying what needs to be done and no involvement from the programmers. Developers finally start working on the project and are told you must meet this date for this functionality. Programmers say WTF!?!? Where did this come from.

Well, it is in the project plan that we designed w/o any involvement from the people who do the work…

PM Survival Tip #5: When in doubt, just SWAG the dates and blame the developers.

Windows 7

2009-03-11T17:26:00.000-07:00

Ok, I am testing out Windows 7 and it seems great...except it didn't recognize my printer and wouldn't let me install the driver. It errored out with:

Unable to Install Printer. The printer driver is not compatible with a policy enabled on your computer that blocks Windows NT 4.0 drivers.

Ok, the solution (as is often the case under Vista) is to disable UAC. Don't go looking for a policy setting (it isn't there that I could find and I scoured all of them), don't try to install a local port like Microsoft advices for Windows Server 2003. Just disable UAC and you are good to go.

I know this one isn't SAS related but people really rely on some tips like these when they are beating their heads against the wall.

Opportunity

2009-02-24T19:33:00.001-08:00

It will be very rare when I criticize a SAS PR release but this one was a bad release, in my opinion:

http://www.sas.com/news/preleases/AberdeenReport.html?utm_medium=RSS&utm_source=Corp_PressRelease

It infers that only mid-size businesses use spreadsheets and that it shows a level of immaturity. My current client, a SAS user, is 15x the size of SAS and uses a lot of spreadsheets. Why? because it is easy and it works. Also, let's not use a generic term spreadsheets. EVERYONE uses Excel so put a name on the usage and don't generalize.

I found this sentence the most galling:

Hatch said that the most successful midsized enterprises are much more likely to be using powerful and relevant technologies such as those from SAS, the leader in business analytics software and services.

A business can use SAS and be relevant while also using Excel. Trying to say that someone needs to use SAS end-to-end is the major issue I have today with SAS. SAS is extremely powerful but to say that their interfaces have to be the vehicle for BI is nonsense. Excel is a fantastic delivery mechanism for SAS BI.

Bad press release, IMO. I am unsure who it targets. The 'unsuccessful' midsized enterprises who love Excel and just want SAS to work with it or the ferociously succesful large businesses who have melded the 2 sides and view them as compatible.

Sell SAS guys but do so with an understanding of what clients want: SAS to work with Microsoft and display data in Excel, Word, Project, Powerpoint, SharePoint, etc. No need for a rift when the 2 sides work well together.

Microsoft Project and Inserting Columns

2009-02-17T18:21:00.001-08:00

I am doing a LOT of work right now with SAS and Microsoft Project using the Microsoft Project interop, Base SAS, and SAS/IntrNet. One of the client requirements was to add in additional columns to the default MS Project columns. Well, I searched high and low and couldn't figure out how to do it. After some slogging, here is what I came up with:



    class MsProjectTest
    {
        Project p ;
        object m = Missing.Value;
        string file = @"c:\temp\test.mpp";

        public void Process()
        {
            Application app = new Application();
            app.Alerts(false);
            p = app.Projects.Add(m,m,m);
            Task t = p.Tasks.Add("Test", m);
            t.Name = "Test Node";
            t.Start = "03/20/2009";
            t.Start7 = "03/31/2009";
            p.Application.TableEdit("Entry", true, m, m, m, m,
            "Start7", "Start Test", 10, PjAlignment.pjLeft, 
            true, true, 255, 1, 4, 1, m, m);
            p.Application.TableApply("Entry");
            p.SaveAs(file, PjFileFormat.pjMPP, m, m, m, m, 
                     m, m, m, m, m, m, m, m, m);
            app.Quit(PjSaveType.pjDoNotSave);

            System.Diagnostics.Process.Start(file);
        }
    }

I hope this helps some other interop developer out
there struggling with the same issue.

Silverlight, WCF, SAS, and Demos

2009-01-06T22:57:00.000-08:00

I had a client ask, very nicely, if I could showcase some of my work to help make their internal sale. Well, this is all good to me since I could possible get some business in 2009 and I needed to do it anyway.

Well, the only time available was the holiday season so I cranked out a lot of code, learned a LOT about WCF and Silverlight, but was able to get my demo site built.

The site utilizes WCF to talk to SAS on the backend and expose SAS out as a service on the front-end. Things are very secure right now so I have held a lot of the 'what is possible' back so that I don't expose a security hole.

Here is the site:

http://demos.savian.net

Silverlight has been the most challenge simply because the tools are still a bit immature and the coding examples are thin, thin, thin but I love the possibilities. The more I work with Silverlight, the more I like it even if the syntax leaves me scratching my head at times.

The examples I put on the web use SAS as a local resource but SAS could easily be sitting on another server and be exposed via IOM or IntrNet (REST).

Consultants, Idealism, and the Real World

2009-01-06T22:40:00.000-08:00

Today, I hit an absolute programming wall. I was deep into my code, client asking for a deliverable date, and I hit a deal-breaker coding moment. I think, we have all had those things happen. You're there in the bowels and suddenly find some code that simply is not going to work and may jeopardize the entire design.

What to do? Well, Google and hope for a miracle. In this case, i got fortunate and finally stumbled onto a solution late into the evening.

However, the issue made me start doing some more research into the particular area I had struggled with and was reading a lot about static vs dynamic languages (this post is not about that). C$ 4.0 recognizes this dilemma and is implementing it but it isn't available now. However, what a firestorm on the web over the addition of dynamic capabilities.

What I realized is how far off the idealists / zealots are from the consultant/business coder.

If I tell my client that I have to rearchitect the 100s of hours of coding, what are they supposed to say? Instead of that Feb 1 deadline, I need to redo everything and deliver it in June? They will drop my contract and possible pursue action. Reality will sink in...quickly...

In the world of consulting, it is critical to deliver projects on time and to estimate accurately. When you hit those walls, there isn't a lot of time to debate the nuances of static/dynamic languages: I just have to have something that works...and NOW. Customers have a real allergy to paying a lot more money on top of what they already paid.

I dogged this problem, like always, and found an answer so I dodged a bullet: sometimes you aren't so lucky. There are times when you, as a consultant, have to take it in the shorts and I have done my time there. However, to ask a a client to pay because 'it isn't perfect' ignores the fact that nothing in computing is perfect: just make it work.

So, at the end of the day I will take tools that focus more on getting the job done than being perfect in theory. And I will also say an extra thank-you tonight for finding that one blog posting I really, really needed...

Catching pop flys,

Alan

SAS and Microsoft: Different Worlds

2008-11-18T07:38:00.000-08:00

Well, first of all, sorry I haven't written recently. My time has been taken up by some great SAS work but it is under wraps for the time being.

I was chatting with a SAS buddy of mine recently and I started to comment on the differences between the SAS and Microsoft development communities. I like them both but they each have the pros and cons.

The Microsoft community travels fast, I mean lightening fast. There are constant updates, massive 3rd party support, great forums, blogs, etc. from the Microsoft developers to always keep people updated. Best of all, the constant changes means that it is always exciting and new. Like getting several Christmas's a year. Cool new technologies like Silverlight (WOW!!), C# 4.0, Office 2007, Windows 7, LINQ, etc. On the 3rd party front, I have dozens of graphing toolsets I can use. Dozens of databases, etc.

That is also their con which is that you always have to be learning and never catch up. It also leads to a lot more uncertainty with how things work which can be a detriment when building enterprise systems.

The SAS community is much slower to change. Part of this is due to the Institute which doesn't release software very often and keeps the software fairly static.
I like that I can count on Base SAS to work each and every time. It is still the best way of manipulating data that I know of. Also, the user community (SAS-L, mainly) is a club with the same players each year. I like the steadiness of the SAS community and the software. When I go to a SAS conference, I know people. The Microsoft conferences tend to be a madhouse and you have to arrange to meet people you might know.

The con with SAS is that steadiness. The lack of change means that people still use older technologies such as DDE and do not explore further. So often, I see the same way of doing things being recommended rather than people stepping back and asking if there is a better way. The insularity of the community tends to hold back innovation, IMO. SAS does not have a 3rd party ecosystem so you don't have other companies being encouraged to develop new ways of using SAS.

At the end of the day, though, I like SAS and Microsoft. Each has strengths and weaknesses.

Lastly, I would encourage SAS developers to find dual interests simply to see alternate ways of doing things. Not everyone needs to embrace Microsoft but I see a great positive in adopting multiple technologies simply to change the view of the world.

SAS Integration Technologies and Vista

2008-08-12T21:49:00.000-07:00

Ok, I am sure that I will get some grief about this but I needed to submit my SAS code from a .NET application. this was on my new PC which is running Vista 32. Anyway, no matter what I did, I kept getting the following error:

"Retrieving the COM class factory for component with CLSID {440196D4-90F0-11D0-9F41-00A024BB830C} failed due to the following error: 80040154."

Well, normally that is addressed by simply installing the IntTech client and setting permissions to None in dcomcnfg. It didn't work this time.

Here is what i did to fix it (people can try these in isolation to see which one is the root):

1. Right-click on the the inttech.exe file and run it in compatibility mode for Win XP SP2

2. Go into dcomcnfg and set every single SAS DCOM object with permissions of None.

I could play with this some more but I am now behind.

Update 3/31/2010

This worked for me on Windows 7 64 and when the dcom stuff failed:

To register the IOM server,

1) Copy SASVRSRV.DLL from !SASROOT\CORE\SASEXE which can be found on
the SAS System CD or a Network copy of SAS to your Client
!SASROOT\CORE\SASEXE directory.

2) From a command prompt run !SASROOT\SAS.EXE /REGSERVER

Microsoft Project Files, SAS, and .NET

2008-05-06T20:32:00.000-07:00

Ok, another fun, fun time with SAS and an obscure area. Here's the scenario, the client needs data from an MPP file (Microsoft Project) converted into a SAS dataset. I tried lots of routes, all to no avail. As usual, I get to go through the mess of COM interop, lack of documentation on the web, and SAS not supporting write access in the local data provider. Ahhhh, the joys of consulting ;-]

I finally got the following C# code operational and it writes the data into a SAS-friendly XML format. I hope this helps someone else:

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Linq;
using System.Text;
using Microsoft.Office.Interop.MSProject;
using System.Reflection;
using Savian.Core;
using Savian.DataManagement.Delimited;

namespace Client.ReadProjectFile
{
public class Main
{
List tasks = new List();

public void Process(string file)
{
ReadMppFile(file);
ConvertFileToSas();
Console.ReadLine();
}

private void ConvertFileToSas()
{
DateTime start = DateTime.Now;
Console.WriteLine("Start converting to SAS..." + start.ToShortTimeString());
DataTable dt = tasks.ToDataTable("TABLE");
dt.WriteXml(@"c:\temp\Client.xml");
DateTime end = DateTime.Now;
Console.WriteLine("Finished conversion..." + end.ToShortTimeString());
Console.WriteLine("Elapsed time: " + (end - start).TotalSeconds + " seconds");
}

private void ReadMppFile(string file)
{
ApplicationClass app = new ApplicationClass();
app.DisplayAlerts = false;
app.ScreenUpdating = false;
app.Visible = false;
app.MacroVirusProtection = false;
app.FileOpen(file, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, PjPoolOpen.pjDoNotOpenPool,
Missing.Value, Missing.Value, Missing.Value, Missing.Value);
var mppTasks = app.ActiveProject.Tasks;
int i = 0;
DateTime start = DateTime.Now;
Console.WriteLine("Start creating records..." + start.ToShortTimeString());
foreach (Task task in mppTasks)
{
ProjectInfo pi = GetProjectInfo(task);
tasks.Add(pi);
i++;
if (i % 100 == 0)
{
Console.Write("Records created: " + i);
Console.SetCursorPosition(0, 1);
}
}
Console.WriteLine("Records created: " + i);
DateTime end = DateTime.Now;
Console.WriteLine("Record read completed..." + end.ToShortTimeString());
Console.WriteLine("Elapsed time: " + (end-start).TotalSeconds + " seconds");
app.FileClose(PjSaveType.pjDoNotSave, Missing.Value);
}

private static ProjectInfo GetProjectInfo(Task task)
{
ProjectInfo pi = new ProjectInfo();
AssignFields(ref pi, task);
return pi;
}

private static void AssignFields(ref ProjectInfo pi, Task t)
{
pi.Name = t.Name;
pi.Start = (DateTime)t.Start;
pi.Finish = (DateTime)t.Finish;
pi.Id = t.ID;
pi.PercentComplete = Extensions.GetIntValue(t.PercentComplete);
pi.Duration = Extensions.GetIntValue(t.Duration);

}
}
}

class ProjectInfo
{
public int Id { get; set; }
public string Name { get; set; }
public DateTime Start { get; set; }
public DateTime Finish { get; set; }
public int? PercentComplete { get; set; }
public int? Duration { get; set; }
}

Silverlight Lessons Learned

2008-03-23T07:32:00.001-07:00

After a shotgun week of Silverlight 2.0, I thought I would share a few lessons learned.

Blank page

If you get a blank page after uploading your xap file, make sure that NETWORK SERVICE is in the security accounts.

XAP File

XAP is the only file that needs to be moved typically. As you do your code updates, just move the XAP file into place.

Dynamic XAML

Dynamic XAML requires a namespace attribute now:

<Grid xmlns="http://schemas.microsoft.com/client/2007">

Timing Issues

There seems to be a timing issue in Silverlight that is causing an event to get triggered before the data streams down from a site. This still requires investigation.

Visual Studio 2008 and Silverlight Debugging

For some reason, if a serious Silverlight error occurs, debugging will be disabled in the web application. This may make it seem like events are not firing when they are, just that the debugger is broken. Right-click properties on the web application --> Start Options --> Debuggers --> Check Silverlight

Overall

Overall, I love XAML and Silverlight. It was absolutely incredible to work with but it took a lot of time due to minimla information on the web right now. Hopefully, this post will help some other folks out.

Microsoft Silverlight

2008-03-07T06:04:00.000-08:00

I have been in Vegas all week at the Microsoft MIX08 conference for the rollout of Silverlight 2.0. It is so revolutionary that it really got me thinking. That and SAS Global Forum made me mull a few thoughts:

1. Silverlight is going to revolutionize the world. Bold statement, I know.However, what I saw, as a web developer, absolutely stunned me. If you don't believe me, see what NBC will do with the Olympics. 2200 hours of hi-def video, 4 channels per user, VOD, and just much, much more.

However, check this out for something live now:

http://memorabilia.hardrock.com/

Data visualization and BI will move toward Silverlight. SAS should move there as well. as quickly as possible. SAS should be a leader here and not follow the inevitable.

2. Microsoft is innovating at an amazing pace. Windows Server 2008, Silverlight, Surface, Vista, IE8, IIS7, and on and on. Meanwhile we await a .07 release from SAS that is languishing once again. Basic project management: reduce scope, increase budget, adjust timeframes...hmmm, it seems like the only option at SAS is the latter and I honestly don't understand it. Hopefully, SAS Global Forum will provide some answers.

3. Silverlight controls are being released open source. Office formats are now open, the .NET source code has been released open source. I think SAS can learn from this and realize that money can be made without keeping everything tight to the vest.

4. Integration Technologies should be bundled with Base. All of this new technology requires it so why, as a customer, do I have to have a separate line item? Put SAS/IntrNet in that category as well. Why does web enablement cost extra from SAS. Pay more and realize it is 2008 and web enablement is part of Base SAS.

5. Microsoft actively encourages blogging even if it is critical of the company. People should be trusted and I have found most use discretion, especially if they are employees. Blogging and active user participation helps sell the company message and actively engages the user community. A once a year conference is not enough in today's fast-paced world. SAS Forums are fine, SAS-L operates via 'birdies' but wouldn't it be great to have blogs from Paul, Vince, Chris, Eric, etc. out in the wild? Open and free, give and take, user comments...

As a SAS advocate, user, former employee, and partner, I am asking for change. More openness, more releases, more interaction. Simple stuff that is doable today.

SAS LanguageService and You

2008-02-06T21:40:00.000-08:00

For those of us who work with SAS LanguageService on a regular basis, it is frustrating. The entire SAS COM interface is very poorly documented and is light on functionality. It gets better over time but it would be much nicer to have a .NET interface so we don't have 50 COM nulls in a single method call.

Ok, that all said, I just came off of a marathon (10+ hours) bug fix that damn near drove me mad.

I was submitting a simple query via the Language Service:

proc sql;
create outdata.test as
select *
from DICTIONARY.COLUMNS
where …some criteria…
;
quit;

Ok, no problem. I could submt the query within the SAS Editor and it would work fine. However, DEPENDING ON WHERE I WAS IN MY .NET CODE, it would return 0 rows. Huh!?!? It wasn't the dataset, it wasn't the syntax for the call, it was where the call occured at during processing. Holy smokes Batman! That's a bear of a bug to catch.

You do the normal stuff and isolate, isolate, isolate. Well, it took a long time to isolate that it was where it was being called. Damn, damn, damn...one of the toughest bugs I have hit in years.

My answer for how to solve it:

Common.SAS.LanguageService.Reset();

(I am using a static class called Common).

Will this answer stand the test of time? Not sure. I have asked my buddy at SAS whether I am on the right track.

Back to the first point: better documentation would be welcome. Even better, an ADO.NET data provider WITHIN Base SAS. Don't make me buy IOM to use SQL processing.

Ah well, back out to the far corners of the ballpark I go...

SAS and LINQ - Part 1

2007-12-07T03:44:00.000-08:00

So, I am just getting my feet wet with LINQ, the new integrated query language within .NET. There are loads of articles on LINQ elsewhere. What I want to do is to illustrate LINQ with SAS and this is my starting point.

Look at the following code:


using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Xml.Linq;
using System.Windows.Forms;

namespace WindowsFormsApplication2
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void btnGetData_Click(object sender, EventArgs e)
        {
            XElement table =XElement.Parse(
               @"<TABLE>
               <CLASS>
                  <Name> Alfred </Name>
                  <Sex> M </Sex>
                  <Age> 14 </Age>
                  <Height> 69 </Height>
                  <Weight> 112.5 </Weight>
               </CLASS>
               <CLASS>
                  <Name> Henry </Name>
                  <Sex> M </Sex>
                  <Age> 14 </Age>
                  <Height> 63.5 </Height>
                  <Weight> 102.5 </Weight>
               </CLASS>
               <CLASS>
                  <Name> James </Name>
                  <Sex> M </Sex>
                  <Age> 12 </Age>
                  <Height> 57.3 </Height>
                  <Weight> 83 </Weight>
               </CLASS>
               <CLASS>
                  <Name> Jeffrey </Name>
                  <Sex> M </Sex>
                  <Age> 13 </Age>
                  <Height> 62.5 </Height>
                  <Weight> 84 </Weight>
               </CLASS>
               <CLASS>
                  <Name> John </Name>
                  <Sex> M </Sex>
                  <Age> 12 </Age>
                  <Height> 59 </Height>
                  <Weight> 99.5 </Weight>
               </CLASS>
               <CLASS>
                  <Name> Philip </Name>
                  <Sex> M </Sex>
                  <Age> 16 </Age>
                  <Height> 72 </Height>
                  <Weight> 150 </Weight>
               </CLASS>
               <CLASS>
                  <Name> Robert </Name>
                  <Sex> M </Sex>
                  <Age> 12 </Age>
                  <Height> 64.8 </Height>
                  <Weight> 128 </Weight>
               </CLASS>
               <CLASS>
                  <Name> Ronald </Name>
                  <Sex> M </Sex>
                  <Age> 15 </Age>
                  <Height> 67 </Height>
                  <Weight> 133 </Weight>
               </CLASS>
               <CLASS>
                  <Name> Thomas </Name>
                  <Sex> M </Sex>
                  <Age> 11 </Age>
                  <Height> 57.5 </Height>
                  <Weight> 85 </Weight>
               </CLASS>
               <CLASS>
                  <Name> William </Name>
                  <Sex> M </Sex>
                  <Age> 15 </Age>
                  <Height> 66.5 </Height>
                  <Weight> 112 </Weight>
               </CLASS>
               </TABLE>");


            var obs = (from o in table.Elements("CLASS")
                       where (string)o.Element("Sex") == " M "
                       select new
                           { 
                               Name = (string) o.Element("Name"),
                               Age = (string) o.Element("Age")
                           }
                       );
            List observations = new List();
            foreach (var ob in obs)
            {
                observations.Add(ob.Name) ;
            }
        }
    }
}

The above is C# code.

Notice that the input data is a SAS XML representation (I am doing it inline right now as a demo. It could easily come from a file)? Notice the SQL-like code toward the bottom? That is LINQ.

One of the oft-used criticisms of low-level languages is that they were fine for a lot of things except processing data. For that, we needed languages such as SAS or SQL. LINQ doesn't substitute for the database but what it does do is enable us to combine data processing with the full-blown power of a language like C#. The syntax isn't as elegant as SAS (by any means) but what it does provide is a way to juice up the power of data processing against SAS data when needed.

SAS is OleDb and ODBC compliant. As of yet, Microsoft has not provided support for either one within LINQ. When it does, I will post examples of using LINQ against a SAS dataset directly rather than via XML.

The above represents a fraction of the power of LINQ and it is in its first iteration. Using the same query language against any data source (SAS, XML, SQL Server, etc.) and then combine that with the power of a language such as C# holds a lot of potential. Power SAS users, especially on the ETL side, should keep an eye on LINQ because it offers up a great way to do data processing.

Demarcation

2007-11-26T06:59:00.000-08:00

Good systems practice is to have demarcations between systems layers. This involves separation of the following layers (at a minimum):

Application
Business logic
Data access
Data

SAS programmers though tend to muddle all together:

libname indata ...; <-- Data Access
data mydata...; <--- Business Logic
ods html; proc report...; <--- Application Layer / UI

A better way to handle is to treat all layers as standalone and get them out of each other's space. The easiest way for a SAS coder to accomplish this is to use macro libraries to get it all started:

%GetFinanceData() ;
%DetermineProfit() ;
%OutputResults() ;

However, even this is constrained since everything is called together and a single program controls what happens top-to-bottom. Rather than doing the final piece as a part of the batch job, consider doing it on demand and perhaps through other means:

%GetFinanceData() ;
%DetermineProfit() ;

...wait until use requests information....

Get information on demand and make the presentation logic extraneous to SAS. Someone could even keep it in SAS ODS but the idea would be to pull it out to an application layer that is completely separated from how the information was formulated.

SAS programmers would be better off putting in extremely strong lines of demarcations between layers. This makes code easier to maintain, easier to change, and easier to understand.

Longer term, web service calls will simplify this even more but the concept of processing silos can be done now. Break your code apart so everything isn't glued together so tightly that code reuse is hard and code libraries are non-existent or little used.

C# and DBF files

2007-11-26T04:11:00.000-08:00

I have a client that asked me to read DBF files generated by their SAS application. Well, this posting is to explain to someone what i have learned in reading DBF files using C#. The main thing I learned was no spaces in the file path.

Here is the code that worked for me:

string connectionString = "Driver={Microsoft dBase Driver (*.dbf)};SourceType=DBF;SourceDB=C:\projects\Client\Data\WeeklyAvailableComparison;Exclusive=No; Collate=Machine;NULL=NO;DELETED=NO;BACKGROUNDFETCH=NO;";
string selectCommand = @"SELECT * FROM C:\projects\Client\Data\WeeklyAvailableComparison\ac_141.dbf";
DataTable dt = new DataTable();

OdbcConnection oConn = new OdbcConnection();
oConn.ConnectionString = connectionString;
oConn.Open();
OdbcCommand oCmd = oConn.CreateCommand();
oCmd.CommandText = selectCommand;

dt.Load(oCmd.ExecuteReader());

I tried doing a 8.3 conversion and it wouldn't work for the select clause.

I hope this helps someone.

Alan

Using SAS\Share and C#

2007-07-11T16:33:00.000-07:00

Ok, I went a little crazy getting SAS\Share working with OleDb from C#. Since I got it figured out, someone else may have the same issue.

Here is how I did it. First of all, make sure you client machine and the SAS\Share server can talk to each other. I tested from a development machine that has SAS on it. Otherwise, a test app will have to be built.

In the web.config, I have these settings:

<add key=\"ShareConnectionString\" value=\"Provider=sas.ShareProvider.1;Data Source=share1;Location=192.168.1.199;User ID=Administrator;Password=<span color=\">*****</span>;Mode=ReadShare Deny None\"/>
<add key=\"SasLibrary\" value=\"demo\">
<add key=\"SasDataSet\" value=\"user_info\">

Here is the C# code:

private static DataTable LoadSasDataSet()
{
try
{
string library = ConfigurationManager.AppSettings["SasLibrary"] ;
string dataset = ConfigurationManager.AppSettings["SasDataSet"] ;
string conn = ConfigurationManager.AppSettings["ShareConnectionString"];

DataSet sasDs = new DataSet();
OleDbConnection sas = new OleDbConnection(conn);
sas.Open();

OleDbCommand sasCommand = sas.CreateCommand();
sasCommand.CommandType = CommandType.TableDirect;
sasCommand.CommandText = library + "." + dataset;

OleDbDataAdapter da = new OleDbDataAdapter(sasCommand);
da.Fill(sasDs);
sas.Close();

return sasDs.Tables[0];
}
catch (Exception ex)
{
Common.AddLogEntry("ERROR", "Unable to load SAS dataset", "", DateTime.Now, ex.ToString());
return null;
}
}

Keep in mind that there is also SQL capability within Share so that would be an option as well.

SAS, ASP.NET AJAX, Web Services

2007-02-13T10:01:00.000-08:00

In preparation for SAS Global Forum 2007, I have posted 2 videos showing 1) How to set up a web service to read a SAS dataset, 2) How to consume that service in an ASP.NET AJAX page. Simple demos but they should help get people started:

There are several videos demonstrating the concepts involved. They can be found here:

http://utilities.savian.net

Look under the video tab.

Code generation, macros, et al

2007-01-19T08:20:00.000-08:00

As a SAS programmer evolves, they start to think:

"Hey, I can create SAS code using data step or macros!"

So they start to go down the happy path of code generation:

data _null_;
file "myplace";
put "data test ;
/ " set a; "
/ " x = wereHavingFunFunc(y)" ;
... etc.
run;

...and pretty soon they have elaborate SAS code in macros, etc. that does nothing but generate code. If you don't use my utility SasEncase to help you do this, you are doing a LOT of extra work but that is another story.

Well, I think the next level of thought (at least for me) was:

"Hey! Why can't I just use ANY programming language to generate SAS code"

Now, think about that for a sec. ANY programming language can be used to generate SAS code.

The reason why SAS is an entry into this area is because that is what all of us know and love. But, don't confine yourself to just using SAS for code generation. Instead, pick other languages as the need arises.

public void CreateSomeSasCode()
{
StreamWriter sw = new StreamWriter(@"c:\temp\mySasCode.sas") ;
sw.WriteLine("data test;") ;
....
}

Now, that's better, a little C# to play with.

Ok, so you would have to wrap a lot of code.

I think I have evolved. Now I actually write the SAS code with macro parms and store them on a server. Then you can just call the stored process and pass the parameters using web services:

DataSet ds = SasServer.Services.ExecuteStoredProcedure("mySasCode.sas", @"%let outdata = 'c:\temp'");

Now, I think we have nirvana: SAS doing what it does best being driven by a modern OOP environment.

Dropping flyballs in left field,
Alan

Inserting records into SQL Server

2006-10-30T05:59:00.000-08:00

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;

SAS and AJAX

2006-10-03T02:13:00.000-07:00

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

Weird VS2005 Error

2006-08-31T15:29:00.000-07:00

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.

EG Tasks not displaying

2006-08-06T21:19:00.000-07:00

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

2006-08-06T20:57:00.000-07:00

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

Excel 2007 and SAS Programmers

2006-06-13T17:56:00.000-07:00

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

MHTML, AOL, C#, and the new WebBrowser

2006-04-30T22:09:00.000-07:00

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

Web Services

2006-03-02T07:38:00.000-08:00

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.

RoboHelp and IIS 6.0

2006-02-27T05:05:00.000-08:00

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

Web Services and SAS

2006-02-09T12:19:00.001-08:00

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

2006-02-09T12:19:00.000-08:00

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

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

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

Convert SAS datasets to Excel

2006-01-23T07:03:00.000-08:00

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.

SAS and Processes

2006-01-13T21:39:00.000-08:00

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

Approach to Excel and SAS

2006-01-10T03:15:00.000-08:00

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

2006-01-10T02:50:00.000-08:00

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

2006-01-10T02:29:00.000-08:00

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