industry-specialist auditor dummy

[mei]

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.

[FloydNevseta · Accepted solution]

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;

[Haikuo]

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

[mei]

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!!