Collapse (count) if

[Olena Onishchenko]

Dear Stata Users

My data is in a panel format at the daily frequency (id, date). I run time-series regression for each firm (id). I obtain beta coefficients b_X1; b_X2; b_X3 and corresponding t-stats t_X1, t_X2, t_X3 for each out of 1121 firms (id).

I need to calculate the mean beta coefficient.I use the following code:

collapse (mean) b_X1; b_X2; b_X3

Beside mean betas I also need to calculate:
1. Percent of positive beta coefficients for each coefficient (out of 1121)
2. Percent of positive and significant beta coefficients for each coefficient (out of 1121; p<0.1)
3. Percent of negative and significant beta coefficients for each coefficient (out of 1121; p<0.1)

How do I alter the code so that I can count three of the above?

Thank you.

[Scott Merryman]

Determine the number of positive values before the -collapse- command. For example:

sysuse auto,clear
statsby, by(rep) clear : regress mpg gear turn
l
foreach var in gear turn {
    gen sign_`var' = cond(sign(_b_`var')==1, 1,0)
    }
collapse (mean) _b* (sum) sign_* (count) N = _b_gear
gen percent_pos_gear = sign_gear/N
gen percent_pos_turn = sign_turn/N
l _b* percent*

[Olena Onishchenko]

Scott

Thank you. Your code works.

Can I ask you kindly how to incorporate one more condition? In particular, I need not only positive coefficients but positive (negative) and significant. My regression output contains t-stats.
How do I alter the above code to make the following condition:

1.give value of 1 if t-stat>0 and t-stat>1.65
2. give value of 1 if t-stat<0 and t-stat<-1.65

Thank you.

[Scott Merryman]

Add more conditional statements to accumulate the counts that you need:

sysuse auto,clear
drop if rep == 1
statsby _b _se, by(rep) clear  noisily: regress mpg gear turn
foreach var in gear turn {
    gen sign_`var' = cond(sign(_b_`var')==1, 1,0)
    gen pos_sig_`var' = cond(_b_`var'/_se_`var' > 1.65, 1,0)
    gen neg_sig_`var' = cond(_b_`var'/_se_`var' < -1.65, 1,0)
}
collapse (mean) _b* (sum) sign_* (sum)  pos_sig_*  neg_sig_* (count) N = _b_gear
gen percent_pos_gear = sign_gear/N
gen percent_pos_turn = sign_turn/N
gen per_pos_sig_gear = pos_sig_gear/N
gen per_pos_sig_turn = pos_sig_turn/N
gen per_neg_sig_gear = neg_sig_gear/N
gen per_neg_sig_turn = neg_sig_turn/N

l _b* per*, ab(20)