The following perhaps can work:
*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 absolute market share difference between current auditor & closest competitors in the market (market size is depend on CBSA, SIC2 and fyear); data cluster.dist (drop=_:); if _n_=1 then do; set sum3( rename=(zip1=_zip1) obs=1); dcl hash h(dataset: 'sum3 (rename=(zip1=_zip1 fee_shr = _fee_shr))', multidata: 'yes'); h.definekey('fyear','sic2', 'cbsa'); h.definedata(all: 'YES'); h.definedone(); end; set sum3; _ct=0; _rc=h.find(); do while (_rc=0); distance = zipcitydistance(_zip1,zip1); shr_diff = abs(sum(fee_shr, - _fee_shr)); if zipcitydistance(_zip1,zip1) =min(zipcitydistance(_zip1,zip1)) and abs(sum(fee_shr, - _fee_shr)) = min (abs(sum(fee_shr, - _fee_shr))) then _ct1+1; h.has_next(result: _r); if _r ne 0 then _rc=h.find_next(); else leave; end; run;
... View more