Share your ideas for the JMP Scripting Unsession at Discovery Summit by September 17th. We hope to see you there!
Choose Language Hide Translation Bar
Highlighted
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
Highlighted
ms
Super User ms
Super User

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
Highlighted
ms
Super User ms
Super User

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


Highlighted
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

Highlighted
ms
Super User ms
Super User

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

Highlighted
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;

 

 

 

 

 

Highlighted
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
Highlighted
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 !
Highlighted
ms
Super User ms
Super User

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;

);

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

Highlighted
ms
Super User ms
Super User

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

Article Labels

    There are no labels assigned to this post.