Subscribe Bookmark RSS Feed

Select patient ID with second clinic visit listed and convert to new variable ("2nd visit")

Coker

New Contributor

Joined:

Feb 7, 2017

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.

  

IDDateSeen
85/3/2016
86/20/2015
112/3/2013
102/28/2012
99/13/2013
98/23/2014
67/10/2008
31/1/2014
26/15/2011
54/1/2011
12/26/2014
34/30/2016
18/6/2015
95/9/2009
410/1/2011
27/23/2011
810/5/2013
81/10/2009
18/11/2016
96/6/2015
79/20/2014
311/21/2015
23/1/2014
91/8/2010
68/1/2015
38/6/2016
311/9/2013
48/27/2011
17/11/2014
1010/31/2009
24/1/2015
28/11/2007
57/18/2014
910/15/2013
1 ACCEPTED SOLUTION

Accepted Solutions
txnelson

Super User

Joined:

Jun 22, 2012

Solution

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
);
Jim
5 REPLIES
txnelson

Super User

Joined:

Jun 22, 2012

Solution

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

New Contributor

Joined:

Feb 7, 2017

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.  :-)

Coker

New Contributor

Joined:

Feb 7, 2017

Hi, thank you for your help! I failed to mention that I need to find out the number of unique client IDs before I run the script. This script assumes there are only 10.

Is there a way to find out the number of unique IDs and perhaps use that array to populate the new client ID column? In the actual data, there are around 150 total rows total and about 85 of those are unique client IDs. These numbers occasionally change as we run new reports. I would really appreciate your help with this.
txnelson

Super User

Joined:

Jun 22, 2012

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

New Contributor

Joined:

Feb 7, 2017

Got it, thank you :-)