Days between date[_n] and date with highest observation

[Huib Jan Meulenbelt]

Dear Statelist,

I have the following dataset in which:
mhigh represents the highest price observed during the month
price represent the closing price of the month
high12m represents the highest price in the last 12 months

* Example generated by -dataex-. To install: ssc install dataex
clear
input long date float(id price mhigh high12m)
1551 6 22.916666      23.5        35
1581 6        23 24.791666        35
1609 6 22.583334    23.625        35
1642 6 24.291666 24.583334        35
1673 6 26.083334 26.083334        35
1704 6 26.208334 27.166666        35
1734 6 28.166666 28.166666 28.166666
1764 6        29 29.166666 29.166666
1795 6 28.083334 29.291666 29.291666
1826 6      26.5 27.333334 29.291666
1855 6 27.916666 27.916666 29.291666
1883 6 26.916666        28 29.291666
1916 6        28 28.166666 29.291666
1946 6    30.375 30.583334 30.583334
1974 6     28.75 30.833334 30.833334
2007 6     25.75 28.666666 30.833334
2037 6        26      26.5 30.833334
2069 6     27.25     27.25 30.833334
2099 6 26.916666  56.66667  56.66667
2128 6 29.833334     30.25  56.66667
2160 6     29.25 31.583334  56.66667
2191 6      31.5 31.833334  56.66667
2222 6 34.333332 35.583332  56.66667
2250 6 33.333332 36.083332  56.66667
2281 6     31.75     32.75  56.66667
2310 6 31.833334        33  56.66667
2342 6 31.666666 32.666668  56.66667
2372 6      33.5 35.833332  56.66667
2401 6        33 35.083332  56.66667
2434 6 28.166666 33.583332  56.66667
2464 6 26.916666 29.583334 36.083332
2495 6     26.25 26.416666 36.083332
2525 6     26.25 28.166666 36.083332
2555 6      25.5 26.583334 36.083332
2587 6 29.083334     29.75 36.083332
2615 6 27.666666     30.75 35.833332
2646 6        32 32.666668 35.833332
2674 6 33.666668 33.666668 35.833332
2707 6 32.666668 35.416668 35.833332
2737 6 34.666668 35.166668 35.416668
2768 6 36.083332      38.5      38.5
2799 6 33.333332      36.5      38.5
2828 6 32.916668 34.416668      38.5
2860 6 31.583334     35.75      38.5
2890 6 29.833334 31.333334      38.5
2919 6 29.916666 30.333334      38.5
2952 6 28.666666 30.916666      38.5
2981 6     27.25 29.333334      38.5
3010 6 26.583334 28.333334      38.5
3042 6        32        32      38.5
end
format %d date

Now, I would like to create a new variable which represent the date on which the new 12-month high occurred. So, this variable should be 1551 for the first 6 rows. For row 7 it should be 1734. For rows 8 to 13 it should be equal to 1764 etc

I tried the following

 
 egen high12m_date = max(cond(migh==high12m), date, .)), by(id)

However, in this case, if there is more than one date on which the maximum price is achieved, the last of those dates will be selected. So, for each id I only have 1 date. However, this is not what I desire as described earlier.

Thanks your help in advance!!

[Clyde Schechter]

Try this:

sort date
gen wanted = date if high12m != high12m[_n-1]
replace wanted = wanted[_n-1] if missing(wanted)
format wanted %td