Column Attributes

Use a collection of message pairs for data table columns to control all of the various attributes or characteristics of a column, including its name, data, states, and metadata. The messages come in pairs, one to “set” or assign each attribute and one to “get” or query the current setting of each attribute.

For example, you can hide, exclude, label, and turn on or off the scroll lock for a column through scripting. The value is Boolean; enter a one to turn the column attribute on, and a zero to turn it off.

In the following examples, the name column is unhidden, unexcluded, labeled, and locked from horizontal scrolling.

Column( "name" ) << Hide( 0 );

Column( "name" ) << Exclude( 0 );

Column( "name" ) << Label( 1 );

Column( "name" ) << Set Scroll Locked( 1 );

Note: All the messages to set various arguments (for example, Set Name, Set Values, Set Formula) start with Set. The word Set is optional for all messages except Set Name (recall that Name is already used for something else, the command that lets you use unusual characters in a name). Use whichever form you prefer or find easier to remember. The corresponding messages to retrieve the current value of an argument (for example, Get Formula) are the same, except that they start with Get instead of Set, and the word Get is not optional.

To deselect all selected columns, send a Clear Column Selection message to the data table object.

dt << Clear Column Selection;

Set or Get a Column Name

Set Name lets you name or rename a column, and Get Name returns the name for a column. The following example changes the name of the second column from age to ratio. It then returns the current column name to the log.

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

col = Column( 2 );

col << Set Name( "ratio" );

col << Get Name;

"ratio"

Set or Get Column Values

Similarly, Set Values sets values for a column. If the variable is character, the argument should be a list. If the variable is numeric, the argument should be a matrix (vector). If the number of values is greater than the current number of rows, the necessary rows are added to the data table. Get Values returns the values in list or matrix form. Get As Matrix is similar to Get Values but returns values in the numeric columns.

col << Set Values( myMatrix ); // for a numeric variable

col << Set Values( myList ); // for a character variable

col << Get Values; // returns a matrix, or list if character

col << Get Matrix(<list of column names>|<list of column numbers>|<column range>; // returns the specified columns as a matrix

The following example returns a list and a matrix of values:

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

Column( "name" ) << Values( {Fred, Wilma, Fred, Ethel, Fred, Lamont} );

myList = :name << Get Values;

{"Fred", "Wilma", "Fred", "Ethel", "Fred", "Lamont", "JAMES", "ROBERT", "BARBARA", ...}

Column( "age" ) << Values( [28, 27, 51, 48, 60, 30] );

myVector = :age << Get Values;

[28, 27, 51, ... ]

myMatrix = :weight << Get as Matrix;

[95, 123, 74, ... ]

Set or Get Value Labels

Note: For more information about value labels, see Value Labels in Using JMP.

Value labels provide a method of displaying a descriptive label for abbreviated data. For example, you might have a column of 0 and 1 values, where 0 represents a male and 1 represents a female. The value label “male” for 0 and “female” for 1 are more readable.

You can specify value labels in any one of the following three ways. Using the Big Class.jmp sample data table, assume that M maps to Male, and F maps to Female.

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

:sex << Value Labels( {"F", "M"}, {"Female", "Male"} ); // use two lists

:sex << Value Labels( {"F", "Female", "M", "Male"} ); // use a list of pairs

:sex << Value Labels( {"F" = "Female", "M" = "Male"} ); // use a list of assignments

You can activate value labels by sending Use Value Labels as a message to the column.

:sex << Use Value Labels( 1 );

To revert back to showing the column’s actual values:

:sex << Use Value Labels( 0 );

The same message can be used for the data table to turn value labels on and off for all columns.

dt << Use Value Labels( 1 );

Set or Get Data and Modeling Types

You can set or get the data type of a column using JSL. The choices are character, numeric, and row state. The following example adds a new column that has a character data type to the Big Class.jmp sample data table.

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

dt << New Column( "New" );

Column( "New" ) << Data Type( Character );

Column( "New" ) << Get Data Type;

"Character"

To set or get the modeling type of a column:

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

col = New Column( "New" );

col << Modeling Type( "Continuous" );

col << Get Modeling Type;

"Continuous"

You can specify the format of a column when changing its data type:

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

col = New Column( "Date" );

Column( "Date" ) << Data Type( Numeric, Format( "ddMonYYYY" ) );

Set or Get Formats

The Format message controls numeric and date/time formatting. The first argument is a quoted string from the list of format choices shown in the Column Info window. Subsequent arguments depend on the format choice. You can also set the field width by itself.

col << Format( "Best", 5 ); // width is 5

col << Format( "Fixed Dec", 9, 3 ); // width is 9, with 3 decimal places

col << Format( "PValue", 6 );

col << Format( "d/m/y", 10 );

col << Set Field Width( 30 );

For date formats, the Format message sets how dates appear in a data table column. To set the format that you use for entering data, or for displaying the current cell when you have it selected for entry or editing, use the Input Format message.

col << Format( "d/m/y", 10 ); // display the date in day-month-year order

col << Input Format( "m/d/y" ); // enter the date in month-day-year order

For more information about date/time formatting choices, see Date-Time Functions and Formats.

To get the current format of a column, submit a Get Format message:

col << Get Format;

Display Numbers as Four Digits

The Format() function and the Format message have a custom format capability that enables you to provide your own script to do the formatting. The name of the format is “Custom” (rather than “Best”, “Fixed”, and so on) and the argument is a script that works with the variable named “value” to return a string to represent that value. To make positive or negative integers always have at least four digits by adding zeros, you could do this:

New Table( "Untitled",

  New Column( "Column 1",

    Numeric,

    "Continuous",

    Format(

      "Custom", // make a custom format

      Formula( // using a JSL formula

        Local( {sign, length = 4, result}, // local variables

          sign = If( value < 0, -1, 1 ); // handle negative numbers

          value = Char( Abs( value ) );

          If( Length( value ) > length, // handle too big

            value = Repeat( "#", length )

          );

          result = Right( value, length, "0" ); // add zeros

          If( sign < 0,

            result = "-" || result // re-attach sign

          );

          result; // return the modified value

        )

      )

    ),

// sample data to test positive, negative, zero

    Set Values( [0, 3, -5, 33, -55, 333, -555, 3333, -5555, 33333, -55555] )

  )

);

See Create Custom Functions, Transforms, and Formats for more information about creating custom formats.

Specify the Significant Digits in a Numeric Column

Significant digits are the first nonzero digit and all succeeding digits. For example, 3.14159 has six significant digits, and 0.00314 has three significant digits.

Set the number of significant digits with the Precision column format:

Format( "Precision", 12, 3 ); // 12 wide with 3 significant digits

Set, Get, or Evaluate a Formula

The following examples show how to set, get, and evaluate a formula:

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

col = New Column( "Ratio" ); // create column and stores its reference

col << Set Formula( :height/:weight ); // set the formula

col << Eval Formula; // evaluate the formula

col << Get Formula; // return the expression :height/:weight

To use the values from columns in scripts, be sure to add commands to evaluate the formula. Formula evaluation timing can differ between different versions of JMP.

Notes:

• When formulas are added, they are scheduled to be evaluated in a background task. This can be a problem for scripts if they depend on the column having the values while the script is running.

• To force a single column to evaluate, you can send an Eval Formula command to the column. You can do this inside the command to create the column, right after the Formula clause:

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

dt << New Column( "Ratio",

	Numeric,

	Formula( :height / :weight ),

	Eval Formula

);

where Formula() is an alias for Set Formula().

However, it is best to wait until you are finished adding a set of formulas, and then use the command Run Formulas to evaluate all of the formulas in their proper order, as follows:

dt << Run Formulas;

• The Run Formulas command is preferable to the Eval Formula command, because while it is evaluating the formulas, Eval Formula does not suppress the background task from evaluating them again. The formula dependency system background task takes great care to evaluate the formulas in the right order, and RunFormulas simply calls this task until all the formulas are finished evaluating.

• If you use random numbers and use the Random Reset(seed) feature to make a reproducible sequence, then you have another reason to use Run Formulas, in order to avoid a second evaluation in the background.

• If formula suppression is enabled with Supress Eval, turn it off to enable Eval Formula to run.

• The Add Custom Function() function enables you to create your own functions and display them in the Formula Editor’s Functions list. See Create Custom Functions, Transforms, and Formats.

Set and Get Range and List Checks

You can manipulate list and range check properties using JSL. The following examples use the Big Class.jmp sample data table.

Set and clear the list check property in the sex column:

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

Column( "sex" ) << List Check( {"M", "F"} ); // set the property

Column( "sex" ) << List Check(); // clear the property

Range checks require the specification of a range using the syntax in Table 9.3.

The following example specifies that the values in the age column must be greater than zero and less than 120:

Column( "age" ) << Range Check( LTLT( 0, 120 ) );

All of the functions can be preceded by Not and one of them can be missing. The following example specifies that the values in the age column should be greater than or equal to 12:

Column( "age" ) << Range Check( not( LT( 12 ) ) );

To clear a range check state, submit an empty Range Check():

Column( "age" ) << Range Check();

To retrieve the list or range check assigned to a column, send a Get List Check or Get Range Check message to the column:

Column( "sex" ) << Get List Check;

Column( "age" ) << Get Range Check;

Here is an example of Get Range Check for the age column in Big Class.jmp:

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

Column( "age" ) << Range Check( LTLT( 0, 120 ) );

Column( "age" ) << Get Range Check;

Range Check( LTLT( 0, 120 ) )

Note that you can also use Set Property, Get Property, and Delete Property to set, retrieve, and remove list checks and range checks. See Column Properties.

Note: Operations sent through JSL that involve range check columns show any warnings in the log rather than in interactive windows.

Get a Column Script

Get Script returns a script to create the column.

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

New Column( "Ratio", Set Formula( :height/ :weight) );

Column( "Ratio" ) << Get Script;

New Column( "Ratio",

Numeric,

"Continuous",

Format( "Best", 10 ),

Formula( :height / :weight )

);

Preselect Roles

To preselect a role on a column, use the Preselect Role message. Choices include No Role, X, Y, Weight, Freq, and Validation. The Get Role message returns the current setting.

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

col = New Column( "New" );

col << Preselect Role( X );

col << Get Role;

Lock a Column or Cells

To lock or unlock a column, use Lock or Set Lock with a Boolean argument. Get Lock returns the current setting.

col << Lock( 1 ); // lock

col << Set Lock( 0 ); // unlock

col << Get Lock; // show current state

Want more information? Have questions? Get answers in the JMP User Community (community.jmp.com).