How to give all cells of one column the same value that is currently in only one cell?

[Roy Steinvoort]

Hi all,

I am struggling with the following situation. I have this data where Annualized monhtly returns are calculated per fiscal year per gvkey (firm).
The problem is that I need all cells covered with the same Annualized monthly return per fiscal year per gvkey but currently only one cell per fiscal year per gvkey is covered.

Attached, you'll find examples of the data. For the first company with gvkey 1004 I already did some work by hand so that you know what I mean.

I know the gvkey, fyear and Annualized returns are duplicates but that is because I have other data in my master data set as well. I first need this and in the end I will drop all duplicates.

Is there anyone out there that knows how to code this in stata? I cannot do this manally for 850,000 observations.

I hope someone can help me out here.

Thanks in advance,

Best,

Roy Steinvoort


Filled out manually:

Also want the cells to be like in the picture above:

[Nick Cox]

Roy:

Please contact the forum administrators to change your registration to "Roy Steinvoort".
http://www.statalist.org/forums/help#realnames explains.

Please see http://www.statalist.org/forums/help#stata on posting readable copies of data examples, not screenshots.

The question title

How to give all cells of one column the same value that is currently in only one cell?

doesn't match the question, but the question itself seems to match a question commonly asked, e.g.

http://www.statalist.org/forums/foru...e-within-group

http://www.statalist.org/forums/foru...data-with-egen

http://www.statalist.org/forums/foru...iables-in-sata

[Roy Steinvoort]

Thanks Nick, it worked for me.

Now I have this other question.

I have 2000 unique firms (identified by gvkey) in my dataset over the period 2007-2013 (fiscal years). Now, I would like to know for every unique firm (hence, per gvkey) what the most recent fiscal year is. This because some firms still have data in 2013 where others went bankrupt in e.g. 2010.

Is there anyone who has an idea on how to code this in stata?

Thanks in advance,

Best,

Roy Steinvoort

[Clyde Schechter]

by gvkey (year), sort: gen most_recent_year = year[_N]

Note: This assumes that firms have no observations beyond their most recent year in the data set. If there are observations but they contain only missing values for other variables, then the code would be different.

[Roy Steinvoort]

Thanks Clyde the code worked for me.

Is there also an extension to this code available which allows me to track the most recent year before lets say 2014. Hence, for the firms that have data available until 2015/2016, stata will say 2013 is the most recent year before 2014.

I need this because some CEOs became CEO only in 2014 or later but since the remainder of my data is only until 2013. Therefore I would like to know what the most recent date was the a person became CEO of a firm before 2014.

Best,

Roy Steinvoort

[Nick Cox]

Yes, Note that an alternative to Clyde's code is

bysort gvkey: egen most_recent_year = max(year)

and this can be extended to look at the latest year, provided that it is before 2014:

bysort gvkey: egen most_recent_year_bf2014 = max(year/ (year < 2014)) 


bysort gvkey: egen most_recent_year_bf2014 = max(cond(year < 2014, year, .))

I give two solutions, and clearly you only need to choose one: the extra code ensures that years 2014 and later are ignored, i.e. those values are treated as if missing, in which case egen will ignore them to the extent possible

Much more can be found on these tricks at http://www.stata-journal.com/sjpdf.h...iclenum=dm0055 Sections 9 and 10.