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

Coker

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

txnelson

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