Subscribe Bookmark RSS Feed

How to select rows based on multiple criteria?

poulravn

Community Trekker

Joined:

Jan 25, 2012

Hi,

I am writing a script to Subset a data table. Following the scripting guids' coding example I can select according to a criterium:

for each row(Selected(Rowstate())=(name=="KATIE"));

How can I generalize this to select both KATIE and LOUISE?

This does not work:

for each row(Selected(Rowstate())=(name=="KATIE","LOUISE"));

So what is the syntax for this?

Regards

Poul

   

1 ACCEPTED SOLUTION

Accepted Solutions
Solution

Same principle:

dt = Open( "$SAMPLE_DATA/Big Class.jmp" );

For Each Row( Selected( Row State() ) = Or( contains(:name, "ATIE"), contains(:name, "ISE")) );

// Or alternatively

dt << Select where(  Or( contains(:name, "ATIE"), contains(:name, "ISE")) );


7 REPLIES
ms

Super User

Joined:

Jun 23, 2011

Use Or():

dt = Open( "$SAMPLE_DATA/Big Class.jmp" );

For Each Row( Selected( Row State() ) = Or( name == "KATIE", name == "LOUISE" ) );

// Or alternatively

dt << Select where( Or( name == "KATIE", name == "LOUISE" ) );


poulravn

Community Trekker

Joined:

Jan 25, 2012

But what if I want to further use 'Contains' to match the strings inly on part of the strings? Like "ATIE" and "ISE"? Regards Poul

Solution

Same principle:

dt = Open( "$SAMPLE_DATA/Big Class.jmp" );

For Each Row( Selected( Row State() ) = Or( contains(:name, "ATIE"), contains(:name, "ISE")) );

// Or alternatively

dt << Select where(  Or( contains(:name, "ATIE"), contains(:name, "ISE")) );


ms

Super User

Joined:

Jun 23, 2011

If there are many strings to match, it would be tempting to use of list instead of constructing a long OR() clause. However, Contain() cannot not look for substrings in a list (of strings). A loop over the list is then required. Here's an example:

dt = Open( "$SAMPLE_DATA/Big Class.jmp" );

name_list = {"ATIE", "ISE", "CLY", "WWW"};

n = N Items( name_list );

For Each Row(

  For( i = 1, i <= n, i++,

  If( (s_flag = Contains( :name[], name_list[i] )) > 0,

  Break()

  )

  );

  Selected( Row State() ) = s_flag;

);

poulravn

Community Trekker

Joined:

Jan 25, 2012

Hi, these answers were really helpfull. Now if I want to add a condition for value in another column, say I wanted to select name="KATIE" and "ROBERT", (or even better, containing "ATIE"), AND sex="F". Then I would have to combine the Or- condition with an and condition.

dt = Open( "$SAMPLE_DATA/Big Class.jmp" );

For Each Row( Selected( Row State() ) = Or( contains(:name, "ATIE"), contains(:name, "OBER"))  And (contains(:sex, "F")));

I already something like this, but was ot succesfull.

Regards

Poul

(PS Obviously I used Base SAS a lot. Now I try to implement selections on tables this way around. Thanks for the help.)

ms

Super User

Joined:

Jun 23, 2011

Almost there. The And() must enclose both arguments; for And() to be true, both its arguments must be true.

This should work:

For Each Row( Selected( Row State() ) = And(Or( contains(:name, "ATIE"), contains(:name, "OBER")),  contains(:sex, "F")));

pmroz

Super User

Joined:

Jun 23, 2011

This syntax might be a little more readable.  You can use the vertical bar for OR, and the ampersand for AND:

dt = open("$sample_data\Big Class.jmp");

dt << select where((contains(:name, "ATIE") | contains(:name, "OBER"))

                                      & contains(:sex, "F"));