Deleting common pair after merging with cross command

[rahul kumar singh]

I want to calculate distance between lat long of between household ids. The code I used is as follows
clear
use "C:\Users\SOM\OneDrive - Indian Institute of Technology Bombay\Desktop\PhD_thesis_data_kanzara\gps\househo ld_gps_location_google_map.dta"
rename (hhid head_name latitude longitude)( hhid1 head_name1 latitude1 longitude1)
cross using "C:\Users\SOM\OneDrive - Indian Institute of Technology Bombay\Desktop\PhD_thesis_data_kanzara\gps\househo ld_gps_location_google_map.dta"
drop if hhid1== hhid
geodist latitude1 longitude1 latitude longitude,gen(distance_vincenty)
geodist latitude1 longitude1 latitude longitude,gen(distance_haversine) sphere
in this case let for ids 1 and 2 we get two pairs (1,2) and (2,1) the distance between these two pairs will be same. i want to keep only one pair say (1,2). can you suggest what should I do.
Data after merging

* Example generated by -dataex-. For more info, type help dataex
clear
input int hhid1 double(latitude1 longitude1) int hhid double(latitude longitude distance_vincenty    distance_haversine)
1 77.3578571 20.6651418  3 77.3575606 20.6648568  .033826256685093856   .03369096703494456
3 77.3575606 20.6648568  1 77.3578571 20.6651418   .03382625668529262   .03369096703494456
1 77.3578571 20.6651418  4 77.3575485 20.6650475  .034529089640824545   .03439141012761076
4 77.3575485 20.6650475  1 77.3578571 20.6651418  .034529089640778395   .03439141012761076
3 77.3575606 20.6648568  4 77.3575485 20.6650475  .004852904673477477 .0048321225041347855
4 77.3575485 20.6650475  3 77.3575606 20.6648568 .0048529046734201685 .0048321225041347855
1 77.3578571 20.6651418  5 77.3575002 20.6650337  .039931793061539066  .039772572156142605
5 77.3575002 20.6650337  1 77.3578571 20.6651418   .03993179306139327  .039772572156142605
3 77.3575606 20.6648568  5 77.3575002 20.6650337  .008010249239978716  .007977571065833007
5 77.3575002 20.6650337  3 77.3575606 20.6648568   .00801024924006957  .007977571065833007
1 77.3578571 20.6651418  6 77.3574477 20.6649944   .04584715395176294   .04566432021418458
6 77.3574477 20.6649944  1 77.3578571 20.6651418   .04584715395185029   .04566432021418458
4 77.3575485 20.6650475  5 77.3575002 20.6650337  .005402747174080315  .005381205579727998
5 77.3575002 20.6650337  4 77.3575485 20.6650475  .005402747174105186  .005381205579727998
3 77.3575606 20.6648568  6 77.3574477 20.6649944   .01304514893077448  .012992873586428146
6 77.3574477 20.6649944  3 77.3575606 20.6648568  .013045148930648719  .012992873586428146
1 77.3578571 20.6651418  7 77.3573469 20.6649224  .057210565876799964  .056982368228243506
7 77.3573469 20.6649224  1 77.3578571 20.6651418  .057210565876624056  .056982368228243506
4 77.3575485 20.6650475  6 77.3574477 20.6649944  .011327900321212028  .011282700641271669
6 77.3574477 20.6649944  4 77.3575485 20.6650475  .011327900321051779  .011282700641271669
3 77.3575606 20.6648568  7 77.3573469 20.6649224  .023911269645074153  .023815927210059512
7 77.3573469 20.6649224  3 77.3575606 20.6648568  .023911269645198203  .023815927210059512
5 77.3575002 20.6650337  6 77.3574477 20.6649944  .005939288934780543  .005915565599479631
6 77.3574477 20.6649944  5 77.3575002 20.6650337  .005939288934919838  .005915565599479631
1 77.3578571 20.6651418  8 77.3574113 20.6649839  .049918488500280356  .049719421935199154
4 77.3575485 20.6650475  7 77.3573469 20.6649224    .0227133669725922  .022622701529399786
7 77.3573469 20.6649224  4 77.3575485 20.6650475   .02271336697268739  .022622701529399786
8 77.3574113 20.6649839  1 77.3578571 20.6651418  .049918488500233706  .049719421935199154
3 77.3575606 20.6648568  8 77.3574113 20.6649839  .016954876948828607  .016887113566055816
8 77.3574113 20.6649839  3 77.3575606 20.6648568   .01695487694873152  .016887113566055816
5 77.3575002 20.6650337  7 77.3573469 20.6649224  .017329263815016975   .01726005392712035
7 77.3573469 20.6649224  5 77.3575002 20.6650337   .01732926381493958   .01726005392712035
4 77.3575485 20.6650475  8 77.3574113 20.6649839  .015395678452089924   .01533426237481381
8 77.3574113 20.6649839  4 77.3575485 20.6650475  .015395678451985187   .01533426237481381
1 77.3578571 20.6651418  9 77.3572835 20.6648907   .06433009205478166   .06407349013859497
9 77.3572835 20.6648907  1 77.3578571 20.6651418    .0643300920546915   .06407349013859497
6 77.3574477 20.6649944  7 77.3573469 20.6649224  .011390079375972122  .011344592342489466
7 77.3573469 20.6649224  6 77.3574477 20.6649944  .011390079376082803  .011344592342489466
5 77.3575002 20.6650337  8 77.3574113 20.6649839  .009999152843099721  .009959249617845842
8 77.3574113 20.6649839  5 77.3575002 20.6650337  .009999152843036705  .009959249617845842
3 77.3575606 20.6648568  9 77.3572835 20.6648907   .03094651436478564  .030823157659931853
9 77.3572835 20.6648907  3 77.3575606 20.6648568  .030946514364674802  .030823157659931853
4 77.3575485 20.6650475  9 77.3572835 20.6648907  .029831789929681864   .02971272530504808
9 77.3572835 20.6648907  4 77.3575485 20.6650475  .029831789929662112   .02971272530504808
6 77.3574477 20.6649944  8 77.3574113 20.6649839  .004071788971915234  .004055554043316913
8 77.3574113 20.6649839  6 77.3574477 20.6649944   .00407178897168955  .004055554043316913
5 77.3575002 20.6650337  9 77.3572835 20.6648907  .024443558834852434  .024345968792529725
9 77.3572835 20.6648907  5 77.3575002 20.6650337  .024443558834932787  .024345968792529725
7 77.3573469 20.6649224  8 77.3574113 20.6649839  .007345075410399752 .0073156998614609114
8 77.3574113 20.6649839  7 77.3573469 20.6649224   .00734507541054561 .0073156998614609114
6 77.3574477 20.6649944  9 77.3572835 20.6648907  .018505680096308492  .018431806891616102
9 77.3572835 20.6648907  6 77.3574477 20.6649944    .0185056800961626  .018431806891616102
1 77.3578571 20.6651418 11 77.3573635 20.6647909   .05576891137734457  .055546197647863595
11 77.3573635 20.6647909  1 77.3578571 20.6651418   .05576891137737377  .055546197647863595
3 77.3575606 20.6648568 11 77.3573635 20.6647909  .022063102465210382  .021975123384303283
7 77.3573469 20.6649224  9 77.3572835 20.6648907 .0071202544518154925  .007091846820384856
9 77.3572835 20.6648907  7 77.3573469 20.6649224    .0071202544519485  .007091846820384856
11 77.3573635 20.6647909  3 77.3575606 20.6648568  .022063102465375142  .021975123384303283
4 77.3575485 20.6650475 11 77.3573635 20.6647909  .021584689696044896   .02149806963181075
11 77.3573635 20.6647909  4 77.3575485 20.6650475   .02158468969594528   .02149806963181075
1 77.3578571 20.6651418 12 77.3568858 20.6647254    .1089124458721655   .10847802698771365
8 77.3574113 20.6649839  9 77.3572835 20.6648907  .014448298144443164  .014390593386197107
9 77.3572835 20.6648907  8 77.3574113 20.6649839  .014448298144484334  .014390593386197107
12 77.3568858 20.6647254  1 77.3578571 20.6651418   .10891244587238458   .10847802698771365
5 77.3575002 20.6650337 11 77.3573635 20.6647909  .016374459074049937  .016308500923845382
11 77.3573635 20.6647909  5 77.3575002 20.6650337   .01637445907414185  .016308500923845382
3 77.3575606 20.6648568 12 77.3568858 20.6647254   .07540304463008887   .07510245312967531
12 77.3568858 20.6647254  3 77.3575606 20.6648568   .07540304463001914   .07510245312967531
6 77.3574477 20.6649944 11 77.3573635 20.6647909  .010634961698612899  .010591824193995542
11 77.3573635 20.6647909  6 77.3574477 20.6649944   .01063496169871284  .010591824193995542
4 77.3575485 20.6650475 12 77.3568858 20.6647254   .07440149288076585      .07410466969902
12 77.3568858 20.6647254  4 77.3575485 20.6650475   .07440149288085882      .07410466969902
5 77.3575002 20.6650337 12 77.3568858 20.6647254   .06900426840551871    .0687289613014099
12 77.3568858 20.6647254  5 77.3575002 20.6650337   .06900426840539937    .0687289613014099
1 77.3578571 20.6651418 13 77.3569502 20.6645447   .10229272151430825   .10188432436021004
7 77.3573469 20.6649224 11 77.3573635 20.6647909 .0037101770023627383  .003694493653913419
11 77.3573635 20.6647909  7 77.3573469 20.6649224  .003710177002435724  .003694493653913419
13 77.3569502 20.6645447  1 77.3578571 20.6651418   .10229272151411901   .10188432436021004
3 77.3575606 20.6648568 13 77.3569502 20.6645447   .06857066432381853   .06829707692217528
13 77.3569502 20.6645447  3 77.3575606 20.6648568   .06857066432400225   .06829707692217528
6 77.3574477 20.6649944 12 77.3568858 20.6647254   .06307411081319587   .06282248491816965
12 77.3568858 20.6647254  6 77.3574477 20.6649944   .06307411081312252   .06282248491816965
4 77.3575485 20.6650475 13 77.3569502 20.6645447    .0679153744719298    .0676439570261961
8 77.3574113 20.6649839 11 77.3573635 20.6647909 .0071225518837364716  .007093157969535096
11 77.3573635 20.6647909  8 77.3574113 20.6649839  .007122551883650792  .007093157969535096
13 77.3569502 20.6645447  4 77.3575485 20.6650475   .06791537447206526    .0676439570261961
7 77.3573469 20.6649224 12 77.3568858 20.6647254  .051701884852473526  .051495663594055496
12 77.3568858 20.6647254  7 77.3573469 20.6649224   .05170188485244265  .051495663594055496
5 77.3575002 20.6650337 13 77.3569502 20.6645447   .06255446788669154   .06230439925072215
13 77.3569502 20.6645447  5 77.3575002 20.6650337    .0625544678866699   .06230439925072215
1 77.3578571 20.6651418 14 77.3570159 20.6647558   .09438422978121276   .09400772090925508
14 77.3570159 20.6647558  1 77.3578571 20.6651418   .09438422978133376   .09400772090925508
9 77.3572835 20.6648907 11 77.3573635 20.6647909    .0092583315366793  .009221222303066616
11 77.3573635 20.6647909  9 77.3572835 20.6648907  .009258331536926923  .009221222303066616
3 77.3575606 20.6648568 14 77.3570159 20.6647558   .06086034933892053   .06061773477227844
6 77.3574477 20.6649944 13 77.3569502 20.6645447   .05661809493900837  .056391736234691174
13 77.3569502 20.6645447  6 77.3574477 20.6649944   .05661809493909648  .056391736234691174
14 77.3570159 20.6647558  3 77.3575606 20.6648568  .060860349338887156   .06061773477227844
8 77.3574113 20.6649839 12 77.3568858 20.6647254   .05900604506371607   .05877063689395524
12 77.3568858 20.6647254  8 77.3574113 20.6649839   .05900604506358183   .05877063689395524
end

copy up to and including the previous line ------------------

[Carlo Lazzaro]

Rahul:
I would go:

. bysort distance_haversine: g counter=_n

. drop if counter==2

[rahul kumar singh]

Dear Lazzaro,
Thanks, but this will not work as more than two observations for same distance. is there any method so that we can drop using ids combinations,

[Mike Lacy]

Instead of:

drop if hhid1== hhid

try

drop if hhid1 >=  hhid

My understanding here is that you are facing a problem that usually occurs with procedures that create all possible pairs. The preceding is a standard approach to keeping only one observation when pairs are identical except for the permuted id variables.

You could check this identity among pairs by doing the following before you drop any observations:

ds hhid hhid1, not
duplicates report `r(varlist)'

[rahul kumar singh]

Dear Mike and Lazzaro,
I have data on latitude and longitude of household ids. i want to calculate distance between each household. for that i cross merge this data to same data set so that i can get all possible pair of households. but by doing this, i get similar pairs twice such as for pair of household ids 1 and 2 is get (1,2) and (2,1) and a self pair like (1,1) and (2,2). i have dropped the self pair by using drop if hhid1==hhid. i want to delete one of the similar pair, like drop any one from (1,2) or (2,1). i hope now i explained the problem very well. please suggest a solution. Thanks
Original data is in format.

* Example generated by -dataex-. For more info, type help dataex
clear
input int hhid double(latitude longitude)
  1 77.3578571 20.6651418
  3 77.3575606 20.6648568
  4 77.3575485 20.6650475
  5 77.3575002 20.6650337
  6 77.3574477 20.6649944
  7 77.3573469 20.6649224
  8 77.3574113 20.6649839
  9 77.3572835 20.6648907
 11 77.3573635 20.6647909
 12 77.3568858 20.6647254
 13 77.3569502 20.6645447
 14 77.3570159 20.6647558
 15 77.3571849 20.6648706
 16 77.3570025  20.664546
 17 77.3568268 20.6645413
 18  77.356745 20.6645287
 19 77.3568791 20.6644142
 20 77.3566444 20.6645049
 21 77.3565907  20.664354
 22 77.3564794    20.6643
 23 77.3564083  20.664295
 24 77.3563185 20.6642499
 25 77.3561817 20.6642009
 26 77.3563051 20.6644848
 27 77.3563386 20.6645058
 28 77.3563762  20.664541
 29 77.3564352 20.6645861
 30 77.3565197 20.6646514
 31 77.3566028 20.6647066
 32  77.356682 20.6647584
 33 77.3567812  20.664806
 34 77.3567262 20.6648926
 35 77.3568711 20.6648738
 36 77.3567222 20.6649798
 37 77.3567665 20.6650488
 38  77.356906 20.6651787
 39 77.3568925 20.6649829
 40 77.3569113 20.6649842
 41  77.357091 20.6649127
 42 77.3571527  20.664944
 44  77.357205 20.6650394
 45 77.3572694 20.6650445
 46 77.3573203 20.6650557
 47 77.3583682 20.6642695
 48 77.3573552 20.6650633
 49  77.357087 20.6653223
 50 77.3570427 20.6652357
 51 77.3570199 20.6651379
 52 77.3573619 20.6652226
 53 77.3572479 20.6652608
 54 77.3569982 20.6653447
 55 77.3569542  20.665494
 56 77.3571993 20.6656885
 57 77.3572423 20.6656822
 58 77.3572745 20.6656722
 59 77.3572825 20.6656621
 60 77.3573013  20.665637
 61 77.3583694 20.6647749
 62 77.3573485 20.6656344
 63 77.3574464 20.6655867
 64 77.3575094 20.6655616
 65 77.3576006 20.6655126
 66 77.3578648 20.6653395
 67 77.3579627 20.6653244
 68 77.3588544 20.6647348
 69 77.3592527 20.6646319
 70 77.3593453 20.6645653
 71  77.359663 20.6646257
 72 77.3558469 20.6618896
 73 77.3595732 20.6646345
 77 77.3597582  20.664573
 78 77.3599138 20.6646232
 79 77.3601552 20.6644977
 80 77.3600427 20.6643871
 81 77.3601016 20.6644513
 82  77.359855 20.6643871
 84 77.3598295  20.664406
 85 77.3598965 20.6643821
 86 77.3601056 20.6646169
 87 77.3601941 20.6643759
 88 77.3601592 20.6642981
 89 77.3601808 20.6642366
 90 77.3601995 20.6641801
 91 77.3601726 20.6641625
 92 77.3601941 20.6641287
 93 77.3602799 20.6641048
 94 77.3603134 20.6641245
 95 77.3602799 20.6641048
 96 77.3602678 20.6640944
 97 77.3602172 20.6639693
 98 77.3600938 20.6636192
100 77.3600723 20.6635577
101 77.3600911 20.6637698
102 77.3600562 20.6638815
103 77.3599467 20.6639011
104  77.359838 20.6639174
105  77.360075 20.6639894
107 77.3599353 20.6640107
108 77.3600734 20.6641288
109 77.3598295 20.6642327
end

------------------ copy up to and including the previous line ------------------

[rahul kumar singh]

Thanks Mike,
It works!