Generating a new variable using 2 variables that are in different rows for the same record

[Gabriela Vintimilla]

I have a data set where there are up to 5 events and each event has several instruments. Therefore I have several rows for each record. Something like this:

RECORD	EVENT	Date instrument 1	Question instrument 1	Question2 instrument 1	Question instrument 1	Date instrument 2	Question instrument 2	Question instrument 2	Question instrument 2	Question instrument 2
1	1	yyy/mm/dd		yyy/mm/dd
1	2
1	3	-	-	-	-	yyy/mm/dd
2	1
2	2

Let's say I want to calculate the age of participant 1 at the time of the date of instrument 2 recorded during event 3. During event 1 and on the first instrument there was a question asking about date of birth. So the birthday was recorded in event 1, instrument 1, and question 2. Now I want to calculate how old record 1 was at event 3.
I want a new variable as follows:
days of life = date instrument 2 (event 3) - date question2 instrument 1 (event 1)
When I try, the new variable has no values because the 2 dates are in different rows.

[George Ford]

egen date1 = max(dateinstrument1), by(record)
egen date2 = max(dateinstrument2), by(record)

you can then use date functions to get the spread.

[Nick Cox]

Does participant correspond to record?

[George Ford]

if it's always a difference between record 1 and 3,

g spread = dateinstrument2[3] - dateinstrument1[1]

[Hemanshu Kumar]

#2 will work best when variables such as dateinstrument1 and dateinstrument2 are recorded only once for a participant/record, across all events. Is this true?

Without more information, a more general approach I would suggest is to reshape wide, so that you have only a single observation/row for a participant, and so can play with all their information more easily.