How to copy a column based on specific conditions

[Eleonora De Nicolo]

Dear Stata users,
I am constructing my dataset based on bilateral trade variables from 1996 to 2020. My goal is to add variables useful for creating a gravity model. For this, I have merged population data that matches with the country of origin: the data merged is thus "countryname_exp" and "total_pop_origin". I would like to create another column "total_pop_dest" which copies the population data that I have merged to the dataset, but based on the importer country and on the year. Maybe with a code similar to vlookup on excel?
I hope this is clear, I'm not very familiar with Stata.
Below you will find the dataex to show you my data:

* Example generated by -dataex-. For more info, type help dataex
clear
input int year str52 countryname_exp double total_pop_origin str32 importer_name float total_pop_dest
1996 "Hong Kong SAR, China"    6435500 "France"                           .
1996 "Austria"                 7959017 "Malta"                            .
1996 "France"                 59753095 "Belgium-Luxembourg"               .
1996 "United Kingdom"         58166950 "Thailand"                         .
1996 "Austria"                 7959017 "Hungary"                          .
1996 "Sweden"                  8840998 "Algeria"                          .
1996 "Czechia"                10315241 "Hungary"                          .
1996 "Mexico"                 93147045 "Austria"                          .
1996 "United Kingdom"         58166950 "Ukraine"                          .
1996 "Netherlands"            15530498 "Austria"                          .
1996 "Austria"                 7959017 "Indonesia"                        .
1996 "New Zealand"             3732000 "Argentina"                        .
1996 "Germany"                81914831 "Switzerland"                      .
1996 "Ukraine"                51057800 "Mexico"                           .
1996 "Hong Kong SAR, China"    6435500 "Japan"                            .
1996 "Ireland"                 3637510 "TFYR of Macedonia"                .
1996 "France"                 59753095 "Turkey"                           .
1996 "Canada"                 29610218 "Switzerland"                      .
1996 "Germany"                81914831 "TFYR of Macedonia"                .
1996 "Switzerland"             7071850 "Thailand"                         .
1996 "Korea, Rep."            45524681 "Indonesia"                        .
1996 "United Arab Emirates"    2539121 "Japan"                            .
1996 "Brazil"                164614682 "Venezuela"                        .
1996 ""                              . "Italy"                            .
1996 "France"                 59753095 "Belize"                           .
1996 "Australia"              18311000 "Indonesia"                        .
1996 "Switzerland"             7071850 "United Kingdom"                   .
1996 "Belgium"                10156637 "Denmark"                          .
1996 "Switzerland"             7071850 "Hungary"                          .
1996 "Hong Kong SAR, China"    6435500 "Canada"                           .
1996 "Czechia"                10315241 "Russian Federation"               .
1996 "Denmark"                 5263074 "Malaysia"                         .
1996 "Germany"                81914831 "Belgium-Luxembourg"               .
1996 "Russian Federation"    148160129 "Turkey"                           .
1996 "Japan"                 125757000 "Sweden"                           .
1996 "Malaysia"               21017619 "Argentina"                        .
1996 "India"                 982365248 "Pakistan"                         .
1996 "Australia"              18311000 "Indonesia"                        .
1996 ""                              . "Japan"                            .
1996 "Finland"                 5124573 "Poland"                           .
1996 "Spain"                  39889852 "United Arab Emirates"             .
1996 "Germany"                81914831 "Venezuela"                        .
1996 "Indonesia"             199901231 "USA"                              .
1996 "Italy"                  56860281 "Ireland"                          .
1996 "Turkiye"                59423278 "United Kingdom"                   .
1996 "China"                1217550000 "Poland"                           .
1996 "India"                 982365248 "Singapore"                        .
1996 "Switzerland"             7071850 "Morocco"                          .
1996 "Malaysia"               21017619 "France"                           .
1996 "Malaysia"               21017619 "Argentina"                        .
1996 "Australia"              18311000 "Indonesia"                        .
1996 "Singapore"               3670704 "Bulgaria"                         .
1996 "France"                 59753095 "Russian Federation"               .
1996 "France"                 59753095 "Italy"                            .
1996 "United States"         269394000 "Oman"                             .
1996 "United Kingdom"         58166950 "Nigeria"                          .
1996 "Germany"                81914831 "Egypt"                            .
1996 "Germany"                81914831 "Venezuela"                        .
1996 "Morocco"                27383472 "France"                           .
1996 "Japan"                 125757000 "Czechia"                          .
1996 "Egypt, Arab Rep."       63601632 "Belgium-Luxembourg"               .
1996 "Germany"                81914831 "Hungary"                          .
1996 "Austria"                 7959017 "Tunisia"                          .
1996 "Libya"                   5036173 "United Kingdom"                   .
1996 "Sweden"                  8840998 "Côte d'Ivoire"                   .
1996 "Belgium"                10156637 "Singapore"                        .
1996 "France"                 59753095 "Russian Federation"               .
1996 "France"                 59753095 "Russian Federation"               .
1996 "Sweden"                  8840998 "Bolivia (Plurinational State of)" .
1996 "Germany"                81914831 "Sri Lanka"                        .
1996 "Denmark"                 5263074 "Greenland"                        .
1996 "Morocco"                27383472 "New Zealand"                      .
1996 ""                              . "Mexico"                           .
1996 "Australia"              18311000 "Indonesia"                        .
1996 "France"                 59753095 "China, Hong Kong SAR"             .
1996 "India"                 982365248 "Mexico"                           .
1996 "Japan"                 125757000 "Venezuela"                        .
1996 "Germany"                81914831 "Venezuela"                        .
1996 "Norway"                  4381336 "Colombia"                         .
1996 "Korea, Rep."            45524681 "Malaysia"                         .
1996 "United Arab Emirates"    2539121 "Switzerland"                      .
1996 "Mozambique"             15960445 "Czechia"                          .
1996 "Malaysia"               21017619 "Netherlands"                      .
1996 "India"                 982365248 "Thailand"                         .
1996 "Hong Kong SAR, China"    6435500 "Turkey"                           .
1996 "Australia"              18311000 "Solomon Isds"                     .
1996 "Italy"                  56860281 "Slovenia"                         .
1996 "Portugal"               10063945 "Greece"                           .
1996 "Mexico"                 93147045 "Cuba"                             .
1996 "France"                 59753095 "Norway"                           .
1996 "Korea, Rep."            45524681 "Italy"                            .
1996 "Denmark"                 5263074 "China, Hong Kong SAR"             .
1996 "United States"         269394000 "Argentina"                        .
1996 "Ireland"                 3637510 "United Kingdom"                   .
1996 "Switzerland"             7071850 "Romania"                          .
1996 "United States"         269394000 "Malaysia"                         .
1996 "Greece"                 10608800 "Spain"                            .
1996 "Netherlands"            15530498 "Other Asia, nes"                  .
1996 "Singapore"               3670704 "United Kingdom"                   .
1996 "Dominica"                  70933 "Rep. of Korea"                    .
end

[Hemanshu Kumar]

drop total_pop_dest

preserve
    keep year countryname_exp total_pop_origin
    drop if missing(countryname_exp) | missing(total_pop_origin)
    duplicates drop year countryname_exp, force
    rename countryname_exp importer_name
    rename total_pop_origin total_pop_dest
    tempfile pops
    save `pops'
restore

merge m:1 year importer_name using `pops', keep(1 3)

[Nick Cox]

I read the problem differently. It seems to me that you want a yearly total over exporter countries for each importer. That interpretation is quite at odds with any wording of "copying" but otherwise why say that you want a new variable that is a total?

If Hemanshu Kumar decoded the question correctly, that's excellent. Otherwise check out egen, total().

[Eleonora De Nicolo]

Thank you so much Hemanshu Kumar , it seems to work! However, I cannot drop the duplicates, as I also have a variable listing product codes, and thus creates duplicates, as the majority of the countries might trade more than one product with the same partner. Is there a way to not drop the duplicates? I tried to delete the line "duplicates drop year countryname_exp, force" and see if it works, but as I imagined the error shows "variables year importer_name do not uniquely identify observations in the using data r(459)"

Here is another part of my dataset for more clarity:

* Example generated by -dataex-. For more info, type help dataex
clear
input int(year iso3num_o iso3num_d) str6 hs6 str32 exporter_name str52 importer_name
1996 842  4 "854240" "USA"                  "Afghanistan"
1996 842  4 "853400" "USA"                  "Afghanistan"
1996 842  4 "854140" "USA"                  "Afghanistan"
1996 842  4 "854250" "USA"                  "Afghanistan"
1996 842  4 "854219" "USA"                  "Afghanistan"
1996 842  4 "854230" "USA"                  "Afghanistan"
1996 842  4 "854110" "USA"                  "Afghanistan"
1996 246  4 "854140" "Finland"              "Afghanistan"
1996 484  4 "853400" "Mexico"               "Afghanistan"
1996 842  4 "854213" "USA"                  "Afghanistan"
1996 842  4 "854214" "USA"                  "Afghanistan"
1996 842  4 "854212" "USA"                  "Afghanistan"
1996 842  4 "854190" "USA"                  "Afghanistan"
1996 842  4 "854290" "USA"                  "Afghanistan"
1996 381  8 "854121" "Italy"                "Albania"    
1996 251  8 "854212" "France"               "Albania"    
1996 381  8 "854140" "Italy"                "Albania"    
1996  40  8 "854212" "Austria"              "Albania"    
1996 757  8 "854129" "Switzerland"          "Albania"    
1996 807  8 "854212" "TFYR of Macedonia"    "Albania"    
1996 381  8 "854219" "Italy"                "Albania"    
1996  58  8 "854290" "Belgium-Luxembourg"   "Albania"    
1996  40  8 "854213" "Austria"              "Albania"    
1996 251  8 "854230" "France"               "Albania"    
1996 381  8 "854129" "Italy"                "Albania"    
1996 381  8 "854212" "Italy"                "Albania"    
1996 381  8 "854250" "Italy"                "Albania"    
1996 348  8 "854213" "Hungary"              "Albania"    
1996 203  8 "854110" "Czechia"              "Albania"    
1996 381  8 "903141" "Italy"                "Albania"    
1996 381  8 "854110" "Italy"                "Albania"    
1996 276  8 "854190" "Germany"              "Albania"    
1996 381  8 "854213" "Italy"                "Albania"    
1996 381  8 "854290" "Italy"                "Albania"    
1996  58  8 "903141" "Belgium-Luxembourg"   "Albania"    
1996 276  8 "854129" "Germany"              "Albania"    
1996 300  8 "854110" "Greece"               "Albania"    
1996 276  8 "854213" "Germany"              "Albania"    
1996 807  8 "854230" "TFYR of Macedonia"    "Albania"    
1996 842  8 "854290" "USA"                  "Albania"    
1996 842  8 "854110" "USA"                  "Albania"    
1996 842  8 "854250" "USA"                  "Albania"    
1996 300  8 "854121" "Greece"               "Albania"    
1996 372  8 "853400" "Ireland"              "Albania"    
1996 381  8 "853400" "Italy"                "Albania"    
1996 792  8 "854190" "Turkey"               "Albania"    
1996 276  8 "854230" "Germany"              "Albania"    
1996  40  8 "854110" "Austria"              "Albania"    
1996 276  8 "853400" "Germany"              "Albania"    
1996  40  8 "854214" "Austria"              "Albania"    
1996 348  8 "903141" "Hungary"              "Albania"    
1996 788 12 "853400" "Tunisia"              "Algeria"    
1996 276 12 "854240" "Germany"              "Algeria"    
1996 458 12 "854230" "Malaysia"             "Algeria"    
1996 392 12 "853400" "Japan"                "Algeria"    
1996 208 12 "854150" "Denmark"              "Algeria"    
1996 762 12 "854121" "Tajikistan"           "Algeria"    
1996 579 12 "854250" "Norway"               "Algeria"    
1996 208 12 "854130" "Denmark"              "Algeria"    
1996 842 12 "903141" "USA"                  "Algeria"    
1996 764 12 "854212" "Thailand"             "Algeria"    
1996  58 12 "854214" "Belgium-Luxembourg"   "Algeria"    
1996 752 12 "854213" "Sweden"               "Algeria"    
1996 724 12 "854213" "Spain"                "Algeria"    
1996 344 12 "854129" "China, Hong Kong SAR" "Algeria"    
1996 372 12 "854250" "Ireland"              "Algeria"    
1996 792 12 "854129" "Turkey"               "Algeria"    
1996 842 12 "854190" "USA"                  "Algeria"    
1996 842 12 "854130" "USA"                  "Algeria"    
1996 724 12 "854150" "Spain"                "Algeria"    
1996 752 12 "854250" "Sweden"               "Algeria"    
1996 757 12 "854212" "Switzerland"          "Algeria"    
1996 757 12 "854150" "Switzerland"          "Algeria"    
1996 842 12 "854212" "USA"                  "Algeria"    
1996 410 12 "854230" "Rep. of Korea"        "Algeria"    
1996 392 12 "854219" "Japan"                "Algeria"    
1996 381 12 "854212" "Italy"                "Algeria"    
1996 208 12 "854290" "Denmark"              "Algeria"    
1996 792 12 "854240" "Turkey"               "Algeria"    
1996 724 12 "854129" "Spain"                "Algeria"    
1996 764 12 "853400" "Thailand"             "Algeria"    
1996 703 12 "854110" "Slovakia"             "Algeria"    
1996 842 12 "853400" "USA"                  "Algeria"    
1996 757 12 "854219" "Switzerland"          "Algeria"    
1996 251 12 "854230" "France"               "Algeria"    
1996 124 12 "854219" "Canada"               "Algeria"    
1996  58 12 "853400" "Belgium-Luxembourg"   "Algeria"    
1996 246 12 "854250" "Finland"              "Algeria"    
1996 826 12 "854212" "United Kingdom"       "Algeria"    
1996 752 12 "854160" "Sweden"               "Algeria"    
1996 392 12 "854212" "Japan"                "Algeria"    
1996 490 12 "854129" "Other Asia, nes"      "Algeria"    
1996  58 12 "903082" "Belgium-Luxembourg"   "Algeria"    
1996 381 12 "854140" "Italy"                "Algeria"    
1996 724 12 "854212" "Spain"                "Algeria"    
1996 842 12 "854110" "USA"                  "Algeria"    
1996 156 12 "854129" "China"                "Algeria"    
1996 752 12 "854129" "Sweden"               "Algeria"    
1996 757 12 "853400" "Switzerland"          "Algeria"    
1996 208 12 "854240" "Denmark"              "Algeria"    
end

And thank you Nick Cox , I realize I wasn't clear at all regarding my request, and I apologize for that. I didn't want to compute the total. I have a column already which shows the population of origin, so the variable I named "total_pop_origin" (that I should have named only as pop_origin) that represents the population of the exporting countries (the variable "countryname_exp") and I have another variable which shows the importer countries, named "importer_name". So I wanted the variable "pop_dest", the population of importing countries, to incorporate the same data of the variable "pop_origin" but "matching" the population data with the importer_name and year variables.

[Hemanshu Kumar]

Eleonora De Nicolo dropping the duplicates should not create a problem as long as each country only has a single value for the population in any year. Please double-check the results of the code I provided and see if it creates a problem. If so, please show us what happens.

[Eleonora De Nicolo]

Hemanshu Kumar I double-checked and you're right, everything works fine. Thank you so much again!