SAS-X Blog Aggregator

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

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

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

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

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

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

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.