Calculate Auditor Tenure in Compustat

[Theresa Schmitt]

Dear all,

I am trying to get auditor tenure from Compustat. They only have auditor information, so, I have to count how many years that auditor works for the client since 1995 as the tenure variable. Can someone please help me?
So far, I already
- got the ‘basic’ data: gvkey, fyear, au (auditor code), etc. for each firm year
- append audit firm for the previous year (you could do a ‘data step’ with a lagged value here)
- make an indicator variable auditChange to equal 1 if the audit firm last year was a different one
- how do I create a count variable "Tenure" now that increments by one each year the auditor does not change for a company?

Thanks a lot in advance!

[Clyde Schechter]

There are several approaches to this in Stata, and which one is best depends on the organization of your data. While you have given a fairly careful description of your data, in fact, it leaves out some information that is key here. Descriptions of data are never an adequate substitute for an actual example.

Please post back showing a data example, and please use the -dataex- command to do so. If you are running version 15.1 or a fully updated version 14.2, it is already part of your official Stata installation. If not, run -ssc install dataex- to get it. Either way, run -help dataex- to read the simple instructions for using it. -dataex- will save you time; it is easier and quicker than typing out tables. It includes complete information about aspects of the data that are often critical to answering your question but cannot be seen from tabular displays or screenshots. It also makes it possible for those who want to help you to create a faithful representation of your example to try out their code, which in turn makes it more likely that their answer will actually work in your data.

When asking for help with code, always show example data. When showing example data, always use -dataex-.

Since you refer to a "data step" in your post, I'm guessing that you are a current or recent SAS user. Stata's model of data management is rather different from SAS's, and you will only confuse yourself if you try to approach problems in Stata from a SAS perspective. If you are going to continue to use both, you should make strenuous efforts to compartmentalize them in your mind: what you do in either will be of little help and often harmful in the other. If you are switching from SAS to Stata, try to forget you ever knew SAS.

[Theresa Schmitt]

Thanks for the advice! Attached, please find a small data sample (only including 2 sample firms)
[CODE]
* Example generated by -dataex-. To install: ssc install dataex
clear
input str6 gvkey double fyear long datadate str8 au
"001004" 1993 12569 "6"
"001004" 1994 12934 "6"
"001004" 1995 13300 "6"
"001004" 1996 13665 "8"
"001004" 1997 14030 "8"
"001004" 1998 14395 "8"
"001004" 1999 14761 "8"
"001004" 2000 15126 "8"
"001004" 2001 15491 "8"
"001004" 2002 15856 "8"
"001004" 2003 16222 "8"
"001010" 1994 12783 "6"
"001010" 1995 13148 "6"
"001010" 1996 13514 "6"
"001010" 1997 13879 "6"
"001010" 1998 14244 "6"
"001010" 1999 14609 "6"
"001010" 2000 14975 "6"
"001010" 2001 15340 "6"
"001010" 2002 15705 "6"
"001010" 2003 16070 "6"
end

[Nick Cox]

Does this help? The code counts how many years were 1995 or later for each pair of parties. (If year is ever missing, you need a twist to the code.)

* Example generated by -dataex-. To install: ssc install dataex
clear
input str6 gvkey double fyear long datadate str8 au
"001004" 1993 12569 "6"
"001004" 1994 12934 "6"
"001004" 1995 13300 "6"
"001004" 1996 13665 "8"
"001004" 1997 14030 "8"
"001004" 1998 14395 "8"
"001004" 1999 14761 "8"
"001004" 2000 15126 "8"
"001004" 2001 15491 "8"
"001004" 2002 15856 "8"
"001004" 2003 16222 "8"
"001010" 1994 12783 "6"
"001010" 1995 13148 "6"
"001010" 1996 13514 "6"
"001010" 1997 13879 "6"
"001010" 1998 14244 "6"
"001010" 1999 14609 "6"
"001010" 2000 14975 "6"
"001010" 2001 15340 "6"
"001010" 2002 15705 "6"
"001010" 2003 16070 "6"
end

bysort gvkey au : egen wanted = total(fyear >= 1995)

list, sepby(gvkey au)

     +-----------------------------------------+
     |  gvkey   fyear   datadate   au   wanted |
     |-----------------------------------------|
  1. | 001004    1993      12569    6        1 |
  2. | 001004    1994      12934    6        1 |
  3. | 001004    1995      13300    6        1 |
     |-----------------------------------------|
  4. | 001004    1996      13665    8        8 |
  5. | 001004    1997      14030    8        8 |
  6. | 001004    1998      14395    8        8 |
  7. | 001004    1999      14761    8        8 |
  8. | 001004    2000      15126    8        8 |
  9. | 001004    2001      15491    8        8 |
 10. | 001004    2002      15856    8        8 |
 11. | 001004    2003      16222    8        8 |
     |-----------------------------------------|
 12. | 001010    1994      12783    6        9 |
 13. | 001010    1995      13148    6        9 |
 14. | 001010    1996      13514    6        9 |
 15. | 001010    1997      13879    6        9 |
 16. | 001010    1998      14244    6        9 |
 17. | 001010    1999      14609    6        9 |
 18. | 001010    2000      14975    6        9 |
 19. | 001010    2001      15340    6        9 |
 20. | 001010    2002      15705    6        9 |
 21. | 001010    2003      16070    6        9 |
     +-----------------------------------------+

[Theresa Schmitt]

This is not 100% it yet.. When the firm with gvkey 001004 changes its auditor in year 1996, the tenure in that year would be 1. In year 1997 it would be 2, etc. If a new auditor is employed, it starts with 1 again

[Nick Cox]

 
 bysort gvkey au (fyear) : gen wanted = sum(fyear > 1995)

[Theresa Schmitt]

Thanks so much!!