Sorry about the barrage of questions but I am struggling with this. I have a file which look like this

IDnumber date_of_exam SCI MCI dementia
1 16dec2003 1 0 0
1 20nov2010 0 0 1
1 20dec2012 0 0 1
2 10jan2004 0 1 0
2 15feb2006 0 1 0
2 19apr2010 0 0 1
2 20dec2012 0 0 1
3 02mar2013 0 1 0
4 04apr2014 0 0 1
5 19sep2013 0 1 0
5 20aug2016 - - -
6 20jan2004 0 1 0
6 25feb2006 0 0 1
6 02apr2010 - - -
6 20dec2012 - - -


date_of_exam is the date of examination.

SCI (subjective cognitive impairment) MCI (mild cognitive impairment) and dementia are diagnoses. I want to compare the diagnosis at the first date_of_exam with the diagnosis at the very last date_of_exam for each IDnumber.

So in the case of IDnumber 1, I would like stata to tell me that on 16dec2003 diagnosis was SCI and on 20dec2012 the diagnosis was dementia.

Similarly in the case of IDnumber 2 I would like stata to tell me that the diagnosis was MCI on the first date_of_exam 10jan2004 and the diagnosis on the last date_of_exam diagnosis was dementia on 20dec2012.

Note please that some IDnumber s have only one date of exam like in IDnumber 3 and 4.

In the case of IDnumber 5 , the diagnosis recorded on the first date_of_exam was MCI but there is no diagnosis recorded on the last date_of_exam (missing data). In this dataset a missing value in the last date_of_exam means that the diagnosis stayed the same as the preceding date_of exam. Therefore here the diagnosis on the last date_of_exam 20aug2016 was MCI as recorded on 19sep2013 and I need to know that.


Also in IDnumber 6 first date_of_exam recorded MCI on 20jan2004. Last date_of_exam diagnosis recorded as missing on 20dec2012. In this dataset this means that on last date_of_exam 20dec2012 the diagnosis was dementia as recorded last on 25feb2006. I would like stata to tell me on first date_of_exam the diagnosis 20jan2004 was MCI and on the last day of exam 20dec2012 the diagnosis was dementia.


Hope this is clear. Would really appreciate your help with the best way forward with this dataset.

Many thanks for your help 😊