Sorting with xtile

[Olena Onishchenko]

Dear Stata users

My data is in panel format, sorted by date. For each day stocks are assigned a portfolio. For example on 03/15/2004 there only 3 stocks in the portfolio with respective ids of 73,74,75.

* Example generated by -dataex-. To install: ssc install dataex
clear
input float date str12 IssueCode float p_winner byte stock_id_long double TVN
16145 "KR7003601002" 2 73  16.22814687509245
16145 "KR7007190002" 2 74  36.49090305762375
16145 "KR7007050008" 1 75  .1385657756472274
16145 "KR7009830001" 2  .  4.010736934867263
16145 "KR7016590002" 2  . 1.0417176309731364
16145 "KR7006980007" 2  .  1.198009268161519
16145 "KR7051911006" 2  .  1.153283370665912
end
format %tdNN/DD/CCYY date

I need to sort them further based on TVN into two portfolios with High and Low TVN.

I use the following code:

egen p_winner = xtile(TVN), nquantiles (2) by( stock_id_long )

However, it assigns a portfolio based on TVN for all available stocks rather than those which are in a portfolio. It creates bias for stocks in a portfolio.

How should I fix it so the p_winner appears only for stocks 73,74,75?

Thank you.

[Carole J. Wilson]

I'm not speaking to the wisdom of this approach, but I think this code might be what you want:

xtile p_winner2 = TVN   if !mi(stock_id_long), nquantiles (2)

*if you want to have a separate grouping by date:
bysort date: xtile p_winner2 = TVN   if !mi(stock_id_long), nquantiles (2)

[Olena Onishchenko]

Carole

I indeed need grouping by date, so that stocks are sorted by TVN each day.

I have tried the code you suggested:

bysort date: xtile p_winner2 = TVN if !mi(stock_id_long), nquantiles (2)

It does not work displaying the following error:

xtile may not be combined with by

How to overcome this?

Thank you.

[Carole J. Wilson]

For some reason, I was mistakenly under the impression that egen xtile did not accept -if- conditions. egen xtile will accept both by(date) and if !mi() , so this should work:

egen p_winner3 = xtile(TVN)   if !mi(stock_id_long), nquantiles(2) by(date)

[Nick Cox]

People should be aware that the xtile() function mentioned here is user-written. The package egenmore (SSC) must be downloaded before it can be used.