cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
poulravn
Level IV

How to select rows based on multiple criteria?

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
ms
Super User (Alumni) ms
Super User (Alumni)

Re: How to select rows based on multiple criteria?

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


View solution in original post

10 REPLIES 10
ms
Super User (Alumni) ms
Super User (Alumni)

Re: How to select rows based on multiple criteria?

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
Level IV

Re: How to select rows based on multiple criteria?

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

ms
Super User (Alumni) ms
Super User (Alumni)

Re: How to select rows based on multiple criteria?

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


wu
wu
Level III

Re: How to select rows based on multiple criteria?

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;

 

 

 

 

 

txnelson
Super User

Re: How to select rows based on multiple criteria?

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 ) );
Jim
wu
wu
Level III

Re: How to select rows based on multiple criteria?

Thanks,
I used to subset each criteria into a individual table, and then combined all of them.
Happy New Year !
ms
Super User (Alumni) ms
Super User (Alumni)

Re: How to select rows based on multiple criteria?

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
Level IV

Re: How to select rows based on multiple criteria?

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 (Alumni) ms
Super User (Alumni)

Re: How to select rows based on multiple criteria?

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