topic Re: is there a JSL command to group all of my excluded rows at the top of my data table? in Discussions

is there a JSL command to group all of my excluded rows at the top of my data table?

learning_JSL — Fri, 09 Jun 2023 19:58:11 GMT

Hi - I have not been able to find a JSL command to group all excluded rows at the top of my data table. Is this possible?

Re: is there a JSL command to group all of my excluded rows at the top of my data table?

txnelson — Thu, 30 Sep 2021 16:28:26 GMT

The way I handle this is to create a matrix of the row numbers that are excluded.

Names Default To Here( 1 );
dt = Current Data Table();
ex = dt << get excluded rows;

Then you can manipulate them somewhat like a group

Re: is there a JSL command to group all of my excluded rows at the top of my data table?

learning_JSL — Thu, 30 Sep 2021 17:16:13 GMT

Or perhaps there's another approach using JSL? The goal is to group all the excluded rows together as part of my validation testing procedure. (I am training my model using un-excluded rows and testing it using excluded rows....thus the need to easily access and view the excluded rows in one place in the data table. Otherwise they are intermixed throughout the dataset.) I will need to repeat this step numerous times which is why I need to script it.

Re: is there a JSL command to group all of my excluded rows at the top of my data table?

txnelson — Thu, 30 Sep 2021 17:32:19 GMT

Here is a simple extension to my first script, that reorders the rows

Names Default To Here( 1 );
dt = Current Data Table();
ex = dt << get excluded rows;
dt << New Column( "__row__", set each value( Row() ) );
dt << New Column( "__exclude__" );
dt:__exclude__[ex] = 1;
dt << sort( by( :__exclude__, :__row__ ), order( descending, ascending ), replace table( 1 ) );
dt << delete columns( {"__row__", "__exclude__"} );

Re: is there a JSL command to group all of my excluded rows at the top of my data table?

ron_horne — Thu, 30 Sep 2021 17:37:24 GMT

Hi @learning_JSL

see if you can make use of this example:

/*:

// Open Data Table: Big Class.jmp
// → Data Table( "Big Class" )
Open( "/C:/Program Files/SAS/JMPPRO/14/Samples/Data/Big Class.jmp" );


// Toggle selected rows' hide and exclude states
Data Table( "Big Class" ) << Select Randomly( Sampling Rate( 0.1 ) ) <<
Hide and Exclude;


// New column: Excluded
Data Table( "Big Class" ) << New Column( "Excluded",
	Numeric,
	"Continuous",
	Format( "Best", 12 )
);


// Column formula: Excluded
:Excluded << Set Formula( If( Excluded(), 1, 0 ) );


// Sort data table
Data Table( "Big Class" ) << Sort(
	By( :Excluded ),
	Replace Table,
	Order( Descending ),
	Copy formula( 0 )
);

let us know if it worked

Re: is there a JSL command to group all of my excluded rows at the top of my data table?

learning_JSL — Thu, 30 Sep 2021 18:03:29 GMT

Thanks very much. That worked perfectly. I appreciate the help, txnelson.

Re: is there a JSL command to group all of my excluded rows at the top of my data table?

Mark_Bailey — Thu, 30 Sep 2021 18:30:36 GMT

Here is another approach.

Names Default to Here( 1 );

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

// exclude some rows like a user might
excluded = J( 5, 1, Random Integer( 1, N Row( dt ) ) );
dt << Select Rows( excluded ) << Exclude( 1 ) << Clear Select;
Wait( 2 );

// actual solution:

// select excluded rows
excluded = dt << Get Excluded Rows;
dt << Select Rows( excluded );

// move selected rows to top of data table
dt << Move Rows( At Start ) << Clear Select;

Re: is there a JSL command to group all of my excluded rows at the top of my data table?

learning_JSL — Thu, 30 Sep 2021 22:35:01 GMT

Thank you Mark. Various ways to get there from here!

Re: is there a JSL command to group all of my excluded rows at the top of my data table?

learning_JSL — Mon, 11 Oct 2021 15:26:30 GMT

Thank you Ron. This worked perfectly after minor massaging. I appreciate it. Here is what I ended up with.

dt = Open(
    "C:\Users\trcampbell\Desktop\ECOLI\share ecoli data\for Rachel\092921 MODEL\092921 n=205.jmp",
    window bounds( 217, 221, 1622, 1057 )
);
Random_rows = dt << Select Randomly( 0.2 ) << Hide and Exclude();
New Column( "Excluded", Numeric, "Continuous", Format( "Best", 12 ) );
:Excluded << Set Formula( If( Excluded(), 1, 0 ) );
dt << Sort( By( :Excluded ), Replace Table, Order( Descending ), Copy formula( 0 ) );

Close(
    dt,
    save(
        "C:\Users\trcampbell\Desktop\ECOLI\share ecoli data\for Rachel\092921 MODEL\test 10 - 092921.jmp"
    )
);