1 of 2
1
How to calculate auditor tenure in Compustat?
Posted: 25 July 2013 03:09 PM   [ Ignore ]
Newbie
Rank
Total Posts:  4
Joined  2013-07-25

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 an auditor works for the client since 1995 as the tenure variable.  Do you know how to achieve this in SAS?  Thank you so much! 

Profile
 
 
Posted: 25 July 2013 03:36 PM   [ Ignore ]   [ # 1 ]
Administrator
Avatar
RankRankRankRank
Total Posts:  901
Joined  2011-09-19

hi Shoe,

I would think of something like this:
- get the ‘basic’ data: gvkey, fyear, datadate, etc. for each firm year
- append audit firm for the fyear (if this is also in Funda, then this is easy)
- 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
- do a data step by gvkey (i.e. by firm), where you ‘retain’ a counter (auditTenure); increment the counter by one each year and reset to zero if auditChange equals 1

=>auditTenure will be the variable you need.

best regards,

Joost

 Signature 

To reply/post new questions: Please use the group WRDS/SAS on Google Groups! http://groups.google.com/d/forum/wrdssas

Profile
 
 
Posted: 25 July 2013 05:49 PM   [ Ignore ]   [ # 2 ]
Newbie
Rank
Total Posts:  4
Joined  2013-07-25

Joost,

Thank you so much!

I will try this.  At the same time, what is the counter proc in sas? 

Thanks again!

Joost Impink - 25 July 2013 03:36 PM

hi Shoe,

I would think of something like this:
- get the ‘basic’ data: gvkey, fyear, datadate, etc. for each firm year
- append audit firm for the fyear (if this is also in Funda, then this is easy)
- 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
- do a data step by gvkey (i.e. by firm), where you ‘retain’ a counter (auditTenure); increment the counter by one each year and reset to zero if auditChange equals 1

=>auditTenure will be the variable you need.

best regards,

Joost

Profile
 
 
Posted: 25 July 2013 06:29 PM   [ Ignore ]   [ # 3 ]
Administrator
Avatar
RankRankRankRank
Total Posts:  901
Joined  2011-09-19

hi Shoe,

You’re welcome.

Something like this to construct tenure variable:

/* sort the data by firm and year (needed for next data step) */
proc sort data work.by gvkey fyear;run;

/* compute tenure; assuming there is a variable called 'firm' to identify audit firm */
data z2;
set z;
retain tenure prev_firm/* will remember the values while processing the rows */
by gvkey
if 
first.gvkey then prev_firm firm/* set prev_firm for first record of each gvkey */
if prev_firm ne firm then tenure 0/* audit firm changed: reset tenure */
tenure tenure 1;
prev_firm firm;  /* update prev_firm */
run

The datastep with the ‘by’ clause will repeat the step for each gvkey. I am assuming there are no ‘gaps’ here. If there are gaps in the data then you can set an extra variable to the fyear that the audit firm started. Instead of adding 1 to tenure, tenure would be fyear minus that beginning year.

best regards,

Joost

 

 Signature 

To reply/post new questions: Please use the group WRDS/SAS on Google Groups! http://groups.google.com/d/forum/wrdssas

Profile
 
 
Posted: 25 July 2013 06:43 PM   [ Ignore ]   [ # 4 ]
Newbie
Rank
Total Posts:  4
Joined  2013-07-25

Wow, you are Gorgeous!

Joost Impink - 25 July 2013 06:29 PM

hi Shoe,

You’re welcome.

Something like this to construct tenure variable:

/* sort the data by firm and year (needed for next data step) */
proc sort data work.by gvkey fyear;run;

/* compute tenure; assuming there is a variable called 'firm' to identify audit firm */
data z2;
set z;
retain tenure prev_firm/* will remember the values while processing the rows */
by gvkey
if 
first.gvkey then prev_firm firm/* set prev_firm for first record of each gvkey */
if prev_firm ne firm then tenure 0/* audit firm changed: reset tenure */
tenure tenure 1;
prev_firm firm;  /* update prev_firm */
run

The datastep with the ‘by’ clause will repeat the step for each gvkey. I am assuming there are no ‘gaps’ here. If there are gaps in the data then you can set an extra variable to the fyear that the audit firm started. Instead of adding 1 to tenure, tenure would be fyear minus that beginning year.

best regards,

Joost

Profile
 
 
Posted: 25 July 2013 07:31 PM   [ Ignore ]   [ # 5 ]
Administrator
Avatar
RankRankRankRank
Total Posts:  901
Joined  2011-09-19

in that case the code better work! smile

Joost

 Signature 

To reply/post new questions: Please use the group WRDS/SAS on Google Groups! http://groups.google.com/d/forum/wrdssas

Profile
 
 
Posted: 25 July 2013 07:34 PM   [ Ignore ]   [ # 6 ]
Newbie
Rank
Total Posts:  4
Joined  2013-07-25

It not only works, but works great!

Profile
 
 
Posted: 25 July 2013 08:17 PM   [ Ignore ]   [ # 7 ]
Administrator
Avatar
RankRankRankRank
Total Posts:  901
Joined  2011-09-19

That is a relief smile

Joost

 Signature 

To reply/post new questions: Please use the group WRDS/SAS on Google Groups! http://groups.google.com/d/forum/wrdssas

Profile
 
 
Posted: 20 September 2015 08:46 PM   [ Ignore ]   [ # 8 ]
Newbie
Rank
Total Posts:  6
Joined  2015-09-20

I have a large data set that does have gaps in it.  I know I need to use the written instructions below, but am not sure how to do that.  This first.gvkey always gives me trouble!  Can someone help?

Something like this to construct tenure variable:

/* sort the data by firm and year (needed for next data step) */
proc sort data = work.z ; by gvkey fyear;run;

/* compute tenure; assuming there is a variable called ‘firm’ to identify audit firm */
data z2;
set z;
retain tenure prev_firm; /* will remember the values while processing the rows */
by gvkey;
if first.gvkey then prev_firm = firm; /* set prev_firm for first record of each gvkey */
if prev_firm ne firm then tenure = 0; /* audit firm changed: reset tenure */
tenure = tenure + 1;
prev_firm = firm;  /* update prev_firm */
run;
The datastep with the ‘by’ clause will repeat the step for each gvkey. I am assuming there are no ‘gaps’ here. If there are gaps in the data then you can set an extra variable to the fyear that the audit firm started. Instead of adding 1 to tenure, tenure would be fyear minus that beginning year.

Profile
 
 
Posted: 21 September 2015 08:55 AM   [ Ignore ]   [ # 9 ]
Administrator
Avatar
RankRankRankRank
Total Posts:  901
Joined  2011-09-19

hi,

One way I can think of is to add another variable to retain: say firstYear.
Set firstYear for the first record, or when firm changes changes. Basically grab the fiscal year when the audit firm changes. Tenure would then be fyear - firstYear.

Hope this helps,

Joost

 Signature 

To reply/post new questions: Please use the group WRDS/SAS on Google Groups! http://groups.google.com/d/forum/wrdssas

Profile
 
 
Posted: 11 October 2015 12:35 AM   [ Ignore ]   [ # 10 ]
Newbie
Rank
Total Posts:  6
Joined  2015-09-20

I still cannot get this to work.  Like I said I cannot get the whole “first” thing to work and no one has been able to explain it to me.  I can only get firstyear to show up in the year of the change and it is a missing value in every other year.  So I cannot do the operation for subsequent years.  I have been working on this for weeks and still cannot get anything to work.

data tenure1;
set tenurer;
retain tenure prev_au;
by gvkey;
if first.gvkey then prev_au = au;
if prev_au ne au then tenure = 0;
THIS IS THE STEP THAT I CANNOT GET TO WORK NO MATTER WHAT I DO.  I NEED TO GET FIRSTYEAR TO CARRY DOWN UNTIL EITHER GVKEY CHANGES OR AU CHANGES AGAIN
tenure = fyear - firstyear;
prev_au = au; 
run;

Profile
 
 
Posted: 11 October 2015 12:41 AM   [ Ignore ]   [ # 11 ]
Newbie
Rank
Total Posts:  6
Joined  2015-09-20

I guess what I am asking is how do I grab firstyear for every row to which it applies?

Profile
 
 
Posted: 11 October 2015 01:15 AM   [ Ignore ]   [ # 12 ]
Newbie
Rank
Total Posts:  6
Joined  2015-09-20

Okay, so just to get an idea of my numbers I ran the code exactly as it was previously listed and in reviewing I am finding errors. 

I am trying to include a screenshot of the output, but I cannot paste.  I will include the output file as an attachment.

Look at the tenure numbers generated for gvkeys 1019, 1021, and 1034.  The first year is not resetting to zero and even with an auditor change the tenure number is continuing to count…

This is the code I used.  It was taken from earlier in the post but changes “firm” to “au” as that is the identifier for the auditor. 

data tenure2;
set tenurer;
retain tenure prev_au; /* will remember the values while processing the rows */
by gvkey;
if first.gvkey then prev_au = au; /* set prev_au for first record of each gvkey */
if prev_au ne au then tenure = 0; /* audit firm changed: reset tenure */
tenure = tenure+1;
prev_au = au;  /* update prev_au */
run;

Image Attachments
Capture1.PNG
Profile
 
 
Posted: 11 October 2015 01:17 AM   [ Ignore ]   [ # 13 ]
Newbie
Rank
Total Posts:  6
Joined  2015-09-20

This is another area where I am getting bad results…

Image Attachments
Capture2.PNG
Profile
 
 
Posted: 11 October 2015 08:59 AM   [ Ignore ]   [ # 14 ]
Administrator
Avatar
RankRankRankRank
Total Posts:  901
Joined  2011-09-19

hi,

Try this code (I did test it this time):

data au (keep gvkey fyear au conm);
set comp.funda;
/* auditor not missing -- note: Audit Analytics has better measure of auditor */
if au ne .;
/* subsample: years 2001-, firm with name starting with 'A' */
if substr(conm,1,1eq "A"
if 
fyear 2000;
/* general filter for funda */
if indfmt='INDL' and datafmt='STD' and popsrc='D' and consol='C' ;
run;

/* sort the data (is needed for data step with 'by') */
proc sort data=auby gvkey fyear;run;

/* create tenure as fyear - first year auditor started */
data tenure;
set au;
/* retain remembers variables accross observations */
retain prev_au auStartYear
by gvkey
if 
first.gvkey then do;
 
/* reset prev_au (new firm) and set auditor start year (auStartYear)*/
 
prev_au = . ;
 
auStartYear fyear;
end;
else do;
 
/* not first year and changed auditor: reset auStartYear */
 
if prev_au ne au then auStartYear fyear;
end
/* compute tenure */
tenure fyear auStartYear;
/* update prev_au */
prev_au au;  
run

Note that I am using AU from Compustat Funda; if you have Audit Analytics, you may want to use the auditor_fkey of AA (less measurement error).

Let me know if this works as it should.

Best,

Joost

 

 Signature 

To reply/post new questions: Please use the group WRDS/SAS on Google Groups! http://groups.google.com/d/forum/wrdssas

Profile
 
 
Posted: 11 October 2015 01:46 PM   [ Ignore ]   [ # 15 ]
Newbie
Rank
Total Posts:  6
Joined  2015-09-20

Oh wow!  Yes!  That looks perfect so far from what I can see.  That one is a keeper!  Thanks so much.  I would never ever have figured that out but everything looks good so far.  Thanks a bunch.  I also took your suggestion and used AA instead of Compustat.  It did drop a year of data (1996 start instead of 1995 start) but it is more consistent with my other data.  Thanks again.

Profile
 
 
   
1 of 2
1