identifying the last nonmissing value of a string variable for each group

[johnkim]

Hi, I have a group variable and for each group, I have a string variable of interest (nasdaq). For each group, I would like to identify the last nonmissing value of the string variable and then use it to replace that string variable.
If it weren't for the group variable, I can do:

gen obs=_n
summarize obs if !missing(nasdaq), meanonly
replace nasdaq=nasdaq[r(max)]

Now if I do:

by group: gen obs=_n
by group: summarize obs if !missing(nasdaq), meanonly
by group: replace nasdaq=nasdaq[r(max)]

obviously, the last step fails (since Stata uses the last group's r(max) to repeat the last line for all groups).

My question is, is there a way of combining the last two lines, say

by group: {summarize obs if !missing(nasdaq), meanonly
replace nasdaq=nasdaq[r(max)]}
?
I'd be most grateful for all your help!

Best,
John

[Nick Cox]

This is an FAQ:

http://www.stata.com/support/faqs/da...t-occurrences/

Here is another way to do it given a time variable:

egen when_last_nonmissing = max(time / (!missing(nasdaq)) , by(group)
gen what_last_nonmissing  = nasdaq if time == when_last_nonmissing
bysort group (what_last_nonmissing) : replace what_last_nonmissing = what_last_nonmissing[_N]

Here is another way to do it:

 
bysort group (time) : gen what_last_nonmissing = nasdaq if !missing(nasdaq) 
by group: replace what_last_nonmissing = what_last_nonmissing[_n-1] if missing(nasdaq) & _n > 1 
by group: replace what_last_nonmissing = what_last_nonmissing[_N]

[Nick Cox]

You need an extra ) in the first line of code in my previous.

[johnkim]

Dear Nick,
It works like a charm. Thank you so much!

Best,
John