Hi Everyone,
I was hoping somebody could help me with my following problem:

I have an unbalanced panel dataset consisting of firms (permno) and monthly dates (month_id).

I am trying to assign to each deviation (defined as current reporting variable minus the mean of the previous three quarters) a percentile value relative to all stocks’ deviations in the previous year.

The following code solved the problem but is super slow as I am working with a huge dataset:

foreach x in cheq_deviation oiadpq_deviation req_deviation saleq_deviation capxy_deviation icaptq_deviation invtq_deviation {
g `x'_rank = . //rank compared with all permno's in that 1 year day window
g `x'_nobs = . //contains total considered in ranking
loc d = 12 //set window length here

su month_id, meanonly
quietly forval n = `r(min)'/`r(max)' {
count if month_id == `n'
if r(N) {
tempvar i j
egen `i' = rank(`x') if inrange(month_id, `n' - `d' + 1, `n'), track
replace `x'_rank = `i' if month_id == `n'
egen `j' = count(`x') if inrange(month_id, `n' - `d' + 1, `n')
replace `x'_nobs = `j' if month_id == `n'
drop `i' `j'
}
}
}


Using Mata would probably be way faster. The following code does not properly work but would look somehow like this:


mata:
mata clear

real rowvector myrank(real matrix X)
{
real matrix R
R = mm_ranks(X,1,1)
return(rows(X), R[rows(X)])
}
end
rangestat (myrank) cheq_deviation, interval (month_id -12 0) casewise

I need the relative ranking across ALL firms for the previous year. The number of observations when running in the code are perfectly fine. However, I want different rankings for different firms. Within the current (latest) month of the event window all ranks are the same across all firms, even though the values for the deviations are different across firms.

Also can I combine mata with the "foreach" loop?


Thank you very much in advance!

Best,
Michael