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

Showing results for

- JMP User Community
- :
- Discussions
- :
- Determining if a column exists

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Feb 6, 2013 9:48 AM
(2951 views)

I have a JMP script that creates a new column with a formula in it. I'm trying to find out how to detect if the column already exists so that I can skip creating it, and avoid generating extra columns. I'm sure it's there, but I'm not finding it in the literature that I have.

Thanks.

Mike

1 ACCEPTED SOLUTION

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Solution

dt = open**(**"$sample_data\Big Class.jmp"**)**;

col_name_list = dt << get column names**(**string**)**;

new_column = "BMI";

// English BMI Formula

// BMI = ( Weight in Pounds / ( Height in inches x Height in inches ) ) x 703

if **(**!contains**(**col_name_list, new_column**)**,

dt <<New Column**(**"BMI", numeric, continuous,

formula**(****703** * :weight / :height / :height**)**

**)**;

**)**;

8 REPLIES

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

dt = open**(**"$sample_data\Big Class.jmp"**)**;

col_name_list = dt << get column names**(**string**)**;

new_column = "BMI";

// English BMI Formula

// BMI = ( Weight in Pounds / ( Height in inches x Height in inches ) ) x 703

if **(**!contains**(**col_name_list, new_column**)**,

dt <<New Column**(**"BMI", numeric, continuous,

formula**(****703** * :weight / :height / :height**)**

**)**;

**)**;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Feb 6, 2013 11:13 AM
(1872 views)

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Feb 9, 2013 3:42 PM
(1872 views)

There is no single function that I am aware of. But *Is missing()* in combination with *Is Scriptable()* can do the trick.

The column also need to be enclosed by a Try() statement to avoid the script from stopping if the column would not exist.

dt = Open**(** "$sample_data\Big Class.jmp" **)**;

new_column = "BMI";

If**(** Is Missing**(** Is Scriptable**(** Try**(** Column**(** new_column **)** **)** **)** **)**,

dt << New Column**(** new_column,

numeric,

continuous,

formula**(** **703** * :weight / :height / :height **)**

**)**

**)**;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Feb 13, 2013 8:01 AM
(1872 views)

Also handy to know. I'm always glad to have another tool to add to the toolbox.

Thanks.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Feb 9, 2013 2:43 PM
(1872 views)

I am not certain if this is true in all recent versions of JMP. I make this comment only to highlight an opportunity to improve the robustness of the code.

'Contains' returns the position of the item, which is fine to use in an 'if' statement, since zero will be interpreted as false and a positive value will be interpreted true. When preceding a contains with a not '!', the condition works when the item is not present or when present as the first item; however, when the item isn't first ( a value greater than 1 ), the not '!' can fail to convert the condition to false. This can be avoided by using a comparison '>0' with the 'contains'. See below for syntax.

dt = open**(**"$sample_data\Big Class.jmp"**)**;

col_name_list = dt << get column names**(**string**)**;

new_column = "BMI";

// English BMI Formula

// BMI = ( Weight in Pounds / ( Height in inches x Height in inches ) ) x 703

if **(**!**(**contains**(**col_name_list, new_column**)> 0)**,

dt <<New Column**(**"BMI", numeric, continuous,

formula**(****703** * :weight / :height / :height**)**

**)**;

**)**;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Feb 13, 2013 7:58 AM
(1872 views)

That's very handy to know. I'll have to watch out for that.

Thanks

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Feb 13, 2013 10:40 AM
(1872 views)

Hello wiebepo,

I tried different combinations and negating a positive integer, no matter how large, results in a 0. So my code will work (in JMP 9 and 10). Having said that I appreciate the heads up - this approach might not work in all languages. It certainly bears further testing.

b = **2000000000000**;

!b;

The log shows:

**0**

Regards,

PMroz

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Feb 24, 2013 5:37 AM
(1872 views)

PMroz,

Let me start by confirming that the code you wrote worked for me as well, and the explaination I wrote is not consistent with JMP 9 or 10 behavior.

After reading your reply, I also tried several different combinations of contains, and if statements with lists, string, and numbers in JMP 9 and 10. Although my attempts were not exhaustive, all of my results are consistent with yours. Initially, when I read your first post I recalled some troubleshooting that I had perform years prior where I had adopted the the use of contains()>0. I am unable to recreate the scenario; however, I did find the following information in the JMP scripting guide. It details using contains with >0, which may be appropriate for their example, but does not explicitly state that it is or is not required.

Page 136 of the JMP scripting guide version 10:

"To assess whether an item is in a list, use Loc() and Contains() with >0. A returned value of zero

means that the item is not in the list. A returned value of 1 means that the item is in the list at least once."

...

"Find out if the number 5 exists in the numList:

NRow(Loc(numList, 5)) >0;

*0*

Contains(numList, 5) >0;

*0"*

Hope this helps.