How to Create New Column Based on Values from Two Other Columns?

[#1]

Hello:
I'm a long time JMP user, and have just started delving into scripting and use of the formula editor. I have a large table (approx 10K rows). I have several columns of interest:

• Patient_ID: unique identifier for each patient
• Time: timepoint at which lab sample was collected
• LAB_VALUE: value for the lab parameter with 3 discrete possibilities (NORMAL, MILD, SEVERE)
• SEX: Sex of the patient

I'd like to create a new column called "STATUS" based on the following:

• If Baseline value for Lab_Value = Normal, and at least one post-baseline value is MILD or SEVERE then flag as "1" in STATUS column
• If Baseline value for Lab_Value = Mild, and at least one post-baseline value is SEVERE, then flag as "2" in STATUS column
• If Baseline value for Lab_Value = Severe, and at least one post-baseline value is MILD or NORMAL, then Flag as "3" in STATUS column

This gets a little complicated because some patients drop out before getting all of the labs done. Some patients have data until endpoint (Day 372), and others don't. So it makes it difficult to write a script. Could you assist in telling me how to do this? I tried to create a bunch of if statements using the formula feature but couldn't get it to work. Any help would be appreciated.

Many thanks

[#2 · Accepted solution]

Here is one way of setting the values for STATUS.  It isn't very efficient code, but it should work.  I was not able to fully test it, but the logic is pretty straight forward.

dt = current data table();
dt << New Column( "STATUS", formula(
		If( :TIME == "BASELINE",
			theRow = Row();
			thePatient_ID = :PATIENT_ID;
			If(:LAB_VALUE == "NORMAL",
				If( N Rows( dt << get rows where(
						Row()>theRow &
						thePatient_ID == :PATIENT_ID &  
						(:LAB_VALUE  == "MILD" | :LAB_VALUE == "SEVERE")
					)
				) > 0
				,
				1,
				)
			,
			:LAB_VALUE =="MILD",
				If( N Rows( dt << get rows where(
						Row()>theRow &
						thePatient_ID == :PATIENT_ID &
						:LAB_VALUE == "SEVERE"
						)
					) > 0
				,
				2
				)
			,
			:LAB_VALUE == "SEVERE"
				If( N Rows( ct << get rows where(
						Row()>theRow &
						thePatient_ID == :PATIENT_ID &
						(:LAB_VALUE  == "MILD" | :LAB_VALUE == "NORMAL")
						)
					) > 0
				,
				3
				)
			)
		)
	)
);

I can imagine a more efficient way to do this, but it would not be implemented as a formula, but as a script using some of the JMP Platforms.

[#3]

Dear Jim:

Thank you, this was so incredibly helpful. I can't thank you enough. Your script worked for me.

I spent the last day trying to figure out how to append to your script. In particular I want to create a 4th category for LAB_VALUE. If a patient's lab value status doesn't meet any of the 3 conditions below, then I'd like to flag it as a fourth category "4".

I tried to add ",4)" to the last if statement, but it wouldn't work. The script editor kept complaining about missing , or ). Or it wouldn't evaluate some of the rows. Any help you can offer would be greatly appreciated.

---

@txnelson wrote:

Here is one way of setting the values for STATUS.  It isn't very efficient code, but it should work.  I was not able to fully test it, but the logic is pretty straight forward.

dt = current data table();
dt << New Column( "STATUS", formula(
		If( :TIME == "BASELINE",
			theRow = Row();
			thePatient_ID = :PATIENT_ID;
			If(:LAB_VALUE == "NORMAL",
				If( N Rows( dt << get rows where(
						Row()>theRow &
						thePatient_ID == :PATIENT_ID &  
						(:LAB_VALUE  == "MILD" | :LAB_VALUE == "SEVERE")
					)
				) > 0
				,
				1,
				)
			,
			:LAB_VALUE =="MILD",
				If( N Rows( dt << get rows where(
						Row()>theRow &
						thePatient_ID == :PATIENT_ID &
						:LAB_VALUE == "SEVERE"
						)
					) > 0
				,
				2
				)
			,
			:LAB_VALUE == "SEVERE"
				If( N Rows( ct << get rows where(
						Row()>theRow &
						thePatient_ID == :PATIENT_ID &
						(:LAB_VALUE  == "MILD" | :LAB_VALUE == "NORMAL")
						)
					) > 0
				,
				3
				)
			)
		)
	)
);

I can imagine a more efficient way to do this, but it would not be implemented as a formula, but as a script using some of the JMP Platforms.

---