How to find lowest date within set of days for each group

[#1]

Hi,

I am trying to find a way to make splitting my data by which visit it is is easier. I have data in the following format (this is of course, fake data):

Name	Date	Diagnosis	Provider
Bob	1/1/18	Rash	Dr. Sally
Bob	1/1/18	flat foot	Dr. Jane
Bob	7/1/18	Psoriasis	Dr. Sally
Mary	1/1/18	sprained ankle	Dr. Jane
Louis	8/1/18	Rash	Dr. Sally

I want to create a new column which has the "encounter number" for each one. So for Bob, both 1/1/18 visits would be labelled "1" in the new column, but his 7/1/18 visit would be labeled "2," and Louis' 8/1/18 visit would also be labelled "1." I would like to keep the data on diagnosis and provider for both, so that I can split the data by the provider later on. My thinking is an "if (Date is minimum for Name), then "1") then iterate up to whatever encounter number this is, which is a bit unwieldy.

Any thoughts on the best way to do this? Currently using JMP Pro 16.

[#2 · Accepted solution]

The Col Cumulative Sum() function will give you the results you want

Use the following formula to generate the values for your encounter number column

Col Cumulative Sum( 1, :Name, :Date )

[#3]

Hi! Thanks for your reply. This works in theory, but in your example you can see that the 1/1/18 visits are 1/2 when they should both be "1" and the 7/1/18 visit isn't "2." Ideally I want a function or column which can tell me if this is the patients first time to the hospital, second time, etc.

Any tips or tricks to get this there?

Thanks,

Liz

[#4]

Try adding the Provider into the Grouping variables for the Col Cumulative Sum() function

Col Cumulative Sum( 1, :Name, :Provider, :Date )