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
- :
- Discussions
- :
- How do I assign a start date?

Topic Options

- Start Article
- 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
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Jul 24, 2017 7:32 AM
(4803 views)

I have a longitudinal study with sites entering on different dates. To make them comparable, I need to assign each entry date as "1" and the second data collection point as "2" and so on. The data are in long format and there are a lot of missing data (missed data collection)

The data look like this, I want to generate "Count":

ID Date Var1 Count

001 Jan-1 . .

001 Feb-1 . .

001 Mar-1 10 1

001 Apr-1 13 2

002 Jan-1 8 1

002 Feb-1 10 2

002 Mar-1 . .

I used a JRP formula, but it counts every line whether the first measure is missing or not.

1 ACCEPTED SOLUTION

Accepted Solutions

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

I have attached a new version of the formula. It should work as your latest modification was stated.

Please note, the Discussion Community is not intended as a "Code Factory". It is there to help users solve problems so they can learn and from that be able to solve their own problems.

JMP provides a wealth of documentation. Please go to

Help==>Books==>Using JMP

and

Help==>Books==>Scripting Guide

Jim

10 REPLIES 10

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

Re: How do I assign a start date?

Here is one version of the formula that will work

```
If( Lag( :ID ) != :ID,
If( Is Missing( :Var1 ) == 0,
TheCount = 1,
TheCount = .
),
If( Is Missing( :Var1 ) == 0,
TheCount = Sum( TheCount, 1 )
)
);
TheCount;
```

Jim

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

Re: How do I assign a start date?

This formula gives me the following error

"Illegal Reference (recursion) 1 times At rows:2"

I follow the formula, but I can't figure out where the error is.

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

Re: How do I assign a start date?

attached is a data set with the formula set for column Count

Jim

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

Re: How do I assign a start date?

Hmm. Same error message. I made up the data I gave you, but it's the same idea, except ID is character in my data. Would that make a difference?

Also, noticed that your formula stopped counting at 2.

Thanks!

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

Re: How do I assign a start date?

I appears that you opened the data table I attached. And from what you stated, you can see the values for column Count. If you are seeing those values, then the formula is working. The reason the count only goes to 2 is that the counting resets when the ID column changes, the counter restarts, just like in the example you provided.

Are you changing anything in the formula I am providing? The recursive issue can be happening if you are attempting to use the name "Count" in the formula for the column named "Count". That is why I use the variable name "TheCount" for the calculation. The formula will use the last item calculated to pass to the value to use for the formula

Finally, what version of JMP are you using?

Jim

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

Re: How do I assign a start date?

Jim,

Thanks for following up.

1. Can we make theCount column stop counting when the value is missing? In you example, it repeats the last count number, will will presummably do so until the ID number changes.

2. I copied and pasted your formula. The only thing I changed in your formula is the name of the variables so that it exactly matches my data set.

3. The word "count" does not appear in any of my variable names. I renamed my "TheCount" variable to "Column 10" and got the same error message. I've also tried renaming my site variable. Same problem.

4. I'm using JMP Pro 13.1

Is Missing (Var1) == 0 Then TheCount=1

Translates as if Var1 is missing then the new variable (TheCount)=1? But what does "==0" do?

This is what I have, including my variable names.

If( Lag( :SiteID ) != :SiteID,

If( Is Missing( :NozeroValue ) == 0,

:Column 10 = 1,

:Column 10 = .

),

If( Is Missing( :NozeroValue ) == 0,

:Column 10 = Sum( :Column 10, 1 )

)

);

:Column 10;

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

Re: How do I assign a start date?

1. I have attached a new example data table with the new formula that handles what you are requesting

2. Yes, you need to change the :ID column name to whatever the variable name is that restarts the counter, and :Var 1 to whatever the measurement column name is.

3. You should not have changed the name of the Memory Variable called "TheColumn"; This is not a column name, it is just intended to do some calculations in the formula. In the new formula I changed the variable name to "Zippy" in an attempt to show that it is not related to any column name. The only connection to the column that the final listing of the variable at the end of the formula. JMP takes whatever the last thing specified (in this case, the calculated value of Zippy) and passes it to the value to be used for the column the format is being applied to, for the row the formula is currently being worked on.

2. Is Missing() is a function that if the contents of the function ":Var 1" is a missing value, then the function will return a 1. If the value is not missing, it returns a 0. the "==" is a comparison oprator of "Equal To". So "== 0" is asking if the "Is Missing()" function returning a "0", which would say the variable value is a valid value.

Jim

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

Re: How do I assign a start date?

Great! Thank you for the clarification and the revised fomula! I appreciate it.

The revision works EXCEPT if the missing pattern is mixed. For example, see "Site=001" below, my results look like this with the revised formula (Count) and what I want it to give me.

Site date Var1 Count What_I_Want

001 May . . .

001 Jun 10 1 1

001 Jul . . .

001 Aug 12 . 2

001 Sep 15 . 3

002 May 13 1 1

002 Jun 14 2 2

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

I have attached a new version of the formula. It should work as your latest modification was stated.

Please note, the Discussion Community is not intended as a "Code Factory". It is there to help users solve problems so they can learn and from that be able to solve their own problems.

JMP provides a wealth of documentation. Please go to

Help==>Books==>Using JMP

and

Help==>Books==>Scripting Guide

Jim