Monday, 12 December 2016

Look out - Locale Gotcha

As gotcha's go, this was a good one!

We had a report being generated with dates that were inconsistent with the source file (MMDDYY instead of DDMMYY), but only for certain records.  This was definitely related to the ANYDTDTE. informat, but the strange thing was - this issue only occurred when the report was generated by the Stored Process Server (it was fine in batch).

The ANYDTDTE. informat is driven by the locale setting but as per the chain of sasv9.cfg files we had -LOCALE en_GB defined for both servers.  So I checked the logs, and found something interesting..

One of our STP applications can be triggered from either Excel or a browser.  I could see that the same user had triggered two requests, less than a minute apart, one from excel and one from a browser (as evidenced by the value of the _HTUA variable)

Yet one session had _USERLOCALE=en_US and the other had _USERLOCALE=en_GB!!

As it turns out, the locale for a Stored Process session can change according to the context of the client - as described in this usage note.

So the fix is simply to add the following code (as per your desired locale):
options locale=en_gb;
But where?

I tried fixing this via the autoexec, but this had no effect (likely because the server is running before the request arrives) so instead I added it to the stp init program - which worked a treat.


I recommend adding this to your init program, unless you really do want your outputs to change according to the locale setting of the client application!

Friday, 18 November 2016

Importing flow: ERROR - An error occurred trying to connect the responsible parties for the imported objects. Reason: Can't find resource for bundle java.util.PropertyResourceBundle, key ResponsiblePartyHandler.InvalidSearchAttrib.txt

If you are getting the following message when importing a package containing a FLOW:

ERROR - An error occurred trying to connect the responsible parties for the imported objects.  Reason: Can't find resource for bundle java.util.PropertyResourceBundle, key ResponsiblePartyHandler.InvalidSearchAttrib.txt

Then as per this usage note, and as I just verified locally, it is because the Responsibilities metadata is not imported.  So if you set your flow properties as follows:


You will see that after export / import the metadata is gone:


The metadata therefore has to be manually added back following the import.  In our case, we never use this field, and the ERROR has not caused any problems thus far (many '00s of flow deployments).  Still, if anyone has any tips for avoiding the ERROR do please advise!

SAS As A Service - an easy way to get SAS into Excel, Power BI, and '000s of other tools / languages

When we think about using SAS with Excel, one begins to groan at the myriad of options - DDE, SAS Addin, various Excel-ish library engines, IOM in VBA, etc etc etc.

What if there were a simple technique, quick to set up, and easy to adjust?

A technique that works on ANY version of Excel, requires no install, works cross platform and encompasses full metadata security (at point of connection)?

A technique that has been available to us for at least 15 years¹?

For those of us who work with SAS Web Applications, the idea of streaming data via a URL is not unusual.  It's a great approach as it ensures we can get the latest version of data, using the latest version of the code, with the permission set granted to that user at that point in time.

Extending this concept to Excel is as easy as 1,2,3:

1 - Set up the Stored Process

The code below should be registered as a stored process using SAS Management Console with STREAMing output:
/* STP URL Parameters */
%global type libds;
/**
 * assign metadata library from libref
 */
%let libref=%scan(&libds,1,.);
data _null_;
  length lib_uri LibName $200;
  if metadata_getnobj("omsobj:SASLibrary?@Libref='&libref'",1,lib_uri) then do;
    rc=metadata_getattr(lib_uri,"Name",LibName);
    call symputx('libname',libname);
  end;
run;
libname &libref meta library="&libname";
 /* set content type if CSV */
data _null_;
  if "%upcase(&type)"="CSV" then
    rc=stpsrv_header("Content-type"
      ,"Content-Type: text/csv; encoding=utf-8"
    );
run;
 /* send data */
PROC EXPORT DATA=&libds OUTFILE=_webout DBMS=&type REPLACE;
run;

2 - Set up the Workbook

The 'trick' here is simply to set up a web query to call the STP above.  The options vary as per the version of Excel, here is the path for Excel 2010:

Next step is to add the (appropriately formatted) URL, eg below.  Note that I called my STP "SAAS" and placed it in a metadata root folder called Web.
http://YOURSASMID:8080/SASStoredProcess/do?_PROGRAM=/Web/SAAS&libds=sashelp.class&type=tab
We are asking for the sashelp.class dataset, and for it to be TAB delimited.  This gets pasted into the web query box, and gives results as follows:

We now have our data in excel, but for those who wish to avoid the need to 'right click / refresh' we should:

3 - Automate

The beauty of this approach is that it is so easy to automate in VBA!


By the Power of Greyskull

Now that our STP is configured, it is gobsmackingly straightforward to get the power of SAS into Power BI.  Again, simply select Get Data and Web:

Enter an appropriately formatted URL, using type=CSV in the parameters:
http://YOURSASMID:8080/SASStoredProcess/do?_PROGRAM=/Web/SAAS&libds=sashelp.class&type=CSV
You may then need to select the appropriate credentials with which to connect (eg windows authentication for IWA, or use Basic if you normally enter passwords for SAS).

In the final screen simply mark the type of your input:


and - voila!


Of course this is a very simple example, but using the above template you can start to add more parameters on the SAS side - enabling where clauses, drop statements, business logic etc.

The same approach can be used to get data into Powershell, Python, R, uncountable client applications, and of course - javascript (see building web apps with SAS).

Got any other tips?  Please share in the comments below..

References:
¹ Vincent DelGobbo discusses web queries with SAS/IntrNet in this SUGI27 (2002) paper 
2 Stig Eide also discusses this approach in this blog post from 2013

Tuesday, 8 November 2016

Finding MIN (or even MAX) of character variables in SQL

It is perfectly valid to use min() as a summary function on character values in proc sql, eg as follows:
proc sql;
create table test as 
  select min(name) as minval
  from sashelp.class;


This is not so, however, when using in an inline context:
proc sql;
create table test as
  select min(name,sex) as minval
  from sashelp.class;

Which gives:
ERROR: Function MIN requires a numeric expression as argument 1.
ERROR: Function MIN requires a numeric expression as argument 2.

So what are the alternatives?  If we were in datastep, we might have had the option of the little-known infix operator for min (><):
data _null_;
  minval='Less'><'More'><'Lots';
  put minval=;  /* minval=Less */
run;

As it happens though, the min & max infix operators are not valid in proc sql.  Another approach, if comparing just two values, would be ifc() - eg:
proc sql;
create table test as
   select ifc(name<sex,name,sex) as minval
  from sashelp.class;


But what if we have more than two values?  In this instance, it becomes necessary to take matters into your own hands!  I've gone ahead and rolled up an FCMP function below, which could surely be improved, but does the job when comparing three character values in either data step or proc sql:

proc fcmp outlib=work.myfuncs.test;
  function minC(a $, b $, c $) $;
    length result $1000;
    result=a><b><c;   
    return (result);
  endsub;
quit;
options cmplib=work.myfuncs;
data _null_;
  x='Killing';
  y='Me';
  z='Softly';
  outvar=minC(x,y,z);
  put outvar=;
run;
proc sql;
create table test as 
  select minC('Constant',name,sex)
  from sashelp.class;


Would you deal with the requirement any differently?  Let me know in the comments!

Friday, 21 October 2016

Building SAS Apps Locally

I've recently been using Visual Studio to build my SAS Web Apps, which is great as the intellisense will even scan css files to help with code completion.  Perhaps the best feature though is the ability to right click and immediately 'view in browser' - which spins up a local web server and avoids the need to constantly modify files on the main dev SAS web server.

However - there are a few things to remember when using this feature to actually connect to SAS.

Same Origin Policy

In simple terms, this is a browser security feature that prevents javascript from connecting to a different server than that from which the page was served.  You'll know you're affected if you see something like this in the console:

XMLHttpRequest cannot load http://SASMIDTIER:8080/SASStoredProcess/do. No 'Access-Control-Allow-Origin' header is present on the requested resource. Origin 'http://localhost:54048' is therefore not allowed access.
To avoid this (using chrome) simply launch from the command line with that feature disabled - as follows:
"C:\Program Files (x86)\Google\Chrome\Application\chrome.exe" --user-data-dir="C:/Chrome dev session" --disable-web-security

hostUrl

Now that we are launching from a local host, the Boemska SAS adapter is unable to automatically determine the mid-tier location.  This info needs to be provided, but not hard coded as we still need the code to work in different environments when it gets promoted.  The following javascript code serves:

  if (location.hostname === 'localhost' || location.hostname === '127.0.0.1') {
    var strHostURL = 'http://SASMIDTIER:8080/';
  } else {
    strHostURL = null;
  }

  var adapter = new h54s({ hostUrl: strHostURL });

HTML file location

This may not be an issue with your web server / configuration, but something to remember.  I normally keep my my web files organised as follows:
  • ROOT.war
    • JS
    • CSS
    • HTML
    • Images
In order to reference scripts / css / images from my html files, I normally use the ".." syntax to tell the browser to look 'up and then down' a directory, eg as follows:

  <script src=../js/h54s.js></script>

Unfortunately that approach doesn't work in the web server spun up by VS2015, so I have to temporarily move my html file up into the parent folder and permanently change my references to read as follows:

  <script src=/js/h54s.js></script>

For more information on building Web Apps with SAS you can also check out this guide.  Enjoy!

Wednesday, 5 October 2016

The native implementation module for the security package could not be found in the path.

Noticed today that our UAT 9.3 environment was failing to execute the SAS ExportPackage utility, with the following error returned:
The export process has failed.  The native implementation module for the security package could not be found in the path.
I'd come across this issue before, and this was (effectively) the same piece of code.  So what gives?  I launched a shell session locally, using the system account (sassrvuat), and reran the command.

It worked.

Hmm..  Must be an issue with the UAT machine (most likely since our 9.3 upgrade as it worked on 9.2).  I logged into sasapp with sassrvuat and tried to open DI Studio using IWA.  I get an interesting popup:
C:\Program Files\SASHome\SASDataIntegrationStudio\4.3\sspiauth.dll: Can't load IA 32-bit .dll on a AMD 64-bit platform The native implementation module for the security package could not be found in the path.
Seems my old colleague Stig was onto something with regard to the sspiauth.dll file and 32 vs 64 bit compatibility.  Checking out the folder below, we can see several versions:
Comparing the file sizes it was clear that we had 32 bit DLLs (112kb) instead of 64 (107kb).  The solution therefore was simply to copy sspiauth.dll from:
  • C:\Program Files\SASHome\SASFoundation\9.3\core\sasext
To the following locations:
  • C:\Program Files\SASHome\SASDataIntegrationStudio\4.3
  • C:\Program Files\SASHome\SASPlatformObjectFramework\9.3 




Thursday, 22 September 2016

proc metalib - ERROR: Invalid use of sign notation

So today I dropped a column in a SQL Server table (using SSMS) and duly checked out the SAS metadata in DI Studio in order to 'Update Metadata'.

It failed with a strange message:

ERROR: Invalid use of sign notation.

Curious, I extracted the code and ran it in Enterprise Guide (the repository ID would ensure it ran against my checkout).  Same result, so following the advice in usage note 40429 I ran with maximum logging (for 9.3) as follows:

options sastrace=',,,d' sastraceloc=saslog nostsuffix;
proc metalib tl=4095; /* max is 4094 for 9.2 */
  omr (libid="B900004R" repid="A5HOSDWY");
  report(type = summary);
  update_rule = (noadd);
  select (A5GF9HGW.AD00016K);
run;

The last operation before my SAS error was:

(MSCMLB) getREPindices: Enter
(MSCMLB) getREPindices: Index object name is pk_rm_objects
(MSCMLB) getREPindices: Index metaid is A5GF9HGW.AI00015K
(MSCMLB) getREPindices: IndexName is pk_rm_objects
(MSCMLB) getREPindices: Exit;  Return Code is -2147475442

This indicated an issue with the index.  I tried various ways of redefining the index in SQL Server (from clustered to non-clustered, uppercase name to lowercase etc) to no avail.  In the end, the solution was simply to right click the metadata item in DI Studio, Properties, selecting the Index tab.  At this point I got a popup (similar to below) which mentioned the index needing to be marked as unique, and also about re-ordering columns.  I clicked yes, and - voila - I was then able to update the metadata!


Tuesday, 20 September 2016

SAS/Share ERROR: Invalid object specifi...

Had an interesting error message today (SAS 9.3, Windows 2012R2), which appeared to be truncated:

ERROR: Attempt to connect to server "dev01.domain.com".__8551 rejected by server.
ERROR: Invalid object specifi...
ERROR: Error in the LIBNAME statement.

The issue occurred when trying to connect to a SAS/Share library using the following syntax:

LIBNAME MYLIBREF REMOTE  HOSTNAME="dev01.domain.com"  Server=__8551 slibref=MYLIBREF;

The fix was simple - simply log into the application server, open Services, and restart the "SAS Share Server" service.
This left the customer happy, but I'm still curious about what caused the elipsis (...)

Any ideas

Thursday, 15 September 2016

Adding SAS Configuration Manager plugin to SMC

The SAS Configuration Manager plug-in is great for tweaking settings of the SAS Web Applications without poking around in xml and config files.  However, you may find it missing from the usual place under the Application Management folder in SAS Management Console.

If this is the case, first of all check that you are in the administrators group with an unrestricted user (or otherwise have the correct role / capability to view the plugin).
If it is not there, the chances are that it is not installed.  To install it, you need access to the SAS Software Depot - simply run the Deployment Wizard, selecting either:
  • SAS Web Infrastructure Client (9.2)
  • SAS Configuration Manager (9.3)
And then..   ka pow!

Useful resources (9.2):
 - SAS Usage Note 41009
 - Angela Hall blog

Useful resources (9.3)
 - SAS documentation
 - Paul Homes blog

Thursday, 8 September 2016

Stripping Non-ASCII Characters within Macro

Hit once more with a pesky en-dash issue (likely related to the transcoding between SAS & SQL Server) I discovered today there was no 'in-built' way to remove non-ascii (or extended-ascii) characters within SAS.

There is a great SUGI paper about this topic (here) but the approach required the use of a data step.  Let me save you some fiddling around if you need this as a macro capability, with the extract below.

  %macro ascii();
    %local i asciichars;
    /* adjust here to include any additional chars */
    %do i=32 %to 126;
      %let asciichars=&asciichars%qsysfunc(byte(&i));
    %end;
    %str(&asciichars)
  %mend;

  /* store in macvar for efficiency */
  %let ascii_chars=%ascii();
  %put &=ascii_chars;

  /**
   * Example usage within macro language
   */
  %put %sysfunc(compress(my – endash,&ascii_chars,k ));

  /**
   * Example usage within data step
   */
  data _null_;
    str="goodbye •–—˜™š›œžŸ ¡¢£¤¥¦§¨©ª«¬­®¯°±²³´µ¶· nasties";
    asciichars=symget('ascii_chars');
    out=compress(str,asciichars,'k');
    put out=;
  run;

The main gotchas were as follows:

  • The characters in byte(3,4,5,12,13) do funny things in macro (open code recursion etc)
  • It is not advisable to reference rank() above 127 as this extended set can vary country to country (the byte # may not be the same as the rank #)
  • The 32-126 range includes apostrophe and single quote, and thus they need to be handled appropriately!

Tuesday, 6 September 2016

Reading UTF-8 in Filename Pipe

Having been blessed with an en-dash (alt + 2013) in our TFS project root, I've been having lots of fun getting SAS to 'play nicely' with non-ascii characters when running OS commands (eg tf.exe).

Storing the values in macro variables works fine for direct use (eg in an X command), but running a SAS generated .bat file was proving challenging - until I realised that the issue was actually within the windows shell, rather than SAS itself.

The solution turned out to be twofold.  Firstly, the batch file itself needed to be UTF-8 encoded WITHOUT the BOM marker.  This required the nobomfile  system option to be in place before creating the file.

Secondly, the shell needs to be configured to read UTF-8 characters.  This can be done by changing the codepage (using  chcp 65001) and chaining with an ampersand.

Sample code as follows:
options nobomfile;
%let work=%sysfunc(pathname(work));
%let file=&work/mybatchfile.bat;
         data _null_;
      file "&file" encoding='utf-8';
      put "dir ""&work"" ";
      put 'echo Nonstandard – Dash';
    run;
    filename mypipe pipe "chcp 65001 & ""&file"" ";
    data _null_;infile mypipe;input;list;run;


Wednesday, 31 August 2016

Invoking Stored Processes with Powershell - the easy way

There are a number of ways to run SAS from Powershell:
  1. Using SAS Integration Technologies (Chris Hemedinger has some great resources on this)
  2. Directly by calling sas.exe (presuming you are running on a box with a local install)
  3. Indirectly via the SAS Stored Process Web Application.
Let me explain the awesomeness of option 3:
  • It deals with authentication (no need for embedded passwords)
  • Can perform tasks using system account (sassrv), as SAS code is secured server-side
  • Resource management (no need to tidy up any redundant sessions)
  • Can be done in just ONE LINE of Powershell code!
For the sake of mobile readers, I'm going to split the example over three lines:
$uri="http://YOURSERVER.YOURDOMAIN:8080/SASStoredProcess/do"
$pgm="/Products/SAS Intelligence Platform/Samples/Sample: Hello World"
invoke-webrequest -Uri "$($uri)?_program=$pgm" -UseDefaultCredentials
Which gives the response:


The key part to this request, to avoid HTTP Status 401 and "This request requires HTTP authentication" response, is the -UseDefaultCredentials parameter.  This uses the account that Powershell is running in, to connect to the server.

Some prerequisites:
  • You need to configure your SAS code to run inside a Stored Process using SAS Management Console - set for streaming output 
  • You may need to set your execution policy (explained here)
  • The account being used to run the script must have a SAS identity, and the web server must be configured for single signon.


Tuesday, 9 August 2016

Chaining Windows Commands in SAS Filename Pipe (&)

Using filename pipe (instead of the X command, or call execute) is a great way to submit OS commands, not least because the standard output can be read directly into SAS.

But how does one submit multiple commands in the same filename (or file) statement?

Easy - & I'll show you how:

  data _null_;
    infile "D: & cd ""%sysfunc(pathname(work))"" & dir" pipe;
    input;
    list;
  run;

The above snippet opens a shell (via the pipe fileref), changes the working directory to the D drive, then to the work folder (our work location is on a different drive), then executed the dir command to demonstrate that we are indeed in the work folder.

The delimiter is, of course, the ampersand (&).

Friday, 29 July 2016

Visual Studio Build Controller does not contain an enabled build agent with name * and no tags.

Currently working on putting our SAS deployment process into TFS and Release Management - part of which involves creating a 'build' in Visual Studio.

The below caused a build failure, and didn't come up in google:

Exception Message: The build controller Visual Studio Controller - internal-svr-name does not contain an enabled build agent with name * and no tags. (type SoapException)SoapException Details: <soap:Detail xmlns:soap="http://www.w3.org/2003/05/soap-envelope" />

The fix was very straightforward (presuming you are using a Default Template).  In Visual Studio, simply open your build definition, in the 'Process' section, under '5.Advanced' expand 'Agent Settings' and set your Tag comparison operator to 'MatchAtLeast' (instead of MatchExactly):


Wednesday, 22 June 2016

Extracting Active Directory accounts into SAS using Powershell

Having a list of domain users and associated properties can be useful for a number of reasons:

  • Identifying joiners / leavers
  • Getting a list of employees
  • Converting usernames into email addresses
  • Alerting administrators when accounts are locked out
Below is an approach for obtaining those users, producing a SAS dataset that could serve in a DI job for staging a database table.
/* set up command via pipe fileref */
options noquotelenmax;
filename process pipe
  "powershell ""Get-ADUser -Filter {enabled -eq $True} -properties Title,LockedOut,Created,Department,EmailAddress
  |Select-Object SamAccountName,Name,GivenName,Surname,UserPrincipalName,DistinguishedName,Title,LockedOut,created,Department,EmailAddress
  |Export-CSV  '%sysfunc(pathname(work))\ad_users.csv' "" ";

/* execute command and capture any output from the shell */
data _null_;
  infile process;
  input;
  list;
run;

/* load CSV into SAS dataset */
data AD_USERS;
  infile "%sysfunc(pathname(work))\ad_users.csv" dsd
    firstobs=3 lrecl=10000;
  input SamAccountName:$100. name:$100. GivenName:$50.
    SurName:$50. UserPrincipalName:$200.
    DistinguishedName:$500. Title:$100. LockedOut:$5.
    created:$25. Department:$50. EmailAddress:$100.;
  if index(DistinguishedName,'OU=Employees') then EMPLOYEE_FLG=1;
  else EMPLOYEE_FLG=0;
run;

This will extract all ACTIVE accounts (enabled eq $True) with some selected additional properties (more here).  Some conditional logic is applied to distinguish employees from service accounts.

Prerequisites:
  • X Command enabled (see here for instructions, is similar process for workspace servers)
  • Powershell installed
Enjoy!


Wednesday, 4 May 2016

Converting SAS Date to Javascript Date (in Javascript)

This isn't hard to figure out, but is one of those things that just should be easy to google for!

  var dtSAS=20563;
  var dtJS=new Date(+new Date(1960,0,1) + dtSAS * 86400000);
  alert(dtJS.toUTCString());

Right, that should get rid of 95% of readers, for the rest of you, a quick explanation.

First, it's important to note that SAS dates are stored as number of days since Jan 1st, 1960 - whereas Javascript "dates" are actually the number of milliseconds since Jan 1st 1970.  This explains the 86400000 value (24 days * 60 minutes * 60 seconds * 1000 milliseconds), and why the embedded Date() object is set to 1960.

Next, there's the "+" sign before that nested Date object.  That tells Javascript to return a numeric value (-315619200000) instead of a formatted value (Fri Jan 01 1960 00:00:00 GMT+0000 (GMT Standard Time)).

Finally, the .toUTCString() method avoids contention with local time conventions (eg the extra 1 hour in BST).

To make this even easier to deal with, I've knocked together a simple Javascript function - see fiddle.

// valid in ECMASCRIPT 2016
function dtSAStoJS(dtSAS,dtType='DATE'){
  // accepts SAS unformatted DATE or DATETIME
  // dtType should be used to determine the above
  // -315619200000 is equivalent to +new Date(1960,0,1)
  // 86400000 is equivalent to 24h * 60m * 60s * 1000ms
  if(dtType==='DATE'){
    return new Date(-315619200000 + dtSAS * 86400000);
  } else if (dtType==='DATETIME'){
    return new Date(-315619200000 + dtSAS * 1000);
  } else {
    console.log('Unknown dtType value - ' + dtType);
    return null;
  }
};
alert(dtSAStoJS(20578,'DATE').toUTCString());
alert(dtSAStoJS(1776743820,'DATETIME').toUTCString());

And in case you were wondering - datetime 1776743820 (GMT) was when Dr Goodnight officially announced SAS Viya!