How can I generate one table sorted by year showing the mean median and number of observations?

[Sophia Marens]

Hello!

I am really new to Stata so I hope this question isn't too easy for this forum but my problem is the following:

I have annually observations of ~500 companies over the last 20 years. What I am looking for is one table that generates the median, mean and number of observations for the cash, leverage and long-term debt in all companies balance sheets.
The structure should be as follows:

Year Cash Average_Cash Median_Cash Average_Leverage (.....)
1992 xxxx xxxx xxxx xxxx
1993 xxxx xxxx xxxx xxxx
xxxx xxxx xxxx xxxx xxxx

I know its possible with "tabstat" and "Stats" to get the statistics for one variable but I didnt find out how to do it with many variables without creating more than one table.

I would really appreciate your help! Thanks in advance.

[Clyde Schechter]

You are asking for a table with 9 columns of figures. It's going to be too wide to print on a normal page unless you shrink the typeface to a very small, barely readable size. I think a better layout, and also very easy to get with version 17's -table- command, would be three columns: one each for cash, leverage and long-term debt, and the years going down the first column, with each "cell" having 3 numbers laid out vertically: the count, the mean and the median. You didn't post example data, but assuming your data set is panel data in long layout, the following code using the grunfeld.dta illustrates how you would do it:

webuse grunfeld, clear

local fortable invest mvalue kstock
table (year) (var), statistic(count `fortable') statistic(mean `fortable') ///
    statistic(median `fortable')

If you find the repeated listing of the names of the statistics disagreeable, adding the -style(Table-1)- option will rid you of those. Also you may prefer to impose your own number of decimal places using the -nformat()- options. See -help table- for details.

[Sophia Marens]

Hello Clyde - thank you very much for your answer and suggestions. My data has indeed the same structure as the grunfeld.dta set. Unfortunately I will have to stick we the format as described in my question (9 columns and 20 rows). Do you think there is a way to realize this? I would be very grateful for your help!

[Clyde Schechter]

webuse grunfeld, clear

collapse (count) n_invest = invest n_mvalue = mvalue n_kstock = kstock ///
    (mean) mean_invest = invest mean_mvalue = mvalue mean_kstock = kstock ///
    (p50) median_invest = invest median_mvalue = mvalue median_kstock = kstock, ///
    by(year)
    
order year *_invest, first
order *_mvalue, after(median_invest)
order *_kstock, after(median_mvalue)

list, noobs clean abbrev(14)