Wednesday, 30 December 2015

Building Web Apps with SAS

Surprisingly little attention is given to the (huge) potential of the SAS platform for delivering web apps.  On the assumption this is due to sheer ignorance, the aim of this post is to share some of my experience, and give budding SAS web-developers a kick start in the right direction.

But first, what kind of things can you build?  Well, anything really, by combining HTML / CSS / Javascript and using SAS as a server side language (via the Stored Process Server) you have an immensely powerful / scalable application development capability.

For instance, to reference some examples of stuff I've done over the years (all browser based):
  • Drilldown Reports - Click a number, see the numbers & formula used to create that number, all the way back to source system.
  • Build & Test harness - Allowed selection of a development branch from remote GIT repo, checked out all macros / metadata, imported / deployed the jobs, ran entire solution plus tests, scanned logs for warnings / errors, worked for multiple users.
  • Release Management System - To manage the entire workflow from creating a DEV package through peer review, checking in / out of SVN, interfacing via APIs to internal and external systems, moving promotion artefacts between SAS environments, reporting etc.
  • Data Editor - A generic browser based tool for making auditable changes to data, moving the data through an approvals process (showing exact changes to base) with signoff resulting in immediate loading to the target table.
Plus a number of smaller, client specific tools.  Am sure you can think of lots of use cases where a small web app would be helpful to your customers.

The following is a quick start guide to get you talking to SAS from your favourite browser (which for me is Firefox, it has the best debugging capabilities).

Step 1 - Create a folder structure

This should go in the root of the web server, eg ROOT.war in JBOSS ( eg Jboss\jboss-as\server\SASServer1\deploy\jboss-web.deployer\ROOT.war) on the mid-tier machine, or equivalent for a different web server.

You may choose to organise .html, .js, .css  files and images etc into separate folders or distinguish by project..  But either way the number of these files will grow so be sure to have a plan to keep things organised.  At this point you may wish to ask your admin to create a network share on this directory, so you can push files without having to log into the server.  

Step 2 - Create your HTML file (example.html)

Example contents below:

<!DOCTYPE html>
<html lang="en">
  <head>
    <meta http-equiv="X-UA-Compatible" content="IE=edge;" charset="utf-8"/>
    <title>SAS Web App#1</title>
    <script src=../js/jquery.js></script>
    <script src=../js/h54s.js></script>     
    <script src=../js/example.js></script>
  </head>
  <body>
    <h1> Click submit to see a listing of SAS groups </h1>
    <ul id=SASgroups></ul>
    <button id=sasSubmit>Submit</button>
  </body>
</html>

Some things to notice regarding this markup:
  • <!DOCTYPE html> means this is a HTML5 document
  • We are referencing a jQuery library (highly recommended).  The source code is here.
  • We are referencing the Boemska data connector (highly recommended).  The source code is here.
  • We have a dedicated .js file to manage our new app (example.js)
  • The document is fairly loosely typed, for brevity (unquoted ids for instance).  This is all legal in HTML5.
  • Syntax highlighting from http://qbnz.com/highlighter/demo.php

Step 3 - Create your Javascript file (example.js)

Example contents below:

$(document).ready(function(){
  $("#sasSubmit").on('click',function(){ youClicked() });
});

function youClicked(){
  $('#SASgroups').empty(); /* empty list before repopulating */
  var adapter = new h54s(); /* only need one instance */
  var myParams = {}; /* create empty object */
  myParams.VARNAME='Variable Value'; /* create a variable & value */
  var jsTablesObject = new h54s.Tables([myParams],'SASControlTable'); /* make a H54s dataset */
  adapter.call('/Webapp/example',jsTablesObject,function(err,res) {
    /* we just submitted an STP request, now deal with response */
    $.each(res.fromSAS, function(i, item) {
      /* cycle through each attribute and add to html list */
      $('#SASgroups').append('<li>' + item.GROUPNAME + '</li>');
    }); 
  });
};

Step 4 - Create your SAS Stored Process (example.sas)

Two parts - first create the .sas file (example contents below, save in the usual STP location), then register as an STP in Management Console (be sure it has STREAMING output).

/* get Boemska data connector macros */
%inc "SASEnvironment\SASCode\Programs\h54short.sas";

/* load parameters (not actually used, just for demo) */
%hfsGetDataset(SASControlTable, work.controlparameters);

data groups; /* grab all metadata groups */
  attrib uriGrp uriMem GroupId GroupName Group_or_Role  length=$64;
  attrib GroupDesc          length=$256;
  attrib rcGrp rcMem rc i j length=3;
  call missing (of _all_);
  drop uriGrp uriMem rcGrp rcMem rc i j Group_or_Role;
  i=1
  rcGrp=metadata_getnobj("omsobj:IdentityGroup?@id contains '.'",i,uriGrp)
  do while (rcGrp>0);
    call missing (rcMem,uriMem,GroupId,GroupName,Group_or_Role);
    rc = metadata_getattr(uriGrp,"Id",GroupId);
    rc = metadata_getattr(uriGrp,"Name",GroupName);
    rc = metadata_getattr(uriGrp,"PublicType",Group_or_Role);
    rc = metadata_getattr(uriGrp,"Desc",GroupDesc);
    if Group_or_Role = 'UserGroup' then output;
    i+1;
    rcGrp=metadata_getnobj("omsobj:IdentityGroup?@id contains '.'",i,uriGrp)
  end;
run;

/* send data back */
%hfsHeader; /* sets up the json */
  %hfsOutDataset(fromSAS,WORK, groups)/* contains our desired data */
  %hfsOutDataset(justDemo,WORK, controlparameters)/* demo sending more data */
%hfsFooter;


The main thing to note about the above is that we are using the (free!) Boemska data connector (source code).  This makes it sooo easy to send / receive datasets from the browser, as well as being incredibly efficient (will send as many records as will fit in a 32k macro variable, then further macro variables as necessary).  I do rate this piece of kit and will be doing a separate blog on it at some point.

Step 5 - upload all your files

Suggested locations:
  1. example.html ->  ROOT.war/web  (midtier)
  2. example.js -> ROOT.war/js  (midtier)
  3. jquery.js -> ROOT.war/js  (midtier)
  4. h54s.js -> ROOT.war/js  (midtier)
  5. example.sas -> SASEnvironment\SASCode\Stored_Processes  (sasapp)
  6. h54short.sas -> SASEnvironment\SASCode\Programs   (sasapp)
For the STP, I'd recommend creating a new sas metadata folder (eg /Webapp/) and putting all your web apps in that (for fine grained permissions control).

Step 6 - Open the app!

Example URL;  http://dev-sasmidtier.yourCompany.int:8080/web/example.html


And that's it!  If you've built web apps with SAS before, it's likely this approach will be unfamiliar.  I mean, where are the put statements?!  And why so many files?

Believe me, it may seem like a lot of configuration initially (although it isn't), but the benefits of this approach are HUGE.  
  • Having separate CSS files (not in the demo) mean that styles can be quickly tweaked
  • Having dedicated HTML files means that layouts can be easily be reconfigured 
  • Using the Boemska connector ensures that the application logic can be built with jquery / javascript, whilst the actual data management part can be cleanly separated and managed with SAS.  Apart from tidy code, this also means that web developers and SAS developers can work alongside each other - without having to know each other's craft.  
  • Serving html files direct from the web server means that functionality can be provided even when the SAS server is down, or slow
Hopefully the above makes sense and is enough to get you started, if not then just let me know via the comments below..



Friday, 18 December 2015

Running EG or other client tools as SASSRV

Occasionally when debugging it can be useful to run as a system account for checking permissions etc.

A useful utility for this (in windows) is the "runas" command.  For instance (from the command window):

runas /user:"YourDomain\sassrv" "C:\Program Files\SAS\EnterpriseGuide\4.3\SEGuide.exe"

or

runas /user:"YourDomain\sassrv" "C:\Program Files (x86)\Microsoft SQL Server\120\Tools\Binn\ManagementStudio\Ssms.exe"

You will be prompted for the password before the client tool opens on your machine.

This post was inspired by the fact that one should not log into the application server as sassrv, to avoid conflicting / hung sessions in the STP server, but I can't find the SAS note to back this up!  However those hung sessions were confirmed real..  Don't log into sasapp as sassrv.

Tuesday, 13 October 2015

DIRECT_EXE warning

This one had me puzzled for a bit (SAS 9.2, Windows 2008, SQLNCLI10.1, OLEDB):

WARNING: The current setting of the DIRECT_EXE libname option will not allow this SQL statement to be passed directly to the DBMS for processing.

Explicitly setting the DIRECT_EXE option to DELETE had no effect.  Assigning the library in a new session, and performing the update also worked fine.  Thankfully the SYSCC variable was not affected, so jobs / processes continued to run, but still...

Anyways, it turned out this was actually caused by the SASTRACE option!  Turning this OFF removed the warning.

Friday, 4 September 2015

Event Logging of Stored Process Server Sessions

Having seemingly brought down our 9.2 Windows 2008 STP server (only in DEV!!) during development of a yet another (awesome) STP web app, it occurred to me that more information was needed about just how many requests were being batted over.


Error Stored Process Error

Unable to execute stored process.

The server is not running (paused/deferred stop mode?).



So, inspired by Quentin McMullen's excellent post, I wanted to share my approach.

Step 1 was to create a table to log the events.  This was structured as follows:

     CREATE TABLE [dbo].[STP_LOGGER](
          [PROCESSED_DTTM] [datetime2](3) NOT NULL,
         [STATUS_CD] [char](8) NOT NULL,
         [_PROGRAM] [char] (500) NOT NULL,
          [_METAPERSON] [char] (100) NOT NULL,
          [SYSJOBID] [char] (12) NOT NULL,
          [_SESSIONID] [char] (50) NULL,
          [GLOB_VARS] [char] (2000) NULL

Note that this table was not a SAS table (which could be subject to locking).  If you have no other choice but to use a SAS table, please refer to the previous post for attempting SAS locks (noting that this can not be done via the meta engine).


Step 2 was to create a macro to update the table (to be placed somewhere in SASAUTOS).  Example below:

%macro stp_logger(status_cd= );
  %local global_vars;
  proc sql noprint;
  select cats(name,'=',value)
    into: global_vars
    separated by '|'
    from  dictionary.macros
    where scope = 'GLOBAL'
      and substr(name,1,3) not in('SYS', 'SQL','SAS')
      and substr(name,1,1) ne '_';

  %assign_lib(libref=web);
  proc datasets library=work; delete append; run;
  data append /view=append;
    if 0 then set web.stp_logger;
    PROCESSED_DTTM=%sysfunc(datetime());
    STATUS_CD="&status_cd";
    _PROGRAM="&_program";
    _METAPERSON="&_metaperson";
    SYSJOBID="&sysjobid";
  %if not %symexist(_SESSIONID) %then %do;
    /* session id is stored in the replay variable but needs to be extracted */
    _replay=symget('_replay');
    _replay=subpad(_replay,index(_replay,'_sessionid=')+11,length(_replay));
    index=index(_replay,'&')-1;
    if index=-1 then index=length(_replay);
    _replay=substr(_replay,1,index);
    _SESSIONID=_replay;
    drop _replay index;
  %end;
  %else %do;
    /* explicitly created sessions are automatically available */
    _SESSIONID=symget('_SESSIONID');
  %end;
    GLOB_VARS=symget('global_vars');
    output;
    stop;
  run;

  proc append base=web.stp_logger data=append;run;
  proc sql; drop view append;

%mend stp_logger;

Step 3 was to call the macro.   The quick / dirty way would be to simply include it in the SAS programs directly, but that quickly becomes unmanageable when dealing with a large number of STPs.  Another option is to include in the autoexec, but that will only deal with program ENTRY (not exit).
The best approach, as endorsed by Don Henderson, is to use the STP init and term programs.  These are straightforward .sas programs that run on an STP session's entry and exit.  Configuration is simple, and explained here:  http://support.sas.com/kb/39/250.html.  This approach is fantastic as it will ensure that ALL calls to the STP server are logged (eg via EG, AMO, WRS, every custom web app, etc etc).   Simply add the following one-liners to each file:

%stp_logger(status_cd=SrvEnter)  /* goes in the init file */
%stp_logger(status_cd=SrvExit)   /* goes in the term file */

Boom, we are done, and here is an extract of the new logging table:



See that highlighted row?  A hung session with my name against it...




EDIT 13OCT2015: The connection issue was finally resolved by setting the Recycle Activation Limit to 1.  This had no noticeable effect on performance.



Tuesday, 17 March 2015

Get Physical Path From Metadata Libref

So it turns out that it is not possible to place a SAS lock on a table referenced via the Metadata Libname Engine:

WARNING: LOCK is not supported in MLE.

To circumvent this issue, one approach is to assign a libref directly. The following macro takes a libref (placed in the lib= keyword parameter) and queries the metadata for a physical path (placed in macro variable specified in the outvar= keyword parameter).

Note that not all librefs have an underlying directory (eg database tables).

%macro get_path_from_metalibref(lib=,outvar=filepath);
   data _null_;
      putlog "NOTE: Getting physical path for &lib library";
      length lib_uri up_uri filepath $256;
      call missing (of _all_);
      /* get URI for the particular library */
      rc1=metadata_getnobj("omsobj:SASLibrary?@Libref ='&lib'",1,lib_uri);
      put rc1= lib_uri= ;
      /* get first object of the UsingPackages association (assumed to be Path) */
      rc2=metadata_getnasn(lib_uri,'UsingPackages',1,up_uri);
      put rc2= up_uri= ;
      /* get the DirectoryName attribute of the previous object */
      rc3=metadata_getattr(up_uri,'DirectoryName',filepath);
      put rc3= filepath=;
      call symputx("&outvar",filepath,'g');
   run;
%mend;

Friday, 23 January 2015

Use OR condition in metadata OMSOBJ query

Make your metadata queries more versatile by checking for multiple conditions in a single pass!  When using the OR condition, remember to prefix subequent attributes with the @ symbol.  See below for testing whether a Stored Process user is in a certain set of groups:

%let _MetaPerson=John Smith;

data _null_;
   length obj type $256 id $20;
   obj="omsobj:IdentityGroup?IdentityGroup["!!
      "@Name='SASAdministrators' or @Name='ETL Developers']"!!
      "[MemberIdentities/Person[@Name='"||"&_METAPERSON"||"']]";
   rc=metadata_resolve(obj,type,id);
   if (rc) then Call symputx('Isamemberc','Yes');
   else Call symputx('Isamemberc','No');
run;

%put "Is &_MetaPerson in groups ETL developer or SAS Administrator??";
%put "&Isamemberc";

Friday, 9 January 2015

Platform Computing - LSF documentation

In building a script to read in the LSF log files (lsb.events files), it was apparent that the file structure has changed across LSF versions.  What is not so apparent is where the documentation resides..  So here's  a quick ref guide.

First though, you need to know your LSF version (different to the Process Manager version!).  Simply open a shell / command prompt on the server machine and type "lsid".  For Process Manager version, it would be "jid" (then log on).

Once you have the LSF version (lsid), see below:
Remember to take a copy of the lsb.events file before reading, as the file will normally be locked..  (and the lsb.events.n file may be about to change)