722305_en-US

liliconcom

Label the first event for each patient in a large dataset

I have a medical dataset where each row represents an event for a patient. Some patients have only one event, some have multiple events (which are represented by several rows, each with the same "Tx ID" (see picture below), however, I just want to create a column which labels the first event for each patient.

Basic Data Analysis and ModelingmacOSRe: Label the first event for each patient in a large dataset

Hi @cjw99 -
There are a number of ways to do this. One way that just uses a column formula is to use Col Rank()

 

This ranks the date (earliest is lowest rank) by the ID and checks if it is 1 ( returning 1 or 0 ). 

In a similar data table, you will get this output:

 

The nice thing about Col Rank is that you don't have to have your data table sorted, it will work if you jumble the rows.

Re: Label the first event for each patient in a large dataset

You can accomplish this by

1. Select the TX ID column
2. Go to pull down menu    Rows=>Row Selection=>Select Duplicate Rows
3. Go to the RowState column and right click on one of the selected rows and select   "Invert Selection"
4. In the RowState column, go to one of the rows that was selected after the Inversion, and right click and select   Label/UnLabel.

You will now have selected all first lrows for each different TX ID values.

Re: Label the first event for each patient in a large dataset

Thank You! I knew there must be a simple way to do this

Re: Label the first event for each patient in a large dataset

I don't want to abuse this service, but is there a way to use this function AND specify the rows ranked that meet a second condition from another column?

 

For example....in my dataset, the dates are biopsy dates. Some of these dates are associated with an outcome of interest (kidney transplant rejection) while others are not.

 

What I would like to be able to do is rank all the rows in order if they meet the rejection condition while skipping the other rows.

In the end, I would have a column that has labelled the 1st rejection, 2nd rejection, etc.

 

For example...like below. In this data "Bx Date" = biopsy date, and "TCMR Sequence" refers to T cell-mediated rejection.

Here I have already put them all in sequence in the TCMR sequence column but ideally, I would like to generate a formula that does this automatically for all 3,700 rows in the database.

I am familiar with creating simple If/Then formulas but I always struggle once the formula has to evaluate multiple rows for a given patient.