turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- JMP User Community
- :
- Blogs
- :
- JMP Blog
- :
- Lookup tables in JMP

Article Options

- Subscribe to RSS Feed
- Mark as New
- Mark as Read
- Bookmark
- Subscribe
- Email to a Friend
- Printer Friendly Page
- Report Inappropriate Content

Apr 2, 2014 4:21 PM
(171 views)

Lookup tables in JMP

A few days ago, I showed a customer how she could use lookup tables in JMP, and I thought it would be a good idea to share this with everyone.

Those of you who have used lookup tables elsewhere already know how handy they can be. For those who have never used one, let’s first look at a simple example: assigning a letter grade based on a numeric grade. (By the way, for more on using JMP with grade books, look out for an upcoming blog post on that topic by JMP Academic team member Julian Parris.)

Suppose we have the following correspondence between numeric and letter grades:

E: x < 60

D: 60 <= x < 70

C: 70 <= x < 80

B: 80 <= x < 90

A: 90 <= x

We’d like to automatically assign letter grades to the numeric grades below — but how?

The most common way is to use a formula column with an IF clause. Statements are evaluated in the order encountered, and once a statement evaluates to “True,” execution stops — so the clause must be constructed with this in mind:

This technique works perfectly well, but unfortunately, it can become cumbersome in certain instances:

Fortunately, we can tackle cases like these with the help of a powerful matrix function: Loc Sorted().

How does Loc Sorted() work?

For example, running this code gives a result of [3], because 14 is greater than or equal to the number stored in the **3**rd position of the * x* matrix, but less than the number stored in the

x = [0, 5,10,15,20,25];

y = 14;

show(loc sorted (x, y));

Similarly, running this code gives a result of [4,6,6,1]:

x = [0, 5,10,15,20,25];

y = [17, 25, 50, 3];

show(loc sorted (x, y));

Note that the minimum of the * x* matrix should be the lowest value of

In the letter grade example, we can use loc sorted() to pick the grade from list {“E”, “D”, “C”, “B”, “A”}:

This is great if we’ve got only a few categories to consider. But what happens when we have a whole table full of options, or need to not merely look up a single value, but look up several different values and use them together?

Fortunately, this is easy to do. All we need is an extra table to hold the information. Computing tax from a tax table is a classic (and timely) example of such a case.

*** Warning: I am not a tax professional. I do not play one on TV. Please obtain your 2013 tax tables from the IRS.

In today’s example, * which is for illustrative purposes only, should not be construed as tax advice, and is not from a tax professional*, we will use the table below, which I made from information I found online.

Our goal is to compute the tax owed, given the table above and the amount of income being taxed.

First, we need to place this information into a data table (notice that we will lower the contents of the first column by $1, because of the way Loc Sorted () works.) To follow along with the example, use the following names for the table and columns, or download the example from the JMP File Exchange (download requires free SAS login).

Table name: **MarriedFilingJointlyTable**

Column names:

We want to use the table above to determine the tax for the taxable incomes in the following table, named **MarriedFilingJointlyTable**:

Armed with Loc Sorted() and the two tables above, we’re ready to begin.

In the income table, we add a column by selecting **Cols > New Column…**

We enter “Tax” as the Column Name and select “Currency” as the Format.

We then select “Formula” from the Column Properties drop-down, at which point the Formula Editor appears.

Did you know that we can actually enter a program into a column formula? That is what we will do here — and even though our script is only three lines, the easiest and least error-prone way to do this is by copying and pasting from a script window.

So, open up a script window (**File > New > New Script**) and type the following:

**Line 1** points the variable **dt** to the tax table:

**Line 3** determines which row of the tax table we will use for a given amount of income:

**Line 5** computes the tax. Using the first income value of $41,196.47 as an example:

** **To this amount, we must add the product of:

Once we’ve entered the script into the script window, we select all of it, copy it and paste it into the red box in the formula editor (which until now has contained nothing):

After pressing “OK” to close the formula editor and “OK” to close the column properties editor, we find that the tax has been computed for each row in the income table, and see by the “+” icon that the tax is formula-based; the lookup table needs to be open whenever you wish to re-evaluate the formula. (If you prefer to remove the formula at this point, simply click on the “+” icon, select “Clear” when the formula editor opens, then click “OK.”)

For those of you who prefer scripting, the solution is similar. In the script below, I’ve written values directly to the table without a formula, but using the formula() option in the << New Column () message would work just as well.

And there you have it. That’s all there is to using lookup tables in JMP! If you’re like many of our customers, you’ll see plenty of opportunities for their use — enjoy.

2 Comments

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.