Announcement

Collapse
No announcement yet.
X
Collapse
  • Page of 1
  • Filter
  • Time
  • Show
Clear All
new posts
  • #1

    unifying the names of companies?

    Dear All, I have this data set.
    Code:
    * Example generated by -dataex-. For more info, type help dataex
clear
input byte firm int year str30 auditorfirm
 3 2005 "Deloitte & Touche LLP155 E Bro"
 3 2006 "Deloitte & Touche LLP155 E. Br"
 3 2007 "Deloitte & Touche155 E. Broad"
 3 2008 "Deloitte & Touche155 E. Broad"
 3 2009 "Deloitte & Touche155 E. Broad"
 3 2010 "Deloitte & Touche180 E. Broad"
 3 2011 "Deloitte & Touche180 E. Broad"
 3 2012 "Deloitte & Touche  180 E. Broa"
 3 2013 "Deloitte & Touche 180 E. Broad"
 3 2014 "Deloitte & Touche 180 E. Broad"
 3 2015 "Deloitte & Touche 180 E. Broad"
 3 2016 "Deloitte & Touche 180 E. Broad"
 3 2017 "Deloitte & Touche 180 E. Broad"
 3 2018 "Deloitte & Touche 180 E. Broad"
 3 2019 "Deloitte & Touche 180 E. Broad"
 3 2020 "DELOITTE & TOUCHE 180 E. BR"  
 4 2005 "Ernst & Young, LLP. Two Commer"
 4 2006 "Ernst & Young LLP, Two Commerc"
 4 2007 "Ernst & Young LLP, Two Commerc"
 4 2008 "Ernst & Young LLP, Two Commerc"
 4 2009 "ERNST & YOUNG LLP, TWO COMMERC"
 4 2010 "ERNST & YOUNG LLP, TWO COMMERC"
 4 2011 "Egan & Egan, CPA's  20910 Mart"
 4 2012 "Egan & Egan, CPA's  20910 Mart"
 4 2013 "Egan & Egan, CPA's  1545 N. Co"
 4 2014 "Egan & Egan, CPA's  1545 N. Co"
 4 2015 "Egan & Egan, CPA's  1545 N. Co"
 4 2016 "Egan & Egan, CPA's  1545 N. Co"
 4 2017 "Egan & Egan, CPA's  1545 N. Co"
 4 2018 "Egan & Egan, CPA's  1545 N. Co"
 4 2019 "Egan & Egan, CPA's  1545 N. Co"
 4 2020 "Egan & Egan CPA's  1545 N."    
 6 2005 "DELOITTE & TOUCHE LLP, TWO HIL"
 6 2006 "DELOITTE & TOUCHE LLP, TWO HIL"
 6 2007 "DELOITTE & TOUCHE LLP, TWO HIL"
 6 2008 "DELOITTE & TOUCHE LLP, TWO HIL"
 6 2009 "DELOITTE & TOUCHE LLP, TWO HIL"
 6 2010 "DELOITTE & TOUCHE LLP, TWO HIL"
 6 2011 "DELOITTE & TOUCHE LLP, TWO HIL"
 6 2012 "DELOITTE & TOUCHE LLP, TWO HIL"
 6 2013 "DELOITTE & TOUCHE LLP,185 ASYL"
 6 2014 "DELOITTE & TOUCHE LLP,185 ASYL"
 6 2015 "DELOITTE & TOUCHE LLP,185 ASYL"
 6 2016 "ERNST & YOUNG LLP 5 TIMES SQUA"
 6 2017 "ERNST & YOUNG LLP 5 TIMES SQU"
 6 2018 "ERNST & YOUNG LLP 5 TIMES SQU"
 6 2019 "Ernst & Young, LLP, 560 Missio"
 6 2020 "Ernst & Young LLP 560 Missi"  
 7 2005 "Piercy, Bowler, Taylor, Kerns,"
 7 2006 "Piercy, Bowler, Taylor, Kerns,"
 7 2007 "Insurance Industry Auditing &"
 7 2008 "Insurance Industry Auditing &"
 7 2009 "Insurance Industry Auditing an"
 7 2010 "Insurance Industry Auditing an"
 7 2011 "Ernst & Young, 999 Third Avenu"
 7 2012 "Ernst & Young, 999 Third Avenu"
 7 2013 "Ernst & Young, 999 Third Avenu"
 7 2014 "Johnson Lambert LLP, 700 Sprin"
 7 2015 "JOHNSON LAMBERT LLP, 700 SPRIN"
 7 2016 "STROHM BALLWEG, LLP  9701 BRAD"
 7 2017 "STROHM BALLWEG, LLP  9701 BRAD"
 7 2018 "STROHM BALLWEG, LLP  9701 BRAD"
 7 2019 "STROHM BALLWEG, LLP  9701 BRAD"
 7 2020 "STROHM BALLWEG LLP  9701 BR"  
 8 2005 "Larson & Company - 99065 S 130"
 8 2006 "Larson & Company - 99065 S 130"
 8 2007 "Larson and Company - 99065 S 1"
 8 2008 "Larson & Rosenberger LLP, 9065"
 8 2009 "Larson & Rosenberger LLP, 9065"
 8 2010 "Larson & Rosenberger LLP, 9065"
 9 2005 "VILLAFANE & OTI CPAs Parana St"
 9 2006 ""                              
 9 2007 "Villafane & Oti PSC"          
 9 2008 "Villafae & Oti PSC"            
11 2006 "STROHM BALLWEG, LLP.  -  MADIS"
11 2007 "STROHM BALLWEG, LLP"          
11 2010 "STROHM BALLWEG, LLP, 999 FOURI"
11 2011 "STROHM BALLWEG, LLP, 999 FOURI"
11 2012 "STROHM BALLWEG, LLP, 999 FOURI"
11 2014 "STROHM BALLWEG, LLP9701 BRADER"
11 2015 "STROHM BALLWEG, LLP9701 BRADER"
11 2016 "STROHM BALLWEG, LLP9701 BRADER"
11 2018 "STROHM BALLWEG INC"            
11 2019 "STROHM BALLWEG INC9701 BRADER"
11 2020 "STROHM BALLWEG INC9701 BRAD"  
14 2005 "Ernst & Young, LLP, 200 Claren"
14 2006 "Ernst & Young, LLP 200 Clarend"
14 2007 "Ernst & Young, LLP 200 Clarend"
14 2008 "Ernst & Young, LLP 200 Clarend"
14 2009 "Ernst & Young, LLP 200 Clarend"
14 2010 "Ernst & Young, LLP 200 Clarend"
14 2011 "Ernst & Young, LLP 200 Clarend"
14 2012 "Ernst & Young, LLP 200 Clarend"
14 2013 "Ernst & Young, LLP 200 Clarend"
14 2014 "Ernst & Young, LLP 200 Clarend"
14 2015 "Ernst & Young, LLP 200 Clarend"
14 2016 "Ernst & Young, LLP 200 Clarend"
14 2017 "Ernst & Young, LLP 200 Clarend"
14 2018 "Ernst & Young, LLP 200 Clarend"
14 2019 "Ernst & Young, LLP 200 Clarend"
end
    For each firm, I'd like to know whether its audit firm has been changed (in some particular years), according to the name of `auditorfirm'. However, as you can see, the names of the same audit firm might differ.
    For instance, for firm = 3, its audit firm is the same for all years but with different (but similar) names. I wonder if there is a way to deal with this kind of problem? Thanks.
    Ho-Chuan (River) Huang
    Stata 17.0, MP(4)
    Tags: None
  • #2
    This may not be as difficult as you think as you are just matching names within firms. The following uses the fuzzy matching command matchit from SSC. 30% similarity appears to be a good benchmark here, but adjust it if it misses some targets or if it too optimistic.


    Code:
    * Example generated by -dataex-. For more info, type help dataex
clear
input byte firm int year str30 auditorfirm
 3 2005 "Deloitte & Touche LLP155 E Bro"
 3 2006 "Deloitte & Touche LLP155 E. Br"
 3 2007 "Deloitte & Touche155 E. Broad"
 3 2008 "Deloitte & Touche155 E. Broad"
 3 2009 "Deloitte & Touche155 E. Broad"
 3 2010 "Deloitte & Touche180 E. Broad"
 3 2011 "Deloitte & Touche180 E. Broad"
 3 2012 "Deloitte & Touche  180 E. Broa"
 3 2013 "Deloitte & Touche 180 E. Broad"
 3 2014 "Deloitte & Touche 180 E. Broad"
 3 2015 "Deloitte & Touche 180 E. Broad"
 3 2016 "Deloitte & Touche 180 E. Broad"
 3 2017 "Deloitte & Touche 180 E. Broad"
 3 2018 "Deloitte & Touche 180 E. Broad"
 3 2019 "Deloitte & Touche 180 E. Broad"
 3 2020 "DELOITTE & TOUCHE 180 E. BR"  
 4 2005 "Ernst & Young, LLP. Two Commer"
 4 2006 "Ernst & Young LLP, Two Commerc"
 4 2007 "Ernst & Young LLP, Two Commerc"
 4 2008 "Ernst & Young LLP, Two Commerc"
 4 2009 "ERNST & YOUNG LLP, TWO COMMERC"
 4 2010 "ERNST & YOUNG LLP, TWO COMMERC"
 4 2011 "Egan & Egan, CPA's  20910 Mart"
 4 2012 "Egan & Egan, CPA's  20910 Mart"
 4 2013 "Egan & Egan, CPA's  1545 N. Co"
 4 2014 "Egan & Egan, CPA's  1545 N. Co"
 4 2015 "Egan & Egan, CPA's  1545 N. Co"
 4 2016 "Egan & Egan, CPA's  1545 N. Co"
 4 2017 "Egan & Egan, CPA's  1545 N. Co"
 4 2018 "Egan & Egan, CPA's  1545 N. Co"
 4 2019 "Egan & Egan, CPA's  1545 N. Co"
 4 2020 "Egan & Egan CPA's  1545 N."    
 6 2005 "DELOITTE & TOUCHE LLP, TWO HIL"
 6 2006 "DELOITTE & TOUCHE LLP, TWO HIL"
 6 2007 "DELOITTE & TOUCHE LLP, TWO HIL"
 6 2008 "DELOITTE & TOUCHE LLP, TWO HIL"
 6 2009 "DELOITTE & TOUCHE LLP, TWO HIL"
 6 2010 "DELOITTE & TOUCHE LLP, TWO HIL"
 6 2011 "DELOITTE & TOUCHE LLP, TWO HIL"
 6 2012 "DELOITTE & TOUCHE LLP, TWO HIL"
 6 2013 "DELOITTE & TOUCHE LLP,185 ASYL"
 6 2014 "DELOITTE & TOUCHE LLP,185 ASYL"
 6 2015 "DELOITTE & TOUCHE LLP,185 ASYL"
 6 2016 "ERNST & YOUNG LLP 5 TIMES SQUA"
 6 2017 "ERNST & YOUNG LLP 5 TIMES SQU"
 6 2018 "ERNST & YOUNG LLP 5 TIMES SQU"
 6 2019 "Ernst & Young, LLP, 560 Missio"
 6 2020 "Ernst & Young LLP 560 Missi"  
 7 2005 "Piercy, Bowler, Taylor, Kerns,"
 7 2006 "Piercy, Bowler, Taylor, Kerns,"
 7 2007 "Insurance Industry Auditing &"
 7 2008 "Insurance Industry Auditing &"
 7 2009 "Insurance Industry Auditing an"
 7 2010 "Insurance Industry Auditing an"
 7 2011 "Ernst & Young, 999 Third Avenu"
 7 2012 "Ernst & Young, 999 Third Avenu"
 7 2013 "Ernst & Young, 999 Third Avenu"
 7 2014 "Johnson Lambert LLP, 700 Sprin"
 7 2015 "JOHNSON LAMBERT LLP, 700 SPRIN"
 7 2016 "STROHM BALLWEG, LLP  9701 BRAD"
 7 2017 "STROHM BALLWEG, LLP  9701 BRAD"
 7 2018 "STROHM BALLWEG, LLP  9701 BRAD"
 7 2019 "STROHM BALLWEG, LLP  9701 BRAD"
 7 2020 "STROHM BALLWEG LLP  9701 BR"  
 8 2005 "Larson & Company - 99065 S 130"
 8 2006 "Larson & Company - 99065 S 130"
 8 2007 "Larson and Company - 99065 S 1"
 8 2008 "Larson & Rosenberger LLP, 9065"
 8 2009 "Larson & Rosenberger LLP, 9065"
 8 2010 "Larson & Rosenberger LLP, 9065"
 9 2005 "VILLAFANE & OTI CPAs Parana St"
 9 2006 ""                              
 9 2007 "Villafane & Oti PSC"          
 9 2008 "Villafae & Oti PSC"            
11 2006 "STROHM BALLWEG, LLP.  -  MADIS"
11 2007 "STROHM BALLWEG, LLP"          
11 2010 "STROHM BALLWEG, LLP, 999 FOURI"
11 2011 "STROHM BALLWEG, LLP, 999 FOURI"
11 2012 "STROHM BALLWEG, LLP, 999 FOURI"
11 2014 "STROHM BALLWEG, LLP9701 BRADER"
11 2015 "STROHM BALLWEG, LLP9701 BRADER"
11 2016 "STROHM BALLWEG, LLP9701 BRADER"
11 2018 "STROHM BALLWEG INC"            
11 2019 "STROHM BALLWEG INC9701 BRADER"
11 2020 "STROHM BALLWEG INC9701 BRAD"  
14 2005 "Ernst & Young, LLP, 200 Claren"
14 2006 "Ernst & Young, LLP 200 Clarend"
14 2007 "Ernst & Young, LLP 200 Clarend"
14 2008 "Ernst & Young, LLP 200 Clarend"
14 2009 "Ernst & Young, LLP 200 Clarend"
14 2010 "Ernst & Young, LLP 200 Clarend"
14 2011 "Ernst & Young, LLP 200 Clarend"
14 2012 "Ernst & Young, LLP 200 Clarend"
14 2013 "Ernst & Young, LLP 200 Clarend"
14 2014 "Ernst & Young, LLP 200 Clarend"
14 2015 "Ernst & Young, LLP 200 Clarend"
14 2016 "Ernst & Young, LLP 200 Clarend"
14 2017 "Ernst & Young, LLP 200 Clarend"
14 2018 "Ernst & Young, LLP 200 Clarend"
14 2019 "Ernst & Young, LLP 200 Clarend"
end

replace auditorfirm= ustrregexra( auditorfirm, "&", "&")
tempfile data
save `data'
keep firm year auditorfirm
rename (auditorfirm year) (auditorfirm2 year2)
joinby firm using `data'
matchit auditorfirm auditorfirm2
keep if similscore>.3
keep firm auditorfirm auditorfirm2 similscore
contract firm auditorfirm auditorfirm2
replace _freq=_n
rename auditorfirm auditorfirm1
reshape long auditorfirm, i(_freq) j(which)
bys _freq (which): gen auditorfirm2=auditorfirm[1]
keep firm auditorfirm auditorfirm2
gen order=_n
bys auditorfirm (order): keep if _n==_N
tempfile list
save `list', replace
use `data', clear
merge m:1 firm auditorfirm using `list',keep (master match) nogen

    ADDED IN EDIT: For firm 6, some Deloitte observations are matched as Ernst & Young. This is because the strings are at least 30% similar. It helps a lot if we convert "&" in the strings to "&". I have thus modified this in the code and it seems that we get better matching although not perfect. Some manual input may ultimately be needed after the fuzzy matching.

    Res.:

    Code:
    . sort firm year

. l, sepby(auditorfirm2)

     +---------------------------------------------------------------------------------------+
     | firm   year                      auditorfirm                     auditorfirm2   order |
     |---------------------------------------------------------------------------------------|
  1. |    3   2005       Deloitte & Touche LLP155 E       Deloitte & Touche180 E. Br      60 |
  2. |    3   2006       Deloitte & Touche LLP155 E       Deloitte & Touche180 E. Br      60 |
  3. |    3   2007       Deloitte & Touche155 E. Br       Deloitte & Touche180 E. Br      62 |
  4. |    3   2008       Deloitte & Touche155 E. Br       Deloitte & Touche180 E. Br      62 |
  5. |    3   2009       Deloitte & Touche155 E. Br       Deloitte & Touche180 E. Br      62 |
  6. |    3   2010       Deloitte & Touche180 E. Br       Deloitte & Touche180 E. Br      64 |
  7. |    3   2011       Deloitte & Touche180 E. Br       Deloitte & Touche180 E. Br      64 |
  8. |    3   2012       Deloitte & Touche  180 E.        Deloitte & Touche180 E. Br      56 |
  9. |    3   2013       Deloitte & Touche 180 E. B       Deloitte & Touche180 E. Br      58 |
 10. |    3   2014       Deloitte & Touche 180 E. B       Deloitte & Touche180 E. Br      58 |
 11. |    3   2015       Deloitte & Touche 180 E. B       Deloitte & Touche180 E. Br      58 |
 12. |    3   2016       Deloitte & Touche 180 E. B       Deloitte & Touche180 E. Br      58 |
 13. |    3   2017       Deloitte & Touche 180 E. B       Deloitte & Touche180 E. Br      58 |
 14. |    3   2018       Deloitte & Touche 180 E. B       Deloitte & Touche180 E. Br      58 |
 15. |    3   2019       Deloitte & Touche 180 E. B       Deloitte & Touche180 E. Br      58 |
 16. |    3   2020       DELOITTE & TOUCHE 180 E. B       Deloitte & Touche180 E. Br      54 |
     |---------------------------------------------------------------------------------------|
 17. |    4   2005       Ernst & Young, LLP. Two Co       Ernst & Young, LLP. Two Co     100 |
 18. |    4   2006       Ernst & Young LLP, Two Com       Ernst & Young, LLP. Two Co      98 |
 19. |    4   2007       Ernst & Young LLP, Two Com       Ernst & Young, LLP. Two Co      98 |
 20. |    4   2008       Ernst & Young LLP, Two Com       Ernst & Young, LLP. Two Co      98 |
 21. |    4   2009       ERNST & YOUNG LLP, TWO COM       Ernst & Young, LLP. Two Co      96 |
 22. |    4   2010       ERNST & YOUNG LLP, TWO COM       Ernst & Young, LLP. Two Co      96 |
     |---------------------------------------------------------------------------------------|
 23. |    4   2011       Egan & Egan, CPA's  20910        Egan & Egan, CPA's  20910       88 |
 24. |    4   2012       Egan & Egan, CPA's  20910        Egan & Egan, CPA's  20910       88 |
 25. |    4   2013       Egan & Egan, CPA's  1545 N       Egan & Egan, CPA's  20910       86 |
 26. |    4   2014       Egan & Egan, CPA's  1545 N       Egan & Egan, CPA's  20910       86 |
 27. |    4   2015       Egan & Egan, CPA's  1545 N       Egan & Egan, CPA's  20910       86 |
 28. |    4   2016       Egan & Egan, CPA's  1545 N       Egan & Egan, CPA's  20910       86 |
 29. |    4   2017       Egan & Egan, CPA's  1545 N       Egan & Egan, CPA's  20910       86 |
 30. |    4   2018       Egan & Egan, CPA's  1545 N       Egan & Egan, CPA's  20910       86 |
 31. |    4   2019       Egan & Egan, CPA's  1545 N       Egan & Egan, CPA's  20910       86 |
 32. |    4   2020       Egan & Egan CPA's  1545 N.       Egan & Egan, CPA's  20910       84 |
     |---------------------------------------------------------------------------------------|
 33. |    6   2005       DELOITTE & TOUCHE LLP, TWO       DELOITTE & TOUCHE LLP,185      106 |
 34. |    6   2006       DELOITTE & TOUCHE LLP, TWO       DELOITTE & TOUCHE LLP,185      106 |
 35. |    6   2007       DELOITTE & TOUCHE LLP, TWO       DELOITTE & TOUCHE LLP,185      106 |
 36. |    6   2008       DELOITTE & TOUCHE LLP, TWO       DELOITTE & TOUCHE LLP,185      106 |
 37. |    6   2009       DELOITTE & TOUCHE LLP, TWO       DELOITTE & TOUCHE LLP,185      106 |
 38. |    6   2010       DELOITTE & TOUCHE LLP, TWO       DELOITTE & TOUCHE LLP,185      106 |
 39. |    6   2011       DELOITTE & TOUCHE LLP, TWO       DELOITTE & TOUCHE LLP,185      106 |
 40. |    6   2012       DELOITTE & TOUCHE LLP, TWO       DELOITTE & TOUCHE LLP,185      106 |
     |---------------------------------------------------------------------------------------|
 41. |    6   2013       DELOITTE & TOUCHE LLP,185        ERNST & YOUNG LLP 5 TIMES     122 |
 42. |    6   2014       DELOITTE & TOUCHE LLP,185        ERNST & YOUNG LLP 5 TIMES     122 |
 43. |    6   2015       DELOITTE & TOUCHE LLP,185        ERNST & YOUNG LLP 5 TIMES     122 |
     |---------------------------------------------------------------------------------------|
 44. |    6   2016       ERNST & YOUNG LLP 5 TIMES        Ernst & Young LLP 560 Miss     128 |
     |---------------------------------------------------------------------------------------|
 45. |    6   2017       ERNST & YOUNG LLP 5 TIMES       ERNST & YOUNG LLP 5 TIMES     126 |
 46. |    6   2018       ERNST & YOUNG LLP 5 TIMES       ERNST & YOUNG LLP 5 TIMES     126 |
     |---------------------------------------------------------------------------------------|
 47. |    6   2019       Ernst & Young, LLP, 560 Mi       Ernst & Young, LLP, 560 Mi     136 |
 48. |    6   2020       Ernst & Young LLP 560 Miss       Ernst & Young, LLP, 560 Mi     134 |
     |---------------------------------------------------------------------------------------|
 49. |    7   2005   Piercy, Bowler, Taylor, Kerns,   Piercy, Bowler, Taylor, Kerns,     156 |
 50. |    7   2006   Piercy, Bowler, Taylor, Kerns,   Piercy, Bowler, Taylor, Kerns,     156 |
     |---------------------------------------------------------------------------------------|
 51. |    7   2007   Insurance Industry Auditing &a   Insurance Industry Auditing an     144 |
 52. |    7   2008   Insurance Industry Auditing &a   Insurance Industry Auditing an     144 |
 53. |    7   2009   Insurance Industry Auditing an   Insurance Industry Auditing an     146 |
 54. |    7   2010   Insurance Industry Auditing an   Insurance Industry Auditing an     146 |
     |---------------------------------------------------------------------------------------|
 55. |    7   2011       Ernst & Young, 999 Third A       Ernst & Young, 999 Third A     138 |
 56. |    7   2012       Ernst & Young, 999 Third A       Ernst & Young, 999 Third A     138 |
 57. |    7   2013       Ernst & Young, 999 Third A       Ernst & Young, 999 Third A     138 |
     |---------------------------------------------------------------------------------------|
 58. |    7   2014   Johnson Lambert LLP, 700 Sprin   Johnson Lambert LLP, 700 Sprin     154 |
 59. |    7   2015   JOHNSON LAMBERT LLP, 700 SPRIN   Johnson Lambert LLP, 700 Sprin     152 |
     |---------------------------------------------------------------------------------------|
 60. |    7   2016   STROHM BALLWEG, LLP  9701 BRAD   STROHM BALLWEG, LLP  9701 BRAD     164 |
 61. |    7   2017   STROHM BALLWEG, LLP  9701 BRAD   STROHM BALLWEG, LLP  9701 BRAD     164 |
 62. |    7   2018   STROHM BALLWEG, LLP  9701 BRAD   STROHM BALLWEG, LLP  9701 BRAD     164 |
 63. |    7   2019   STROHM BALLWEG, LLP  9701 BRAD   STROHM BALLWEG, LLP  9701 BRAD     164 |
 64. |    7   2020      STROHM BALLWEG LLP  9701 BR   STROHM BALLWEG, LLP  9701 BRAD     162 |
     |---------------------------------------------------------------------------------------|
 65. |    8   2005       Larson & Company - 99065 S   Larson and Company - 99065 S 1     176 |
 66. |    8   2006       Larson & Company - 99065 S   Larson and Company - 99065 S 1     176 |
 67. |    8   2007   Larson and Company - 99065 S 1   Larson and Company - 99065 S 1     178 |
     |---------------------------------------------------------------------------------------|
 68. |    8   2008       Larson & Rosenberger LLP,        Larson & Rosenberger LLP,      174 |
 69. |    8   2009       Larson & Rosenberger LLP,        Larson & Rosenberger LLP,      174 |
 70. |    8   2010       Larson & Rosenberger LLP,        Larson & Rosenberger LLP,      174 |
     |---------------------------------------------------------------------------------------|
 71. |    9   2005       VILLAFANE & OTI CPAs Paran       VILLAFANE & OTI CPAs Paran     182 |
     |---------------------------------------------------------------------------------------|
 72. |    9   2006                                                                       180 |
     |---------------------------------------------------------------------------------------|
 73. |    9   2007              Villafane & Oti PSC              Villafane & Oti PSC     190 |
 74. |    9   2008               Villafae & Oti PSC              Villafane & Oti PSC     188 |
     |---------------------------------------------------------------------------------------|
 75. |   11   2006   STROHM BALLWEG, LLP.  -  MADIS   STROHM BALLWEG, LLP9701 BRADER     286 |
 76. |   11   2007              STROHM BALLWEG, LLP   STROHM BALLWEG, LLP9701 BRADER     282 |
 77. |   11   2010   STROHM BALLWEG, LLP, 999 FOURI   STROHM BALLWEG, LLP9701 BRADER     284 |
 78. |   11   2011   STROHM BALLWEG, LLP, 999 FOURI   STROHM BALLWEG, LLP9701 BRADER     284 |
 79. |   11   2012   STROHM BALLWEG, LLP, 999 FOURI   STROHM BALLWEG, LLP9701 BRADER     284 |
 80. |   11   2014   STROHM BALLWEG, LLP9701 BRADER   STROHM BALLWEG, LLP9701 BRADER     288 |
 81. |   11   2015   STROHM BALLWEG, LLP9701 BRADER   STROHM BALLWEG, LLP9701 BRADER     288 |
 82. |   11   2016   STROHM BALLWEG, LLP9701 BRADER   STROHM BALLWEG, LLP9701 BRADER     288 |
 83. |   11   2018               STROHM BALLWEG INC   STROHM BALLWEG, LLP9701 BRADER     276 |
 84. |   11   2019    STROHM BALLWEG INC9701 BRADER   STROHM BALLWEG, LLP9701 BRADER     280 |
 85. |   11   2020      STROHM BALLWEG INC9701 BRAD   STROHM BALLWEG, LLP9701 BRADER     278 |
     |---------------------------------------------------------------------------------------|
 86. |   14   2005       Ernst & Young, LLP, 200 Cl       Ernst & Young, LLP, 200 Cl     296 |
 87. |   14   2006       Ernst & Young, LLP 200 Cla       Ernst & Young, LLP, 200 Cl     294 |
 88. |   14   2007       Ernst & Young, LLP 200 Cla       Ernst & Young, LLP, 200 Cl     294 |
 89. |   14   2008       Ernst & Young, LLP 200 Cla       Ernst & Young, LLP, 200 Cl     294 |
 90. |   14   2009       Ernst & Young, LLP 200 Cla       Ernst & Young, LLP, 200 Cl     294 |
 91. |   14   2010       Ernst & Young, LLP 200 Cla       Ernst & Young, LLP, 200 Cl     294 |
 92. |   14   2011       Ernst & Young, LLP 200 Cla       Ernst & Young, LLP, 200 Cl     294 |
 93. |   14   2012       Ernst & Young, LLP 200 Cla       Ernst & Young, LLP, 200 Cl     294 |
 94. |   14   2013       Ernst & Young, LLP 200 Cla       Ernst & Young, LLP, 200 Cl     294 |
 95. |   14   2014       Ernst & Young, LLP 200 Cla       Ernst & Young, LLP, 200 Cl     294 |
 96. |   14   2015       Ernst & Young, LLP 200 Cla       Ernst & Young, LLP, 200 Cl     294 |
 97. |   14   2016       Ernst & Young, LLP 200 Cla       Ernst & Young, LLP, 200 Cl     294 |
 98. |   14   2017       Ernst & Young, LLP 200 Cla       Ernst & Young, LLP, 200 Cl     294 |
 99. |   14   2018       Ernst & Young, LLP 200 Cla       Ernst & Young, LLP, 200 Cl     294 |
100. |   14   2019       Ernst & Young, LLP 200 Cla       Ernst & Young, LLP, 200 Cl     294 |
     +---------------------------------------------------------------------------------------+
    Last edited by Andrew Musau; 14 Feb 2022, 19:18.
    • 2 likes

    Comment

    • #3
      Dear Andrew, Thank you so much for this wonderful suggestion.
      Ho-Chuan (River) Huang
      Stata 17.0, MP(4)

      Comment

      • #4
        The 4th from last line should be:

        Code:
        bys firm auditorfirm (order): keep if _n==_N

        It does not matter in this example as the audit firm names are distinct across firms.
        • 1 like

        Comment

        • #5
          Dear Andrew, Thanks for this additional information. I have added the following line before your code
          Code:
          replace auditorfirm = ustrupper(auditorfirm)
          Ho-Chuan (River) Huang
          Stata 17.0, MP(4)

          Comment

          Previous Next
          Working...
          X