You might have a better source for lat/long than sashelp...but this might get you started: (yeah for spherical geometry) %let pi180=0.0174532925199433; proc sort in=tmp.testdata out=work.testdata; by gvkey fyear; run; proc sort in=tmp.testdata out=work.testdata_srt nodupkey; by gvkey; run; proc sql; create table work.testdata_geo as select t1.gvkey ,t1.fyear ,t1.sic3 ,t1.zipcode ,t2.y as latitude_y ,t2.x as longitude_x from work.testdata_srt t1 left outer join sashelp.zipcode t2 on input(t1.zipcode,5.)=t2.zip; alter table work.testdata_geo add constraint pk_tg primary key (gvkey); create table work.clusters as select t1.sic3 ,t1.zipcode ,min(t2.fyear) as Cluster_Start from work.testdata_geo t1, work.testdata_geo t2 where t1.sic3=t2.sic3 and (7921.6623*arsin(sqrt((sin((&pi180*t2.latitude_y - &pi180*t1.latitude_y)/2))**2 + cos(&pi180*t1.latitude_y)*cos(&pi180*t2.latitude_y)*(sin((&pi180*t2.longitude_x - &pi180*t1.longitude_x)/2))**2))) < 50 group by t1.sic3, t1.zipcode; create table want as select t1.* ,t2.cluster_start ,t1.fyear - t2.cluster_start + 1 as Cluster_Age from tmp.testdata t1 left outer join work.clusters t2 on t1.sic3=t2.sic3 and t1.zipcode=t2.zipcode; quit;
... View more
