Subscribe Bookmark RSS Feed

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

poulravn

Community Trekker

Joined:

Jan 25, 2012

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

1 ACCEPTED SOLUTION

Accepted Solutions
pmroz

Super User

Joined:

Jun 23, 2011

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"));

5 REPLIES
pmroz

Super User

Joined:

Jun 23, 2011

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"));

poulravn

Community Trekker

Joined:

Jan 25, 2012

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

pmroz

Super User

Joined:

Jun 23, 2011

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?

ms

Super User

Joined:

Jun 23, 2011

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" )

);

pmroz

Super User

Joined:

Jun 23, 2011

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