BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
AThomalla
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
Rick_SAS
SAS Super FREQ

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;

View solution in original post

3 REPLIES 3
Rick_SAS
SAS Super FREQ

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
Obsidian | Level 7

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
SAS Super FREQ

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;

sas-innovate-2024.png

 

Secure your spot at the must-attend AI and analytics event of 2024: SAS Innovate 2024! Get ready for a jam-packed agenda featuring workshops, super demos, breakout sessions, roundtables, inspiring keynotes and incredible networking events.

 

Register by March 1 to snag the Early Bird rate of just $695! Don't miss out on this exclusive offer. 

 

Register now!

Multiple Linear Regression in SAS

Learn how to run multiple linear regression models with and without interactions, presented by SAS user Alex Chaplin.

Find more tutorials on the SAS Users YouTube channel.

From The DO Loop
Want more? Visit our blog for more articles like these.
Discussion stats
  • 3 replies
  • 860 views
  • 3 likes
  • 2 in conversation