turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- JMP User Community
- :
- Discussions
- :
- Discussions
- :
- Use of Select Where, when the condition requires use of 'Contain' to describe wh...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Jun 16, 2014 1:22 PM
(9211 views)

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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