merging data sets

[Purity Nnodi]

hello everyone,
I am new here and relatively new to Stata,
I am trying to merge two data sets (lsms-isa data): one has 'household_id' as its unique identifier and the other data set also has the houshold_id however, isid command returned an error. I understood it was so because several household members were also interviewed. isid "household_id individual_id' didn't return an error message. how do I merge these two data sets with different units of observation?

[Rich Goldstein]

it depends on what you want; e.g., if you want all the household data to be merged with all members of the household, and the data with household_id as an identifier is in memory, then you would use a 1:m merge; see

h merge

if you want something else, then please clarify

[Purity Nnodi]

Thank you very much for your response. what I want is for the household information not to be duplicated among the individuals within the household. I was hoping it could be merged with the first member(head of household) only. the variables existing in the household unit data are geovariables like distance to market, distance to major road, e.t.c and also a variable that indicates if the household filled the post-planting survey questionnaire which I'm interested in.

[Mary Atieno]

it depends on what you want; e.g., if you want all the household data to be merged with all members of the household, and the data with household_id as an identifier is in memory, then you would use a 1:m merge; see

h merge

if you want something else, then please clarify

Hello Rich, I have a similar challenge. My master data are repeated measures therefore an id does not uniquely identify an observation. However in the using dataset, an id uniquely identifies the observation. The using dataset is sociodemographic data which is missing in the master dataset. how do I merge these two datasets so that all observations in the master dataset, have sociodemographic observations?

[Rich Goldstein]

do you have something that identifies which measure it is (e.g., visit number, date of measurement, etc.); if you do and the 2 together are distinct (use -isid- to check), then you can merge using m:1 with the repeated measures data set as the master data set (in memory); see

h isid
h merge

added - if you do not have such a variable, then (1) you can fake it by something like:

sort id
by id: gen number=_n

and continuing as above

or, you may find some third variable that will give you a distinct id

[Odiri Metieh]

Hello Rich Goldstein , I have an almost similar issue to what Mary Atieno posted. In my case I need help with merging two datasets accurately in stata. None of the two datasets have a unique identifying id. The master dataset has 535,044 rows and the using dataset has about 240,000 rows. The master dataset has 20 variables, 3 of which are named importer, month_id and year. These 3 variables also exist in the using dataset with the same names . However, it is a panel dataset with over 200 countries and spanning 5years (2018-2022), so there are multiple observations that could have the same importer name, month_id and year in both datasets (but the year variable in the using dataset only has 2020, 2021 and 2022 values).

The using dataset has variables called max_cases, max_deaths, max_tests, which I need to merge accurately with the master dataset. I need them to match based on the importer, month_id and year in both datasets. For example, using dataset has an observations that looks like this - importer(AFG), month_id(2) and year(2020), max_cases(5), max_deaths(2), max_tests(0). The master dataset also has an observations that looks like this - importer(AFG), month_id(2) and year(2020), value(54345), hs06(23456), bico(102) but without the max_cases and deaths as shown in the using dataset. I need the resulting dataset to have this observation - importer(AFG), month_id(2) and year(2020), value(54345), hs06(23456), bico(102), max_cases(5), max_deaths(2). When I tried the m:m merge, I got observations which had the year 2018 have values for max_cases and max_deaths which shouldn't be. I need max_cases and max_deaths values for all observations with 2018 and 2019 to be blank because the using dataset only contains max_cases and max_deaths values from 2020.

Really sorry for the long text. I hope you can help, as this process has taken a huge chunk of my productive time.

Thank you.

[Clyde Schechter]

I see you are new to Statalist, so you probably have not encountered any of the many posts by many people warning that -merge m:m- should never be used because it creates data salad.

Despite your detailed description, it is hard to see what the data are like and what they mean. In particular, why are there multiple observations having the same values of importer, month_id, and year in the using data set? Are they all just exact copies of each other? If not, how do we know which one of them is to provide the information on max_cases, max_deaths, and max_tests that you want to bring in. There must be some other information in the using data set that would enable you to choose the correct one. What variables convey that information?

In any case, these problems can almost never be solved unless you show example data from both data sets (and, in this situation, the examples should include some observations that should be paired up, and some that should not.) The helpful way to do that is with the -dataex- command. If you are running version 18, 17, 16 or a fully updated version 15.1 or 14.2, -dataex- is already part of your official Stata installation. If not, run -ssc install dataex- to get it. Either way, run -help dataex- to read the simple instructions for using it. -dataex- will save you time; it is easier and quicker than typing out tables. It includes complete information about aspects of the data that are often critical to answering your question but cannot be seen from tabular displays or screenshots. It also makes it possible for those who want to help you to create a faithful representation of your example to try out their code, which in turn makes it more likely that their answer will actually work in your data.

[Odiri Metieh]

Thank you for your response, Clyde Schechter Yes, I am new to Statalist and Stata as a whole. There are multiple values having the same importer, month_id and year because the master dataset is a bilateral trade dataset showing monthly exports of different commodities (hs06) from an exporting country (Canada) to an importing country across a time period of 5years (2018 - 2022). For month_id, January is 1, February is 2 and so on. The variable called "id2" was a variable I created by grouping month_id, importer and year in both dataset, this was what I intended to match both datasets with. Below is an excerpt from the master dataset.

* Example generated by -dataex-. For more info, type help dataex
clear
input str3 exporter str8 importer int conco long hs06 float(id value) byte month_id float year byte region str27 regionlabel int(bico mdgrp) float(bico_bval bico_ival bico_hval cropval hortval liveval fbval id2)
"CAN" "ABW" 877 110100 9230  18293 1 2018 4 "Caribbean" 204 1600 . 18293      . 18293      .     .     . 1
"CAN" "ABW" 877 110412 9232    320 1 2018 4 "Caribbean" 204 1600 .   320      .   320      .     .     . 1
"CAN" "ABW" 877 200580 9239      0 1 2018 4 "Caribbean" 310  700 .     .      0     .      0     .     . 1
"CAN" "ABW" 877  71333 9226      0 1 2018 4 "Caribbean" 213  800 .     0      .     0      .     .     . 1
"CAN" "ABW" 877 210210 9240      0 1 2018 4 "Caribbean" 215 2000 .     0      .     .      .     .     0 1
"CAN" "ABW" 877 200490 9238      0 1 2018 4 "Caribbean" 310  700 .     .      0     .      0     .     . 1
"CAN" "ABW" 877 220299 9243   4374 1 2018 4 "Caribbean" 301 2200 .     .   4374     .      .     .  4374 1
"CAN" "ABW" 877 150790 9234      0 1 2018 4 "Caribbean" 203 1700 .     0      .     0      .     .     . 1
"CAN" "ABW" 877  71340 9228      0 1 2018 4 "Caribbean" 213  800 .     0      .     0      .     .     . 1
"CAN" "ABW" 877  21099 9220      0 1 2018 4 "Caribbean" 304 1200 .     .      0     .      .     0     . 1
"CAN" "ABW" 877  21019 9218      0 1 2018 4 "Caribbean" 304 1200 .     .      0     .      .     0     . 1
"CAN" "ABW" 877 100590 9229      0 1 2018 4 "Caribbean" 101  300 0     .      .     0      .     .     . 1
"CAN" "ABW" 877 190531 9235   2863 1 2018 4 "Caribbean" 302 1600 .     .   2863  2863      .     .     . 1
"CAN" "ABW" 877 190590 9236   5065 1 2018 4 "Caribbean" 302 1600 .     .   5065  5065      .     .     . 1
"CAN" "ABW" 877  40690 9222      0 1 2018 4 "Caribbean" 311 1500 .     .      0     .      .     0     . 1
"CAN" "ABW" 877 200410 9237  37256 1 2018 4 "Caribbean" 310  700 .     .  37256     .  37256     .     . 1
"CAN" "ABW" 877  60420 9223      0 1 2018 4 "Caribbean" 306 1100 .     .      0     .      0     .     . 1
"CAN" "ABW" 877 220870 9244      0 1 2018 4 "Caribbean" 301 2200 .     .      0     .      .     .     0 1
"CAN" "ABW" 877  71320 9225      0 1 2018 4 "Caribbean" 213  800 .     0      .     0      .     .     . 1
"CAN" "ABW" 877 220110 9242      0 1 2018 4 "Caribbean" 301 2200 .     .      0     .      .     .     0 1
"CAN" "ABW" 877  71339 9227      0 1 2018 4 "Caribbean" 213  800 .     0      .     0      .     .     . 1
"CAN" "ABW" 877 210690 9241      0 1 2018 4 "Caribbean" 303 2000 .     .      0     .      .     .     0 1
"CAN" "ABW" 877 110313 9231      0 1 2018 4 "Caribbean" 204 1600 .     0      .     0      .     .     . 1
"CAN" "ABW" 877  30542 9221      0 1 2018 4 "Caribbean" 304 1300 .     .      0     .      .     0     . 1
"CAN" "ABW" 877  21020 9219      0 1 2018 4 "Caribbean" 304 1200 .     .      0     .      .     0     . 1
"CAN" "ABW" 877  71310 9224      0 1 2018 4 "Caribbean" 213  800 .     0      .     0      .     .     . 1
"CAN" "ABW" 877 150110 9233      0 1 2018 4 "Caribbean" 203 1400 .     0      .     .      .     0     . 1
"CAN" "ABW" 877 200490 9330   2027 1 2019 4 "Caribbean" 310  700 .     .   2027     .   2027     .     . 2
"CAN" "ABW" 877 110100 9319      0 1 2019 4 "Caribbean" 204 1600 .     0      .     0      .     .     . 2
"CAN" "ABW" 877 121490 9322      0 1 2019 4 "Caribbean" 207  400 .     0      .     0      .     .     . 2
"CAN" "ABW" 877 190590 9328   9253 1 2019 4 "Caribbean" 302 1600 .     .   9253  9253      .     .     . 2
"CAN" "ABW" 877  71310 9312      0 1 2019 4 "Caribbean" 213  800 .     0      .     0      .     .     . 2
"CAN" "ABW" 877 150110 9323      0 1 2019 4 "Caribbean" 203 1400 .     0      .     .      .     0     . 2
"CAN" "ABW" 877  60490 9311      0 1 2019 4 "Caribbean" 306 1100 .     .      0     .      0     .     . 2
"CAN" "ABW" 877 100590 9318      0 1 2019 4 "Caribbean" 101  300 0     .      .     0      .     .     . 2
"CAN" "ABW" 877 150790 9324  37038 1 2019 4 "Caribbean" 203 1700 . 37038      . 37038      .     .     . 2
"CAN" "ABW" 877 151710 9326      0 1 2019 4 "Caribbean" 316 1700 .     .      0     0      .     .     . 2
"CAN" "ABW" 877  60420 9310      0 1 2019 4 "Caribbean" 306 1100 .     .      0     .      0     .     . 2
"CAN" "ABW" 877  20322 9298      0 1 2019 4 "Caribbean" 304 1200 .     .      0     .      .     0     . 2
"CAN" "ABW" 877 210210 9331      0 1 2019 4 "Caribbean" 215 2000 .     0      .     .      .     .     0 2
"CAN" "ABW" 877  90121 9317      0 1 2019 4 "Caribbean" 104 2300 0     .      .     .      .     .     0 2
"CAN" "ABW" 877  30539 9305      0 1 2019 4 "Caribbean" 304 1300 .     .      0     .      .     0     . 2
"CAN" "ABW" 877 151419 9325      0 1 2019 4 "Caribbean" 203 1700 .     0      .     0      .     .     . 2
"CAN" "ABW" 877 110313 9320      0 1 2019 4 "Caribbean" 204 1600 .     0      .     0      .     .     . 2
"CAN" "ABW" 877  71339 9315      0 1 2019 4 "Caribbean" 213  800 .     0      .     0      .     .     . 2
"CAN" "ABW" 877  21019 9302      0 1 2019 4 "Caribbean" 304 1200 .     .      0     .      .     0     . 2
"CAN" "ABW" 877  71333 9314      0 1 2019 4 "Caribbean" 213  800 .     0      .     0      .     .     . 2
"CAN" "ABW" 877  40510 9308      0 1 2019 4 "Caribbean" 311 1500 .     .      0     .      .     0     . 2
"CAN" "ABW" 877  71340 9316      0 1 2019 4 "Caribbean" 213  800 .     0      .     0      .     .     . 2
"CAN" "ABW" 877  40690 9309   7240 1 2019 4 "Caribbean" 311 1500 .     .   7240     .      .  7240     . 2
"CAN" "ABW" 877 110412 9321      0 1 2019 4 "Caribbean" 204 1600 .     0      .     0      .     .     . 2
"CAN" "ABW" 877  30542 9306      0 1 2019 4 "Caribbean" 304 1300 .     .      0     .      .     0     . 2
"CAN" "ABW" 877 210690 9332   6763 1 2019 4 "Caribbean" 303 2000 .     .   6763     .      .     .  6763 2
"CAN" "ABW" 877  20641 9300      0 1 2019 4 "Caribbean" 304 1200 .     .      0     .      .     0     . 2
"CAN" "ABW" 877  20649 9301      0 1 2019 4 "Caribbean" 304 1200 .     .      0     .      .     0     . 2
"CAN" "ABW" 877  30559 9307      0 1 2019 4 "Caribbean" 304 1300 .     .      0     .      .     0     . 2
"CAN" "ABW" 877  71320 9313      0 1 2019 4 "Caribbean" 213  800 .     0      .     0      .     .     . 2
"CAN" "ABW" 877  30365 9304      0 1 2019 4 "Caribbean" 304 1300 .     .      0     .      .     0     . 2
"CAN" "ABW" 877 190531 9327      0 1 2019 4 "Caribbean" 302 1600 .     .      0     0      .     .     . 2
"CAN" "ABW" 877  21020 9303      0 1 2019 4 "Caribbean" 304 1200 .     .      0     .      .     0     . 2
"CAN" "ABW" 877 220299 9333      0 1 2019 4 "Caribbean" 301 2200 .     .      0     .      .     .     0 2
"CAN" "ABW" 877  20329 9299      0 1 2019 4 "Caribbean" 304 1200 .     .      0     .      .     0     . 2
"CAN" "ABW" 877 200410 9329 143147 1 2019 4 "Caribbean" 310  700 .     . 143147     . 143147     .     . 2
"CAN" "ABW" 877  71339 8928      0 1 2020 4 "Caribbean" 213  800 .     0      .     0      .     .     . 3
"CAN" "ABW" 877  71333 8927      0 1 2020 4 "Caribbean" 213  800 .     0      .     0      .     .     . 3
"CAN" "ABW" 877  21020 8920      0 1 2020 4 "Caribbean" 304 1200 .     .      0     .      .     0     . 3
"CAN" "ABW" 877  71332 8926      0 1 2020 4 "Caribbean" 213  800 .     0      .     0      .     .     . 3
"CAN" "ABW" 877 150790 8935      0 1 2020 4 "Caribbean" 203 1700 .     0      .     0      .     .     . 3
"CAN" "ABW" 877 400129 8945      0 1 2020 4 "Caribbean" 108 2500 0     .      .     .      .     .     0 3
"CAN" "ABW" 877  21019 8919      0 1 2020 4 "Caribbean" 304 1200 .     .      0     .      .     0     . 3
"CAN" "ABW" 877 150110 8934      0 1 2020 4 "Caribbean" 203 1400 .     0      .     .      .     0     . 3
"CAN" "ABW" 877 200410 8940 124968 1 2020 4 "Caribbean" 310  700 .     . 124968     . 124968     .     . 3
"CAN" "ABW" 877 100590 8930      0 1 2020 4 "Caribbean" 101  300 0     .      .     0      .     .     . 3
"CAN" "ABW" 877 220299 8944   5658 1 2020 4 "Caribbean" 301 2200 .     .   5658     .      .     .  5658 3
"CAN" "ABW" 877 160100 8936      0 1 2020 4 "Caribbean" 312 1200 .     .      0     .      .     0     . 3
"CAN" "ABW" 877 190531 8938      0 1 2020 4 "Caribbean" 302 1600 .     .      0     0      .     .     . 3
"CAN" "ABW" 877  71320 8925      0 1 2020 4 "Caribbean" 213  800 .     0      .     0      .     .     . 3
"CAN" "ABW" 877 210210 8942      0 1 2020 4 "Caribbean" 215 2000 .     0      .     .      .     .     0 3
"CAN" "ABW" 877  30542 8921      0 1 2020 4 "Caribbean" 304 1300 .     .      0     .      .     0     . 3
"CAN" "ABW" 877 190590 8939  11941 1 2020 4 "Caribbean" 302 1600 .     .  11941 11941      .     .     . 3
"CAN" "ABW" 877  40690 8922   6769 1 2020 4 "Caribbean" 311 1500 .     .   6769     .      .  6769     . 3
"CAN" "ABW" 877  71340 8929      0 1 2020 4 "Caribbean" 213  800 .     0      .     0      .     .     . 3
"CAN" "ABW" 877  71310 8924      0 1 2020 4 "Caribbean" 213  800 .     0      .     0      .     .     . 3
"CAN" "ABW" 877 200490 8941   8824 1 2020 4 "Caribbean" 310  700 .     .   8824     .   8824     .     . 3
"CAN" "ABW" 877  60420 8923      0 1 2020 4 "Caribbean" 306 1100 .     .      0     .      0     .     . 3
"CAN" "ABW" 877 160413 8937      0 1 2020 4 "Caribbean" 312 1300 .     .      0     .      .     0     . 3
"CAN" "ABW" 877 210690 8943   9851 1 2020 4 "Caribbean" 303 2000 .     .   9851     .      .     .  9851 3
"CAN" "ABW" 877 121490 8933      0 1 2020 4 "Caribbean" 207  400 .     0      .     0      .     .     . 3
"CAN" "ABW" 877 110100 8931  16901 1 2020 4 "Caribbean" 204 1600 . 16901      . 16901      .     .     . 3
"CAN" "ABW" 877 110313 8932      0 1 2020 4 "Caribbean" 204 1600 .     0      .     0      .     .     . 3
"CAN" "ABW" 877  81340 8574      0 1 2021 4 "Caribbean" 313  650 .     .      0     .      0     .     . 4
"CAN" "ABW" 877 190520 8581      0 1 2021 4 "Caribbean" 302 1600 .     .      0     0      .     .     . 4
"CAN" "ABW" 877 160413 8578      0 1 2021 4 "Caribbean" 312 1300 .     .      0     .      .     0     . 4
"CAN" "ABW" 877 220299 8587  13117 1 2021 4 "Caribbean" 301 2200 .     .  13117     .      .     . 13117 4
"CAN" "ABW" 877 150110 8577  31259 1 2021 4 "Caribbean" 203 1400 . 31259      .     .      . 31259     . 4
"CAN" "ABW" 877  71339 8572      0 1 2021 4 "Caribbean" 213  800 .     0      .     0      .     .     . 4
"CAN" "ABW" 877 210690 8586      0 1 2021 4 "Caribbean" 303 2000 .     .      0     .      .     .     0 4
"CAN" "ABW" 877  71333 8571      0 1 2021 4 "Caribbean" 213  800 .     0      .     0      .     .     . 4
"CAN" "ABW" 877  21020 8568      0 1 2021 4 "Caribbean" 304 1200 .     .      0     .      .     0     . 4
"CAN" "ABW" 877 200490 8584      0 1 2021 4 "Caribbean" 310  700 .     .      0     .      0     .     . 4
end
label values conco conco_label
label def conco_label 877 "Aruba", modify
label values id2 id1
label def id1 1 "ABW 1 2018", modify
label def id1 2 "ABW 1 2019", modify
label def id1 3 "ABW 1 2020", modify
label def id1 4 "ABW 1 2021", modify

This is an excerpt from the using dataset;

* Example generated by -dataex-. For more info, type help dataex
clear
input str8 importer str32 location byte month_id float year byte month double handwash float(max_cases max_deaths max_tests max_vac max_string avg_string id2)
"ABW" "Aruba"        1 2  5      .   6966   59       .        . 48.15   41.9371   1
"ABW" "Aruba"        1 3  5      .  33000  193       .   166440 34.26  31.75355   2
"ABW" "Aruba"        2 2  4      .   7891   73       .        . 56.48  54.62857   3
"ABW" "Aruba"        2 3  4      .  33684  211       .   167820 30.56 28.899286   4
"ABW" "Aruba"        3 1  8      .     55    .       .        . 85.19  39.17947   5
"ABW" "Aruba"        3 2  8      .   9443   86       .    25766 67.59  57.55516   6
"ABW" "Aruba"        3 3  8      .  33966  212       .   169341  28.7  27.53839   7
"ABW" "Aruba"        4 1  1      .    100    2       .        . 88.89     85.56   8
"ABW" "Aruba"        4 2  1      .  10638   99       .    76865 67.59  62.90333   9
"ABW" "Aruba"        4 3  1      .  34846  271       .   170323 25.93     25.93  10
"ABW" "Aruba"        5 1  9      .    101    3       .        . 85.19 74.492905  11
"ABW" "Aruba"        5 2  9      .  10985  107       .   114154 60.19  53.43581  12
"ABW" "Aruba"        5 3  9      .  37034  213       .   171159 25.93     25.93  13
"ABW" "Aruba"        6 1  7      .    103    3       .        . 57.41  50.02967  14
"ABW" "Aruba"        6 2  7      .  11138  107     793   127256 48.15  37.09667  15
"ABW" "Aruba"        6 3  7      .  40718  221       .   172014 25.93     25.93  16
"ABW" "Aruba"        7 1  6      .    121    3       .        . 47.22     47.22  17
"ABW" "Aruba"        7 2  6      .  11730  110       .   138509 33.33 29.208065  18
"ABW" "Aruba"        7 3  6      .  42080  224       .   172668 25.93     25.93  19
"ABW" "Aruba"        8 1  2      .   2006   10       .        . 73.15  64.06742  20
"ABW" "Aruba"        8 2  2      .  14576  146       .   148225 50.93  40.17355  21
"ABW" "Aruba"        8 3  2      .  42848  227       .   173120 25.93     25.93  22
"ABW" "Aruba"        9 1 12      .   3963   27       .        . 73.15    63.394  23
"ABW" "Aruba"        9 2 12      .  15500  166       .   156179 47.22    34.692  24
"ABW" "Aruba"        9 3 12      .  42970  230       .   173442 25.93     25.93  25
"ABW" "Aruba"       10 1 11      .   4513   38       .        . 58.33  54.74613  26
"ABW" "Aruba"       10 2 11      .  15925  171       .   159406 34.26     34.26  27
"ABW" "Aruba"       10 3 11      .  44145  232       .   173754 25.93     25.93  28
"ABW" "Aruba"       11 1 10      .   4845   45       .        . 47.22  42.68633  29
"ABW" "Aruba"       11 2 10      .  16354  174       .   161643 34.26     34.26  30
"ABW" "Aruba"       11 3 10      .  43641  236       .   173984 25.93     25.93  31
"ABW" "Aruba"       12 1  3      .   5489   49       .        . 38.89  34.82645  32
"ABW" "Aruba"       12 2  3      .  20461  181       .   163759 34.26     34.26  33
"ABW" "Aruba"       12 3  3      .  43752  280       .   174138 25.93     25.93  34
"AFG" "Afghanistan"  1 2  5 37.746  55023 2400       .        . 12.04     12.04  35
"AFG" "Afghanistan"  1 3  5 37.746 162926 7414  853003  5125146 19.44     19.44  36
"AFG" "Afghanistan"  2 1  4 37.746      5    .       .        .  8.33      8.33  37
"AFG" "Afghanistan"  2 2  4 37.746  55714 2443       .     8200 12.04     12.04  38
"AFG" "Afghanistan"  2 3  4 37.746 173659 7598  889430  5535254 19.44     19.44  39
"AFG" "Afghanistan"  3 1  8 37.746    166    4       .        . 67.59  40.83194  40
"AFG" "Afghanistan"  3 2  8 37.746  56454 2484       .    54000 30.56  24.10613  41
"AFG" "Afghanistan"  3 3  8 37.746 177747 7670  904055  5751015 19.44 17.295485  42
"AFG" "Afghanistan"  4 1  1 37.746   1827   60       .        . 84.26     80.74  43
"AFG" "Afghanistan"  4 2  1 37.746  59745 2625       .   240000 26.85     26.85  44
"AFG" "Afghanistan"  4 3  1 37.746 178879 7683       .  5986139 16.67     16.67  45
"AFG" "Afghanistan"  5 1  9 37.746  15180  254       .        . 84.26     84.26  46
"AFG" "Afghanistan"  5 2  9 37.746  71838 2944       .   600152 54.63 33.302258  47
"AFG" "Afghanistan"  5 3  9 37.746 180347 7705  981844  6118557 16.67 12.724194  48
"AFG" "Afghanistan"  6 1  7 37.746  31445  739       .        . 84.26  84.07467  49
"AFG" "Afghanistan"  6 2  7 37.746 118659 4871       .   886854  46.3    44.417  50
"AFG" "Afghanistan"  6 3  7 37.746 182528 7723  994894  6445359 11.11     11.11  51
"AFG" "Afghanistan"  7 1  6 37.746  36628 1275       .        .  78.7      78.7  52
"AFG" "Afghanistan"  7 2  6 37.746 147154 6708       .  1171064 56.02  53.73419  53
"AFG" "Afghanistan"  7 3  6 37.746 185749 7748       .  8040605 11.11     11.11  54
"AFG" "Afghanistan"  8 1  2 37.746  38248 1406       .        .  78.7  56.84097  55
"AFG" "Afghanistan"  8 2  2 37.746 153220 7118       .  1979652 52.31 35.570324  56
"AFG" "Afghanistan"  8 3  2 37.746 193250 7777       . 11562525 11.11     11.11  57
"AFG" "Afghanistan"  9 1 12 37.746  39354 1462       .        .  28.7      28.7  58
"AFG" "Afghanistan"  9 2 12 37.746 155174 7204       .  2369625 42.59 27.348333  59
"AFG" "Afghanistan"  9 3 12 37.746 199188 7800       . 11881035 11.11      9.26  60
"AFG" "Afghanistan" 10 1 11 37.746  41334 1533       .        .  28.7 17.057096  61
"AFG" "Afghanistan" 10 2 11 37.746 156250 7280       .        . 38.89  33.99387  62
"AFG" "Afghanistan" 10 3 11 37.746 203063 7822       . 12055358  5.56      5.56  63
"AFG" "Afghanistan" 11 1 10 37.746  46215 1763       .        . 12.04 11.298667  64
"AFG" "Afghanistan" 11 2 10 37.746 157289 7308       .  4018197 27.78     27.78  65
"AFG" "Afghanistan" 11 3 10 37.746 205907 7833       . 12275911  5.56 3.4286666  66
"AFG" "Afghanistan" 12 1  3 37.746  52330 2189       .        . 12.04     12.04  67
"AFG" "Afghanistan" 12 2  3 37.746 158084 7356       .  4674518 27.78 23.744516  68
"AFG" "Afghanistan" 12 3  3 37.746 207559 7849       . 12449870  2.78  .4483871  69
"AGO" "Angola"       1 2  5 26.664  19796  466       .        . 65.74  63.31871  70
"AGO" "Angola"       1 3  5 26.664  98116 1895 1368254 14588435 71.51  70.10677  71
"AGO" "Angola"       2 2  4 26.664  20807  508       .        . 62.96  60.08357  72
"AGO" "Angola"       2 3  4 26.664  98741 1900 1424011 16259606  68.4  65.36643  73
"AGO" "Angola"       3 1  8 26.664      7    2       .        . 90.74  49.84417  74
"AGO" "Angola"       3 2  8 26.664  22311  537       .   130750 59.26  57.91258  75
"AGO" "Angola"       3 3  8 26.664  99169 1900 1491179 17535412 64.32  47.38903  76
"AGO" "Angola"       4 1  1 26.664     27    2       .        . 90.74  87.52934  77
"AGO" "Angola"       4 2  1 26.664  26652  596       .   456349 59.26  58.02333  78
"AGO" "Angola"       4 3  1 26.664  99287 1900 1499795 17896626 42.37  42.35833  79
"AGO" "Angola"       5 1  9 26.664     86    4       .        . 81.48  78.52484  80
"AGO" "Angola"       5 2  9 26.664  34551  766       .   909215 58.33  40.58807  81
"AGO" "Angola"       5 3  9 26.664  99761 1900 1618311 18956332 43.29  43.21323  82
"AGO" "Angola"       6 1  7 26.664    284   13       .        . 77.78  76.36166  83
"AGO" "Angola"       6 2  7 26.664  38849  900       .  1488292 56.94    48.193  84
"AGO" "Angola"       6 3  7 26.664 101320 1900 1618566 20397116 43.25    39.741  85
"AGO" "Angola"       7 1  6 26.664   1148   52       .        . 79.17  76.45065  86
"AGO" "Angola"       7 2  6 26.664  42777 1011       .  1592537 56.94     56.94  87
"AGO" "Angola"       7 3  6 26.664 102301 1912       . 21323036 28.33 28.294193  88
"AGO" "Angola"       8 1  2 26.664   2654  108       .        . 79.17  78.18355  89
"AGO" "Angola"       8 2  2 26.664  47544 1217       .  1841568 56.94     56.94  90
"AGO" "Angola"       8 3  2 26.664 102636 1917       . 22226536 28.27  28.23742  91
"AGO" "Angola"       9 1 12 26.664   4972  183       .        . 81.02     78.01  92
"AGO" "Angola"       9 2 12 26.664  56583 1537       .  3134137 63.89     63.89  93
"AGO" "Angola"       9 3 12 26.664 103131 1917       . 22297104 28.21  27.28333  94
"AGO" "Angola"      10 1 11 26.664  10805  284       .        .    75  70.16322  95
"AGO" "Angola"      10 2 11 26.664  64433 1710       .  6164219  62.5  61.06581  96
"AGO" "Angola"      10 3 11 26.664 103131 1917       . 22990788 22.65     22.65  97
"AGO" "Angola"      11 1 10 26.664  15139  348       .        . 60.19    59.217  98
"AGO" "Angola"      11 2 10 26.664  65168 1733       .  9026310 61.11  56.69533  99
"AGO" "Angola"      11 3 10 26.664 104676 1924       . 23335624 22.65     22.28 100
end
label values year year_
label def year_ 1 "2020", modify
label def year_ 2 "2021", modify
label def year_ 3 "2022", modify
label values month month_
label def month_ 1 "Apr", modify
label def month_ 2 "Aug", modify
label def month_ 3 "Dec", modify
label def month_ 4 "Feb", modify
label def month_ 5 "Jan", modify
label def month_ 6 "Jul", modify
label def month_ 7 "Jun", modify
label def month_ 8 "Mar", modify
label def month_ 9 "May", modify
label def month_ 10 "Nov", modify
label def month_ 11 "Oct", modify
label def month_ 12 "Sep", modify
label values id2 id2
label def id2 1 "ABW 1 2021", modify
label def id2 2 "ABW 1 2022", modify
label def id2 3 "ABW 2 2021", modify
label def id2 4 "ABW 2 2022", modify
label def id2 5 "ABW 3 2020", modify
label def id2 6 "ABW 3 2021", modify
label def id2 7 "ABW 3 2022", modify
label def id2 8 "ABW 4 2020", modify
label def id2 9 "ABW 4 2021", modify
label def id2 10 "ABW 4 2022", modify
label def id2 11 "ABW 5 2020", modify
label def id2 12 "ABW 5 2021", modify
label def id2 13 "ABW 5 2022", modify
label def id2 14 "ABW 6 2020", modify
label def id2 15 "ABW 6 2021", modify
label def id2 16 "ABW 6 2022", modify
label def id2 17 "ABW 7 2020", modify
label def id2 18 "ABW 7 2021", modify
label def id2 19 "ABW 7 2022", modify
label def id2 20 "ABW 8 2020", modify
label def id2 21 "ABW 8 2021", modify
label def id2 22 "ABW 8 2022", modify
label def id2 23 "ABW 9 2020", modify
label def id2 24 "ABW 9 2021", modify
label def id2 25 "ABW 9 2022", modify
label def id2 26 "ABW 10 2020", modify
label def id2 27 "ABW 10 2021", modify
label def id2 28 "ABW 10 2022", modify
label def id2 29 "ABW 11 2020", modify
label def id2 30 "ABW 11 2021", modify
label def id2 31 "ABW 11 2022", modify
label def id2 32 "ABW 12 2020", modify
label def id2 33 "ABW 12 2021", modify
label def id2 34 "ABW 12 2022", modify
label def id2 35 "AFG 1 2021", modify
label def id2 36 "AFG 1 2022", modify
label def id2 37 "AFG 2 2020", modify
label def id2 38 "AFG 2 2021", modify
label def id2 39 "AFG 2 2022", modify
label def id2 40 "AFG 3 2020", modify
label def id2 41 "AFG 3 2021", modify
label def id2 42 "AFG 3 2022", modify
label def id2 43 "AFG 4 2020", modify
label def id2 44 "AFG 4 2021", modify
label def id2 45 "AFG 4 2022", modify
label def id2 46 "AFG 5 2020", modify
label def id2 47 "AFG 5 2021", modify
label def id2 48 "AFG 5 2022", modify
label def id2 49 "AFG 6 2020", modify
label def id2 50 "AFG 6 2021", modify
label def id2 51 "AFG 6 2022", modify
label def id2 52 "AFG 7 2020", modify
label def id2 53 "AFG 7 2021", modify
label def id2 54 "AFG 7 2022", modify
label def id2 55 "AFG 8 2020", modify
label def id2 56 "AFG 8 2021", modify
label def id2 57 "AFG 8 2022", modify
label def id2 58 "AFG 9 2020", modify
label def id2 59 "AFG 9 2021", modify
label def id2 60 "AFG 9 2022", modify
label def id2 61 "AFG 10 2020", modify
label def id2 62 "AFG 10 2021", modify
label def id2 63 "AFG 10 2022", modify
label def id2 64 "AFG 11 2020", modify
label def id2 65 "AFG 11 2021", modify
label def id2 66 "AFG 11 2022", modify
label def id2 67 "AFG 12 2020", modify
label def id2 68 "AFG 12 2021", modify
label def id2 69 "AFG 12 2022", modify
label def id2 70 "AGO 1 2021", modify
label def id2 71 "AGO 1 2022", modify
label def id2 72 "AGO 2 2021", modify
label def id2 73 "AGO 2 2022", modify
label def id2 74 "AGO 3 2020", modify
label def id2 75 "AGO 3 2021", modify
label def id2 76 "AGO 3 2022", modify
label def id2 77 "AGO 4 2020", modify
label def id2 78 "AGO 4 2021", modify
label def id2 79 "AGO 4 2022", modify
label def id2 80 "AGO 5 2020", modify
label def id2 81 "AGO 5 2021", modify
label def id2 82 "AGO 5 2022", modify
label def id2 83 "AGO 6 2020", modify
label def id2 84 "AGO 6 2021", modify
label def id2 85 "AGO 6 2022", modify
label def id2 86 "AGO 7 2020", modify
label def id2 87 "AGO 7 2021", modify
label def id2 88 "AGO 7 2022", modify
label def id2 89 "AGO 8 2020", modify
label def id2 90 "AGO 8 2021", modify
label def id2 91 "AGO 8 2022", modify
label def id2 92 "AGO 9 2020", modify
label def id2 93 "AGO 9 2021", modify
label def id2 94 "AGO 9 2022", modify
label def id2 95 "AGO 10 2020", modify
label def id2 96 "AGO 10 2021", modify
label def id2 97 "AGO 10 2022", modify
label def id2 98 "AGO 11 2020", modify
label def id2 99 "AGO 11 2021", modify
label def id2 100 "AGO 11 2022", modify

[Clyde Schechter]

OK, there is a data problem that is simple enough to fix. Whoever created the using data set made the mistake of -encode-ing the year variable. So in the using data, despite what you see in listings and the data browser, the values of year in that data set are 1, 2, and 3--which means they will not match with any year in the master data set. So the code begins by fixing that. Then it's a simple m:1 -merge-:

//  FIX THE YEAR VARIABLE IN THE USING FILE: IT WAS ENCODED, BUT SHOULD NOT HAVE BEEN
use `using', clear
decode year, gen(_year)
drop year
destring _year, replace
rename _year year
save `using', replace

use `master', clear
merge m:1 importer month year using `using', keep(match master) ///
    keepusing(max_cases max_deaths max_tests)

Replace `master' and `using' by the actual names of your master and using data sets.

[Odiri Metieh]

OMG! Clyde Schechter You are a life saver! Thank you so very much! It worked finally! Can't believe I've been on this issue for the past 1week and this was all the code needed to end my misery lol. Thank you sooo much! I'm truly grateful.

[Odiri Metieh]

Hello Clyde Schechter Please I'm trying to specify the panel structure of my dataset. Here is my code for more context;

tab year, gen(year_)
egen time = group(month_id year)
egen pairid = group(importer exporter)
xtset pairid time

After running this, I get an error saying there are repeated time values within the panel. Yes, there are repeated values, but these observations are not duplicates. Any ideas how I can fix this problem please? I appreciate your time/advise.

* Example generated by -dataex-. For more info, type help dataex
clear
input str3 exporter str8 importer long hs06 float value byte month_id float year byte region int bico float(bico_bval bico_ival) byte(year_1 year_2 year_3 year_4 year_5) float(time pairid)
"CAN" "ABW" 110100  18293 1 2018 4 204 . 18293 1 0 0 0 0 1 1
"CAN" "ABW" 110412    320 1 2018 4 204 .   320 1 0 0 0 0 1 1
"CAN" "ABW" 200580      0 1 2018 4 310 .     . 1 0 0 0 0 1 1
"CAN" "ABW"  71333      0 1 2018 4 213 .     0 1 0 0 0 0 1 1
"CAN" "ABW" 210210      0 1 2018 4 215 .     0 1 0 0 0 0 1 1
"CAN" "ABW" 200490      0 1 2018 4 310 .     . 1 0 0 0 0 1 1
"CAN" "ABW" 220299   4374 1 2018 4 301 .     . 1 0 0 0 0 1 1
"CAN" "ABW" 150790      0 1 2018 4 203 .     0 1 0 0 0 0 1 1
"CAN" "ABW"  71340      0 1 2018 4 213 .     0 1 0 0 0 0 1 1
"CAN" "ABW"  21099      0 1 2018 4 304 .     . 1 0 0 0 0 1 1
"CAN" "ABW"  21019      0 1 2018 4 304 .     . 1 0 0 0 0 1 1
"CAN" "ABW" 100590      0 1 2018 4 101 0     . 1 0 0 0 0 1 1
"CAN" "ABW" 190531   2863 1 2018 4 302 .     . 1 0 0 0 0 1 1
"CAN" "ABW" 190590   5065 1 2018 4 302 .     . 1 0 0 0 0 1 1
"CAN" "ABW"  40690      0 1 2018 4 311 .     . 1 0 0 0 0 1 1
"CAN" "ABW" 200410  37256 1 2018 4 310 .     . 1 0 0 0 0 1 1
"CAN" "ABW"  60420      0 1 2018 4 306 .     . 1 0 0 0 0 1 1
"CAN" "ABW" 220870      0 1 2018 4 301 .     . 1 0 0 0 0 1 1
"CAN" "ABW"  71320      0 1 2018 4 213 .     0 1 0 0 0 0 1 1
"CAN" "ABW" 220110      0 1 2018 4 301 .     . 1 0 0 0 0 1 1
"CAN" "ABW"  71339      0 1 2018 4 213 .     0 1 0 0 0 0 1 1
"CAN" "ABW" 210690      0 1 2018 4 303 .     . 1 0 0 0 0 1 1
"CAN" "ABW" 110313      0 1 2018 4 204 .     0 1 0 0 0 0 1 1
"CAN" "ABW"  30542      0 1 2018 4 304 .     . 1 0 0 0 0 1 1
"CAN" "ABW"  21020      0 1 2018 4 304 .     . 1 0 0 0 0 1 1
"CAN" "ABW"  71310      0 1 2018 4 213 .     0 1 0 0 0 0 1 1
"CAN" "ABW" 150110      0 1 2018 4 203 .     0 1 0 0 0 0 1 1
"CAN" "ABW" 200490   2027 1 2019 4 310 .     . 0 1 0 0 0 2 1
"CAN" "ABW" 110100      0 1 2019 4 204 .     0 0 1 0 0 0 2 1
"CAN" "ABW" 121490      0 1 2019 4 207 .     0 0 1 0 0 0 2 1
"CAN" "ABW" 190590   9253 1 2019 4 302 .     . 0 1 0 0 0 2 1
"CAN" "ABW"  71310      0 1 2019 4 213 .     0 0 1 0 0 0 2 1
"CAN" "ABW" 150110      0 1 2019 4 203 .     0 0 1 0 0 0 2 1
"CAN" "ABW"  60490      0 1 2019 4 306 .     . 0 1 0 0 0 2 1
"CAN" "ABW" 100590      0 1 2019 4 101 0     . 0 1 0 0 0 2 1
"CAN" "ABW" 150790  37038 1 2019 4 203 . 37038 0 1 0 0 0 2 1
"CAN" "ABW" 151710      0 1 2019 4 316 .     . 0 1 0 0 0 2 1
"CAN" "ABW"  60420      0 1 2019 4 306 .     . 0 1 0 0 0 2 1
"CAN" "ABW"  20322      0 1 2019 4 304 .     . 0 1 0 0 0 2 1
"CAN" "ABW" 210210      0 1 2019 4 215 .     0 0 1 0 0 0 2 1
"CAN" "ABW"  90121      0 1 2019 4 104 0     . 0 1 0 0 0 2 1
"CAN" "ABW"  30539      0 1 2019 4 304 .     . 0 1 0 0 0 2 1
"CAN" "ABW" 151419      0 1 2019 4 203 .     0 0 1 0 0 0 2 1
"CAN" "ABW" 110313      0 1 2019 4 204 .     0 0 1 0 0 0 2 1
"CAN" "ABW"  71339      0 1 2019 4 213 .     0 0 1 0 0 0 2 1
"CAN" "ABW"  21019      0 1 2019 4 304 .     . 0 1 0 0 0 2 1
"CAN" "ABW"  71333      0 1 2019 4 213 .     0 0 1 0 0 0 2 1
"CAN" "ABW"  40510      0 1 2019 4 311 .     . 0 1 0 0 0 2 1
"CAN" "ABW"  71340      0 1 2019 4 213 .     0 0 1 0 0 0 2 1
"CAN" "ABW"  40690   7240 1 2019 4 311 .     . 0 1 0 0 0 2 1
"CAN" "ABW" 110412      0 1 2019 4 204 .     0 0 1 0 0 0 2 1
"CAN" "ABW"  30542      0 1 2019 4 304 .     . 0 1 0 0 0 2 1
"CAN" "ABW" 210690   6763 1 2019 4 303 .     . 0 1 0 0 0 2 1
"CAN" "ABW"  20641      0 1 2019 4 304 .     . 0 1 0 0 0 2 1
"CAN" "ABW"  20649      0 1 2019 4 304 .     . 0 1 0 0 0 2 1
"CAN" "ABW"  30559      0 1 2019 4 304 .     . 0 1 0 0 0 2 1
"CAN" "ABW"  71320      0 1 2019 4 213 .     0 0 1 0 0 0 2 1
"CAN" "ABW"  30365      0 1 2019 4 304 .     . 0 1 0 0 0 2 1
"CAN" "ABW" 190531      0 1 2019 4 302 .     . 0 1 0 0 0 2 1
"CAN" "ABW"  21020      0 1 2019 4 304 .     . 0 1 0 0 0 2 1
"CAN" "ABW" 220299      0 1 2019 4 301 .     . 0 1 0 0 0 2 1
"CAN" "ABW"  20329      0 1 2019 4 304 .     . 0 1 0 0 0 2 1
"CAN" "ABW" 200410 143147 1 2019 4 310 .     . 0 1 0 0 0 2 1
"CAN" "ABW"  71339      0 1 2020 4 213 .     0 0 0 1 0 0 3 1
"CAN" "ABW"  71333      0 1 2020 4 213 .     0 0 0 1 0 0 3 1
"CAN" "ABW"  21020      0 1 2020 4 304 .     . 0 0 1 0 0 3 1
"CAN" "ABW"  71332      0 1 2020 4 213 .     0 0 0 1 0 0 3 1
"CAN" "ABW" 150790      0 1 2020 4 203 .     0 0 0 1 0 0 3 1
"CAN" "ABW" 400129      0 1 2020 4 108 0     . 0 0 1 0 0 3 1
"CAN" "ABW"  21019      0 1 2020 4 304 .     . 0 0 1 0 0 3 1
"CAN" "ABW" 150110      0 1 2020 4 203 .     0 0 0 1 0 0 3 1
"CAN" "ABW" 200410 124968 1 2020 4 310 .     . 0 0 1 0 0 3 1
"CAN" "ABW" 100590      0 1 2020 4 101 0     . 0 0 1 0 0 3 1
"CAN" "ABW" 220299   5658 1 2020 4 301 .     . 0 0 1 0 0 3 1
"CAN" "ABW" 160100      0 1 2020 4 312 .     . 0 0 1 0 0 3 1
"CAN" "ABW" 190531      0 1 2020 4 302 .     . 0 0 1 0 0 3 1
"CAN" "ABW"  71320      0 1 2020 4 213 .     0 0 0 1 0 0 3 1
"CAN" "ABW" 210210      0 1 2020 4 215 .     0 0 0 1 0 0 3 1
"CAN" "ABW"  30542      0 1 2020 4 304 .     . 0 0 1 0 0 3 1
"CAN" "ABW" 190590  11941 1 2020 4 302 .     . 0 0 1 0 0 3 1
"CAN" "ABW"  40690   6769 1 2020 4 311 .     . 0 0 1 0 0 3 1
"CAN" "ABW"  71340      0 1 2020 4 213 .     0 0 0 1 0 0 3 1
"CAN" "ABW"  71310      0 1 2020 4 213 .     0 0 0 1 0 0 3 1
"CAN" "ABW" 200490   8824 1 2020 4 310 .     . 0 0 1 0 0 3 1
"CAN" "ABW"  60420      0 1 2020 4 306 .     . 0 0 1 0 0 3 1
"CAN" "ABW" 160413      0 1 2020 4 312 .     . 0 0 1 0 0 3 1
"CAN" "ABW" 210690   9851 1 2020 4 303 .     . 0 0 1 0 0 3 1
"CAN" "ABW" 121490      0 1 2020 4 207 .     0 0 0 1 0 0 3 1
"CAN" "ABW" 110100  16901 1 2020 4 204 . 16901 0 0 1 0 0 3 1
"CAN" "ABW" 110313      0 1 2020 4 204 .     0 0 0 1 0 0 3 1
"CAN" "ABW"  81340      0 1 2021 4 313 .     . 0 0 0 1 0 4 1
"CAN" "ABW" 190520      0 1 2021 4 302 .     . 0 0 0 1 0 4 1
"CAN" "ABW" 160413      0 1 2021 4 312 .     . 0 0 0 1 0 4 1
"CAN" "ABW" 220299  13117 1 2021 4 301 .     . 0 0 0 1 0 4 1
"CAN" "ABW" 150110  31259 1 2021 4 203 . 31259 0 0 0 1 0 4 1
"CAN" "ABW"  71339      0 1 2021 4 213 .     0 0 0 0 1 0 4 1
"CAN" "ABW" 210690      0 1 2021 4 303 .     . 0 0 0 1 0 4 1
"CAN" "ABW"  71333      0 1 2021 4 213 .     0 0 0 0 1 0 4 1
"CAN" "ABW"  21020      0 1 2021 4 304 .     . 0 0 0 1 0 4 1
"CAN" "ABW" 200490      0 1 2021 4 310 .     . 0 0 0 1 0 4 1
end

[Clyde Schechter]

Yes, you have large numbers of observations with the same value of pairid and time. So you need something else to identify the "panel" for -xtset-. It looks like hs06, in combination with pairid and time will do that in the example data. But I don't know what hs06 is, and that might not make any sense, and it might not work in the full data set. If hs06 designates a sector or industry or some particular trade route between importers and exporters, or something that further classifies the pairing, then it is appropriate to do:

egen panel = group(exporter importer hs06)
xtset panel time

If it isn't hs06, then you need to check other variables in your data set that will do this and make sense. If there is no such variable, then it is not possible to -xtset- this data with a time variable. You can still -xtset pairid- and most -xt- commands will remain available to you. What you will lose is the ability to work with time series operators like lags and leads or analyses involving autoregressive correlation structure.

That said, the way you have created your time variable looks dangerous to me. In the example data it works out all right, because month_id is always 1 in the example data. But if the real data has other months, then the variable time will be in the wrong order: January 2018 will be followed by January 2019, followed by January 2020... and you won't get until February 2018 until after January 2021 (or whatever is the last year int he full data set). It is wiser to make a real Stata monthly date variable:

gen time = ym(year, month_id)
format time %tm

and use that.

Finally, why did you create the year_1 to year_5 indicator ("dummy") variables. There is seldom any need for those in Stata. If you plan to use them to represent year as a category variable in regressions, factor-variable notation (-help fvvarlist-) is a better way to do that.

[Odiri Metieh]

Thanks again Clyde Schechter Your suggestion was very helpful.

[Odiri Metieh]

Hello! Clyde Schechter I have a problem that may seem a little basic, but I can't seem to figure out what the problem is. I have this code;

*creating dummies for exporter-time- and importer-time FEs:
quietly tab it, gen(EXP_TIME_FE_)
quietly tab jt, gen(IMP_TIME_FE_)

*creating macros for dropping baseline-countries importer-time FEs in estimation 
sum it
local it=`r(max)'                    /* number of exporting country-years in sample */
sum year
local t = `r(max)'-`r(min)'+1         /* number of years in sample */
sum jt
local jt_ref = `r(max)'- `t'        /* number of importer-years without reference country */
local jt_all = `r(max)'                /* number of importer-years with reference country */
scalar jt_first_ref = `jt_ref' + 1     /* number of the first reference importer-year among all jt */
di jt_first_ref
di `jt_ref'
di `jt_all'
di `t'
di `it'

*******************************************************************************
*            Run this part to get estimates for the relevant fixed effects    *
*             Estimates are stored in FE_model.ster for faster computation    *
*******************************************************************************

 log using ppmlhdfe_log, replace
timer clear 1
timer on 1
ppml value "EXP_TIME_FE_1-EXP_TIME_FE_`it'" "IMP_TIME_FE_1-IMP_TIME_FE_`jt_ref'" lndist contig colties comlang fta
estimates save FE_model
save FE_model_dataset
timer off 1
timer list
cap log close 
********************************************************************************
estimates use FE_model

When I run the model, I get this error - "EXP_TIME_FE_ ambiguous abbreviation". I am a bit confused because I am not sure where the ambiguous abbreviation is coming from because the variables are named differently like this; EXP_TIME_FE_1, EXP_FE_TIME_2, EXP_FE_TIME_3 and so on. Please help me understand what could be the problem here!

Many thanks!

[Clyde Schechter]

I don't see anything obviously wrong with the code that would produce this error. But, you would get exactly this error if you are making the mistake of running it in blocks or line by line. This code must be run without interruption from beginning to end.

The reason is that when you run code line by line or in blocks, any local macros, like `it' or `jt' or `jt_ref' that get defined disappear as soon as the code is interrupted. So when you try to refer to them later, as in your -ppml- command, they are treated as empty strings. So, if you interrupted the code anywhere between the definition of local macros it, jt, and jt_ref, and the -ppml- command, Stata will see that command as -ppml value "EXP_TIME_FE_1-EXP_TIME_FE_" "IMP_TIME_FE_1-IMP_TIME_FE_"-, and thatis the source of the ambiguous EXP_TIME_FE_ reference.

If that does not resolve your problem, please post back with example data using the -dataex- command so further troubleshooting can be done. If you are running version 18, 17, 16 or a fully updated version 15.1 or 14.2, -dataex- is already part of your official Stata installation. If not, run -ssc install dataex- to get it. Either way, run -help dataex- to read the simple instructions for using it. -dataex- will save you time; it is easier and quicker than typing out tables. It includes complete information about aspects of the data that are often critical to answering your question but cannot be seen from tabular displays or screenshots. It also makes it possible for those who want to help you to create a faithful representation of your example to try out their code, which in turn makes it more likely that their answer will actually work in your data.