topic How do i use SQL data query in SAS Intelligent Decisioning to do lookup? (How to configure SAS MAS?) in Decisioning

How do i use SQL data query in SAS Intelligent Decisioning to do lookup? (How to configure SAS MAS?)

Winter — Thu, 07 May 2020 15:08:48 GMT

I'm trying to build a Decision Flow in SAS Intelligent Decisioning on SAS Viya 3.5 but i stumble on a key feature that I can't get to work.

To simplify things lets take a basic example: Input to the decision flow is a customer ID, in the flow I need to look up customer information from a table in our internal database. Using that customer information I will do a bunch of cool stuff but first I need to solve how to do the look up.

According to some examples the Data Query (SQL Code) node is the tool to use for this and i've written this simple SQL lookup that should collect the customers status_code and age from the customerInformation table for the specific ID that is inputted to the node:

Select
Status_code as {:Status_code :string:10},
Age as {:Age:decimal},
ID as {:ID:decimal}

from casuser.CustomerInformation where ID = {?:ID:decimal}

However, when I try to run the flow (or validate just the SQL code) I get the following error:

Some information that i've found on the web suggest that some settings need to be configured for the SAS Micro Analytics Service in order to run a Decision Flow containing SQL code, see Configuring Support for SQL Query Files which in turn points me att DS2 configurations that hints at a connection string for FedSQL connections to different databases and now i'm totally lost 🤣 All I want to do is to run a simple sql towards a SAS table in my viya database.

The first question is, is the SQL code the correct approach to enrich the input data in a decision flow with information from a table in the database?
If so, am I doing something wrong or do our environment need additional configuration? If so, what configurations are needed?

Re: How do i use SQL data query in SAS Intelligent Decisioning to do lookup? (How to configure SAS M

amitc2605 — Mon, 11 May 2020 06:02:25 GMT

I can see two problems with your SQL query here:

Attribute names and tables names should be in double-quotes
You cannot query CAS data directly via SQL data query node in SAS ID. Please configure your database connection in MAS configuration settings (link) and then provide a catalog name in place of casuser

See sample SQL code which should work:

Select
"Status_code" as {:Status_code :string:10},
"Age" as {:Age:decimal},
"ID" as {:ID:decimal}
from "<<catalogName>>"."CustomerInformation" where "ID" = {?:ID:decimal}

Re: How do i use SQL data query in SAS Intelligent Decisioning to do lookup? (How to configure SAS M

Winter — Mon, 25 May 2020 12:46:59 GMT

The biggest problem was the connection string and the not so obvious fact that you cant use "in memory" datasets.

For others that might be locking for a solution:
I switched over to using the PUBLIC library for it to be a bit more universal for all users. In my case the physical location of public is /opt/sas/viya/config/data/cas/default/public/ (this can be found if you look at the library in the data manager)
And my connection string is:

driver=SQL;conopts=(driver=base;catalog=public; schema=(name=public;primarypath=/opt/sas/viya/config/data/cas/default/public/))

If you want to use other (PHYSICAL) libraries you can use the following code in SAS Studio to get the relevant connection strings in the log:

option MSGLEVEL=i;
proc fedsql;
select sadasdasd from test.not_an_excisting_table /*this code generates error on purpose, we just want to see the log*/
;quit;

One of the notes in the logg should contain the relevant connection string for all physical libraries that are assigned in SAS Studio. It should look something like this:
NOTE: driver=FEDSQL;conopts=((...