Formula Countif column

[#1]

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

[#2]

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.

[#3]

If that would do it, sure. However, I am not very experienced in JMP and I have not used the SQL scripting. If anyone would be able to do it through the SQL and show it to me, I will be extremely thankful. Thanks art.

[#4]

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);

[#5]

Thanks PMroz, that was exactly what I did. I am trying to transfer all my data analysis from excel to JMP and there are some things that require this kind of workaround. Anyway, if there would be some column formula that could tackle this problem, it would be great. I will keep this discussion open for a little longer in order to see if there are any other suggestions.

[#6 · Accepted 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 )

  )

)

[#7]

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)

[#8]

Yes, the optional "by column" is after the comma. With a by column you get the counts of each value in that column. Whithout a by column you would get the total number of non-missing values, including multiples.

[#9]

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