Use value from certain year for all observations within group

[Ina Brandes]

Dear community,

I have a problem with my panel data. It feels like it should be pretty easy to solve, but it doesn't work out for me.
My panel is grouped by companies and years. I also have data about CEOs and corporate culture of the companies. I want to use the value for corporate culture in the year where the new CEO first appeared for all following years. I managed to generate a new variable that indicates what year that was. Now I need to generate a new variable that uses the culture value of year X for all observations for the company with that CEO.

Does anybody have an idea how to do this? Help would be much appreciated!!

[Andrew Musau]

Provide a data example for specific code suggestions.

[Ina Brandes]

Good point, thank you! So I am looking to fill culture_use with the value that is in culture for the year within the panel that is specified by year_use. I put the value that it is supposed to show in italics.

Comp_ID	YEAR	CULTURE	CEO_ID	year_use	culture_use
1	2006	0.21	1	2006	0.21
1	2007	0.19	1	2006	0.21
1	2008	0.47	2	2008	0.47
1	2009	0.45	2	2008	0.47
1	2010	0.43	2	2008	0.47
1	2011	0.44	2	2008	0.47
2	2006	0.77	3	2006	0.77
2	2007	0.79	3	2006	0.77
2	2008	0.84	4	2008	0.84
2	2009	0.82	4	2008	0.84
2	2010	0.81	5	2010	0.81
2	2011	0.79	5	2010	0.81

I hope it makes more sense now! Thanks in advance

[Ali Atia]

bysort comp_id ceo_id (year): egen wanted = min(cond(year==year[1],culture,.))

[Andrew Musau]

With your generated variable, you could also just sort and pick the first value

bys comp_id year_use (year): gen wanted= culture[1]

[Ina Brandes]

Thank you, Ali and Andrew!
You both suggested always taking the first year. But I need the connection to the year in the year_use variable. Culture is calculated by the mean of the current and previous year with

rangestat (count) cult_w (mean) cult_w, by(company_id) interval(year -1 0)
egen year_use = min(year / (control_w_count ==2)), by(company_id ceo_id)

So I'm using the first year where the mean is calculated from 2 values. I don't want to drop the observations with cult_w_count != 2 because then I lose thousands of observations. For the last year for the company it doesn't matter that cult_w_count != 2 because I use the culture value from an earlier year. So I thought about always using the second year, but that also doesn't make sense because if there is a change in CEOs within the company I can already use the first year. Do you know what I mean?
Is there a way to connect to the year_use? Or maybe a possibility to use the second year when the company_id first appears and if there is a new CEO but still the same company, to use the first value?

[Ali Atia]

You can change year[1] to year_use in my code, but as far as I can tell that produces identical results in the provided data example, so it's not clear to me what you're asking for exactly.

[Ina Brandes]

Hey Ali,
thank you so much, it worked! You are right that it doesn't make a difference in my example, but in other cases it does. So thank you for your help