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!