Wufoo and REST API

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

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

UPDATED: 11/8/2017

[NOTE: I have also created a direct reader for the XPT binary format. That took a long time (a week, maybe). Contact me if you need that]

Complete code:

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using SAS.UV.Transport;

namespace ReadSasXportFiles
{
    class Program
    {

        static void Main(string[] args)
        {
            var tf = new TransportFile(@"x:\temp\shoes.xpt");
            var x = tf.Datasets;

         }

     }

}

IMPORTANT NOTE: Make sure your build is set for x64 and not Any CPU. You will see a warning:

Warning There was a mismatch between the processor architecture of the project being built "MSIL" and the processor architecture of the reference "SAS.UV.Transport", "AMD64".

RegexBuddy and Automated Emails

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

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