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.
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;
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;
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
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!!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.