问题描述:

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

quarter date id trade trade_quarterly rank i

1 1 1 1 2 1 10

1 2 1 1 2 1 17

1 1 2 1 1 2 12

2 1 1 0 1 1 5

2 2 1 1 1 1 9

2 1 2 0 1 1 14

2 2 2 1 1 1 8

2 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 
input quarter date id trade 
 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 
collapse (sum) trade, by(quarter id) 
bysort quarter (trade) : keep if (_N - _n) < 2 
list, sepby(id quarter) 

     +----------------------+
     | quarter   id   trade |
     |----------------------|
  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 
input quarter date id trade 
 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