Our World Statistics Day conversations have been a great reminder of how much statistics can inform our lives. Do you have an example of how statistics has made a difference in your life? Share your story with the Community!
Choose Language Hide Translation Bar
Highlighted
Level II

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

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User

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

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.

Jim
2 REPLIES 2
Highlighted
Super User

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

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.

Jim
Highlighted
Level II

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

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.

Article Labels

There are no labels assigned to this post.