Producing a table with statistics by month and save it to excel file - tabstat question

[Nuno Soares]

Hi everyone,

I'm trying to produce a table with the number of observations that each of 30 variables has for each month and for a period of about 360 months. I'm able to produce the table with tabstat (with a minor issue that I'll explain later) but Stata brakes the results into several sub-tables has it can't show the results in one single table in the output window. My idea is to save the results to an excel file, but can't figure it out how to do it.

I've been using:
tabstat x1-x30, by(datem) col(variables) stat(n)

I've tried using the collapse and the putexcel solutions used here, but they do not seem to work. The collapse solution seems to work fine for a limited number of variables. For the number of variables I need it takes a bit of time to write the command and if I need to change the variables I need to rewrite everything again. The putexcel solution does not seem to work with the by option.

The final issue is that, when producing the table instead of showing the datem as 2000m1, 2000m2, etc, the internal number is shown, I've tried to create a string variable but it is based on Stata internal number for month...

I just need to get a table like this:
datem x1 x2 x3 ....
2000m1 n_x1 n_x2 n_x3
2000m2 n_x1 n_x2 n_x3

any ideas on how to solve this?

Thanks!

[Nick Cox]

The number of observations is the same for each month, regardless of other variables.

I think what you mean is the number of non-missing values.

The comments in the FAQ Advice on reports like "do not seem to work" bite here. Without a precise code example and without a simplified data example that reproduces your problem, we have to work hard to see what the problem is.

I don't see what the problem is in rewriting code. The varlist concerned needs to be specified at most once, and any varlist trick that works is fine (e.g. wildcards, variable ranges).

You should be able to adapt this therefore:

clear 
input id month x1 x2 
1 500  1  2 
2 500  .  3
3 501  4  . 
4 501  5  . 
end 

foreach v of var x* { 
    egen count`v' = count(`v'), by(month) 
} 

collapse count* , by(month) 
 

     +---------------------------+
     | month   countx1   countx2 |
     |---------------------------|
  1. |   500         1         2 |
  2. |   501         2         0 |
     +---------------------------+

To get that into Excel (not excel), use any export command that appeals.

Notes:

To get a string month variable, use some variant on

gen mdate = string(month, "%tm")

If collapse is too slow,

bysort month : keep if _n == 1

is an alternative, as by construction the count variables are constant in each month.

[Nuno Soares]

thanks Nick!
Works perfectly!