SAS XPT Files, IBM Floats, and C#

Well, I recently was asked if I could support XPT files, you know, the SAS transport format. Well, SAS transport formats are documented and fairly straight-forward to read so 'why not?'.

Got started coding, client provided a nice sample, all went well. Around 8-10 hours after starting, I was happily reading SAS transport files and writing them to a tab-delimited file.

However, things weren't happy on Big Rock Candy Mountain.Seems the numbers were off. A little bit of reading in the SAS XPORT Documentation indicated the problem:

"All floating-point numbers in the file are stored using the IBM mainframe representation. If
your application is to read from or write to transport files, it will be necessary to
convert native floating-point numbers to or from the transport representation."

Not a problem. Let's go track down a C# library that handles IBM 370 floats and does the conversion. One small problem...I couldn't find one. What I did find was that this was a nasty little problem. I like to dabble in CompSci stuff up until the point of pain and we were at a severe pain threshhold. Anyone wishing to read what is involved, check the SAS link above.

After playing with lots of things for several days, I settled on using John Hou's great article on CodeProject:

Transform Between IEEE, IBM, or VAX floating point

Fortunately, I had a license for Tangible Software  C++ to C# convertor. That did a lot of lift (80-90%) and left me to do final cleanup. Once I got that all down, next up was to get the endianness correct on the parms passing in and then suddenly I saw a correct value passed back.

Long weekend dealing with it but glad it is over.

The final C# code that creates the conversion is available on John's aoriginal article as an alternative and I will post it on Savian as well. I learned more about SEF, floats, 4/8 byte variances, and everythig else than I ever wanted to know.

However, tonight I sent my client a translated XPT file and it appears to be correct. And I now have a .NET dll for handling the issue. That made all of the difference in the world...

DNN Error

I have been using DotNetNuke (DNN) A LOT recently. For building a web framework, it absolutely rocks and is very easy to use. It is also very popular (I think 800k sites and growing) so there are loads of add-ons, videos, help guides, etc. Plus, it is free. I may need to put my demos site under it and showcase SAS a bit.

Normally, it is also pretty easy to use. Recently, though, I encountered a tough issue to debug.

I encountered the following error on opening the site:

An error has occurred. An error has occurred.

As I read up on it and understood it a bit more, lots of things can cause it. Basically, DNN has a serious error and it will not start.

To help diagnose it:

1. Go to the SQL Server database and open up the EventLog. 
2. Go to the last events in the log. An error on compilation or missing files typically will generate multiple entries. Look for the cluster of errors at the bottom of the log.
3. For the first record of the error, select the LogProperties, right-click and copy. Save this to a file called errors.xml (or whatever). Right-click and open it in a browser.
4. The final part of the XML file will contain the actual exception.

This error will be masked as other things. What I have seen is the following:

System.NullReferenceException: Object reference not set to an instance of an object.

DotNetNuke.UI.Skins.Skin.GetSkin(PageBase page)

 

 

However, if I went up 4 records, I got the real error message:

DotNetNuke.Services.Exceptions.PageLoadException: Unhandled error loading page. ---> System.Web.HttpParseException: The file '/DesktopModules/DDRMenu/Menu.ascx' does not exist.


Ultimately, I had to delete a virtual directory in IIS to get it working again.

HTH someone else in the search engines.

 

  

SAS & Excel Via Local Provider

A question came up on SAS-L for how to get Excel to read SAS datasets using the Data Sources within Excel. Here are some screenshots showing how it works:

Hash a SAS Value

Sometimes, it is good to be able to hash a value so that a unique key can be made into the data. For example, say you were looking at a system performance log. You have a PID, a process name, and a user. PIDs are reused by a system all of the time so trying to narrow down uniqueness throughout a day is hard.

It order to get a unique value, you could concatenate the values into one:

000789654 || WeeklyProcess || gertre5

We are assuming that there is no need to ever reverse the values. This is a key assumption.

There is an undocumented function in SAS called CRCXX1 that can create a unqiue hash. Here is some code illustrating it:

data A;
input name :$200. gender :$8. state :$20.;
x = compress(name||gender||state);
y = CRCXX1(x);
put x= y=32. ;
datalines;
Churchill,Alan Male Colorado
Churchill,John Male Colorado
;
run;

The results:

data A;
884  data A;
885  input name :$200. gender :$8. state :$20.;
886  x = compress(name||gender||state);
887  y = CRCXX1(x);
888  put x= y=32. ;
889  datalines;

x=Churchill,AlanMaleColorado y=1558070123
x=Churchill,JohnMaleColorado y=837584169
NOTE: The data set WORK.A has 2 observations and 5 variables.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds


892  ;
893  run;

This could be very valuable for situations where you need to tighten up processing and have some throwaway field values. The person who mentioned the undocumented function says it is good to about 1 million unique values before it starts to have collisions. Above that, go with the MD5 function.

SAS and LDAP

I was just tasked to read in LDAP records so we could cross-reference userids with login identifiers and general ledger information.

Using SAS to read LDAP was a bit of a challenge. I had used C# to read LDAP before and had been successful at several engagements. However, I had never journeyed into SAS land on the LDAP front. The information on how to do it is sparse. More than that, LDAP is very, very sensitive to wrong information and the response when rnning the code is simply 'LDAP Failed'.

LDAP provides a tremendous datasource for SAS developers.

Let me show you how to work with it:

1. Download and install LDAP browser by Softerra. It is crucial to help navigate the LDAP waters.
2. Pick a simple LDAP server to start with. I will use Colorado State University. Lots of colleges have public LDAPs so they are perfect. For our example, we are going to find details on the faculty of the history dept (my undergrad was history).

Here are the specifics:

LDAP server:	directory.colostate.edu
Port:	389
DNBase:	ou=History (1776), ou=Faculty/Staff, dc=colostate, dc=edu

The SAS code is broken into 6 main pieces:

1. Connect to the server
2. Find the person we need
3. Parse the information
4. End the connection
5. Close the connection
6. Convert the XML into a dataset

Code:

filename outxml 'c:\temp\LDAP.xml';
libname outxml xml 'c:\temp\LDAP.xml';


data _null_;
  file outxml; 
  length entryname $200 Attribute $100 Value $100 filter $100;

  rc =0; handle=0;
  server="directory.colostate.edu";
  port=389;

  /* Make sure these are in order */

  base=" ou=History (1776), ou=Faculty/Staff, dc=colostate, dc=edu";  
  bindDN="";  Pw="";

  /* open connection to LDAP server */
  call ldaps_open(handle, server, port, base, bindDn, Pw, rc);
  if rc ne 0 then do;
     msg = sysmsg();
     putlog msg;
  end;
  else
     putlog "LDAPS_OPEN call successful.";


  shandle=0;
  num=0;
  filter="(objectClass=*)";
  attrs=" ";

  /* search the LDAP directory */
  call ldaps_search(handle,shandle,filter, attrs, num, rc);
  if rc ne 0 then do;
     msg = sysmsg();
     putlog msg;
  end;
  else 
     putlog "LDAPS_SEARCH call successful. Num entries: " num;

  * Start the XML;
  put     '<?xml version="1.0" encoding="windows-1252" ?>'
      /@3 '<TABLE>'
   ;


  do eIndex = 1 to num;
    numAttrs=0;
    entryname='';

    /* retrieve each entry name and number of attributes */
    call ldaps_entry(shandle, eIndex, entryname, numAttrs, rc);
    if rc ne 0 then do;
       msg = sysmsg();
       putlog msg;
    end;

    /* for each attribute, retrieve name and values */

    put @6 '<LDAP>' ;

    do aIndex = 1 to numAttrs;
      Attribute='';
      numValues=0;
      call ldaps_attrName(shandle, eIndex, aIndex, Attribute, numValues, rc);
      if rc ne 0 then 
         do;
            msg = sysmsg();
            putlog msg;
         end;

      do vIndex = 1 to numValues;
        call ldaps_attrValue(shandle, eIndex, aIndex, vIndex, value, rc);
        if rc ne 0 then
           do;
              msg = sysmsg();
              putlog msg;
           end;
        else 
     do; 
             *if vIndex > 1; *This is done to keep duplicates out of XML;
              put @9 '<' Attribute +(-1) '>' Value +(-1) '</' Attribute +(-1) '>' ;
     end;
        end;
      end;
   put @6 '</LDAP>' ;
    end;
  put @3 '</TABLE>';

  /* free search resources */
  call ldaps_free(shandle,rc);
  if rc ne 0 then 
     do;
        msg = sysmsg();
        putlog msg;
     end;
  else
     putlog "LDAPS_FREE call successful.";

  /* close connection to LDAP server */
  call ldaps_close(handle,rc);
  if rc ne 0 then 
     do;
         msg = sysmsg();
         putlog msg;
     end; 
  else
     putlog "LDAPS_CLOSE call successful.";
run;

data test;
   set outxml.LDAP;
run;

Some notes on the above code:

1. The XML libname engine is used to do a conversion from vertical format (LDAP data is vertical) to a dataset layout. Since SAS handles this automagically with XML, why not use it instead of fancy data step.

2. Anyone can run the above code. The CSU LDAP server is in the public so why not.
3. Use LDAP Broser to determine the DNBase and other pertinent information needed. The tool is free and helps get the names and order correct.

[UPDATE: For authenticated servers, you need to bind the user name. For example:

server="directory.colostate.edu";
port=389;
base=" ou=History (1776), ou=Faculty/Staff, dc=colostate, dc=edu";
bindDN="CN=achurchill,CN=Users,DC=MyDomain,DC=savian,DC=net";
Pw="your password";
]

SAS/IntrNet and IIS 7.5

First of all, I had to set up SAS/IntrNet recently. I do this at times and always struggle with security
issues in IIS. Hence, I document it below.

People may ask why use SAS/IntrNet anymore? Well, it is fast and is probably the fastest way to interact with SAS via the web. It has limitations but it implements a standard REST api that is used by loads of web companies (i.e. Twitter, Facebook, etc.). I absolutely love IntrNet due to its simplicity.

Let's make it happen:
===============================================


I posted a short while back on how to get SAS/IntrNet operational on IIS 7. Well, I had to do everything again under IIS 7.5 and eiteher things have changed slightly or I didn't get it all captured last time. So here we go again, but this time with pictures:

1. Open up IIS in Windows Server 2008 R2 and right-click on sites, Add a new site:

2. Fill in the details:

3. Pay attention to the application pool and any host header information. Host headers are nice for handling lots of different sites under a single domain name.

4. You should now have a basic site. Add in a virtual directory for the scripts directory:

5. Point it to the SAS/IntrNet scripts location (normally c:\inetpub\scripts).

6. Make sure that CGI is enabled on your IIS installation. if not, go to the server roles and enable it. if it is enabled, you should see it in the site information:

If you need to add CGI, follow these steps (from: http://www.computerperformance.co.uk/Longhorn/server_2008_uac_user_account_control.htm)

7. Go to Windows Explorer, go to the scripts directory, right-click and select properties. Go to the security tab and add in the app pool identity. This is different than previous versions of IIS. If you used DefaultAppPool as shown above, use the following id:

IIS AppPool\DefaultAppPool

[Note: Even  a single space at the end of the above name will cause it to not work.]

8. Go back to IIS, click on the site, select Handler Mappings, Add Managed Handler:

 9. CRITICAL STEP. While inside of Handler Mappings, click on request restrictions, go to the Access tab, and select Execute:

 10. Finally, go to the Authentication tab for your site and open it. Under Anonymous Authentication, select edit and change it to the pool identity:

If all of the above does not work, call or email.