How to identify the latest date among multiple dates?

[Stine Nielsen]

Hello!

I am working with a dataset with about 25,000 observations for about 5500 unique id’s.

I have several date variables (datevarX) and each id has multiple dates, and for some id’s the datevar is missing.

I am using this code to identify the first date per id:

bysort id (datevar1) : gen seq=_n

If seq==1 & datevar1!=. this is the first date per id.

I need help to identify the latest (opposite of the first) date per id of datevar2

Once I can identify both the first date of datevar1 and the latest date of datevar2 I want to generate a variable that expresses the duration between datevar2 and datevar1 per id., which I think should be straight forward as long as I only have 1 date per id.

I am using Stata/IC 13.1 for Mac.

Kind regards,
Stine Nielsen

[Chris Larkin]

Hi Stine,

Welcome to Statalist! There's a few different ways you can approach your problem. It's best to provide a short data example using dataex (ssc install dataex), so in the future please do.

If I understand your problem correctly though, you can build off what you have already done and use the _N value to determine the maximum date within a group. This should do the trick:

bysort id (datevar1) : gen seq = _n
gen mindate = datevar1 if seq == 1 & datevar1 != .  

bysort id (datevar1) : gen seqmax = _N
gen maxdate = datevar1 if seq == seqmax & datevar1 != .

//You'll then want to take the difference to create the new variable you want

Another option, which is perhaps a little tidier, makes use of the egen functions min() and max()

bysort id : egen mindate = min(datevar1)
bysort id : egen maxdate = max(datevar1)

gen datediff = maxdate - mindate
drop mindate maxdate

Chris

[Nick Cox]

This is an FAQ http://www.stata.com/support/faqs/da...ing-last-date/

Our own FAQ Advice advises

3. What should I do before I post?

Before posting, consider other ways of finding information:

• the online help for Stata
• Stata's search command, which can tell you about all built-in Stata commands, all ado-files published in theStata Journal, all FAQs on the Stata website, www.stata.com, and user-written Stata programs available on the Internet (if you have Stata 12 or earlier, you can use findit to search all these sources at once)

[Stine Nielsen]

Thanks a lot for the quick and useful replies! Sorry I had missed the FAQ on How to generate a variable containing the last of several dates. Both replies very useful. Thank you!