Hello,
I would like to calculate the audit tenure. I have read previous treads (e.g. https://www.statalist.org/forums/for...e-in-compustat). However, these codes will do the following: when a company has auditor X for 5 years, followed by 2 years of auditor Y and then 5 years of auditor X again, they will just say the total tenure for X is 10. However, I would need it to count to 5 two times.
Following the inserted link I generated the column audit_duur (which means tenure). In bold I marked a line that illustrates the error. That line should restart from 1, as there are some years missing.
I would like to calculate the audit tenure. I have read previous treads (e.g. https://www.statalist.org/forums/for...e-in-compustat). However, these codes will do the following: when a company has auditor X for 5 years, followed by 2 years of auditor Y and then 5 years of auditor X again, they will just say the total tenure for X is 10. However, I would need it to count to 5 two times.
Following the inserted link I generated the column audit_duur (which means tenure). In bold I marked a line that illustrates the error. That line should restart from 1, as there are some years missing.
Code:
* Example generated by -dataex-. To install: ssc install dataex clear input double(audit_op_key auditor_key) str70 auditor_name double fiscal_year long(fiscal_year_end_op cik) float(audit_duur change fout) 66900 5 "Arthur Andersen LLP" 2000 14974 20 1 1 0 78252 5 "Arthur Andersen LLP" 2001 15338 20 2 0 0 3538 4 "KPMG LLP" 2002 15702 20 1 1 0 39225 6 "Grant Thornton LLP" 2003 16073 20 1 1 0 84748 6 "Grant Thornton LLP" 2004 16437 20 2 0 0 102979 6 "Grant Thornton LLP" 2005 16801 20 3 0 0 120889 6 "Grant Thornton LLP" 2006 17165 20 4 0 0 148621 6 "Grant Thornton LLP" 2007 17529 20 5 0 0 171604 6 "Grant Thornton LLP" 2008 17900 20 6 0 0 192814 6 "Grant Thornton LLP" 2009 18264 20 7 0 0 156138 4 "KPMG LLP" 2000 14761 1750 1 1 0 74991 4 "KPMG LLP" 2001 15126 1750 2 0 0 45767 4 "KPMG LLP" 2002 15491 1750 3 0 0 17785 4 "KPMG LLP" 2003 15856 1750 4 0 0 51196 4 "KPMG LLP" 2004 16222 1750 5 0 0 90725 4 "KPMG LLP" 2005 16587 1750 6 0 0 109724 4 "KPMG LLP" 2006 16952 1750 7 0 0 132153 4 "KPMG LLP" 2007 17317 1750 8 0 0 159129 4 "KPMG LLP" 2008 17683 1750 9 0 0 179725 4 "KPMG LLP" 2009 18048 1750 10 0 0 203186 4 "KPMG LLP" 2010 18413 1750 11 0 0 226207 4 "KPMG LLP" 2011 18778 1750 12 0 0 249025 4 "KPMG LLP" 2012 19144 1750 13 0 0 270755 4 "KPMG LLP" 2013 19509 1750 14 0 0 292894 4 "KPMG LLP" 2014 19874 1750 15 0 0 314803 4 "KPMG LLP" 2015 20239 1750 16 0 0 336853 4 "KPMG LLP" 2016 20605 1750 17 0 0 359805 4 "KPMG LLP" 2017 20970 1750 18 0 0 383669 4 "KPMG LLP" 2018 21335 1750 19 0 0 407089 4 "KPMG LLP" 2019 21700 1750 20 0 0 62432 5 "Arthur Andersen LLP" 2000 14975 1800 1 1 0 77557 5 "Arthur Andersen LLP" 2001 15340 1800 2 0 0 4938 3 "Deloitte & Touche LLP" 2002 15705 1800 1 1 0 33577 3 "Deloitte & Touche LLP" 2003 16070 1800 2 0 0 79693 3 "Deloitte & Touche LLP" 2004 16436 1800 3 0 0 98479 3 "Deloitte & Touche LLP" 2005 16801 1800 4 0 0 118481 3 "Deloitte & Touche LLP" 2006 17166 1800 5 0 0 145339 3 "Deloitte & Touche LLP" 2007 17531 1800 6 0 0 168007 3 "Deloitte & Touche LLP" 2008 17897 1800 7 0 0 188938 3 "Deloitte & Touche LLP" 2009 18262 1800 8 0 0 213473 3 "Deloitte & Touche LLP" 2010 18627 1800 9 0 0 236848 3 "Deloitte & Touche LLP" 2011 18992 1800 10 0 0 259298 3 "Deloitte & Touche LLP" 2012 19358 1800 11 0 0 280319 3 "Deloitte & Touche LLP" 2013 19723 1800 12 0 0 304542 2 "Ernst & Young LLP" 2014 20088 1800 1 1 0 324509 2 "Ernst & Young LLP" 2015 20453 1800 2 0 0 346280 2 "Ernst & Young LLP" 2016 20819 1800 3 0 0 369520 2 "Ernst & Young LLP" 2017 21184 1800 4 0 0 394240 2 "Ernst & Young LLP" 2018 21549 1800 5 0 0 233148 968 "Barry L Friedman PC" 1998 14244 1830 1 1 0 160681 968 "Barry L Friedman PC" 1999 14548 1830 2 0 0 158257 968 "Barry L Friedman PC" 2000 14761 1830 3 0 0 179179 442 "DeCoria Maichel & Teague PS" 2000 14975 1853 1 1 0 115730 442 "DeCoria Maichel & Teague PS" 2001 15340 1853 2 0 0 17480 442 "DeCoria Maichel & Teague PS" 2002 15705 1853 3 0 0 32726 442 "DeCoria Maichel & Teague PS" 2003 16070 1853 4 0 0 88541 748 "Spector & Wong LLP" 2004 16436 1853 1 1 0 105068 748 "Spector & Wong LLP" 2005 16801 1853 2 0 0 126569 748 "Spector & Wong LLP" 2006 17166 1853 3 0 0 155714 748 "Spector & Wong LLP" 2007 17531 1853 4 0 0 175547 5644 "Spector Wong & Davidian LLP" 2008 17897 1853 1 1 0 199606 11556 "Spector & Associates LLP" 2009 18262 1853 1 1 0 157619 1 "PricewaterhouseCoopers LLP" 2000 14730 1923 1 1 0 74668 1 "PricewaterhouseCoopers LLP" 2001 15095 1923 2 0 0 45951 3 "Deloitte & Touche LLP" 2002 15460 1923 1 1 0 11732 3 "Deloitte & Touche LLP" 2003 15825 1923 2 0 0 52128 3 "Deloitte & Touche LLP" 2004 16191 1923 3 0 0 90794 3 "Deloitte & Touche LLP" 2005 16556 1923 4 0 0 110091 3 "Deloitte & Touche LLP" 2006 16921 1923 5 0 0 132138 3 "Deloitte & Touche LLP" 2007 17286 1923 6 0 0 160363 3 "Deloitte & Touche LLP" 2008 17652 1923 7 0 0 179915 3 "Deloitte & Touche LLP" 2009 18017 1923 8 0 0 203798 3 "Deloitte & Touche LLP" 2010 18382 1923 9 0 0 226999 3 "Deloitte & Touche LLP" 2011 18747 1923 10 0 0 136842 7 "BDO Seidman LLP" 1999 14609 1961 1 1 0 134260 6 "Grant Thornton LLP" 2000 14975 1961 1 1 0 150537 598 "Bongiovanni & Associates PA" 2001 15340 1961 1 1 0 152224 598 "Bongiovanni & Associates PA" 2005 16801 1961 2 1 1 152205 598 "Bongiovanni & Associates PA" 2006 17166 1961 3 0 0 152396 598 "Bongiovanni & Associates PA" 2007 17531 1961 4 0 0 175864 598 "Bongiovanni & Associates PA" 2008 17897 1961 5 0 0 197500 598 "Bongiovanni & Associates PA" 2009 18262 1961 6 0 0 216502 598 "Bongiovanni & Associates PA" 2010 18627 1961 7 0 0 245424 598 "Bongiovanni & Associates PA" 2011 18992 1961 8 0 0 266531 598 "Bongiovanni & Associates PA" 2012 19358 1961 9 0 0 287265 598 "Bongiovanni & Associates PA" 2013 19723 1961 10 0 0 311197 598 "Bongiovanni & Associates PA" 2014 20088 1961 11 0 0 332114 27105 "L&L CPAS PA" 2015 20453 1961 1 1 0 361218 27105 "L&L CPAS PA" 2016 20819 1961 2 0 0 377057 27105 "L&L CPAS PA" 2017 21184 1961 3 0 0 395695 3143 "M&K CPAS PLLC" 2018 21549 1961 1 1 0 184277 4 "KPMG LLP" 1999 14609 1985 1 1 0 65911 4 "KPMG LLP" 2000 14974 2024 1 1 0 55512 4 "KPMG LLP" 2001 15338 2024 2 0 0 17273 4 "KPMG LLP" 2002 15702 2024 3 0 0 145713 4 "KPMG LLP" 2000 14791 2034 1 1 0 75460 4 "KPMG LLP" 2001 15156 2034 2 0 0 6331 4 "KPMG LLP" 2002 15521 2034 3 0 0 20510 4 "KPMG LLP" 2003 15886 2034 4 0 0 56560 4 "KPMG LLP" 2004 16252 2034 5 0 0 end format %d fiscal_year_end_op
Comment