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
- :
- Discussions
- :
- Formula Countif column

Topic Options

- 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 20, 2012 10:56 AM
(3436 views)

I am trying to use a condition to create a formula on a column.

The datatable example is:

Variable 1

CASSYGDGGTGELFF

CATSRSSGYEQYF

CASSPAAGAAYEQYF

CASSPAAGAAYEQYF

CASSVGSDAYEQYF

CASSVGGVSYNEQFF

CASSVSGSAYEQYF

CASSPAAGAAYEQYF

And I would like to create a variable that would be the count of variable 1 for each row.

Variable 1 Count of Variable 1

CASSYGDGGTGELFF 1

CATSRSSGYEQYF 1

CASSPAAGAAYEQYF 3

CASSPAAGAAYEQYF 3

CASSVGSDAYEQYF 1

CASSVGGVSYNEQFF 1

CASSVSGSAYEQYF 1

CASSPAAGAAYEQYF 3

In excel I would use

B1=countif(A:A,A1)

...

B8=countif(A:A,A8)

I know how to do it through tabulate and I can get around this problem in different ways. However, this is one brick of a more complex formula that I would like to implement in JMP.

Thanks,

Jorge Almeida

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

There is a JMP counterpart to countif(): Col Number(col, <optional bycol>). However, it only works for numeric columns so character varibles has to be converted to numbers. Missing values will yield zero.

Try this column formula

New Column**(** "Count of Variable 1",

Numeric,

Continuous,

Formula**(**

Col Number**(** Hex To Number**(** Char To Hex**(** :Variable 1 **)** **)**, :Variable 1 **)**

**)**

**)**

8 REPLIES

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

Feb 20, 2012 2:32 PM
(1808 views)

I'm not a JMP user, thus probably shouldn't be attempting to answer your question. However, upon looking at the JMP documentation, it appears that one can use SQL in JMP.

Using the combination of count and group by statements, with SQL, would do the same thing and much easier than having all the individual statements in Excel.

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

Feb 20, 2012 2:59 PM
(1808 views)

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

Feb 21, 2012 7:27 AM
(1808 views)

JMP does not have SQL as part of JSL. There are SQL-like features, like select where, table joins, updates, etc., but you can't issue SQL commands like you can in SAS. That would be a really great feature (hint hint!).

Anyway I've needed to do something similar and used tabulate and then joined the results back to the original table. I'm not sure a column formula would be easy to implement, and the performance might not be good for large tables.

Here's how to do it using tabulate and update. The table **Untitled 8** contains your original data in a column called **Variable 1**.

dt = data table**(**"Untitled 8"**)**;

tmp_tab = dt << Tabulate**(**

Show Control Panel**(** **0** **)**,

Add Table**(** Row Table**(** Grouping Columns**(** :Variable 1 **)** **)** **)**, invisible

**)**;

tmp_tbl = tmp_tab << Make Into Data Table;

tmp_tab << close window;

Data Table**(** "Untitled 8" **)** << Update**(**

With**(** tmp_tbl **)**,

Match Columns**(** :Variable 1 = :Variable 1 **)**,

Add Columns from Update table**(** :N **)**

**)**;

close**(**tmp_tbl, nosave**)**;

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

Feb 21, 2012 8:19 AM
(1808 views)

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

There is a JMP counterpart to countif(): Col Number(col, <optional bycol>). However, it only works for numeric columns so character varibles has to be converted to numbers. Missing values will yield zero.

Try this column formula

New Column**(** "Count of Variable 1",

Numeric,

Continuous,

Formula**(**

Col Number**(** Hex To Number**(** Char To Hex**(** :Variable 1 **)** **)**, :Variable 1 **)**

**)**

**)**

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

Feb 27, 2012 8:38 AM
(1808 views)

That formula worked. Thank you very much. I was able to integrate it in my other more complex formula as in:

"Variable 1" "Name" "SharedVariable 1" "Count of shared"

CASSYGDGGTGELFF John Private 1

CATSRSSGYEQYF John Private 1

CASSPAAGAAYEQYF John Shared 3

CASSPAAGAAYEQYF Albert Shared 3

CASSVGSDAYEQYF Albert Private 1

CASSVGGVSYNEQFF Albert Private 1

CASSVSGSAYEQYF Albert Private 1

CASSPAAGAAYEQYF Michael Shared 3

New Column**(** "SharedVariable1",

Character,

Nominal,

Formula**(**If**(**ColNumber**(** Hex To Number**(** Char To Hex**(** :Variable 1 **)** **)**, :Variable 1 **)** > Col Number**(** Hex To Number**(** Char To Hex**(** :Variable 1 **)** **)**, :Variable 1 || :Name **)**, Char**(**"Shared"**)**, Char**(**"Private"**)))**

**)**;

New Column**(** "Count of shared",

Numeric,

Continuous,

Formula**((**ColNumber**(** Hex To Number**(** Char To Hex**(** :Variable 1 **)** **)**, :Variable 1 **)** - Col Number**(** Hex To Number**(** Char To Hex**(** :Variable 1 **))**, :Variable 1 || :Name **))**+**1)**

**)**

One question:

Col Number**(** Hex To Number**(** Char To Hex**(** :Variable 1 **)** **) ****is to create a countable variable. To specificy what to count is after the comma on the:**

** Col number(Column with a numeric variable, Condition to count)**

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

Feb 27, 2012 8:58 AM
(1808 views)

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

Aug 4, 2016 2:36 PM
(1808 views)

If anyone comes here looking for the JSL script to simply store a variable with the count of cells with a certain criteria, like I did, here's what i came up with that worked for me:

dt = current data table ();

CountCondition1 = nrows ( dt << get rows where (:[Column with condition] [LOGICAL CONDITION]));

...

CountConditionX = nrows ( dt << get rows where (:[Column with condition] [LOGICAL CONDITION]));

So, for a data table with the set up:

Name Number of Incidences

Apples 3

Oranges 4

Bananas 0

Grapes 0

and you want to know, for instance, what % of fruit has a non-zero value:

CountNonZeros = nrows( dt << get rows where (:Number of Incidences > 0));

// CountNonZeros variable becomes value 2

CountTotal = nrows (dt);

// CountTotal becomes value 4

PercentNonZero = CountNonZeros/CountTotal*100

// PercentNonZero becomes 50