Dear all,
The following SQL program is to calculate the market share difference between an audit firmwith its closest (in terms of distance) competitor in the audit market. The step 4 below - to find the closest audit firm is too time consuming. I would be much appreciate if anyone can find a solution that can save the processing time, possibly in hash object.
*step 1: calculate the total fee of each auditor in the market;
proc sql; create table sum1 as select *,sum(audit_fees) as fee_sum from cluster.forum group by auditor_fkey, fyear,sic2,cbsa; quit;
*step 2: calculate the total fees charged in the market; proc sql; create table sum2 as select *,sum(audit_fees) as market_fee from sum1 group by fyear,sic2,cbsa; quit;
*step 3: calculate the market share of each auditr in the market;
data sum3; set sum2; fee_shr = fee_sum/market_fee; run;
*step 4: calculate the closest competitors in the market (market size is depend on CBSA, SIC2 and fyear);
proc sql; create table cluster.dist as select a.*, min(zipcitydistance(a.zip1,b.zip1)) as min_dist from sum3 a , sum3 b group by a.fyear, a.sic2, a.cbsa having not missing(calculated min_dist); quit;
*step5: only output the data with closest competitors in the market;
Thank you in advance.
Regadrs,
MSPAK
... View more
