Hi Mspak, My solution to your another question could be applied to this senario without many changes: data want (drop=_:);   if _n_=1 then do;      set geog.mutualcom (obs=1 rename=(year=fyear zip=_zip)); dcl hash h(dataset:'geog.mutualcom(rename=(year=fyear zip=_zip))', multidata:'yes', hashexp:16); h.definekey('fyear'); h.definedata('_zip'); h.definedone(); end; set geog.comp;    _ct=0;    _rc=find();    do while (_rc=0);       if .< zipcitydistance(zipcode, _zip)<=60 then _ct+1; _rc=find_next(); end;     mutual_density=log(1+_ct); run; of couse not tested, so my code is subjected to typo and other errors. Regards, Haikuo Edit: Before running the code, I would clean up the tables to make sure all of the zip codes are legit per zipcitydistance(). ... View more

Patrick's direction is definitely applaudable. I have tried SQL first, and stop it after 30-mins of running. Here is my hash approach, I think we may not need to use hiter, as both table are identical. After making sure that all of the zipcodes are included in the sashelp.zipcodes, 407  data want (drop=_:); 408     if _n_=1 then do; 409        set comp( rename=(tic=_tic zip=_zip) obs=1); 410        dcl hash h(dataset: 'comp (rename=(tic=_tic zip=_zip))', multidata: 'yes'); 411        h.definekey('fyear','sic3'); 412        h.definedata('_tic','_zip'); 413        h.definedone(); 414      end; 415 416        set comp; 417           _ct=0; 418           _rc=h.find(); 419            do while (_rc=0); 420              if .<zipcitydistance(_zip,zip) <=60 then _ct+1; 421               h.has_next(result: _r); 422               if _r ne 0 then _rc=h.find_next(); 423               else leave; 424            end; 425            DENSITY_INDUSTRY=log(_ct); 426 427  run; NOTE: There were 66452 observations read from the data set WORK.COMP. NOTE: There were 1 observations read from the data set WORK.COMP. NOTE: There were 66452 observations read from the data set WORK.COMP. NOTE: The data set WORK.WANT has 66452 observations and 5 variables. NOTE: DATA statement used (Total process time):       real time           5:26.81       cpu time            5:17.99 Less than 6mins, Not too shady. BTW, the has_next() method is not a must-have here. You can just use: data want (drop=_:);    if _n_=1 then do;       set comp( rename=(tic=_tic zip=_zip) obs=1);       dcl hash h(dataset: 'comp (rename=(tic=_tic zip=_zip))', multidata: 'yes');       h.definekey('fyear','sic3');       h.definedata('_tic','_zip');       h.definedone();       dcl hiter hi('h');     end;       set comp ;          _ct=0;          _rc=h.find();           do while (_rc=0);             if .<zipcitydistance(_zip,zip) <=60 then _ct+1;              _rc=h.find_next();           end;           DENSITY_INDUSTRY=log(_ct); run; I was hoping has_next will help the efficiency, well, it turned out no difference. Regards, Haikuo ... View more

":" here is wildcards. 'col:' represents all of the variable names starting with 'col'. ... View more

I don't think format matters. data _null_; X=94365;/*x=1994365*/ a=put(datejul(x),mmddyy10.); b=datejul(x); put a; put b / b date9.; run; OP, please show some raw data of your julian dates, as you can see, I can't repeat your problem. Regards, Haikuo ... View more

Efficiency wise, I also suggest you take a look into hash object. After SAS 9.2, Hash is capable to do lots of summary and does support non-unique key. Regards, Haikuo ... View more

Thanks, Ksharp. I thought about it. I have a 'return' inside the do-loop, will that lead to 'leave' action in the context of this code? Haikuo ... View more