Hi Reeza,   Thank you indeed!   Yes, region is my administrative boundary and the WHERE condition works well for it. Now, my main question is: how to pick a single minimum distance for each health facility having in mind the following criteria?   One health facility should be linked to one cluster in a region One cluster can be linked to one or more health facilities in a region   Kind regards Teketo ... View more

Hi Reeza,   Many thanks for your unreserved kindly support.    Oh, I have got the main idea of the cross join. However, after the cross join, I am still worried about how to select health facilities that are linked to respective clusters based on the minimum distance and administrative boundary limit. Therefore, at the end, the joined dataset should fulfil the following conditions:   One health facility should be linked to one cluster One cluster can be linked to one or more health facilities All health facility and cluster linkage should be within regions boundary The least one minimum distance should be taken among a couple of cross joins   Once these conditions are fulfilled, I can do statistical analysis. Otherwise, the analysis will not give the correct figure and information about the problem I am studying.   Kind regards Teketo ... View more

Hi Reeza,   Many thanks.  Yes, each cluster has one location information; however, there are a number of people interviewed per each cluster. For example, in the fictitious data set, two women were interviewed in cluster 1. Since they were in the same cluster, they have the same location information; that is, lat = 14.0 and long = 38.7 is the same for both women.   In my actual data set, an average of 23 women were interviewed per one cluster; that is, a single coordinate or location information works for all the 23 women. Therefore, one location information is duplicated for 23 times for each individual woman in that particular cluster.   What is the importance of cross join or Cartesian product here? I don’t need a duplicate merge. I just need each cluster to link to one or more health facilities based on the minimum distance that each facility is located from a cluster. Therefore, one health facility will be linked to only one cluster; a single health facility can’t be linked to more than one cluster. But a single cluster can be linked to one or more health facilities depending on each facility proximity taking into consideration that both the health facility and the cluster are within the same region.     Therefore, the cross join, in this case, can’t work; it gives me a cross product.   Kind regards Teketo   ... View more

Hi Reeza,   Many thanks for your unreserved support. How do the GEODIST function work in calculating the distance between data set ‘x’ and data set ‘y’? Do the two data sets merged first to do the distance calculation?  If that is the case, how can I merge them?   The two data sets should be merged considering the minimum distance between cluster and health facility, and each cluster and health facility merge using the minimum distance should be within the respective regions. For instance, the nearest health facility to a cluster might be in other region, in this case, the merging is not appropriate. Each merge shouldn’t extend beyond region boundary.   I used the below SAS code: *Distance calculation using GEODIST function; Data dist; Set x y; distance = geodist (lat1, lon1, lat2, lon2, 'DK'); put 'Distance = ' distance 'kilometres'; run;   But this brought nothing, I haven’t got the distance matrix. It gives me a merged data set, which is inappropraite.    Here attached the SAS code for the fictitious data set.   *Population data; Data x; Input Cluster Lat1 Lon1 region age FP; datalines; 1 14.0 38.7 1 19 0 1 14.0 38.7 1 21 1 2 11.6 41.5 2 30 0 2 11.6 41.5 2 48 0 2 11.6 41.5 2 25 1 3 12.7 37.5 3 32 1 3 12.7 37.5 3 21 1 4 7.5 39.1 4 40 0 4 7.5 39.1 4 33 1 4 7.5 39.1 4 29 0 5 12.7 37.6 1 40 0 5 12.7 37.6 1 31 1 6 10.4 40.1 2 27 1 6 10.4 40.1 2 29 0 7 12.1 40.0 2 26 0 7 12.1 40.0 2 24 1 8 12.2 37.0 3 32 0 8 12.2 37.0 3 37 1 9 9.9 39.9 3 42 0 9 9.9 39.9 3 37 1 10 9.1 40.7 4 33 0 10 9.1 40.7 4 35 1 10 9.1 40.7 4 36 0 ; *Health facility data; Data y; Input ID Lat2 Lon2 region HFMA FPRS; Datalines; 1 13.6 39.1 1 1 0 2 13.3 38.9 1 2 1 3 10.7 40.1 2 1 1 4 10.3 40.2 2 1 1 5 11.4 40.7 2 1 1 6 10.5 39.5 3 2 0 7 11.6 39.6 3 1 1 8 11.3 37.9 3 2 1 9 9.1 35.6 4 2 0 10 9.5 34.8 4 1 1 11 14.3 37.8 1 2 0 12 13.5 39.4 1 1 1 13 10.4 40.2 2 2 0 14 9.2 40.3 2 1 1 15 9.9 39.8 3 2 1 16 11.0 37.1 3 1 1 17 10.6 39.4 3 2 0 18 8.5 34.5 4 2 1 19 7.2 38.2 4 1 1 20 8.8 35.8 4 2 1 ; run;   Kind regards Teketo ... View more

Hi Reeza,   Once the two data sets are merged properly, I have to do descriptive and regression analysis.  My main concern is how do these data sets be merged to run my statistical analysis?     Kind regards Teketo  ... View more

Hi Reeza,   Many thanks.   I have made some fictitious data set; DHS and Health facility, in excel.  Here attached to get some demonstration how I can manage the data sets using minimum distance and administrative boundary.   In addition to this, I love to see how I can manage the multiple observations per cluster which also impact the merging process and analysis in later stage.   Regards Teketo  ... View more