topic Re: How to select rows based on multiple criteria? in Discussions

How to select rows based on multiple criteria?

poulravn — Fri, 28 Jun 2013 08:38:46 GMT

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

Re: How to select rows based on multiple criteria?

ms — Fri, 28 Jun 2013 08:48:24 GMT

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

Re: How to select rows based on multiple criteria?

poulravn — Fri, 28 Jun 2013 09:29:41 GMT

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

Re: How to select rows based on multiple criteria?

ms — Fri, 28 Jun 2013 09:52:13 GMT

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

Re: How to select rows based on multiple criteria?

ms — Fri, 28 Jun 2013 10:19:01 GMT

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;

);

Re: How to select rows based on multiple criteria?

poulravn — Fri, 28 Jun 2013 14:35:33 GMT

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

Re: How to select rows based on multiple criteria?

ms — Fri, 28 Jun 2013 14:44:45 GMT

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

Re: How to select rows based on multiple criteria?

pmroz — Fri, 28 Jun 2013 15:14:16 GMT

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

Re: How to select rows based on multiple criteria?

wu — Sat, 28 Dec 2019 01:10:49 GMT

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;

Re: How to select rows based on multiple criteria?

txnelson — Sat, 28 Dec 2019 05:09:53 GMT

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

Re: How to select rows based on multiple criteria?

wu — Tue, 31 Dec 2019 17:37:55 GMT

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