topic Re: industry-specialist auditor dummy in SAS Procedures

industry-specialist auditor dummy

mei — Wed, 16 May 2012 09:03:52 GMT

Dear all,

I need to identify for each company code (cik number) whether its auditor is a industry-specialist auditor.

An industry-specialist auditor is firstly required to be identified for each sic2 code (representing industry) for every financial year (fyear) by finding who is the auditor (represented by auditor_fkey) that possesses the highest value of client's (cik number)'s total assets (at).

Once an industry-specialist auditor is chosen for each industry for each financial year, then an industry-specialist auditor dummy need to be created. If the auditor of the company=industry-specialist auditor, dummy=1, otherwise =0.

For example: go to row 7683 to 7773 for sic 80 for financial year 2001, all the total assets value for auditor 1 (PWC) 2, 3 , 4 , 5 need to be summed up for the same sic code. Then we compare which auditor owns the highest value of total asset value of its client. Then that is the industry-specialist auditor for sic code 80. Then we go to each firm from 7683 till 7773 and create a dummy if the auditor=industry-specialist auditor.

This then apply to each sic code for each financial year.

Can you help me on this?

Data is attached.

Re: industry-specialist auditor dummy

FloydNevseta — Wed, 16 May 2012 12:35:54 GMT

Try this:

proc summary data=test nway;
class fyear sic2 auditor_fkey;
var at;
output out=test_sum (drop=_:) sum=;
run;

proc sort data=test_sum;
by fyear sic2 descending at;
run;

data ind_spec;
set test_sum;
by fyear sic2;
if first.sic2;
run;

proc sql;
create table dummy as
select
   a.*,
   case
      when b.auditor_fkey is not null then 1
      else 0
   end as dummy
from
   test    a
   left join
   ind_spec   b
      on
      a.fyear = b.fyear and
      a.sic2 = b.sic2 and
      a.auditor_fkey = b.auditor_fkey
;
quit;

Re: industry-specialist auditor dummy

Haikuo — Wed, 16 May 2012 12:36:52 GMT

I have not run through all of your obs, so I don't how long it would take to run.

libname test 'h:\temp\';

proc sql;

create table want as

select distinct b.*, case when a.fyear=b.fyear and

a.sic2=b.sic2 and

a.auditor_fkey=b.auditor_fkey and

not missing(b.auditor_fkey)

then 1

else 0

end as dummy

from test.test b

left join

(select fyear, sic2, auditor_fkey from

(select fyear, sic2, auditor_fkey, sum(at) as atsum from test.test

group by fyear, sic2, auditor_fkey)

group by fyear, sic2

having atsum=max(atsum)) a

on a.fyear=b.fyear and

a.sic2=b.sic2 and

a.auditor_fkey=b.auditor_fkey ;

quit;

Regards,

Haikuo

Re: industry-specialist auditor dummy

mei — Fri, 18 May 2012 06:01:36 GMT

Hi Hai Kuo:

From the log:

NOTE: The query requires remerging summary statistics back with the original data.

I guess this does not matter right?

Thanks.

The results from the above 2 programs are similar, thanks a lot!!