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

[#1]

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?

[#2]

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

[#3]

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.

[#4 · Accepted solution]

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

[#5]

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

[#6]

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

[#7]

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

[#8]

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;

[#9]

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