RTDM: Using Character List output as input parameter to read selected records from a table

[bhanucharan]

Hi Team,

I am trying to read the selected products from a input table that has 1 million unique products and I want to pass the other node character list output to use as input to data process(input table).

Input table

Prodid, Prodname

1, abc

2, def

3,xyz

......

Character list output: [1,3]

I tried to create the data process, criteria variables data type populated by default as per the input table column data type (character in our example). so it expects single input parameter as input.

Is it possible to specify the character list as a type for criteria variables to read multiple values or Is there any way in RTDM to pull selected records from input table by passing list values as input ?

Thanks

Bhanu Charan

[JamesAnderson]

Hi Bhanu,
The Data Process does not support mapping List type variables as an input to a criteria variable. To achieve what you want you would need to create a SAS process using DS2 code (or using Groovy or Jython), to accept a List and then translate that into a IN clause in the SQL query.
Your first query that retrieves the initial list is OK, then downstream from it add a Process Node.
An example of the type of DS2 code you would need is below. Note the FedServer only supports 16 values in an IN Clause. This code essentially loops through the String List, and builds the IN Clause with the values from the list. It returns a flag to indicate if the customer has one or more of the input products, you can modify this for your needs.
HTH,
James
package CheckCustomerProducts / overwrite=yes;

/** This package will support up to 16 values for the IN CLAUSE. **/
/** FedServer throws a SQL prepare error if more than 16 parameters are passed in the IN clause **/
/** If the products list contains > 16 items, only the first 16 are used. The remainder will be ignored **/

dcl package tap_logger m_logger();
dcl package sqlstmt m_sqlstatement('select CustomerID, prod_nm from {Catalog}.{Schema}.{SomeTable} where CustomerID = ? AND prod_nm IN (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)') ;

method execute( double CustomerID,
package tap_string_array products,
in_out Integer HasProducts);

dcl int i size rc ;

dcl int param_num ;
dcl int m_maxBind;
dcl double startDTTM endDTTM elapsedTime;

m_maxBind = 16 ;

if (m_logger.isDebugEnabled()) then
do;
startDTTM=datetime() ;
m_logger.debug(catt('CheckCustomerProducts: Entered execute method for CustomerID: ',CustomerID));
end ;

size = products.size();


if size > 0 then
do;
if (m_logger.isDebugEnabled()) then
do ;
if (size > m_maxBind) then
m_logger.debug('CheckCustomerProducts: Product list has '||size||' items. Only the first '||m_maxBind||' values will be used.');
else
m_logger.debug('CheckCustomerProducts: Product list has '||size||' items.');
end ;

m_sqlstatement.setdouble(1,CustomerID);

param_num = 1 ;

/** Set parameters from list up to the value of m_maxBind or size of list **/
do while (param_num <= size and param_num <= m_maxBind) ;
m_sqlstatement.setchar(1+param_num, products.get(param_num));
param_num = param_num + 1 ;
end ;

/** If size is smaller than m_maxBind, fill remaining parameters with last value in list **/
do while (param_num <= m_maxBind) ;
m_sqlstatement.setchar(1+param_num, products.get(size));
param_num = param_num + 1 ;
end ;

m_sqlstatement.execute();
rc = m_sqlstatement.fetch();

if rc = 0 then
do;
/* successful fetch therefore has products*/
HasProducts = 1;

if (m_logger.isDebugEnabled()) then
m_logger.debug('CheckCustomerProducts: FETCH returned records therefore HasProducts = '||HasProducts);
end;
else if rc = 1 then
do;
/* ERROR unsuccessful fetch therefore has NO products*/
HasProducts = 0;

if (m_logger.isDebugEnabled()) then
m_logger.debug('CheckCustomerProducts: FETCH returned ERROR therefore HasProducts = '||HasProducts);
end;
else if rc = 2 then
do;
/* NODATA therefore has NO products*/
HasProducts = 0;

if (m_logger.isDebugEnabled()) then
m_logger.debug('CheckCustomerProducts: FETCH returned NODATA therefore HasProducts = '||HasProducts);
end;
end;
else
do;
HasProducts=0;

if (m_logger.isDebugEnabled()) then
do ;
endDTTM = datetime() ;
elapsedTime=endDTTM - startDTTM ;
m_logger.debug('CheckCustomerProducts: No Products to search for provided. Exiting DS2 process');
end ;
end;

if (m_logger.isDebugEnabled()) then
do ;
endDTTM = datetime() ;
elapsedTime=endDTTM - startDTTM ;
m_logger.debug('CheckCustomerProducts: Execution time for CustomerID '||CustomerID|| ' is '||left(put(elapsedTime*1000, COMMA6.2))||'ms.');
end ;
end; /** execute method **/

endpackage;
run;

[bhanucharan]

Hi James,

In our data Some of the products have more than 16 values. I have tried Jython to read cdm data(Postgres). Below is the sample code to fetch the data using sample sql into RTDM using process node.  

import os
print(os.system("hostname"))

import psycopg2 
productlist =[]
data = []
pg_conn = psycopg2.connect(database="postgres", user="xxxxxx", password="xxxxxxxxx", host="postgresdb", port=5432)
pg_cur = pg_conn.cursor()
pg_cur.execute("SELECT productid FROM cdm.table limit 10")
data = pg_cur.fetchall()

Above code is working fine when I submit the same in Mid tier server (cli). When I execute the campaign I am getting the below error

ImportError: No module named _psycopg

Added the _pyscopg package in jython jar file as well and tried the below code to install _psycopg package.

import os
print(os.system("hostname"))
import subprocess
import sys

def install(package):
    subprocess.call([sys.executable, "-m", "pip", "install", psycopg2])
print("Done Install") /* code is executing till this point*/
import psycopg2 /* throwing error */
print("Imported Package Install")

Couldn't find a detailed logs in sasserver6_1,sasserver7_1. It is writing print statements in catalina logs.

Please suggest ?

Thanks

Bhanu

[JamesAnderson]

HI Bhanu,
Im not familiar with Python, however its important to note that for RTDM we are using Jython. To extend this with pure-Python packages you can append to the Jython module search paths with the following -D option (for SASServer7): -Dcom.sas.analytics.crm.rtdm.pythonPaths={Paths to your python modules and code}
Regards
James