Multiple tests for one patient.

[Jeanine OConnell]

Working with the latest version of Stata. I have the dataset organized in a long format. So the unique identifier has been listed multiple times to account for each time they took a test.

I have a series of unique IDs (people) that I need included in the dataset that have undergone a number of different tests.

1) I need to capture if a test (regardless of Positive or Negative result or if it was test1 or test2) has been done for a patient but only include one test per patient.

However, if there are two tests done, I need to include the test with the earlier date. In excel I would use the MIN IF function but that is slowing down my database too much.

Unique ID	Test1Blood	Test2Sputum	DateTest1	DateTest2
1	Positive	Positive	July 1, 2012	September 23, 2009
1	Negative	Negative	September 30, 2012	July 10, 2010
1	Negative	Positive	September 23, 2014	September 3, 2015
2	Positive	Positive	July 1, 2012	January 23, 2012
2	Positive	Positive	December 12, 2019	January 23, 2014
2	Positive	Negative	January 23, 2020	December 23, 2019
3	Negative	Negative	July 1, 2012	December 23, 2010
3	Positive	Positive	December 28, 2012	December 12, 2012
3	Positive	Positive	December 23, 2019	December 3, 2019

Any help getting the right code would be helpful!

Thanks!

[Carlo Lazzaro]

Jeanine:
welcome to this forum.
Do you mean something along the following lines?

. set obs 3
number of observations (_N) was 0, now 3

. g id=1 if _n!=3
. replace id=2 in 3
. g test=1
. g year=2020
. replace year=2021 in 2
. bysort id (year): gen flag=1 if _n==1
. list

     +-------------------------+
     | id   test   year   flag |
     |-------------------------|
  1. |  1      1   2020      1 |
  2. |  1      1   2021      . |
  3. |  2      1   2020      1 |
     +-------------------------+

. keep if flag==1

. list

     +-------------------------+
     | id   test   year   flag |
     |-------------------------|
  1. |  1      1   2020      1 |
  2. |  2      1   2020      1 |
     +-------------------------+

.