SAS IML Mixed-Type Tables: Filter Data

[AThomalla]

Hello,

in SAS IML new objects have been introduced: Mixed Type Tables and Lists.

Is it possible to filter data within such an object? So not to filter data while reading from a SAS Dataset into an IML table, but after the load within such IML Table.

My main goal is to be able to select some observations from Table (which is loaded in memory as a matrix or in the object Table). 

There is also a function called LOC with which one can build such filter function to be applied on a matrix. But I would like to be able to filter on data which is mixed type. In such a case the search/ filter criteria contains several character and numeric attributes. If I load a mixed type data set into matrices, I would have to use two matrices. Then I would have to build a function which uses the search parameters and runs the LOC on both matrices and then combines the search result to apply on the matrices to get the combined result. Or is there an easier way doing this?

Regards,

Adalbert

[Rick_SAS]

That sounds like a useful feature, but it is not supported in tables in SAS/IML 15.1. As you say, you can filter observations

1. When reading from a SAS data set into a matrix

2. When reading from a SAS data set into a table

3. By using the LOC function on elements of a vector or matrix, followed by subsetting the data

Tables are basically storage containers for mixed-type data. To query, transform, or modify the data, you need to first extract the data into vectors or matrices.

If you intend to make only a few queries, you might want to just reread the data set for each query and use WHERE processing to create each table.

[AThomalla]

Thanks,

here is the solution using LOC:

I have a table called MEASURES with two character variables and two numeric variables. I create two matrices:

use work.MEASURES;
read all var {ICG MEASURE} into MEASURES_CHAR;
read all var {REPORTINGDATE VALUE} into MEASURES_NUM
close work.MEASURES;

Then i create a module which extracts some value of MEASURES_NUM based on a filter using charachter and numeric variables as well:

start getMeasure(MEASURES_CHAR, MEASURES_NUM, repDate, icgid, fmeasure);

IDX_CHAR = LOC(MEASURES_CHAR[,1] = icgid & MEASURES_CHAR[,2] = fmeasure);
IDX_NUM = LOC(MEASURES_NUM[,1] = repDate);
IDX = xsect(IDX_CHAR, IDX_NUM);

measure = MEASURES_NUM[IDX, 2];

return (measure);
finish;

The IDX array is an intersection between IDX_CHAR and IDX_NUM using the xsect function....

[Rick_SAS · Accepted solution]

Yes, that's fine. You can actually reduce it to one LOC call, and you might want to check whether the result is valid before you return the result:

start getMeasure(MEASURES_CHAR, MEASURES_NUM, repDate, icgid, fmeasure);

IDX = LOC(MEASURES_CHAR[,1] = icgid & MEASURES_CHAR[,2] = fmeasure & MEASURES_NUM[,1] = repDate);
if ncol(idx) > 0 then 
   return( MEASURES_NUM[IDX, 2] );
else
   return( {} );   /* or missing value? */
finish;