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

- JMP User Community
- :
- Discussions
- :
- How to count the rows by the value in another column

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

Highlighted

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

Created:
Aug 5, 2017 11:32 AM
| Last Modified: Sep 20, 2017 12:34 PM
(5882 views)

I have been using JMP 12.0 for some time and find it fantastic. However, there is a problem where I would appreciate some help since I seem to be the only person who uses JMP wheras the rest uses SPSS.

I would like to get som information regarding the following:

C1 C2

Title Computed order

r1 surgeon 1

r2 surgeon 2

r3 surgeon 3

r4 resident 1

r5 resident 2

r6 intern 1

r7 intern 2

etc.

A friend and collegue of mine uses SPSS and has in that program written the following formula which computes the value in C2 in his SPSS.

Do if Title>LAG(Title).

Compute Order=1.

End if.

Do if Title=LAG(Title).

Compute Order=LAG(Order)+1.

End if.

Execute.

How can I write a corresponding formula in JMP?

Very greatful if someone could explain how.

LarsBirger

2 ACCEPTED SOLUTIONS

Accepted Solutions

Highlighted

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

Created:
Aug 5, 2017 4:30 PM
| Last Modified: Aug 8, 2017 3:10 PM
(6833 views)
| Posted in reply to message from LarsBirger 08-05-2017

Hi, Lars!

Here's a script that I think does what you're asking on the Big Class dataset in the Sample Data:

```
Names Default To Here( 1 );
BC_dt = Open( "$SAMPLE_DATA/Big Class.jmp" );
BCSorted_dt = BC_dt << Sort( By( :age, :sex, :height, :weight ), output table name("Big Class Sorted"));
BCSorted_dt << New Column( "Computed Order",
Numeric,
Ordinal,
Formula( If(row()==1 | :age!=Lag(:age, 1), 1, Lag(:Computed Order, 1)+1) ),
EvalFormula);
```

If all you need is the formula, you can copy the Formula() argument in the script and paste it in the formula editor...just remember to sort the data appropriately first.

Good luck!

Highlighted

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

If you're using JMP 13, the Col Rank() function support tie breaking by row number, so you get the effect you're looking for.

```
dt = Open( "$SAMPLE_DATA/Big Class.jmp" );
dt << New Column( "Rank within age",
Formula( Col Rank( 1, :age, <<Tie("row")) ));
```

The first argument is the value to rank, which is usually another column, but in this case the ranked value doesn't matter so I used the constant, 1.

3 REPLIES 3

Highlighted

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

Created:
Aug 5, 2017 4:30 PM
| Last Modified: Aug 8, 2017 3:10 PM
(6834 views)
| Posted in reply to message from LarsBirger 08-05-2017

Hi, Lars!

Here's a script that I think does what you're asking on the Big Class dataset in the Sample Data:

```
Names Default To Here( 1 );
BC_dt = Open( "$SAMPLE_DATA/Big Class.jmp" );
BCSorted_dt = BC_dt << Sort( By( :age, :sex, :height, :weight ), output table name("Big Class Sorted"));
BCSorted_dt << New Column( "Computed Order",
Numeric,
Ordinal,
Formula( If(row()==1 | :age!=Lag(:age, 1), 1, Lag(:Computed Order, 1)+1) ),
EvalFormula);
```

If all you need is the formula, you can copy the Formula() argument in the script and paste it in the formula editor...just remember to sort the data appropriately first.

Good luck!

Highlighted

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

If you're using JMP 13, the Col Rank() function support tie breaking by row number, so you get the effect you're looking for.

```
dt = Open( "$SAMPLE_DATA/Big Class.jmp" );
dt << New Column( "Rank within age",
Formula( Col Rank( 1, :age, <<Tie("row")) ));
```

The first argument is the value to rank, which is usually another column, but in this case the ranked value doesn't matter so I used the constant, 1.

Highlighted
##

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

Re: How can I make JMP to compute a value reflecting the order of a parameter.

Thank you for the answers. I will have a look into this. A colleague helped me with his statistical program (SPSS) but I will definitely look into this since I use JMP continously for my research.

Sincerely Yours

Lars

Article Labels

There are no labels assigned to this post.