RTDM: Reading two data grids and apply filter

[bhanucharan]

Hello Everyone,

I am a new user to RTDM and trying to apply a filter using two below data grids (A,B), Unfortunately calculated variables has only Tableinnerjoin function available to read two data grids and generates the common records as output data grid.  

DATA GRID A: P1 , P2, P3, P4, P5  

DATA GRID B: P3, P5

Required Output DATA GRID A -B :  P1,P2,P4

I couldn't find any ds2 code that reads the data grid as input and creates the output as data grid. I read that tap_table method in ds2 will read the data grid. It would be helpful if you can provide a sample ds2 code snippet to solve my problem.

Appreciate your help!!

Thanks 

RTDM User

[JamesAnderson · Accepted solution]

HI bhanucharan,
I don't believe there is a nice function to do this, so you will need to iterate through one datagrid, and then in a sub-loop iterate through the second, comparing the values of P3 and P5 as you go, and then conditionally output the record to a new datagrid when they are not matched. Below is some incomplete sample code to show how to instantiate the data grids for inputs and outputs, including creating the new columns for output, You will need to use the get methods of the tap_table package (for example getString or getFloat) to retrieve the values from the data grids as you iterate through them, and then check for equality to decide if you have a match, then use the set methods to write to row and column into the output.
HTH,
James
package MyDS2Package / overwrite=yes;
dcl package tap_logger m_logger();
dcl varchar(10) tmp_A_P3 tmp_A_P5 tmp_B_P3 tmp_B_P5;
dcl int rowA rowB ;
method execute(
package tap_table MyInputDataGrid_A MyInputDataGrid_B,
in_out package tap_table MyOutputDataGrid
);
myPackageName = 'MyDS2Package: ' ;

if (m_logger.isDebugEnabled()) then do ;
m_logger.debug(myPackageName|| 'Entered execute method');
end ;

m_logger.debug(myPackageName|| 'Adding columns.');

MyOutputDataGrid.add_column('P1', 'string');
MyOutputDataGrid.add_column('P2', 'string');
MyOutputDataGrid.add_column('P4', 'string');

m_logger.debug(myPackageName|| 'Adding Rows.');

do rowA = 1 to MyInputDataGrid_A.row_count() ;

/* your comparision logic here */
do rowB = 1 to MyInputDataGrid_B.row_count() ;

/* use the get methods here to retrieve values from each input data grid */
/* use some tmp variables to hold the values from the grid you want to output */

MyOutputDataGrid.add_row();
row = row_count();

/* if your output isnt String then use set that is appropriate */

MyOutputDataGrid.setString('P1',row,tmpA_P1);
MyOutputDataGrid.setString('P2',row,tmpA_P2);
MyOutputDataGrid.setString('P4',row,tmpA_P3);


if (m_logger.isDebugEnabled()) then do ;
m_logger.debug('Added Row number: '||row);
end ;

end; /** inner do loop **/

end; /** do loop **/
end; /** execute method **/

endpackage;
run;

[bhanucharan]

Thanks James !!

[bhanucharan]

Hi James,

Below is the Jython code to read two data grids and apply filter.

ls_out = []
conf_out = []
#data = []
for items in comp_prod_lst:
 if items not in prod_id:
  ind = comp_prod_lst.index(items)
  ls_out += [items]
  conf_out += [conf[ind]]
filter_prod_cnt = len(ls_out)

Thanks

Bhanu 

[TyrantLucifer]

Hi,Bhanu 

I want to use Jython to handle data grids in rtdm,can you provide some examples of Jython operating data grids?I can't find any valid data about it.

Thanks 

Lucifer

[JamesAnderson]

Hi Lucifer,
Some notes and examples below.
Cheers
James
RTDMTable
The RTDMTable (com.sas.analytics.ph.common.RTDMTable) type corresponds to a datagrid. It has many useful features for manipulation & traversal. It is required to include the EventInfo object on many RTDMTable methods

Create, Write, Read, Delete
Here are the basic CRUD operations to work with the RTDMTable object from Python. It also illustrates when the builtin EventInfo
object will also be needed inside your activities (for handling WHERE expressions).
Example:
import java.util.Collections as Collections
import java.lang.Long as Long
import com.sas.analytics.ph.common.RTDMTable as RTDMTable
import com.sas.analytics.ph.common.exp.SymbolTable as SymbolTable
import com.sas.analytics.ph.common.jaxb.DataTypes as DataTypes
newTable = RTDMTable()
# Defining columns
newTable.columnAdd("MY_STRING_COLUMN", DataTypes.STRING, Collections.emptyList());
newTable.columnAdd("MY_INT_COLUMN", DataTypes.INT, Collections.emptyList());
# Adding a new row
newRow = newTable.rowAdd()
newRow.columnDataSet("MY_STRING_COLUMN", "A String value.")
newRow.columnDataSet("MY_INT_COLUMN", Long(52));
# Iterating over the table & reading values
it = newTable.iterator()
while it.hasNext():
row = it.next()
value = row.columnDataGet("MY_STRING_COLUMN")
num_value = row.columnDataGet("MY_INT_COLUMN")
# Deleting all rows with MY_INT_COLUMN greater than 5
newTable.delete(SymbolTable(), "newTable", eventInfo, "newTable.MY_INT_COLUMN GT 5", None);

SELECT from existing datagrids

To sub-select from an existing datagrid, you use the select() method. The parameters should be familiar to anyone who has used
the TableSelect function within a calculated variable.

Here is an example:
("fordCars" is an "Output Variable","cars" is a "Input Variable"):

import com.sas.analytics.ph.common.exp.SymbolTable as SymbolTable
fordCars=cars.select(SymbolTable(), "myTable", eventInfo, "Model", "myTable.Make EQ 'Ford'", None)

Join Tables

Joining tables is a bit more complicated. RTDM supports only INNER joins between in-memory tables. The syntax should be familiar to those using the TableInnerJoin functionality within calculated items.

Here is an example:
("joinedTable" is an "Output Variable", "tableA" and "tableB" are "Input Variables")

import com.sas.analytics.ph.common.exp.SymbolTable as SymbolTable
joinedTable = tableA.innerJoin(
SymbolTable(),
"A", # Name for the customer table for use in the columns & WHERE
tableB, # The right hand side table
"B", # Name for the right hand side table
eventInfo, # The event info object (in case of date math)
"*", # The columns to retain in the joined table, all in this case
"A.Product_code EQ B.Product_number", # The WHERE clause for the JOIN
None )
Sorting

On the RTDMTable object, there is a direct sort method. This sorts the table in place. You can sort on one or more columns.
Example:
("inputTable" is an "Input Variable", "outputTable" is an "Output Variable")

inputTable.sort("Color ASCENDING")
outputTable=inputTable