Hello,

I have a long dataset of clinic visits that is currently formatted in a strange way, with a separate observation for each record ID that includes information on dates of each clinic visit. I would like to create a variable "date" that has the missing values for each row filled in based on this row. For example, as you can see in the dataex example below, there is a variable repeatn that refers to the nth visit. The dates are listed in three separate date variables (date1, date2, date3) in the row where repeatn=missing. My new variable "date" should equal date1 if repeatn==1. I'm sure there is an easy way to do this but am not sure how to refer to that weird row missing repeatn and the dates. After I create this date variable, I will delete the rows where repeatn=missing for my analysis. Any help is much appreciated!

Sarah


Code:
* Example generated by -dataex-. To install: ssc install dataex
clear
input int record_id float(date1 date2 date3) byte repeatn
 1 22074 22018     . .
 1     .     .     . 1
 1     .     .     . 2
 1     .     .     . 1
 1     .     .     . 2
 1     .     .     . 1
 1     .     .     . 2
 2 22074     .     . .
 2     .     .     . 1
 2     .     .     . 1
 2     .     .     . 1
 3 22074     .     . .
 3     .     .     . 1
 3     .     .     . 1
 3     .     .     . 1
 4 22074     .     . .
 4     .     .     . 1
 4     .     .     . 1
 4     .     .     . 1
 5 22074     .     . .
 5     .     .     . 1
 5     .     .     . 1
 5     .     .     . 1
 6 22078 22064     . .
 6     .     .     . 1
 6     .     .     . 2
 6     .     .     . 1
 6     .     .     . 2
 6     .     .     . 1
 6     .     .     . 2
 7 22078 21739     . .
 7     .     .     . 1
 7     .     .     . 2
 7     .     .     . 1
 7     .     .     . 2
 7     .     .     . 1
 7     .     .     . 2
 8 22078     .     . .
 8     .     .     . 1
 8     .     .     . 1
 8     .     .     . 1
 9 22078 22064     . .
 9     .     .     . 1
 9     .     .     . 2
 9     .     .     . 1
 9     .     .     . 2
 9     .     .     . 1
 9     .     .     . 2
10 22078 21826 21938 .
10     .     .     . 1
10     .     .     . 2
10     .     .     . 3
10     .     .     . 1
10     .     .     . 2
10     .     .     . 3
10     .     .     . 1
10     .     .     . 2
10     .     .     . 3
11 22078 21938     . .
11     .     .     . 1
11     .     .     . 2
11     .     .     . 1
11     .     .     . 2
11     .     .     . 1
11     .     .     . 2
12 22078 21739     . .
12     .     .     . 1
12     .     .     . 2
12     .     .     . 1
12     .     .     . 2
12     .     .     . 1
12     .     .     . 2
13 22078 21739     . .
13     .     .     . 1
13     .     .     . 2
13     .     .     . 1
13     .     .     . 2
13     .     .     . 1
13     .     .     . 2
13     .     .     . 1
13     .     .     . 2
14 22077     .     . .
14     .     .     . 1
14     .     .     . 1
14     .     .     . 1
15 22077     .     . .
15     .     .     . 1
15     .     .     . 1
15     .     .     . 1
16 22077     .     . .
16     .     .     . 1
16     .     .     . 1
16     .     .     . 1
17 22077     .     . .
17     .     .     . 1
17     .     .     . 1
17     .     .     . 1
18 22077     .     . .
18     .     .     . 1
18     .     .     . 1
end
format %dM_d,_CY date1
format %dM_d,_CY date2
format %dM_d,_CY date3