Announcement

Collapse
No announcement yet.
X
Collapse
  • Page of 1
  • Filter
  • Time
  • Show
Clear All
new posts
  • #1

    How to merge my 2 datasets without resorting to m:m merge

    I had earlier posted a different question about my data here. Where Clyde helpfully suggested that m:m should not be performed.
    I will hereby post examples of both of my datasets:
    Household data
    Code:
    * Example generated by -dataex-. For more info, type help dataex
clear
input int year str19 DNAME long CID byte(C_AGE C_SEX C_READ C_ENGL C_ARITH)
2019 "ASTORE" 104534 11 1 5 5 6
2019 "ASTORE" 104475  6 0 . . .
2019 "ASTORE" 104569 15 1 5 5 6
2019 "ASTORE" 103761  8 0 5 5 6
2019 "ASTORE" 104121  7 0 5 . 6
2019 "ASTORE" 103441 10 1 5 5 6
2019 "ASTORE" 104453 10 0 5 5 6
2019 "ASTORE" 104215  5 0 3 4 5
2019 "ASTORE" 104142 16 0 5 5 6
2019 "ASTORE" 103601  4 1 . . .
2019 "ASTORE" 103431 12 1 5 5 6
2019 "ASTORE" 104635  4 0 . . .
2019 "ASTORE" 103550  5 1 2 3 3
2019 "ASTORE" 103773  9 0 5 5 6
2019 "ASTORE" 104613  3 0 . . .
2019 "ASTORE" 103184  6 1 1 1 1
2019 "ASTORE" 104292 10 0 3 5 6
2019 "ASTORE" 104327 12 0 5 5 6
2019 "ASTORE" 103354  8 0 4 4 5
2019 "ASTORE" 103546  6 0 1 1 1
2019 "ASTORE" 104577 12 0 5 5 6
2019 "ASTORE" 104474  3 0 . . .
2019 "ASTORE" 103992 14 1 1 1 1
2019 "ASTORE" 103074  7 0 5 5 6
2019 "ASTORE" 103357  8 1 5 5 6
2019 "ASTORE" 104180  5 0 4 4 4
2019 "ASTORE" 104028  9 1 5 2 6
2019 "ASTORE" 104030  7 1 4 1 5
2019 "ASTORE" 104043  8 1 5 5 6
2019 "ASTORE" 103805  5 0 2 2 2
2019 "ASTORE" 103429 10 0 5 5 6
2019 "ASTORE" 104270 16 1 5 5 6
2019 "ASTORE" 104212 12 0 5 5 6
2019 "ASTORE" 103314 10 0 5 5 6
2019 "ASTORE" 104507  3 0 . . .
2019 "ASTORE" 104060  3 1 . . .
2019 "ASTORE" 104101 10 0 5 5 6
2019 "ASTORE" 103134  8 1 4 5 6
2019 "ASTORE" 104302  8 0 4 4 5
2019 "ASTORE" 103180 14 1 5 4 6
2019 "ASTORE" 103078  3 1 . . .
2019 "ASTORE" 103782 12 0 5 5 6
2019 "ASTORE" 103341  8 0 5 4 6
2019 "ASTORE" 103585 10 0 5 1 6
2019 "ASTORE" 104406 12 0 5 5 5
2019 "ASTORE" 103715 16 0 5 5 6
2019 "ASTORE" 103574 15 0 5 5 5
2019 "ASTORE" 104416 10 0 5 5 6
2019 "ASTORE" 103511  3 0 . . .
2019 "ASTORE" 104651 14 0 5 5 6
2019 "ASTORE" 104237  8 0 5 5 6
2019 "ASTORE" 103126  8 1 5 5 6
2019 "ASTORE" 104258 12 1 5 5 6
2019 "ASTORE" 103777  4 1 . . .
2019 "ASTORE" 103139 16 1 5 5 6
2019 "ASTORE" 104347  7 1 3 3 4
2019 "ASTORE" 104603 14 0 5 5 6
2019 "ASTORE" 104572  8 0 1 1 1
2019 "ASTORE" 103238  5 0 1 1 1
2019 "ASTORE" 103487  7 0 2 3 4
2019 "ASTORE" 103560  8 0 3 1 3
2019 "ASTORE" 104294  5 0 1 1 1
2019 "ASTORE" 104615  3 0 . . .
2019 "ASTORE" 104496 10 0 1 1 1
2019 "ASTORE" 104493 12 0 5 5 5
2019 "ASTORE" 103656 10 0 5 5 6
2019 "ASTORE" 104192 16 1 5 5 5
2019 "ASTORE" 104324  7 0 3 4 5
2019 "ASTORE" 103330  9 0 5 5 6
2019 "ASTORE" 104722  8 0 1 1 1
2019 "ASTORE" 103592 13 0 5 4 6
2019 "ASTORE" 104395  6 1 2 3 4
2019 "ASTORE" 103997 14 1 5 5 6
2019 "ASTORE" 103882 14 1 5 5 6
2019 "ASTORE" 104476  8 0 5 5 6
2019 "ASTORE" 103638 10 2 1 1 1
2019 "ASTORE" 103859  5 0 2 2 2
2019 "ASTORE" 103197 10 1 5 5 6
2019 "ASTORE" 103483  4 0 . . .
2019 "ASTORE" 103510  5 0 1 1 1
2019 "ASTORE" 104646 13 1 5 5 6
2019 "ASTORE" 103958  4 1 . . .
2019 "ASTORE" 104358  7 0 4 1 5
2019 "ASTORE" 103823 11 0 5 5 6
2019 "ASTORE" 103688  6 1 . . .
2019 "ASTORE" 104524 16 0 5 5 6
2019 "ASTORE" 103203 15 1 5 5 6
2019 "ASTORE" 104218 16 0 4 5 .
2019 "ASTORE" 103687 10 1 5 5 6
2019 "ASTORE" 103043  3 1 . . .
2019 "ASTORE" 103678 14 1 1 1 1
2019 "ASTORE" 103233 12 1 5 4 5
2019 "ASTORE" 103166  9 1 5 5 6
2019 "ASTORE" 104723  6 0 4 4 3
2019 "ASTORE" 104571  8 0 1 1 1
2019 "ASTORE" 103311  5 0 1 1 1
2019 "ASTORE" 104452  3 0 . . .
2019 "ASTORE" 103517 10 1 5 5 6
2019 "ASTORE" 103731  3 1 . . .
2019 "ASTORE" 104029  5 0 3 4 4
end
label values C_SEX C_SEX_lbl
label def C_SEX_lbl 0 "Male", modify
label def C_SEX_lbl 1 "Female", modify
label values C_ENGL C_ENGL_lbl
label def C_ENGL_lbl 1 "Beginner/Nothing", modify
label def C_ENGL_lbl 2 "Capital letters", modify
label def C_ENGL_lbl 3 "Small letters", modify
label def C_ENGL_lbl 4 "Words", modify
label def C_ENGL_lbl 5 "Sentences", modify
label values C_ARITH C_ARITH_lbl
label def C_ARITH_lbl 1 "Beginner/Nothing", modify
label def C_ARITH_lbl 2 "Recognition of 1-9", modify
label def C_ARITH_lbl 3 "Recognition of 10-99", modify
label def C_ARITH_lbl 4 "Recognition of 100-200", modify
label def C_ARITH_lbl 5 "Subtraction", modify
label def C_ARITH_lbl 6 "Division", modify
label var year "SYEAR" 
label var DNAME "DNAME" 
label var CID "CID" 
label var C_AGE "C_AGE" 
label var C_SEX "Gender of the child" 
label var C_READ "Reading in Local/National language" 
label var C_ENGL "Highest learning level of child in English." 
label var C_ARITH "Highest learning level of child in Arithmetic."
    Air pollution data
    Code:
    * Example generated by -dataex-. For more info, type help dataex
clear
input float(year month modate) str25 DNAME float comean double no2mean float(pm25mean so2mean hummean precmean tempmean TI_Count average_diff)
2011  1 612 "Bagh" 87.17934          1.370e+15      1.28e-08     2.83e-09  .003127968  1.1696461  3.866799  0 -3.9859924
2011  2 613 "Bagh" 91.09612          1.610e+15      1.93e-08     2.14e-09  .004525435  11.461983 4.0603547 12  -4.060998
2011  3 614 "Bagh"  87.0575          1.420e+15      2.12e-08     1.84e-09  .006319631  3.2977514 10.610514  0  -4.098527
2011  4 615 "Bagh"  87.0575          1.420e+15      2.12e-08     1.84e-09  .007800096  3.0574124 13.949953  4 -4.3325086
2011  5 616 "Bagh" 96.89316          1.440e+15      1.78e-08     1.62e-09  .009111885    1.23322  22.08506  0 -4.3246684
2011  6 617 "Bagh"  98.6109          2.030e+15      4.32e-08     1.53e-09  .011554516 .011838347  23.15986  4 -4.3497868
2011  7 618 "Bagh" 94.11146          1.600e+15      4.60e-08     1.06e-09  .014972327   6.880902 21.223934  0 -4.3655386
2011  8 619 "Bagh" 94.68835          7.640e+14      2.83e-08     8.78e-10   .01569618   9.354296 20.463957  0 -4.3588743
2011  9 620 "Bagh" 88.77015          9.940e+14      2.10e-08     1.33e-09  .012881536   3.062021 18.628471  4 -4.2478113
2011 10 621 "Bagh" 83.31566          1.010e+15      2.07e-08     2.21e-09  .007134011   .6190753  15.21117  0  -4.280564
2011 11 622 "Bagh" 80.98125          1.240e+15      1.69e-08     2.53e-09   .00527302  1.1745063 11.935515  4 -4.1260953
2011 12 623 "Bagh" 87.18204          1.090e+15      1.60e-08     3.04e-09  .003115135  1.0088751  6.786922  0  -4.023637
2012  1 624 "Bagh" 92.54285   1213482712170496 1.9973905e-08 2.694217e-09  .003151011    3.60239  1.976028  0  -3.997411
2012  2 625 "Bagh" 94.17786   1651238663880704 2.6739974e-08 2.119005e-09  .003935738   6.656986  2.990808  8  -3.948461
2012  3 626 "Bagh" 89.34711   1923520296648704  4.118444e-08 2.103637e-09  .005118794    2.77118  9.004811  0  -4.066264
2012  4 627 "Bagh" 85.55315   1377365410185216 1.9160225e-08 1.439295e-09  .008139996   3.449885 13.582783  4  -4.256367
2012  5 628 "Bagh" 93.66323   1484169502785536 1.6818568e-08 1.450042e-09  .008624663  1.0974368 18.946047  0   -4.40973
2012  6 629 "Bagh"  89.9923   2322958160756736 2.2464176e-08 1.272671e-09  .007725833   .4800621  23.47219  4  -4.335492
2012  7 630 "Bagh" 96.36054   1990996984332288  3.926007e-08 1.382495e-09  .013052827  1.6001686  23.59337  0 -4.3446145
2012  8 631 "Bagh" 91.70353   1486034122571776  3.938829e-08 1.134257e-09  .015443794   6.004316 21.104954  0  -4.300043
2012  9 632 "Bagh" 86.64808    709125138808832 2.1461503e-08 1.394257e-09  .012638452   4.914126 18.500006  4  -4.262113
2012 10 633 "Bagh" 82.67539   1992372044955648  1.596188e-08 2.075942e-09  .006487353   .3261698  14.36064  0 -4.2243185
2012 11 634 "Bagh" 80.58346   2187282694012928 1.9703734e-08 2.356162e-09  .004225512    .772775  10.42697  4 -4.0962276
2012 12 635 "Bagh"  84.7132   2212265914793984   2.10318e-08 2.640241e-09 .0035673245   2.835265  6.896488  0  -3.999688
2013  1 636 "Bagh" 88.14882        1.43485e+15   3.38527e-08  2.84644e-09  .003168737   1.939727  4.069512  0  -4.087565
2013  2 637 "Bagh" 88.97813        1.38544e+15   2.12863e-08  2.30604e-09  .004725732  10.852698  5.107724 12 -4.0672083
2013  3 638 "Bagh" 82.49726        1.44797e+15    1.9406e-08  2.08583e-09  .006899995   3.105056 11.148764  0  -4.193263
2013  4 639 "Bagh" 84.90105        1.70056e+15   1.91698e-08  1.60761e-09  .008349918    .978727 14.462934  4 -4.3708396
2013  5 640 "Bagh" 87.36565        1.79451e+15   1.92258e-08  1.42456e-09  .008495155    .436298  20.53282  0 -4.3437257
2013  6 641 "Bagh" 88.56911        1.67749e+15   2.93205e-08  1.43239e-09   .01107857  2.2108924  23.47961  4 -4.3087754
2013  7 642 "Bagh" 94.32934        1.70467e+15   4.26452e-08  1.10985e-09  .015565437  4.3432565  22.15843  0   -4.37462
2013  8 643 "Bagh" 93.27677        6.56289e+14   3.01487e-08  1.05225e-09  .015790226  10.545626 20.219236  0 -4.3856983
2013  9 644 "Bagh" 92.97852        1.53361e+15   3.76056e-08  1.49855e-09   .01236818   2.818759 18.473186  4 -4.2893515
2013 10 645 "Bagh" 84.33326        9.63012e+14   3.16654e-08  1.90718e-09  .008449645    .796231 15.827604  0 -4.2110353
2013 11 646 "Bagh" 80.77211        1.53196e+15   1.61689e-08  2.32661e-09  .003981657  1.0914773 10.568498  4 -4.0631094
2013 12 647 "Bagh" 84.74134        1.76257e+15   1.95795e-08  2.62321e-09  .002851172  .57528985  6.718508  0 -3.9797506
2014  1 648 "Bagh" 85.21386   1321478926630912  1.207336e-08 2.651676e-09   .00313978  1.1320031  5.004186  0 -3.8397684
2014  2 649 "Bagh" 89.56825    938583867260928  2.415155e-08 2.201597e-09  .003844974   4.688405 4.7114954 12 -4.0005713
2014  3 650 "Bagh" 86.72038   1562112690225152 2.3130315e-08 1.617281e-09  .005940275   8.582874 8.0801525  0 -4.1475873
2014  4 651 "Bagh"        .   1669715982286848 1.4075296e-08 1.436485e-09  .007837898   3.665759  13.72976  4 -4.1492195
2014  5 652 "Bagh" 91.98306   2213897868148736 2.1218913e-08 1.326236e-09  .009697257   1.948653 18.486076  0 -4.3637557
2014  6 653 "Bagh" 87.15491   1797170143428608  2.779732e-08 1.309768e-09  .009770347  .23367853 23.834375  4  -4.259127
2014  7 654 "Bagh" 91.82771   1738933138358272  4.959615e-08 1.272194e-09  .014765512   5.969679 22.429613  0  -4.396477
2014  8 655 "Bagh" 87.97823   1580905051193344  4.000032e-08 1.318746e-09  .014372448  3.6941996 20.801445  0 -4.2910438
2014  9 656 "Bagh" 90.11967   1370824879636480  2.706414e-08 1.434484e-09  .013294342   5.507071 18.285784  4 -4.2718487
2014 10 657 "Bagh" 85.62978   1504632203378688  2.557196e-08 2.127297e-09  .008168736   .7135426 14.669197  0 -4.1769924
2014 11 658 "Bagh"  81.5078   1004572583657472  1.671437e-08  2.34133e-09 .0045229862   3.488017 10.963312  4 -4.1058745
2014 12 659 "Bagh"    90.72   1975702874226688  1.806155e-08  3.02111e-09  .002959271  .05335829  7.737238  0  -4.016124
2015  1 660 "Bagh" 95.47401        1.59479e+15   2.31832e-08  2.73419e-09  .003192125  1.9521163  5.684761  0  -4.028019
2015  2 661 "Bagh" 94.07554        1.25028e+15   2.70163e-08  2.50889e-09  .004779678   8.310263  6.878791 12 -4.0533495
2015  3 662 "Bagh" 91.07442        1.70294e+15   2.10845e-08  1.75401e-09  .006707262   10.03192  8.832126  0  -4.148861
2015  4 663 "Bagh" 90.42397        1.52653e+15   2.34951e-08  1.65462e-09  .009787794  4.3130617 14.528955  4 -4.2771583
2015  5 664 "Bagh" 91.87411        1.99664e+15   2.00569e-08  1.49132e-09  .010611065   .7848894 19.807613  0 -4.3881235
2015  6 665 "Bagh" 90.01115         2.5208e+15   2.37773e-08  1.41252e-09  .011354707  2.1904905 21.490427  4 -4.3949633
2015  7 666 "Bagh" 95.15714        1.52115e+15   4.07979e-08   1.1332e-09  .016110012   8.216532  21.37282  0 -4.4067082
2015  8 667 "Bagh"  90.6526        1.71113e+15   3.27492e-08  1.17149e-09  .015513403    6.43141 20.305904  0  -4.292745
2015  9 668 "Bagh" 91.18709        1.60216e+15   1.67013e-08  1.70442e-09  .010465883   5.070981 18.652908  4  -4.309525
2015 10 669 "Bagh" 87.68997        1.41633e+15   2.25203e-08  2.13589e-09  .007712538   2.860581 14.973003  0 -4.2506394
2015 11 670 "Bagh" 93.82375        2.31724e+15   2.26985e-08  2.91446e-09  .005785976  2.4009426 10.178153  4  -4.204908
2015 12 671 "Bagh" 88.40134        1.44959e+15   1.89977e-08  3.04636e-09  .004373158   1.054227  6.789611  0 -4.0991936
2016  1 672 "Bagh" 89.19608   1616098348761088  2.027608e-08 2.749651e-09  .004250266  2.7115405  5.903308  0  -4.170458
2016  2 673 "Bagh" 97.50085    927706124386304 2.0700513e-08  2.92747e-09   .00477201  2.0795426  8.013728  8  -4.035442
2016  3 674 "Bagh" 88.66555    956567432200192  2.312301e-08 1.892909e-09  .007230157  11.160146 10.407402  0  -4.184724
2016  4 675 "Bagh" 83.81638   2181041636769792 1.8102016e-08 1.631641e-09    .0094289   1.497066  15.41734  4 -4.3061914
2016  5 676 "Bagh" 92.90459   2386589242097664  2.481389e-08 1.356736e-09  .010680747   1.567482  21.27681  0 -4.3301845
2016  6 677 "Bagh" 93.44053 2179736503582719.8 3.4619948e-08 1.387683e-09  .012849852   .5531863 23.800547  4  -4.341146
2016  7 678 "Bagh" 90.54871   1632678935789568  4.002403e-08 1.063075e-09  .015610992   8.554562 21.814747  0 -4.3762736
2016  8 679 "Bagh" 92.09718   1616700113944576 2.0619135e-08 1.146415e-09  .015145776   6.245529   20.5169  0 -4.3861656
2016  9 680 "Bagh" 89.04362   1653426177966080  3.606842e-08 1.747257e-09  .012386053  2.6152856 19.191677  4 -4.2304864
2016 10 681 "Bagh" 86.99828   1153376121257984 3.0299812e-08 2.293747e-09   .00693867  .13529702  16.39858  0 -4.2311153
2016 11 682 "Bagh" 96.48254   2144521596960768  3.799113e-08 2.813907e-09  .003335137  .13115884 12.600725  4   -3.97206
2016 12 683 "Bagh" 95.28899   1999163394883584   2.72796e-08 3.118837e-09  .002608434  .04939467 10.963993  0  -3.976992
2017  1 684 "Bagh"  97.2655   3133651759923200  2.353207e-08 2.781802e-09  .003899978   10.22799  2.209425  0 -3.8157685
2017  2 685 "Bagh" 93.16988  775373600063488.1 1.6407757e-08 2.869919e-09  .005185057   4.971684   6.21266 12  -3.888585
2017  3 686 "Bagh" 86.10243    578040807555072  2.097392e-08 1.578811e-09  .006854673   4.148264 9.1480875  0 -4.0602155
2017  4 687 "Bagh" 84.08228   1541824539787264  2.588715e-08 1.510895e-09  .009454248   5.532279 16.187765  4 -4.2857304
2017  5 688 "Bagh" 94.94907   2601653354102784  2.525729e-08 1.475436e-09  .010794267   .6998675 20.553614  0  -4.401654
2017  6 689 "Bagh" 91.51187   2134342860013568  4.232872e-08 1.384116e-09   .01230925  4.2286587   22.1469  4  -4.367141
2017  7 690 "Bagh" 93.95013   1869428538998784  3.980618e-08 1.048432e-09  .016079564   7.330976 21.574024  0  -4.354427
2017  8 691 "Bagh" 91.38566   1720190437949440  4.518098e-08 1.328456e-09  .015140137  4.7383447  20.83118  0 -4.2887754
2017  9 692 "Bagh"  90.4781   1887291173765120 2.6163583e-08 1.646634e-09  .011051876  2.5170274  19.09372  4 -4.2901754
2017 10 693 "Bagh" 91.05032   1155190140960768  4.078146e-08 2.408569e-09  .005751996 .028918317 16.934206  0 -4.2425785
2017 11 694 "Bagh" 97.14627   2172625111482368  4.539849e-08 3.224996e-09 .0037931255  .51798505  10.77063  4 -4.0305734
2017 12 695 "Bagh" 90.47581 1883981163266048.3 1.4419516e-08 2.925485e-09   .00347747  1.3165545  7.327122  0 -3.8749466
2018  1 696 "Bagh" 93.36389   1088348101804032  2.582001e-08 3.078299e-09  .003070038   .6975487  6.413716  0 -3.8943734
2018  2 697 "Bagh" 92.23473   1476527145353216  2.196152e-08 2.494247e-09 .0043228734   4.804201    7.1619 12 -4.0322847
2018  3 698 "Bagh" 86.18471    995302064521216  2.179972e-08 1.831236e-09  .006138324   2.908453  12.29359  0 -4.1187353
2018  4 699 "Bagh" 86.29247    887766686629888  3.213903e-08 1.651941e-09  .008195323   7.128837 15.766562  4 -4.2047443
2018  5 700 "Bagh"  92.3371   1731940931600384  3.041294e-08 1.574394e-09   .00906693   1.838588 19.016413  0 -4.2863297
2018  6 701 "Bagh" 93.35083   2476867374284800   4.49902e-08 1.538027e-09  .010944417  1.8576046 23.163395  4  -4.321526
2018  7 702 "Bagh"   88.771   1669264876503040  4.689268e-08 1.137032e-09  .016026296  10.900517 21.181334  0   -4.43627
2018  8 703 "Bagh"  88.9365   1964027945156608  6.038479e-08  1.13786e-09   .01637044   8.943795 20.527534  0 -4.3211327
2018  9 704 "Bagh" 91.99292   1894493297049600  3.854441e-08 1.815003e-09  .012213907   3.904359 18.550716  4  -4.314157
2018 10 705 "Bagh" 86.96629   1558247756529664 2.2696774e-08 2.513549e-09  .006886619  .50373423 14.196733  0  -4.184308
2018 11 706 "Bagh" 85.52283   2457764836147200  2.373712e-08 2.676921e-09  .005159461   1.346202 10.265257  4  -4.062054
2018 12 707 "Bagh" 86.91177   1233642718232576 2.0373603e-08 2.756682e-09 .0034888526  1.1144037  6.463125  0  -3.827928
2019  1 708 "Bagh" 89.98477   1424298396876800  2.334096e-08 2.487495e-09 .0037370615    7.79283 1.8268626  0 -3.8439875
2019  2 709 "Bagh" 97.97309   1255141109923840 2.3679467e-08 2.895977e-09  .004481537   8.719969  2.717334 12 -3.8924375
2019  3 710 "Bagh" 90.87844    851415408836608 2.3279336e-08 2.032633e-09  .006637315   5.699942  7.861566  0  -4.004024
2019  4 711 "Bagh" 85.86795   1523456843710464 2.4165994e-08 1.646448e-09   .01012094    5.68907  15.33409  4  -4.187106
end
format %tm modate
label var DNAME "Name of the district"
    The household dataset contains individual child observations which each have a unique CID. Each child lives in a certain district, denoted by DNAME. The household data is each year, no month is specified. The dependent variables for my research are C_ARITH, C_ENGL & C_READ.

    The air pollution dataset contains district-level data, with the mean pollution levels per district per month of each year. This dataset contains more years of data than the household dataset in order to be able to calculate lead- and lag effects of pollution on the dependent variables.

    The merge should result in a dataset where individual household observations are assigned (monthly) variables about air pollution based on the district they live in and the year of the survey.
    How should I proceed?

    I previously tried to do a m:m merge on year and DNAME.
    However since Clyde advice against a m:m merge, I am unsure about how to proceed.
    Thanks for your advice!
    Tags: None
  • #2
    Clyde Schechter doesn't just advise against m:m merges, in fact, the help file for m:m says that it's such a bad idea that they won't even show an example of it.

    Anyways, thanks for providing your data example. So, my first question for you is, in each dataset, what are the unique IDs for district? That is, which variables uniquely represent your unit of analysis? Presumably, the district and year variables. But, what are they?

    I see the year variable just fine. But what about the district names? Presumably, DNAME is the key one of interest here?


    EDIT: I see DNAME is the thing of interest. So now I've another question: how many districts are there in total, and are all of the ones in dataset 2 capitalized?
    Last edited by Jared Greathouse; 07 Apr 2022, 06:34.

    Comment

    • #3
      Hi Jared Greathouse ,

      Thanks so much for your quick reply! I am not 100% sure whether or not I understand your question.
      In my research, the district will be the unit of analysis. So far I have just worked with the district names (DNAME), I have not generated a unique ID number for each of the districts.
      Does this answer your question?

      Comment

      • #4
        It answers part of it. My other question was, are all of the districts capitalized in dataset 2 versus dataset 1?

        And additionally, in both of the raw (presumably survey?) datasets, do the raw datasets come with a unique numeric ID for each district?

        Comment

        • #5
          Jared Greathouse
          The household dataset contains many different spellings and capitalizations for the same districts. However, I have already written a piece of code to rename it in such a way that the names of the districts will be the same in dataset 1 and dataset 2. The spelling and capitalization will follow the air pollution dataset. So this change can be made in an instant, but I thought it would be best to show you the 'orginal' datasets.
          Please let me know if you want me to change the names and post the new codes in here again.

          In the dataset for air pollution each district does come with a unique numeric ID. In the household dataset, this is only present for some years, but not all of them. Also ID numbers change across the year, so no, no unique numeric ID for each district there.
          Last edited by Benthe Vrijsen; 07 Apr 2022, 07:11.

          Comment

          • #6
            Okay because I was GOING to say "this is where it gets ugly", but if you've written code to ensure that the spellings are the exact same in both datasets, it seems like what you're really interested in is a many to 1 merge, looking roughly like
            Code:
            m:1 DNAME year using dataex1, keepusing(pollution variables)
            Note that I haven't tested this. For me to best help you, I'd like two things:

            First, restrict dataset 2 to the district named ASTORE so we can work with the same district as we see in dataex 1. Post back this example data.

            Second: If you could please post the code to correct the spellings of Astore, that would be most helpful, since we need to make sure your spelling in dataex 1 is in fact the same as dataex 2 so that the merge can work correctly.

            Note that you don't need to begin another thread, this post works just fine. I'll get started on this as soon as I have my coffee, if nobody's beaten me to the punch.

            Comment

            • #7
              Jared Greathouse first of all, you are a lifesaver! I appreciate your help so much.

              I will provide new dataex's for the district Abbottabad for both datasets, which is (I think) the easiest way to fix the issue.

              Household data
              Code:
              * Example generated by -dataex-. For more info, type help dataex
clear
input int year str24 DNAME long CID byte(C_AGE C_SEX C_READ C_ENGL C_ARITH)
2013 "Abbottabad"  94915  3 1 . . .
2013 "Abbottabad"  94674  5 0 2 1 1
2013 "Abbottabad"  94911  5 0 2 4 2
2013 "Abbottabad"  93924 13 1 5 5 6
2013 "Abbottabad"  94902  3 0 . . .
2013 "Abbottabad"  94934 10 1 5 5 5
2013 "Abbottabad"  94935 16 1 5 5 6
2013 "Abbottabad"  94283  9 0 5 4 6
2013 "Abbottabad"  94955 13 0 4 4 5
2013 "Abbottabad"  94959  5 1 2 2 2
2013 "Abbottabad"  94965  7 0 5 5 6
2013 "Abbottabad"  94974 10 0 2 4 3
2013 "Abbottabad"  94986  5 0 1 2 .
2013 "Abbottabad"  94981 12 0 5 5 6
2013 "Abbottabad"  94987  8 0 5 5 6
2013 "Abbottabad"  94890  7 0 3 2 3
2013 "Abbottabad"  95007 11 1 5 5 6
2013 "Abbottabad" 137026 11 1 5 5 6
2013 "Abbottabad"  94878  3 0 . . .
2013 "Abbottabad"  95044  6 1 1 1 1
2013 "Abbottabad" 116852 14 1 5 3 2
2013 "Abbottabad"  95039  5 0 3 2 1
2013 "Abbottabad"  93923  7 0 3 3 3
2013 "Abbottabad"  95056  9 0 3 4 5
2013 "Abbottabad"  95078  3 0 . . .
2013 "Abbottabad"  93910 13 0 5 5 6
2013 "Abbottabad"  95103  6 0 2 3 3
2013 "Abbottabad"  95102  3 0 . . .
2013 "Abbottabad"  94455  7 1 3 4 5
2013 "Abbottabad"  95106  4 1 . . .
2013 "Abbottabad"  95126  . . . . .
2013 "Abbottabad"  94843 16 1 . 4 5
2013 "Abbottabad"  94663  6 0 1 1 1
2013 "Abbottabad" 108854 10 1 5 5 6
2013 "Abbottabad"  94371 10 0 4 4 5
2013 "Abbottabad"  95149 16 1 4 5 6
2013 "Abbottabad"  95156 14 0 4 4 5
2013 "Abbottabad"  95170 12 1 . . .
2013 "Abbottabad"  95181  8 1 2 2 3
2013 "Abbottabad"  95143  5 0 3 2 2
2013 "Abbottabad"  92202 14 0 3 3 2
2013 "Abbottabad"  94793 11 0 4 4 5
2013 "Abbottabad"  95155 16 0 5 5 6
2013 "Abbottabad"  95158  8 1 4 4 5
2013 "Abbottabad"  95200  8 0 4 4 5
2013 "Abbottabad"  94799  5 0 1 1 2
2013 "Abbottabad"  95353  3 0 . . .
2013 "Abbottabad"  95245 16 1 5 5 .
2013 "Abbottabad"  95271  9 1 4 4 6
2013 "Abbottabad"  93882 13 1 5 5 6
2013 "Abbottabad"  94786 15 0 5 5 6
2013 "Abbottabad"  94784 16 0 . . .
2013 "Abbottabad"  95286 10 0 5 5 6
2013 "Abbottabad"  95293 12 0 5 5 6
2013 "Abbottabad"  95186  6 0 1 2 2
2013 "Abbottabad" 108846 14 1 5 5 6
2013 "Abbottabad"  93981  9 0 5 5 5
2013 "Abbottabad"  95315 12 0 5 5 6
2013 "Abbottabad"  93795 13 0 5 5 6
2013 "Abbottabad"  95352  6 0 2 3 2
2013 "Abbottabad"  95341  4 0 . . .
2013 "Abbottabad"  95368  8 0 3 4 3
2013 "Abbottabad"  95372 10 0 4 4 5
2013 "Abbottabad"  94276 13 1 5 5 6
2013 "Abbottabad"  94756 12 0 5 5 6
2013 "Abbottabad"  93710  5 1 2 4 2
2013 "Abbottabad"  94225 12 1 5 5 6
2013 "Abbottabad"  94148 10 1 2 2 2
2013 "Abbottabad"  95404 12 1 5 5 6
2013 "Abbottabad"  94749 12 1 . . .
2013 "Abbottabad"  95412 13 0 5 5 6
2013 "Abbottabad"  95419 16 0 . . .
2013 "Abbottabad"  95422 15 0 . . .
2013 "Abbottabad"  95424 14 1 . . .
2013 "Abbottabad"  95428  3 0 . . .
2013 "Abbottabad"  95437 13 0 5 5 6
2013 "Abbottabad"  94105 10 1 5 4 5
2013 "Abbottabad"  95493  7 0 2 4 3
2013 "Abbottabad"  95488 15 0 5 5 6
2013 "Abbottabad" 109083  7 1 3 3 3
2013 "Abbottabad"  95544  5 0 3 3 3
2013 "Abbottabad"  94716  3 0 . . .
2013 "Abbottabad"  94727 12 0 5 5 6
2013 "Abbottabad"  93977  8 0 5 5 6
2013 "Abbottabad"  95433  8 1 4 4 6
2013 "Abbottabad" 109016  3 1 . . .
2013 "Abbottabad"  94006  7 0 3 3 3
2013 "Abbottabad"  94717  6 1 1 1 3
2013 "Abbottabad"  94127 10 0 3 2 2
2013 "Abbottabad"  94704  7 0 2 2 2
2013 "Abbottabad"  94195  5 1 3 4 5
2013 "Abbottabad"  93140 10 0 5 5 5
2013 "Abbottabad"  96113 13 1 5 5 6
2013 "Abbottabad" 187820  6 0 4 5 5
2013 "Abbottabad"  95701  5 0 . . .
2013 "Abbottabad"  94701  7 1 2 2 2
2013 "Abbottabad"  95398 13 0 3 2 3
2013 "Abbottabad"  94709 12 0 5 4 5
2013 "Abbottabad"  94028 10 0 3 2 3
2013 "Abbottabad"  95885  7 0 3 4 5
end
label values C_SEX C_SEX_lbl
label def C_SEX_lbl 0 "Male", modify
label def C_SEX_lbl 1 "Female", modify
label values C_ENGL C_ENGL_lbl
label def C_ENGL_lbl 1 "Beginner/Nothing", modify
label def C_ENGL_lbl 2 "Capital letters", modify
label def C_ENGL_lbl 3 "Small letters", modify
label def C_ENGL_lbl 4 "Words", modify
label def C_ENGL_lbl 5 "Sentences", modify
label values C_ARITH C_ARITH_lbl
label def C_ARITH_lbl 1 "Beginner/Nothing", modify
label def C_ARITH_lbl 2 "Recognition of 1-9", modify
label def C_ARITH_lbl 3 "Recognition of 10-99", modify
label def C_ARITH_lbl 5 "Subtraction", modify
label def C_ARITH_lbl 6 "Division", modify
label var year "SYEAR" 
label var DNAME "DNAME" 
label var CID "CID" 
label var C_AGE "C_AGE" 
label var C_SEX "Gender of the child" 
label var C_READ "Reading in Local/National language" 
label var C_ENGL "Highest learning level of child in English." 
label var C_ARITH "Highest learning level of child in Arithmetic."
              air pollution data
              Code:
              * Example generated by -dataex-. For more info, type help dataex
clear
input float(year month modate) str25 DNAME float comean double no2mean float(pm25mean so2mean hummean precmean tempmean TI_Count average_diff)
2011  1 612 "Abbottabad"  96.11294          1.310e+15      1.76e-08     2.73e-09   .00392794   .3336239  9.507707  0  -3.997581
2011  2 613 "Abbottabad"  99.00775          1.460e+15      2.65e-08     2.15e-09   .00545092  10.204703  8.897905 12  -4.015178
2011  3 614 "Abbottabad"  94.20843          1.810e+15      2.39e-08     1.78e-09  .007622995   2.124073 16.084745  0 -3.9782245
2011  4 615 "Abbottabad"  94.20843          1.810e+15      2.39e-08     1.78e-09  .009222679  2.5252585  19.86308  4  -4.245835
2011  5 616 "Abbottabad" 104.68707          1.950e+15      1.96e-08     1.58e-09   .01052664  1.0606406  28.08996  0  -4.233064
2011  6 617 "Abbottabad"   109.628          2.290e+15      5.06e-08     1.91e-09  .013622346 .011838347  29.03797  4 -4.2933292
2011  7 618 "Abbottabad" 108.49804          1.920e+15      5.91e-08     1.92e-09  .018228771   6.863993  26.21046  0  -4.336291
2011  8 619 "Abbottabad"  109.3066          1.450e+15      3.76e-08     1.70e-09   .01891132  10.042828  24.76641  0 -4.3274183
2011  9 620 "Abbottabad" 101.22145          1.110e+15      2.74e-08     2.07e-09  .016146185  3.0123484  23.46266  4     -4.225
2011 10 621 "Abbottabad"  90.27409          1.350e+15      2.55e-08     2.20e-09  .009440948   .4462588  20.54009  0 -4.2524214
2011 11 622 "Abbottabad"  88.15783          1.220e+15      2.11e-08     2.38e-09  .006934988   .5463931 17.047546  4 -4.1591687
2011 12 623 "Abbottabad"  94.36868          1.930e+15      2.20e-08     2.98e-09  .003909926   .1488069  12.05056  0 -4.0870976
2012  1 624 "Abbottabad"  99.94749   1889509255938048  2.782192e-08  2.60837e-09  .003782884  2.5814395  7.430996  0 -4.0145164
2012  2 625 "Abbottabad"  98.93758 2397047644050773.5 3.2616246e-08 1.864745e-09 .0044749156   4.400591  7.884643  8 -3.8965504
2012  3 626 "Abbottabad"   95.1821 2177927226239658.8  4.425051e-08 1.990966e-09  .006003992   1.572094 14.459875  0 -3.9774206
2012  4 627 "Abbottabad"  93.47544  911579450529109.4 2.0841245e-08 1.372158e-09  .009904053  3.2744465 19.239843  4 -4.1708307
2012  5 628 "Abbottabad"  97.07664 1734575200578218.8  1.621035e-08   1.2243e-09  .010428986  1.4341682 24.758507  0  -4.313712
2012  6 629 "Abbottabad"  96.54571 2787026379560277.5   2.38403e-08 1.285829e-09   .00865004  .07942466  29.80407  4 -4.2516665
2012  7 630 "Abbottabad"  109.2311   2283370239229952  4.867892e-08 1.823594e-09  .015685901   .6128967 28.975204  0   -4.31371
2012  8 631 "Abbottabad" 104.59014 1527325446023850.8  4.925287e-08 2.057103e-09   .01867117   3.344791 25.772505  0  -4.266935
2012  9 632 "Abbottabad"  95.23199   1161556926660608 2.8072336e-08 1.838347e-09  .016001359  2.0617228  23.33639  4  -4.223338
2012 10 633 "Abbottabad"  89.11844   1520449695514624 1.9658003e-08 1.940535e-09  .008600234  .08928955  19.59357  0  -4.240321
2012 11 634 "Abbottabad"  85.95892   2242177342111744 2.6016703e-08 2.058495e-09  .005726042   .5078251 15.448177  4 -4.1241684
2012 12 635 "Abbottabad"  89.91596 1664319780902229.3  2.604281e-08  2.19356e-09  .004485305   1.637229 11.776385  0 -4.1161275
2013  1 636 "Abbottabad"  92.60094        1.94313e+15   3.77138e-08  2.41391e-09  .003829347   .8444911  9.136307  0 -4.0798388
2013  2 637 "Abbottabad"  95.06524        1.87938e+15   2.98648e-08  2.24287e-09  .005880678   9.429545 10.063504 12  -3.999997
2013  3 638 "Abbottabad"  88.70963        1.43937e+15   2.26445e-08  1.87814e-09  .008420701  3.2791874  16.08373  0   -4.07903
2013  4 639 "Abbottabad"  90.56194         1.4585e+15   2.08604e-08   1.3909e-09  .010196055   .2418624  19.88057  4  -4.305834
2013  5 640 "Abbottabad"    91.076        1.72791e+15   1.95936e-08  1.20822e-09   .01020622   .2325486 26.141603  0 -4.2637095
2013  6 641 "Abbottabad"  97.87124        1.92451e+15   3.34126e-08  1.65939e-09  .013099546  1.8082206 29.132254  4      -4.27
2013  7 642 "Abbottabad" 104.43357        2.07596e+15   5.39985e-08  1.76332e-09  .018933235  2.5392246   27.1648  0  -4.358064
2013  8 643 "Abbottabad" 104.71043        1.31618e+15   4.14442e-08  2.08538e-09   .01901123   7.693342 24.822226  0 -4.3564525
2013  9 644 "Abbottabad" 103.26907        1.25613e+15   4.96716e-08  2.15839e-09  .015489435  1.5389085  23.32317  4 -4.2283316
2013 10 645 "Abbottabad"  94.09236        1.15134e+15    4.0029e-08  2.16936e-09   .01075663  .25782615  20.94896  0  -4.210484
2013 11 646 "Abbottabad"  85.94402        1.77838e+15   1.95026e-08  2.06909e-09  .005402774   .5899205 15.385907  4 -4.1041684
2013 12 647 "Abbottabad"  91.06864        2.34483e+15   2.41227e-08  2.33124e-09  .003511088   .0769775 11.960663  0  -4.004032
2014  1 648 "Abbottabad"  92.46869 2054955467451050.8 1.6139827e-08 2.345427e-09  .003238943   .2727376 10.632012  0  -3.850806
2014  2 649 "Abbottabad"  97.16617 1754077707545258.8  2.887692e-08 2.180447e-09 .0045914087   2.722884  9.791932 12 -3.9321434
2014  3 650 "Abbottabad"  93.75581   1855054491418624  2.692161e-08 1.752437e-09  .006978645  4.4648805 13.333652  0 -4.0637083
2014  4 651 "Abbottabad"         .   1743582507565056 1.5047817e-08 1.327211e-09  .009164255  2.2142391 19.142923  4      -4.05
2014  5 652 "Abbottabad"  99.46532 1503870629620394.8 2.1770195e-08 1.265936e-09  .011653435  1.2830895 24.037764  0  -4.299192
2014  6 653 "Abbottabad"  92.94113   2213488839622656  3.180394e-08 1.383175e-09  .011436623  .05029847  29.69454  4 -4.2066655
2014  7 654 "Abbottabad"  102.6995   1397929713598464  5.941536e-08 1.961298e-09  .017431311   3.521061 27.584726  0  -4.376609
2014  8 655 "Abbottabad"  98.96602 1949545740457301.3  5.215981e-08 2.093902e-09  .017834723  3.6781175 25.869314  0  -4.283871
2014  9 656 "Abbottabad"  101.9624 1368955416827221.3  3.627155e-08 2.135658e-09  .016256062   2.112559  23.24897  4 -4.2325015
2014 10 657 "Abbottabad"  93.20239 1302389195101525.3   3.01346e-08 2.107534e-09  .010277042   .3431065  19.87448  0  -4.193548
2014 11 658 "Abbottabad"  86.39405 1474946619757909.3 2.0336204e-08 2.065525e-09   .00577689   2.577628 16.004822  4 -4.1258335
2014 12 659 "Abbottabad"  100.4571   2725718047850496  2.630644e-08 2.826692e-09  .003765313 .007012808 12.397847  0   -4.07903
2015  1 660 "Abbottabad" 110.44862        1.88569e+15   2.88991e-08  2.74527e-09  .003752843  1.1088774 10.286826  0  -3.973387
2015  2 661 "Abbottabad" 102.31495        1.95594e+15   3.45284e-08  2.60817e-09  .005854315     6.7552  11.92959 12  -3.934823
2015  3 662 "Abbottabad"  96.91565        1.50595e+15   2.35899e-08  1.68313e-09  .008125777   7.248246  14.10447  0 -4.0572543
2015  4 663 "Abbottabad"  98.60025        1.71341e+15   2.62611e-08  1.83112e-09   .01170911   2.868866  20.08065  4 -4.1933327
2015  5 664 "Abbottabad"  98.53368        2.24542e+15   2.04929e-08  1.38215e-09    .0121814   .3097502  25.69384  0 -4.2959685
2015  6 665 "Abbottabad"  99.15784        2.22084e+15    2.8834e-08  1.51366e-09  .012748851  1.0664684  27.46773  4  -4.331669
2015  7 666 "Abbottabad" 105.12056        1.70915e+15   5.15182e-08  1.87055e-09  .018386604    3.19802 26.451986  0 -4.3758073
2015  8 667 "Abbottabad" 100.12718        2.13224e+15    4.3554e-08  2.08866e-09  .018806176   3.296762  25.17417  0 -4.2822585
2015  9 668 "Abbottabad"  99.12728        1.89107e+15   2.19564e-08  1.93508e-09  .013187833  1.9230855 24.002747  4    -4.2525
2015 10 669 "Abbottabad"  94.87969        1.58046e+15   2.97106e-08  2.17138e-09  .009761647  1.8585702  20.22321  0 -4.2258043
2015 11 670 "Abbottabad"  98.77528        2.14874e+15   2.57521e-08  2.78422e-09  .007192711  2.0486941 15.034578  4 -4.2516646
2015 12 671 "Abbottabad"  95.64295        1.55972e+15   2.41762e-08  3.00926e-09  .005407415  .23315525 12.061103  0 -4.1096783
2016  1 672 "Abbottabad"  98.24339   2210406529499136  2.556562e-08 2.868163e-09  .005088837   1.754321   10.5896  0  -4.169357
2016  2 673 "Abbottabad" 104.52613   1306773394817024 2.3876334e-08 2.858757e-09   .00584867   .6881298 13.247365  8 -4.0094843
2016  3 674 "Abbottabad"  96.07843   1264688822222848   2.53855e-08 2.010953e-09  .008804991   7.828474 15.767677  0 -4.1306458
2016  4 675 "Abbottabad"  88.66109 1860925398537557.3  1.952907e-08  1.43839e-09   .01086606   1.339975  20.78674  4 -4.2383327
2016  5 676 "Abbottabad"  97.48742   2542789216698368  2.455278e-08 1.284265e-09  .012110964   .7759994 27.215055  0 -4.2749996
2016  6 677 "Abbottabad" 103.19395   2888313787995477  4.064063e-08 1.692827e-09   .01517543  .15507045 29.686985  4 -4.2641664
2016  7 678 "Abbottabad" 104.06378 2154611190966954.5  5.165793e-08 1.850948e-09   .01922356   6.383112  26.64068  0  -4.379033
2016  8 679 "Abbottabad"   106.416 2027915829750442.8 2.9443203e-08 2.012731e-09   .01877158   4.123931  25.32282  0 -4.3596773
2016  9 680 "Abbottabad"  100.7034 1414894140959402.8  4.463418e-08 2.422531e-09  .015318148  1.0327499  24.67562  4 -4.2225018
2016 10 681 "Abbottabad"  93.65022 1309992159630677.3   3.70537e-08  2.38872e-09  .009215945  .05339586 22.025736  0  -4.212901
2016 11 682 "Abbottabad"  99.40435 2403099879320234.5  4.685437e-08 2.629745e-09  .004405521  .05109868  17.85624  4  -4.000002
2016 12 683 "Abbottabad" 101.99583   2475374067843072 3.3213052e-08 3.002554e-09 .0032341275 .017012155  15.69756  0  -3.993552
2017  1 684 "Abbottabad"  102.4318 4157700499133781.5  3.585694e-08 2.566731e-09  .004848249   6.974658   7.63629  0  -3.759756
2017  2 685 "Abbottabad"  98.41375   1441745829101568 1.9463204e-08 2.621182e-09  .006327221   3.402175 12.169314 12 -3.8961604
2017  3 686 "Abbottabad"  93.08501   1439061810085888  2.336944e-08 1.662089e-09  .008087777   2.315762  14.82795  0 -4.0204825
2017  4 687 "Abbottabad"   89.1006 2024430866442922.8 2.5836966e-08 1.355565e-09  .010432748  3.2298725 22.163013  4  -4.201335
2017  5 688 "Abbottabad" 103.14132 2361130980037973.5  2.716631e-08 1.421383e-09  .011387262  .16236494 27.002256  0 -4.3290334
2017  6 689 "Abbottabad" 101.18838   2368135982219264  4.697955e-08 1.640894e-09  .013500904  2.2224474 28.032614  4 -4.3195834
2017  7 690 "Abbottabad" 105.70515 2129374947398997.3  5.377744e-08 1.892596e-09  .018713687   4.608266 26.536583  0 -4.3308067
2017  8 691 "Abbottabad"  103.5687 1800121591267328.3  5.772475e-08 2.203404e-09   .01867041   3.619065  25.79536  0 -4.2579837
2017  9 692 "Abbottabad"  100.0604 1701298957189119.8  3.365526e-08 2.141319e-09  .013989558  1.8452687   24.2776  4 -4.2596655
2017 10 693 "Abbottabad"  97.96345 1689317161653589.3  4.908114e-08 2.425621e-09  .007531395 .003302701  22.55861  0 -4.2509675
2017 11 694 "Abbottabad" 105.20314   2924821367925419  5.456809e-08 3.086409e-09  .004778198   .4200529 15.976475  4  -4.080582
2017 12 695 "Abbottabad"  98.59682 2520086735399594.5 1.9099634e-08 2.866294e-09  .004233103   .9042324 12.574588  0 -3.8979046
2018  1 696 "Abbottabad" 102.44623 2289069884527957.5 3.2426396e-08 3.246013e-09  .003835864   .2006465 11.183366  0 -3.9281466
2018  2 697 "Abbottabad"  98.22069 1714132229991082.8 2.5546996e-08 2.396801e-09  .005144543  3.0212786 12.350897 12 -3.9851794
2018  3 698 "Abbottabad"  92.42289 1488037232683690.8 2.5609847e-08 1.890565e-09  .007545669   2.568554 17.784193  0  -3.993467
2018  4 699 "Abbottabad"  92.15745 1499904674714965.3  3.590518e-08 1.597453e-09  .009898277   5.158232 21.438934  4 -4.1391673
2018  5 700 "Abbottabad"  97.09755 2012323219417770.8 3.1213037e-08 1.435762e-09   .01097664   .6141531 24.774906  0  -4.209678
2018  6 701 "Abbottabad" 104.08241   2382565193285632  5.407014e-08 1.792892e-09  .013062525  3.0385444 28.928503  4 -4.2849183
2018  7 702 "Abbottabad"  98.80846 2098195229660501.3 6.0765224e-08 1.944652e-09  .019069323   6.738194  26.12833  0  -4.379597
2018  8 703 "Abbottabad"  98.09788 1949142818837845.5  7.541456e-08 2.351727e-09  .019889766     4.4622 25.375484  0  -4.298067
2018  9 704 "Abbottabad" 103.21876 1979803113968981.3   4.87032e-08 2.688709e-09  .015143073   3.641236 23.681053  4 -4.3081684
2018 10 705 "Abbottabad"  94.08669 1465989823376042.8  2.838665e-08 2.567406e-09  .008872074  .13970277  19.81153  0 -4.1882253
2018 11 706 "Abbottabad"  91.10707   2827230405197824 2.5963974e-08 2.663288e-09  .006371015   .4347376  15.54988  4 -4.0405016
2018 12 707 "Abbottabad"  97.35249 2667389797094741.5 2.8099016e-08 3.055254e-09 .0044211494   .8334113 11.265207  0  -3.859274
2019  1 708 "Abbottabad"  99.62814   1932714613669888  3.232046e-08  2.62134e-09   .00467529    6.33387  6.984877  0  -3.815403
2019  2 709 "Abbottabad" 105.13398 1293067639674197.3 2.7337714e-08 2.743786e-09  .005704947   4.620583 8.2302265 12  -3.808125
2019  3 710 "Abbottabad"  97.71956    645019530690560 2.6603914e-08 1.975986e-09  .007962183   4.256084 13.214072  0  -3.915565
2019  4 711 "Abbottabad"  93.49516 1409965106746709.3  2.732811e-08  1.79674e-09    .0119124  3.4632735 20.989145  4 -4.0966663
end
format %tm modate
label var DNAME "Name of the district"

              Comment

              • #8
                Okay cool this works, give me a few minutes.

                Comment

                • #9
                  Okay so if I understand where you're going, you want to calculate the average yearly emissions/pollution for each district, and you need to match these to use as covariates or something similar.


                  Your original intuition was to use the m:m merge, and that's not totally wrong... but, allow me to introduce you to the REAL m:m merge command, joinby.
                  Code:
                  * Example generated by -dataex-. For more info, type help dataex
clear
input int year str24 DNAME long CID byte(C_AGE C_SEX C_READ C_ENGL C_ARITH)
2013 "Abbottabad"  94915  3 1 . . .
2013 "Abbottabad"  94674  5 0 2 1 1
2013 "Abbottabad"  94911  5 0 2 4 2
2013 "Abbottabad"  93924 13 1 5 5 6
2013 "Abbottabad"  94902  3 0 . . .
2013 "Abbottabad"  94934 10 1 5 5 5
2013 "Abbottabad"  94935 16 1 5 5 6
2013 "Abbottabad"  94283  9 0 5 4 6
2013 "Abbottabad"  94955 13 0 4 4 5
2013 "Abbottabad"  94959  5 1 2 2 2
2013 "Abbottabad"  94965  7 0 5 5 6
2013 "Abbottabad"  94974 10 0 2 4 3
2013 "Abbottabad"  94986  5 0 1 2 .
2013 "Abbottabad"  94981 12 0 5 5 6
2013 "Abbottabad"  94987  8 0 5 5 6
2013 "Abbottabad"  94890  7 0 3 2 3
2013 "Abbottabad"  95007 11 1 5 5 6
2013 "Abbottabad" 137026 11 1 5 5 6
2013 "Abbottabad"  94878  3 0 . . .
2013 "Abbottabad"  95044  6 1 1 1 1
2013 "Abbottabad" 116852 14 1 5 3 2
2013 "Abbottabad"  95039  5 0 3 2 1
2013 "Abbottabad"  93923  7 0 3 3 3
2013 "Abbottabad"  95056  9 0 3 4 5
2013 "Abbottabad"  95078  3 0 . . .
2013 "Abbottabad"  93910 13 0 5 5 6
2013 "Abbottabad"  95103  6 0 2 3 3
2013 "Abbottabad"  95102  3 0 . . .
2013 "Abbottabad"  94455  7 1 3 4 5
2013 "Abbottabad"  95106  4 1 . . .
2013 "Abbottabad"  95126  . . . . .
2013 "Abbottabad"  94843 16 1 . 4 5
2013 "Abbottabad"  94663  6 0 1 1 1
2013 "Abbottabad" 108854 10 1 5 5 6
2013 "Abbottabad"  94371 10 0 4 4 5
2013 "Abbottabad"  95149 16 1 4 5 6
2013 "Abbottabad"  95156 14 0 4 4 5
2013 "Abbottabad"  95170 12 1 . . .
2013 "Abbottabad"  95181  8 1 2 2 3
2013 "Abbottabad"  95143  5 0 3 2 2
2013 "Abbottabad"  92202 14 0 3 3 2
2013 "Abbottabad"  94793 11 0 4 4 5
2013 "Abbottabad"  95155 16 0 5 5 6
2013 "Abbottabad"  95158  8 1 4 4 5
2013 "Abbottabad"  95200  8 0 4 4 5
2013 "Abbottabad"  94799  5 0 1 1 2
2013 "Abbottabad"  95353  3 0 . . .
2013 "Abbottabad"  95245 16 1 5 5 .
2013 "Abbottabad"  95271  9 1 4 4 6
2013 "Abbottabad"  93882 13 1 5 5 6
2013 "Abbottabad"  94786 15 0 5 5 6
2013 "Abbottabad"  94784 16 0 . . .
2013 "Abbottabad"  95286 10 0 5 5 6
2013 "Abbottabad"  95293 12 0 5 5 6
2013 "Abbottabad"  95186  6 0 1 2 2
2013 "Abbottabad" 108846 14 1 5 5 6
2013 "Abbottabad"  93981  9 0 5 5 5
2013 "Abbottabad"  95315 12 0 5 5 6
2013 "Abbottabad"  93795 13 0 5 5 6
2013 "Abbottabad"  95352  6 0 2 3 2
2013 "Abbottabad"  95341  4 0 . . .
2013 "Abbottabad"  95368  8 0 3 4 3
2013 "Abbottabad"  95372 10 0 4 4 5
2013 "Abbottabad"  94276 13 1 5 5 6
2013 "Abbottabad"  94756 12 0 5 5 6
2013 "Abbottabad"  93710  5 1 2 4 2
2013 "Abbottabad"  94225 12 1 5 5 6
2013 "Abbottabad"  94148 10 1 2 2 2
2013 "Abbottabad"  95404 12 1 5 5 6
2013 "Abbottabad"  94749 12 1 . . .
2013 "Abbottabad"  95412 13 0 5 5 6
2013 "Abbottabad"  95419 16 0 . . .
2013 "Abbottabad"  95422 15 0 . . .
2013 "Abbottabad"  95424 14 1 . . .
2013 "Abbottabad"  95428  3 0 . . .
2013 "Abbottabad"  95437 13 0 5 5 6
2013 "Abbottabad"  94105 10 1 5 4 5
2013 "Abbottabad"  95493  7 0 2 4 3
2013 "Abbottabad"  95488 15 0 5 5 6
2013 "Abbottabad" 109083  7 1 3 3 3
2013 "Abbottabad"  95544  5 0 3 3 3
2013 "Abbottabad"  94716  3 0 . . .
2013 "Abbottabad"  94727 12 0 5 5 6
2013 "Abbottabad"  93977  8 0 5 5 6
2013 "Abbottabad"  95433  8 1 4 4 6
2013 "Abbottabad" 109016  3 1 . . .
2013 "Abbottabad"  94006  7 0 3 3 3
2013 "Abbottabad"  94717  6 1 1 1 3
2013 "Abbottabad"  94127 10 0 3 2 2
2013 "Abbottabad"  94704  7 0 2 2 2
2013 "Abbottabad"  94195  5 1 3 4 5
2013 "Abbottabad"  93140 10 0 5 5 5
2013 "Abbottabad"  96113 13 1 5 5 6
2013 "Abbottabad" 187820  6 0 4 5 5
2013 "Abbottabad"  95701  5 0 . . .
2013 "Abbottabad"  94701  7 1 2 2 2
2013 "Abbottabad"  95398 13 0 3 2 3
2013 "Abbottabad"  94709 12 0 5 4 5
2013 "Abbottabad"  94028 10 0 3 2 3
2013 "Abbottabad"  95885  7 0 3 4 5
end
label values C_SEX C_SEX_lbl
label def C_SEX_lbl 0 "Male", modify
label def C_SEX_lbl 1 "Female", modify
label values C_ENGL C_ENGL_lbl
label def C_ENGL_lbl 1 "Beginner/Nothing", modify
label def C_ENGL_lbl 2 "Capital letters", modify
label def C_ENGL_lbl 3 "Small letters", modify
label def C_ENGL_lbl 4 "Words", modify
label def C_ENGL_lbl 5 "Sentences", modify
label values C_ARITH C_ARITH_lbl
label def C_ARITH_lbl 1 "Beginner/Nothing", modify
label def C_ARITH_lbl 2 "Recognition of 1-9", modify
label def C_ARITH_lbl 3 "Recognition of 10-99", modify
label def C_ARITH_lbl 5 "Subtraction", modify
label def C_ARITH_lbl 6 "Division", modify
label var year "SYEAR" 
label var DNAME "DNAME" 
label var CID "CID" 
label var C_AGE "C_AGE" 
label var C_SEX "Gender of the child" 
label var C_READ "Reading in Local/National language" 
label var C_ENGL "Highest learning level of child in English." 
label var C_ARITH "Highest learning level of child in Arithmetic."

sa dataex1, replace

* Example generated by -dataex-. For more info, type help dataex
clear
input float(year month modate) str25 DNAME float comean double no2mean float(pm25mean so2mean hummean precmean tempmean TI_Count average_diff)
2011  1 612 "Abbottabad"  96.11294          1.310e+15      1.76e-08     2.73e-09   .00392794   .3336239  9.507707  0  -3.997581
2011  2 613 "Abbottabad"  99.00775          1.460e+15      2.65e-08     2.15e-09   .00545092  10.204703  8.897905 12  -4.015178
2011  3 614 "Abbottabad"  94.20843          1.810e+15      2.39e-08     1.78e-09  .007622995   2.124073 16.084745  0 -3.9782245
2011  4 615 "Abbottabad"  94.20843          1.810e+15      2.39e-08     1.78e-09  .009222679  2.5252585  19.86308  4  -4.245835
2011  5 616 "Abbottabad" 104.68707          1.950e+15      1.96e-08     1.58e-09   .01052664  1.0606406  28.08996  0  -4.233064
2011  6 617 "Abbottabad"   109.628          2.290e+15      5.06e-08     1.91e-09  .013622346 .011838347  29.03797  4 -4.2933292
2011  7 618 "Abbottabad" 108.49804          1.920e+15      5.91e-08     1.92e-09  .018228771   6.863993  26.21046  0  -4.336291
2011  8 619 "Abbottabad"  109.3066          1.450e+15      3.76e-08     1.70e-09   .01891132  10.042828  24.76641  0 -4.3274183
2011  9 620 "Abbottabad" 101.22145          1.110e+15      2.74e-08     2.07e-09  .016146185  3.0123484  23.46266  4     -4.225
2011 10 621 "Abbottabad"  90.27409          1.350e+15      2.55e-08     2.20e-09  .009440948   .4462588  20.54009  0 -4.2524214
2011 11 622 "Abbottabad"  88.15783          1.220e+15      2.11e-08     2.38e-09  .006934988   .5463931 17.047546  4 -4.1591687
2011 12 623 "Abbottabad"  94.36868          1.930e+15      2.20e-08     2.98e-09  .003909926   .1488069  12.05056  0 -4.0870976
2012  1 624 "Abbottabad"  99.94749   1889509255938048  2.782192e-08  2.60837e-09  .003782884  2.5814395  7.430996  0 -4.0145164
2012  2 625 "Abbottabad"  98.93758 2397047644050773.5 3.2616246e-08 1.864745e-09 .0044749156   4.400591  7.884643  8 -3.8965504
2012  3 626 "Abbottabad"   95.1821 2177927226239658.8  4.425051e-08 1.990966e-09  .006003992   1.572094 14.459875  0 -3.9774206
2012  4 627 "Abbottabad"  93.47544  911579450529109.4 2.0841245e-08 1.372158e-09  .009904053  3.2744465 19.239843  4 -4.1708307
2012  5 628 "Abbottabad"  97.07664 1734575200578218.8  1.621035e-08   1.2243e-09  .010428986  1.4341682 24.758507  0  -4.313712
2012  6 629 "Abbottabad"  96.54571 2787026379560277.5   2.38403e-08 1.285829e-09   .00865004  .07942466  29.80407  4 -4.2516665
2012  7 630 "Abbottabad"  109.2311   2283370239229952  4.867892e-08 1.823594e-09  .015685901   .6128967 28.975204  0   -4.31371
2012  8 631 "Abbottabad" 104.59014 1527325446023850.8  4.925287e-08 2.057103e-09   .01867117   3.344791 25.772505  0  -4.266935
2012  9 632 "Abbottabad"  95.23199   1161556926660608 2.8072336e-08 1.838347e-09  .016001359  2.0617228  23.33639  4  -4.223338
2012 10 633 "Abbottabad"  89.11844   1520449695514624 1.9658003e-08 1.940535e-09  .008600234  .08928955  19.59357  0  -4.240321
2012 11 634 "Abbottabad"  85.95892   2242177342111744 2.6016703e-08 2.058495e-09  .005726042   .5078251 15.448177  4 -4.1241684
2012 12 635 "Abbottabad"  89.91596 1664319780902229.3  2.604281e-08  2.19356e-09  .004485305   1.637229 11.776385  0 -4.1161275
2013  1 636 "Abbottabad"  92.60094        1.94313e+15   3.77138e-08  2.41391e-09  .003829347   .8444911  9.136307  0 -4.0798388
2013  2 637 "Abbottabad"  95.06524        1.87938e+15   2.98648e-08  2.24287e-09  .005880678   9.429545 10.063504 12  -3.999997
2013  3 638 "Abbottabad"  88.70963        1.43937e+15   2.26445e-08  1.87814e-09  .008420701  3.2791874  16.08373  0   -4.07903
2013  4 639 "Abbottabad"  90.56194         1.4585e+15   2.08604e-08   1.3909e-09  .010196055   .2418624  19.88057  4  -4.305834
2013  5 640 "Abbottabad"    91.076        1.72791e+15   1.95936e-08  1.20822e-09   .01020622   .2325486 26.141603  0 -4.2637095
2013  6 641 "Abbottabad"  97.87124        1.92451e+15   3.34126e-08  1.65939e-09  .013099546  1.8082206 29.132254  4      -4.27
2013  7 642 "Abbottabad" 104.43357        2.07596e+15   5.39985e-08  1.76332e-09  .018933235  2.5392246   27.1648  0  -4.358064
2013  8 643 "Abbottabad" 104.71043        1.31618e+15   4.14442e-08  2.08538e-09   .01901123   7.693342 24.822226  0 -4.3564525
2013  9 644 "Abbottabad" 103.26907        1.25613e+15   4.96716e-08  2.15839e-09  .015489435  1.5389085  23.32317  4 -4.2283316
2013 10 645 "Abbottabad"  94.09236        1.15134e+15    4.0029e-08  2.16936e-09   .01075663  .25782615  20.94896  0  -4.210484
2013 11 646 "Abbottabad"  85.94402        1.77838e+15   1.95026e-08  2.06909e-09  .005402774   .5899205 15.385907  4 -4.1041684
2013 12 647 "Abbottabad"  91.06864        2.34483e+15   2.41227e-08  2.33124e-09  .003511088   .0769775 11.960663  0  -4.004032
2014  1 648 "Abbottabad"  92.46869 2054955467451050.8 1.6139827e-08 2.345427e-09  .003238943   .2727376 10.632012  0  -3.850806
2014  2 649 "Abbottabad"  97.16617 1754077707545258.8  2.887692e-08 2.180447e-09 .0045914087   2.722884  9.791932 12 -3.9321434
2014  3 650 "Abbottabad"  93.75581   1855054491418624  2.692161e-08 1.752437e-09  .006978645  4.4648805 13.333652  0 -4.0637083
2014  4 651 "Abbottabad"         .   1743582507565056 1.5047817e-08 1.327211e-09  .009164255  2.2142391 19.142923  4      -4.05
2014  5 652 "Abbottabad"  99.46532 1503870629620394.8 2.1770195e-08 1.265936e-09  .011653435  1.2830895 24.037764  0  -4.299192
2014  6 653 "Abbottabad"  92.94113   2213488839622656  3.180394e-08 1.383175e-09  .011436623  .05029847  29.69454  4 -4.2066655
2014  7 654 "Abbottabad"  102.6995   1397929713598464  5.941536e-08 1.961298e-09  .017431311   3.521061 27.584726  0  -4.376609
2014  8 655 "Abbottabad"  98.96602 1949545740457301.3  5.215981e-08 2.093902e-09  .017834723  3.6781175 25.869314  0  -4.283871
2014  9 656 "Abbottabad"  101.9624 1368955416827221.3  3.627155e-08 2.135658e-09  .016256062   2.112559  23.24897  4 -4.2325015
2014 10 657 "Abbottabad"  93.20239 1302389195101525.3   3.01346e-08 2.107534e-09  .010277042   .3431065  19.87448  0  -4.193548
2014 11 658 "Abbottabad"  86.39405 1474946619757909.3 2.0336204e-08 2.065525e-09   .00577689   2.577628 16.004822  4 -4.1258335
2014 12 659 "Abbottabad"  100.4571   2725718047850496  2.630644e-08 2.826692e-09  .003765313 .007012808 12.397847  0   -4.07903
2015  1 660 "Abbottabad" 110.44862        1.88569e+15   2.88991e-08  2.74527e-09  .003752843  1.1088774 10.286826  0  -3.973387
2015  2 661 "Abbottabad" 102.31495        1.95594e+15   3.45284e-08  2.60817e-09  .005854315     6.7552  11.92959 12  -3.934823
2015  3 662 "Abbottabad"  96.91565        1.50595e+15   2.35899e-08  1.68313e-09  .008125777   7.248246  14.10447  0 -4.0572543
2015  4 663 "Abbottabad"  98.60025        1.71341e+15   2.62611e-08  1.83112e-09   .01170911   2.868866  20.08065  4 -4.1933327
2015  5 664 "Abbottabad"  98.53368        2.24542e+15   2.04929e-08  1.38215e-09    .0121814   .3097502  25.69384  0 -4.2959685
2015  6 665 "Abbottabad"  99.15784        2.22084e+15    2.8834e-08  1.51366e-09  .012748851  1.0664684  27.46773  4  -4.331669
2015  7 666 "Abbottabad" 105.12056        1.70915e+15   5.15182e-08  1.87055e-09  .018386604    3.19802 26.451986  0 -4.3758073
2015  8 667 "Abbottabad" 100.12718        2.13224e+15    4.3554e-08  2.08866e-09  .018806176   3.296762  25.17417  0 -4.2822585
2015  9 668 "Abbottabad"  99.12728        1.89107e+15   2.19564e-08  1.93508e-09  .013187833  1.9230855 24.002747  4    -4.2525
2015 10 669 "Abbottabad"  94.87969        1.58046e+15   2.97106e-08  2.17138e-09  .009761647  1.8585702  20.22321  0 -4.2258043
2015 11 670 "Abbottabad"  98.77528        2.14874e+15   2.57521e-08  2.78422e-09  .007192711  2.0486941 15.034578  4 -4.2516646
2015 12 671 "Abbottabad"  95.64295        1.55972e+15   2.41762e-08  3.00926e-09  .005407415  .23315525 12.061103  0 -4.1096783
2016  1 672 "Abbottabad"  98.24339   2210406529499136  2.556562e-08 2.868163e-09  .005088837   1.754321   10.5896  0  -4.169357
2016  2 673 "Abbottabad" 104.52613   1306773394817024 2.3876334e-08 2.858757e-09   .00584867   .6881298 13.247365  8 -4.0094843
2016  3 674 "Abbottabad"  96.07843   1264688822222848   2.53855e-08 2.010953e-09  .008804991   7.828474 15.767677  0 -4.1306458
2016  4 675 "Abbottabad"  88.66109 1860925398537557.3  1.952907e-08  1.43839e-09   .01086606   1.339975  20.78674  4 -4.2383327
2016  5 676 "Abbottabad"  97.48742   2542789216698368  2.455278e-08 1.284265e-09  .012110964   .7759994 27.215055  0 -4.2749996
2016  6 677 "Abbottabad" 103.19395   2888313787995477  4.064063e-08 1.692827e-09   .01517543  .15507045 29.686985  4 -4.2641664
2016  7 678 "Abbottabad" 104.06378 2154611190966954.5  5.165793e-08 1.850948e-09   .01922356   6.383112  26.64068  0  -4.379033
2016  8 679 "Abbottabad"   106.416 2027915829750442.8 2.9443203e-08 2.012731e-09   .01877158   4.123931  25.32282  0 -4.3596773
2016  9 680 "Abbottabad"  100.7034 1414894140959402.8  4.463418e-08 2.422531e-09  .015318148  1.0327499  24.67562  4 -4.2225018
2016 10 681 "Abbottabad"  93.65022 1309992159630677.3   3.70537e-08  2.38872e-09  .009215945  .05339586 22.025736  0  -4.212901
2016 11 682 "Abbottabad"  99.40435 2403099879320234.5  4.685437e-08 2.629745e-09  .004405521  .05109868  17.85624  4  -4.000002
2016 12 683 "Abbottabad" 101.99583   2475374067843072 3.3213052e-08 3.002554e-09 .0032341275 .017012155  15.69756  0  -3.993552
2017  1 684 "Abbottabad"  102.4318 4157700499133781.5  3.585694e-08 2.566731e-09  .004848249   6.974658   7.63629  0  -3.759756
2017  2 685 "Abbottabad"  98.41375   1441745829101568 1.9463204e-08 2.621182e-09  .006327221   3.402175 12.169314 12 -3.8961604
2017  3 686 "Abbottabad"  93.08501   1439061810085888  2.336944e-08 1.662089e-09  .008087777   2.315762  14.82795  0 -4.0204825
2017  4 687 "Abbottabad"   89.1006 2024430866442922.8 2.5836966e-08 1.355565e-09  .010432748  3.2298725 22.163013  4  -4.201335
2017  5 688 "Abbottabad" 103.14132 2361130980037973.5  2.716631e-08 1.421383e-09  .011387262  .16236494 27.002256  0 -4.3290334
2017  6 689 "Abbottabad" 101.18838   2368135982219264  4.697955e-08 1.640894e-09  .013500904  2.2224474 28.032614  4 -4.3195834
2017  7 690 "Abbottabad" 105.70515 2129374947398997.3  5.377744e-08 1.892596e-09  .018713687   4.608266 26.536583  0 -4.3308067
2017  8 691 "Abbottabad"  103.5687 1800121591267328.3  5.772475e-08 2.203404e-09   .01867041   3.619065  25.79536  0 -4.2579837
2017  9 692 "Abbottabad"  100.0604 1701298957189119.8  3.365526e-08 2.141319e-09  .013989558  1.8452687   24.2776  4 -4.2596655
2017 10 693 "Abbottabad"  97.96345 1689317161653589.3  4.908114e-08 2.425621e-09  .007531395 .003302701  22.55861  0 -4.2509675
2017 11 694 "Abbottabad" 105.20314   2924821367925419  5.456809e-08 3.086409e-09  .004778198   .4200529 15.976475  4  -4.080582
2017 12 695 "Abbottabad"  98.59682 2520086735399594.5 1.9099634e-08 2.866294e-09  .004233103   .9042324 12.574588  0 -3.8979046
2018  1 696 "Abbottabad" 102.44623 2289069884527957.5 3.2426396e-08 3.246013e-09  .003835864   .2006465 11.183366  0 -3.9281466
2018  2 697 "Abbottabad"  98.22069 1714132229991082.8 2.5546996e-08 2.396801e-09  .005144543  3.0212786 12.350897 12 -3.9851794
2018  3 698 "Abbottabad"  92.42289 1488037232683690.8 2.5609847e-08 1.890565e-09  .007545669   2.568554 17.784193  0  -3.993467
2018  4 699 "Abbottabad"  92.15745 1499904674714965.3  3.590518e-08 1.597453e-09  .009898277   5.158232 21.438934  4 -4.1391673
2018  5 700 "Abbottabad"  97.09755 2012323219417770.8 3.1213037e-08 1.435762e-09   .01097664   .6141531 24.774906  0  -4.209678
2018  6 701 "Abbottabad" 104.08241   2382565193285632  5.407014e-08 1.792892e-09  .013062525  3.0385444 28.928503  4 -4.2849183
2018  7 702 "Abbottabad"  98.80846 2098195229660501.3 6.0765224e-08 1.944652e-09  .019069323   6.738194  26.12833  0  -4.379597
2018  8 703 "Abbottabad"  98.09788 1949142818837845.5  7.541456e-08 2.351727e-09  .019889766     4.4622 25.375484  0  -4.298067
2018  9 704 "Abbottabad" 103.21876 1979803113968981.3   4.87032e-08 2.688709e-09  .015143073   3.641236 23.681053  4 -4.3081684
2018 10 705 "Abbottabad"  94.08669 1465989823376042.8  2.838665e-08 2.567406e-09  .008872074  .13970277  19.81153  0 -4.1882253
2018 11 706 "Abbottabad"  91.10707   2827230405197824 2.5963974e-08 2.663288e-09  .006371015   .4347376  15.54988  4 -4.0405016
2018 12 707 "Abbottabad"  97.35249 2667389797094741.5 2.8099016e-08 3.055254e-09 .0044211494   .8334113 11.265207  0  -3.859274
2019  1 708 "Abbottabad"  99.62814   1932714613669888  3.232046e-08  2.62134e-09   .00467529    6.33387  6.984877  0  -3.815403
2019  2 709 "Abbottabad" 105.13398 1293067639674197.3 2.7337714e-08 2.743786e-09  .005704947   4.620583 8.2302265 12  -3.808125
2019  3 710 "Abbottabad"  97.71956    645019530690560 2.6603914e-08 1.975986e-09  .007962183   4.256084 13.214072  0  -3.915565
2019  4 711 "Abbottabad"  93.49516 1409965106746709.3  2.732811e-08  1.79674e-09    .0119124  3.4632735 20.989145  4 -4.0966663
end
format %tm modate
label var DNAME "Name of the district"

sa dataex2, replace

cls


u dataex1, clear


joinby DNAME year using dataex2

drop month modate

br
                  Is this the result you were hoping for?

                  Comment

                  • #10
                    Jared Greathouse Yes indeed this is exactly the result I was hoping for.
                    I will also have to calculate 3 month, 6 month, and 9 month pollution averages, so I will keep the month variable in the dataset to do so. But other than that, this code is exactly what I needed.
                    Many thanks to you Jared for taking the time to help me and also to Clyde Schechter for pointing out my initial mistake.

                    Comment

                    Previous Next
                    Working...
                    X