Hi Hai.Kuo, I ran the program and it is efficient. As the definition of industry density is log(1+ number of firms located within 60-mile radius in the same industry, should I change the DENSITY_INDUSTRY as log (1+ct) instead of log(_ct)? Thank you. Regards, mspak ... View more

Hi art297 and Hai.kuo, I tested program suggested by Hai.kuo, some error messages on log: 16   data geog.want (drop=_:); 17 18     if _n_=1 then do; 19 20        set geog.mutualcom (obs=1 rename=(year=fyear zip=_zip)); 21 22   dcl hash h(dataset:'geog.mutualcom(rename=(year=fyear zip=_zip))', multidata:'yes', 22 ! hashexp:16); 23 24   h.definekey('fyear'); 25 26   h.definedata('_zip'); 27 28   h.definedone(); 29 30   end; 31 32 33 34   set geog.comp; 35 36      _ct=0; 37 38      _rc=find();             ----             71 ERROR 71-185: The FIND function call does not have enough arguments. 39 40      do while (_rc=0); 41 42         if .< zipcitydistance(zipcode, _zip)<=60 then _ct+1; 43 44   _rc=find_next();          ---------          68 ERROR 68-185: The function FIND_NEXT is unknown, or cannot be accessed. 45 46   end; 47 48       mutual_density=log(1+_ct); 49 50   run; NOTE: The SAS System stopped processing this step because of errors. WARNING: The data set GEOG.WANT may be incomplete.  When this step was stopped there were 0          observations and 7 variables. NOTE: DATA statement used (Total process time):       real time           1.06 seconds       cpu time            0.03 seconds Thanks for help. I carefully study the data generated by submitting the program suggested by art297, the output is strange as firms in the same year have the same nutualnum and mutual_density. I came across the same problem too. The count function for firms within 60-mile radius is tricky, I re-calculated mutualnum using sum function instead as per follows: proc sql; create table mutualnum as select fyear, tic, sum(0<=mutualdist<=60) as mutualnum1 from geog.fundcom group by tic, fyear; quit; run; proc sql; create table geog.mutualden as select unique fyear, tic, mutualnum1, log(1+ mutualnum1) as mutual_density from mutualnum having not missing (calculated mutual_density); quit; quit; run; The correct result can be achieved by using sum function. I think I must carefully study why and in what circumstances that count function can be tricky? Thank you. mspak ... View more

Thanks HaiKuo for helps, I will test run the program too. Learning SAS Programming with helps from all the experts here is fun . Hope that I can master this skill one day!!! Goodnite (12 Marc 2012, 11.57pm) Regards, mspak ... View more

Thanks art297 for your kind helps especially to me who is a new SAS user. I ran the program and it proves successful. There are many obervations with different pairs of possible origin and destination. In fact I wish to have mutualnum for each tic-fyear. Therefore, the crsp_fundn is not important to be included. I have modified it to generate my desired output format. I came across a data step approach which can significantly reduce the processing time, namely DATA Step Hash Object. I am not sure whether this method can be used for this example and can this method further reduces the data storage and time? Have a nice day Regards, mspak ... View more

Thanks HaiKuo and Patrick, Both hiter and hash objects are new to me. Thanks for sharing. I learned something new here. The hash object provides a fast, easy way to perform lookups without sorting or indexing in data step. I think I have to take time to digest the program again by further reading on this method. Running the program in data step within 6 minutes is great for me!!! I always took a few hours to run the program in SQL. Will update in this discussion forum again in future if it proves successful. Regards, mspak ... View more

Thanks Patrick, I need time to digest your program suggested as I am new to SQL. I will test run the program, I am not sure will this take long time for my notebook to process it? Good nite (11 march 2012, 11.09pm now) Mspak. ... View more

Thanks in advance for your helps. Firm-year's zipcodes are origin zipcodes and the destination zipcode are other firms (other than origin) which are in the same SIC3. Firm-years have chance to be origin and destination among themselves for the same SIC3. Regards, mspak ... View more

Duplicates to be eliminated. The uniqueness to be identified by 1. tic 2. fyear ... View more

Hi again, The observations are unique for each firm-year. Each firm is possible to have same zipcode for every year. I couldn't remain only to have unique zip, tic and sic3 as firms might change their headquarters to another area which has different zipcodes, as such, you might see them repeated. Thank you and I hope my explanation is clear. mspak ... View more

Hi Patrick, The datafile contains all firms with their hardquarters' zipcode in US. Let say, for example the following: TIC FYEAR SIC ZIP AA  2003   246  XXXXX BB  2003   246  XXXXX CC  2003   246  XXXXX We assume that firms with SIC code of 246 only 3 firms. Then I have to calculate the distance between: AA - BB AA - CC If the distance between AA-BB and AA-CC are within 60 mile-radius, then industry density for firm AA = log (2+1) For firm BB, I have to calculate distance between: BB-AA BB-CC If only distance between BB-AA are within 60 mile-radius, the density = LOG (1+1) AND SO FORTH. ... View more