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;