Drop observations of secondary share classes for multiple firms.

[Shaquille Wijngaarde]

Hi Statalisters,

I'm relatively new to Stata.

I need your help with some coding in stata. See -dataex- example below.

I have daily data on the closing stock price of all firms in the CRSP data base from 2006-2017.

Right now, my data is sorted on multiple shrcd permco bdate.

Using the following code, I created the multiple variable that indicates firm's that have duplicate observations on a specific day, i.e. they have multiple share classes trading.

duplicates tag permco bdate, generate(multiple)

I want to have only one daily observation per unique firm (permco) for their primary share class, so that I can declare the data as panel data.

Thus, I want to write a code that drops all firm-day observations that correspond to the non-primary share class of the firm, for all firms that have multiple share classes outstanding.

The challenges here are:
I can't use shrcd (=share code) because these are mostly the same (i.e. 11) for my data.
I can't use shrcls (=share class) because a quick inspection of my data reveals that firms do not consistently indicate their primary share class as class 'A'. Plus, shrcls often has missing values.
I can't use shrout (=shares outstanding) because although it is sometimes obvious from the number of shares outstanding, which share class is the primary. This is not always the case.
I can't use prc (=closing price) because although CRSP indicates the secondary shares buy giving negative values, this is not always the case.

Does anyone know how I could tackle this?

To elaborate further, in the first three rows of the -dataex- you see the same firm (permco: 54311) for a specific date (bdate: 2154) and it has shrcls A, B and C.
prc is roughly the same and both class A and C have a reasonable amount of share outstanding, so I can't tell which one is the primary share class.

* Example generated by -dataex-. To install: ssc install dataex
clear
input long(permco permno) float(bdate prc) long shrout byte shrcd str1 shrcls int multiple
54311 14811 2154   47.27 228666 11 "C" 2
54311 13758 2154   49.99   9874 11 "B" 2
54311 13757 2154   48.94 104459 11 "A" 2
54311 14811 2155   46.49 228666 11 "C" 2
54311 13758 2155   49.36   9874 11 "B" 2
54311 13757 2155   48.26 104459 11 "A" 2
54311 13758 2156 -49.075   9874 11 "B" 2
54311 14811 2156    46.4 228666 11 "C" 2
54311 13757 2156   47.98 104459 11 "A" 2
54311 13757 2157   48.21 104459 11 "A" 2
54311 14811 2157    47.1 228666 11 "C" 2
54311 13758 2157 -49.365   9874 11 "B" 2
54311 13758 2158      49   9874 11 "B" 2
54311 14811 2158   47.82 228666 11 "C" 2
54311 13757 2158   48.39 104459 11 "A" 2
54311 13758 2159  48.205   9874 11 "B" 2
54311 13757 2159   47.05 104459 11 "A" 2
54311 14811 2159      47 228666 11 "C" 2
54311 14811 2160   46.54 228666 11 "C" 2
54311 13758 2160  -47.71   9874 11 "B" 2
end
format %tbtrade_day bdate

[William Lisowski]

You realize, I hope, that this isn't really a question about coding in Stata. If you cannot describe what you would do to choose between 54311/A and 54311/C in your data, we cannot provide code to do what you want but cannot express, and there is no Stata command that has that sort of knowledge built into it.

Statalist does have a lot traffic in questions about financial data, so perhaps someone here will be able to address your question from their knowledge of CRSP. Perhaps the answer is to choose Class A, because in general it is the first class of stock issued and the least restrictive. But you say your data show that firms do not consistently indicate their primary share class as Class A. In those cases, what caused you to decide that Class A is not the firm's primary class? You have not told us how the "primary share class" is defined.

Barring that, if you are at an academic institution you may want to discuss this question with an appropriate faculty member.

[Phil Bromiley]

Go back to the CRSP documentation (WRDS if you are accessing it through WRDS). Somewhere, it will discuss this issue. Once you know the criterion you want to implement, we can help you.

[Shaquille Wijngaarde]

Go back to the CRSP documentation (WRDS if you are accessing it through WRDS). Somewhere, it will discuss this issue. Once you know the criterion you want to implement, we can help you.

Hi Phil,

Thank you for the reply. My apologies. Somewhere along the way in describing my problem, I forgot that I should actually present an idea so that I can get help with the coding. I am doing some additional research and will come back to you ASAP!

[Shaquille Wijngaarde]

Go back to the CRSP documentation (WRDS if you are accessing it through WRDS). Somewhere, it will discuss this issue. Once you know the criterion you want to implement, we can help you.

Hi Phil,

Sorry for the delay. Went through the documentation. I would like to use shares outstanding (shrout) to identify the primary share class and drop the other share classes i.e. I would have to tell Stata to check for every firm-daily observation that has multiple share classes which of the observations have the least amount of shares outstanding and drop them. So in my -dataex- the code would drop the A and B share classes for example.

How could I code this?

Kind regards.

[William Lisowski]

I believe this does what you asked for: it generates todrop = 1 for the observations that are not the largest shrout for the firm/day. In case there are ties, the highest permno will be selected, since there doesn't seem to be anything else that will be guaranteed to be distinct within each permco.

* Example generated by -dataex-. To install: ssc install dataex
clear
input long(permco permno) float(bdate prc) long shrout byte shrcd str1 shrcls int multiple
54311 14811 2154   47.27 228666 11 "C" 2
54311 13758 2154   49.99   9874 11 "B" 2
54311 13757 2154   48.94 104459 11 "A" 2
54311 14811 2155   46.49 228666 11 "C" 2
54311 13758 2155   49.36   9874 11 "B" 2
54311 13757 2155   48.26 104459 11 "A" 2
54311 13758 2156 -49.075   9874 11 "B" 2
54311 14811 2156    46.4 228666 11 "C" 2
54311 13757 2156   47.98 104459 11 "A" 2
54311 13757 2157   48.21 104459 11 "A" 2
54311 14811 2157    47.1 228666 11 "C" 2
54311 13758 2157 -49.365   9874 11 "B" 2
54311 13758 2158      49   9874 11 "B" 2
54311 14811 2158   47.82 228666 11 "C" 2
54311 13757 2158   48.39 104459 11 "A" 2
54311 13758 2159  48.205   9874 11 "B" 2
54311 13757 2159   47.05 104459 11 "A" 2
54311 14811 2159      47 228666 11 "C" 2
54311 14811 2160   46.54 228666 11 "C" 2
54311 13758 2160  -47.71   9874 11 "B" 2
end
format %tbtrade_day bdate
bysort permco bdate (shrout permno): generate todrop = _n<_N
list, noobs sepby(permco bdate)

. list, noobs sepby(permco bdate)

  +---------------------------------------------------------------------------------+
  | permco   permno   bdate       prc   shrout   shrcd   shrcls   multiple   todrop |
  |---------------------------------------------------------------------------------|
  |  54311    13758    2154     49.99     9874      11        B          2        1 |
  |  54311    13757    2154     48.94   104459      11        A          2        1 |
  |  54311    14811    2154     47.27   228666      11        C          2        0 |
  |---------------------------------------------------------------------------------|
  |  54311    13758    2155     49.36     9874      11        B          2        1 |
  |  54311    13757    2155     48.26   104459      11        A          2        1 |
  |  54311    14811    2155     46.49   228666      11        C          2        0 |
  |---------------------------------------------------------------------------------|

 ...

So after creating todrop you can then

drop if todrop

[Shaquille Wijngaarde]

I believe this does what you asked for: it generates todrop = 1 for the observations that are not the largest shrout for the firm/day. In case there are ties, the highest permno will be selected, since there doesn't seem to be anything else that will be guaranteed to be distinct within each permco.
.
.
.
.
...
[/CODE]
So after creating todrop you can then

drop if todrop

Many thanks William! Your code worked perfectly.
I appreciate the help. Cheers!