I have an unbalanced panel data set with daily data similar to this, for n countries:

``quarter date id trade trade_quarterly rank i1 1 1 1 2 1 101 2 1 1 2 1 171 1 2 1 1 2 122 1 1 0 1 1 52 2 1 1 1 1 92 1 2 0 1 1 142 2 2 1 1 1 82 2 3 0 0 3 6``

Given are the first 4 columns.

Interested in information i, I would now like to keep only the 2 most traded ids for each quarter. I aggregated quarterly trades with

``bysort quarter id: egen trade_quarterly =sum(trade)``

to get column 5.

To calculate column 6, I tried using

``bysort quarter id : egen xx =rank(trade_quarterly), "option"``

which does not appear to produce the correct solution.

(Note that since the values are aggregated within ids ranking with `rank(xx), field` would produce a wrong rank for the following id)

The last line of syntax

``````bysort quarter id : egen xx =rank(trade_quarterly), option
``````

is not legal, as the literal text `option` is itself not an option. More generally, `egen, rank()` can not help here with your present data structure.

But consider this, just a matter of a `collapse` to sums (totals) and then keeping only the largest two (the last two after sorting) within cross-combinations:

``````clear
1       1    1    1    2
1       2    1    1    2
1       1    2    1    1
2       1    1    0    1
2       2    1    1    1
2       1    2    0    1
2       2    2    1    1
2       2    3    0    0
end
bysort quarter (trade) : keep if (_N - _n) < 2
list, sepby(id quarter)

+----------------------+
|----------------------|
1. |       1    2       1 |
|----------------------|
2. |       1    1       2 |
|----------------------|
3. |       2    1       1 |
|----------------------|
4. |       2    2       1 |
+----------------------+
``````

If you don't want to `collapse`, then extra technique is to tag each `id`-`quarter` pair just once when ranking.

``````clear
1       1    1    1    2
1       2    1    1    2
1       1    2    1    1
2       1    1    0    1
2       2    1    1    1
2       1    2    0    1
2       2    2    1    1
2       2    3    0    0
end
egen sum = total(trade), by(quarter id)
egen tag = tag(quarter id)
bysort tag quarter (trade) : gen tokeep = tag & (_N - _n) < 2
bysort quarter id (tokeep) : replace tokeep = tokeep[_N]
list if tokeep, sepby(quarter)

+--------------------------------------------------+
| quarter   date   id   trade   sum   tag   tokeep |
|--------------------------------------------------|
1. |       1      2    1       1     2     0        1 |
2. |       1      1    1       1     2     1        1 |
3. |       1      1    2       1     1     1        1 |
|--------------------------------------------------|
4. |       2      2    1       1     1     0        1 |
5. |       2      1    1       0     1     1        1 |
6. |       2      2    2       1     1     0        1 |
7. |       2      1    2       0     1     1        1 |
+--------------------------------------------------+
``````

Note, in agreement with @William Lisowski's comment, that the largest two may not be uniquely identifiable in the presence of ties.

Top