Use of Select Where, when the condition requires use of 'Contain' to describe which rows are wanted

[#1]

Hi, I grabble with this simple peice of querying: I want, i JSL, to select some rows, that contain several different pieces of strings. Like, a columsn has some descriptors such as '1. blood sample'  '2. blood sample', 1. serum sample' etc. I want in one conditional expression to write:

dt >> select where('column with sample names' contains 'blood' or 'serum').

Referring to good old SAS I would just write:

data newset;

set oldset;

where(index('column','blood')>=1 or index('column','serum')>=1);

run;

How to do this in JSL?

Regards Poul

[#2 · Accepted solution]

You're almost there.  Copy the following code into a script window and run it:

New Table( "Search Example",
    Add Rows( 6 ),
    New Column( "Sample Means",
        Character,
        Nominal,
        Set Values(
            {"1. blood sample", "2. blood sample", "1. serum sample",
            "2. serum sample", "1. cardio sample", "2. cardio sample"}
        )
    )
);
 
dt = current data table();
 
dt << select where (contains(as column(dt, "Sample Means"), "blood") |
                  contains(as column(dt, "Sample Means"), "serum"));

[#3]

Hi, the answer is correct and helps me doing what I wanted. In terms of learning from it, could you explain why the 'as column' is there? Is it because 'contains' works only on columns? And the 'as column' returns the right data type?

I have a little trouble understanding this.

Regards

Poul

[#4]

I think I got the "as column" syntax from this discussion forum a while back.  It's a bit folk-lorish - just saying column(dt, "colname") didn't work in this context, but using as column worked.

Can anyone else explain why as column is needed?

[#5]

The code below is an attempt to summarize my understanding of the difference between Column() and As Column().

//PMroz example table
dt=New Table( "Search Example",
    Add Rows( 6 ),
    New Column( "Sample Means",
        Character,
        Set Values(
            {"1. blood sample", "2. blood sample", "1. serum sample",
            "2. serum sample", "1. cardio sample", "2. cardio sample"}
        )
    )
);
 
// Column("X") returns a column reference.
// As Column("X") is equivalent to :X, giving access to the values of Column("X").
// Without an index, :X returns the value of the current row.
//
Row() = 3;
x = As Column( dt, "Sample Means" );
y = dt:Sample Means;
col = Column( dt, "Sample Means" );
Show( x, y, col );
 
// However, with an index (e.g. :X and Column("X")) both the current row
//and the column reference is bypassed; and both commands yield the same result.
x = dt:Sample Means[1];
y = Column( dt, "Sample Means" )[1];
Show( x, y );
 
// Hence, the below messages using As Column() Or Column()[] are equivalent.
// The empty square brackets ensures that "Select Where" loops through the value of each row.
//Without them, it would repeatedly look for a string in a column reference which would fail every time.
 
dt << select where(
  Contains( Column( dt, "Sample Means" )[], "blood" ) |
  Contains( Column( dt, "Sample Means" )[], "serum" )
);
                
dt << select where(
  Contains( As Column( dt, "Sample Means" ), "blood" ) |
  Contains( As Column( dt, "Sample Means" ), "serum" )
);

[#6]

Hmmm more folklore.  There are hints towards this syntax in the scripting guide, but nothing this definitive.  Thanks MS!