Calculating medians on groups (excess cash)

[Ryan Morgan]

Hello all,

I'm new to Stata and to the forum.

I am trying to replicate the method of DeAngelo, DeAngelo and Stulz (2010) with regard to their definition of 'excess cash'. Khieu and Pyles (2012) describe the method as:

'For each sample period, firms are first sorted into three equal-size groups based on total book assets and then three equal-size groups based on the market-to-book ratio. Each observation is then put into one of the nine cross-sectional groups and the median cash level (divided by total noncash assets) of each two-digit SIC industry represents the normal level of cash for each firm within the group for that period. Excess cash is then calculated as actual cash holdings minus this median cash level. We label this variable Excess Cash DDS .'

I have managed to allocate each observation a group for each year as described using the egen, xtile and group commands. I cannot however find a way to progress this to the median cash level for each SIC code without getting a stream of data that would be incredibly labour intensive to assign back to each observation.

What I am looking to achieve is a variable displaying this 'median cash level'. I hope that someone can help!

Best,
Ryan

[Carole J. Wilson]

Does this help you?

bysort SIC_code: egen group_median=median(cash_level)

[Clyde Schechter]

As an aside, giving references like DeAngelo, DeAngelo and Stulz (2010) on this forum is pretty much useless. Stata is a multi-disciplinary Forum, and those who are not in your field will have no idea what journal this might be from, and no idea how to access the article. As it happens, your description of the problem seems sufficient to enable people to understand what you want to do and help you figure it out. So references are superfluous. In a more complicated situation where the approach would be too lengthy to describe in a forum post, a complete reference would be needed. So show all, or nothing at all, when it comes to references.

[Ryan Morgan]

Hi Carole,

Thank you for your reply. That does help, I appreciate it.
I have instead used:

bysort sicf2 (SIC Code) fyear (firm year) x3_atmb (group for assets & market book value): egen group_median = median(cash)

I believe that this has successfully achieved the method above. Do you have any thoughts on it?


Hi Clyde,
Thank you for your message. I will be sure to do that in the future, it is unfortunately a matter of habit!

Appreciate your assistance both!

[Carole J. Wilson]

That seems right to me.