So, I have rows for every clinic visit date. Right now, all clinic visit dates are recorded in the same column under "DateSeen." Instead, I need a single row for every patient ID and I need to create a separate column for their each visit (chronological). For example "first visit, second visit, third visit," etc up to fifth visit.
Can someone help? For obvious reasons, I can't share the actual data but here is some dummy data.
| ID | DateSeen |
| 8 | 5/3/2016 |
| 8 | 6/20/2015 |
| 1 | 12/3/2013 |
| 10 | 2/28/2012 |
| 9 | 9/13/2013 |
| 9 | 8/23/2014 |
| 6 | 7/10/2008 |
| 3 | 1/1/2014 |
| 2 | 6/15/2011 |
| 5 | 4/1/2011 |
| 1 | 2/26/2014 |
| 3 | 4/30/2016 |
| 1 | 8/6/2015 |
| 9 | 5/9/2009 |
| 4 | 10/1/2011 |
| 2 | 7/23/2011 |
| 8 | 10/5/2013 |
| 8 | 1/10/2009 |
| 1 | 8/11/2016 |
| 9 | 6/6/2015 |
| 7 | 9/20/2014 |
| 3 | 11/21/2015 |
| 2 | 3/1/2014 |
| 9 | 1/8/2010 |
| 6 | 8/1/2015 |
| 3 | 8/6/2016 |
| 3 | 11/9/2013 |
| 4 | 8/27/2011 |
| 1 | 7/11/2014 |
| 10 | 10/31/2009 |
| 2 | 4/1/2015 |
| 2 | 8/11/2007 |
| 5 | 7/18/2014 |
| 9 | 10/15/2013 |
Apparenty I caused a little confusion. In the script I included, I had included a step that creates the data table you had included in your first statement. That portion of my code was just to make what I replied with capable of running with some sample data. The part of the script that actually does the work, can run with an unlimited number of patients. So all you need to do is to create the large data table, and to run the last part of the script I created. Just make sure that the patient ID column is called "ID" and the date column is called "Date/Seen". Also, the "Date/Seen" column needs to be a numeric JMP Date column.
// Sort the table so that all visits for a given individual are together
// and in asscending order of the visit date
dt << Sort( By( :ID, :Name( "Date/Seen" ) ), Order( Ascending, Ascending ), Replace table( 1 ) );
// Create a new column which is just a simple counter of the visit number
dt << New Column( "Visit Number",
Numeric,
"Continuous",
Format( "Best", 12 ),
Formula(
If( Lag( :ID ) != :ID,
num = 1,
num = num + 1
);
num;
)
);
// Split the data by the visit number
dt << Split(
Split By( :Visit Number ),
Split( :Name( "Date/Seen" ) ),
Group( :ID ),
Sort by Column Property
);
You are amazing!! Thank you so much! This was stressing me out! I hope you get a free upgrade to first class on your next flight. 
Here is a simple script that does what you want. If you follow the steps in the script, doing these 3 operations are easy to do interactively
Names default to here( 1 );
// Input the original data table
dt = New Table( "Patient Visits",
Add Rows( 34 ),
New Column( "ID",
Character,
"Nominal",
Set Values(
{"8", "8", "1", "10", "9", "9", "6", "3", "2", "5", "1", "3", "1", "9", "4", "2", "8", "8", "1",
"9", "7", "3", "2", "9", "6", "3", "3", "4", "1", "10", "2", "2", "5", "9"}
)
),
New Column( "Date/Seen",
Numeric,
"Nominal",
Format( "m/d/y", 12 ),
Input Format( "mmddyyyy" ),
Set Selected,
Set Values(
[3545078400, 3517603200, 3468873600, 3413232000, 3461875200, 3491596800, 3298492800, 3471379200,
3390940800, 3384460800, 3476217600, 3544819200, 3521664000, 3324672000, 3400272000, 3394224000,
3463776000, 3314390400, 3553718400, 3516393600, 3494016000, 3530908800, 3476476800, 3345753600,
3521232000, 3553286400, 3466800000, 3397248000, 3487881600, 3339792000, 3510691200, 3269635200,
3488486400, 3464640000]
)
)
);
// Sort the table so that all visits for a given individual are together
// and in asscending order of the visit date
dt << Sort( By( :ID, :Name( "Date/Seen" ) ), Order( Ascending, Ascending ), Replace table( 1 ) );
// Create a new column which is just a simple counter of the visit number
dt << New Column( "Visit Number",
Numeric,
"Continuous",
Format( "Best", 12 ),
Formula(
If( Lag( :ID ) != :ID,
num = 1,
num = num + 1
);
num;
)
);
// Split the data by the visit number
dt << Split(
Split By( :Visit Number ),
Split( :Name( "Date/Seen" ) ),
Group( :ID ),
Sort by Column Property
);
