topic Re: Row selection by value labels in Discussions

Row selection by value labels

bfoulkes — Mon, 06 Jan 2020 19:44:19 GMT

I would like to script a row selection by the value labels in a column. As an example, the Big Class data set has a value label where F=Female and M=Male. As far I can tell, I cannot write JSL to do a column selection by "Female". Does anyone know how to do this in JSL?

My bigger issue is that I have several values that have the same label, but instead of trying to select all of those values individually, I would like to just select the label.

Re: Row selection by value labels

Mark_Bailey — Mon, 06 Jan 2020 19:59:44 GMT

Use this example that illustrates one way to reverse the reference from the label to the value. (Note: I manually added the Value Label column property to the sex data column so that there was something to work with. That step is not included in the script. You shouldn't need it because your data column already has this property.)

Names Default to Here( 1 );

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

label = Arg( dt:sex << Get Property( "Value Labels" ), 1 );

For( i = 1, i <= N Items( label ), i++,
	If( Contains(  Arg( label[i], 2 ), "female" ),
		value = Arg( label[i], 1 );
		Break();
	);
);

dt << Select Where( :sex == value );

Re: Row selection by value labels

bfoulkes — Mon, 06 Jan 2020 20:27:41 GMT

thanks Mark. I appreciate the quick response.

Re: Row selection by value labels

txnelson — Tue, 07 Jan 2020 01:20:09 GMT

Here is a different take on a solution for your question. The example jsl simply creates a new column that contains the Value Labels values, which then you can make your Select Where() functions from. Since there is not a function to directly create the new column, the code uses a feature where JMP will move the value label form of a column if the column is copied to a JMP Journal. See the code below for the details

names default to here(1);
dt=open("$SAMPLE_DATA/big class.jmp");

// Create the Value Labels for the Sample data table
dt:sex << set property("value labels", {"F" = "Female", "M" = "Male"} );

// Select the column of interest
dt<<go to(:sex);

// Copy the column of interest into a journal
nwj = dt<<journal;

// Add a column to the existing data table, with the Value Labels as the 
// cell values for the new column
dt<<new column("Sex Label", character, values(nwj[stringColBox(1)]<<get));

// Close the no longer needed journal
nwj<<close window;

// Use the new label column for the row selection
dt << select where(:Sex Label == "Female")

Re: Row selection by value labels

bfoulkes — Tue, 07 Jan 2020 13:12:52 GMT

This is also fantastic. I couldn't quite figure out how to grab the labels, but tossing them into a journal is brilliant. My actual dataset is over 2MM records and this goes 4X faster to complete. Thanks so much for sending this along.