How to select rows based on multiple criteria?

[#1]

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

[#2]

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

[#3]

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

[#4 · Accepted 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")) );

[#5]

Hi ms,

thanks for your script.

I wonder if there is way to dynamically handle name list, and still able to subset the

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

name_list={"clay","Alice","AMY", ...};

for(i=1,i<=nitems(name_list),i++,

        i_name=name_list[i];

       dt<<select where(name=i_name)

);

dt<<subset;

[#6]

Is this what you are trying to accomplish, find all of the matches found in the list called "name_list" and then output them into a subsetted data table?

Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA/Big Class.jmp" );
name_list = {"Clay", "Alice", "AMY"};
For( i = 1, i <= N Items( name_list ), i++,
	i_name = Uppercase( name_list[i] );
	dt << select where( :name == i_name, current selection( "extend" ) );
);
 
dtSub = dt << subset( selected rows( 1 ), selected columns( 0 ) );

[#7]

Thanks,
I used to subset each criteria into a individual table, and then combined all of them.
Happy New Year !

[#8]

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;

);

[#9]

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

[#10]

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