Hello everyone! I really hope someone can help me out with this...

I have a set of data with several thousand rows and 50 columns(each column is a named field).
My first field is named 'PatientUI' for which each value is an 8-digit number. Duplicate values are allowed in this column.

I have another field named 'Stage' for which the possible values are: 0, 1, 2, 3, 4, 5, 6, 7. Duplicate values are allowed in this column.

I want to create a new column with field name 'HighestStage' for which the possible values are 0 and 1. And I want to populate this column by entering a 1 when the row in question has the highest Stage for the PatientUI for that row. And I want to put a 0 for every other row for that PatientUI.

For example, if I have

PatientUI | Stage |
00238574 | 3 |
00238574 | 2 |
00238574 | 4 |
00238574 | 1 |
00238574 | 3 |


I want to create a new column so that I have

PatientUI | Stage | HighestStage |
00238574 | 3 | 0 |
00238574 | 2 | 0 |
00238574 | 4 | 1 |
00238574 | 1 | 0 |
00238574 | 3 | 0 |

And if the highest stage for a patient occurs more than once for that patient, I want to put a 1 in the new column for each occurrence.



Does anyone know how I'd be able to do this? I'd really appreciate any help with this.