Difference in years with bilateral data?

[Killian Foubert]

Hi everyone

I'm facing a problem on stata, I really don't know how to solve this. So basically i have bilateral data per 10 years (stock of migrants) and I would like to get the difference of migrants in a country to be used as a proxy for bilateral flows (i know it is a big assumption, but it quite fits my work). My data look like this:

Year	CountryO	CountryD	MigrantsStock	MigrantsFlows
1970	Afghanistan	x	100
1970	Afghanistan	y	50
1980	Afghanistan	x	75	-25
1980	Afghanistan	y	60	+10

I would like to obtain the MigrantsFlows column. I tried to use the following commands:
xtset year countryO countryD
gen MigrantsFlows = D.MigrantsStock

but too many variables are specified in my xtset. Does someone have any idea about how to do this?

Thank you in advance

Killian

[Clyde Schechter]

Try this:

egen dyad = group(CountryO CountryD)
xtset dyad year, delta(10)
gen MigrantsFlows = D.MigrantsStock

[Killian Foubert]

It works perfectly. Thank you very much

[Killian Foubert]

I have now another issue to get my migrants network though, sorry for the re-post. My network corresponds to the migrants stock 10 years before, such as:

Year	countryO	countryD	migrantstock	migrantsflows	network
1960	Afghanistan	x	10	.	.
1970	Afghanistan	y	20	20-10=10	10 (migrantstock 1960)
1960	Albania	x	15	.	.
1970	Albania	y	30	15	15

I tried to create a sequence but without any conviction. I don't know how to "move" migrant stock by 10 year with respect to countryO countryD pairs.
I hope it is clear.

Thank you in advance

Killian

[Clyde Schechter]

I don't understand what you want to do. Please show an example of what you currently have and then how it would look after doing what it is you want to do.

By the way, you have been using Statalist long enough now that you should be posting your data examples with the -dataex- command, not in unusable HTML tables. If you are not yet familiar with -dataex-, install it by running -ssc intall dataex-. Then run -help dataex- to read the simple instructions for using it. When you use -dataex- to show example data, you enable those who want to help you to create a completely faithful replica of your example in Stata with a simple copy/paste operation. That enables them to try out code before posting it.

[Killian Foubert]

* Example generated by -dataex-. To install: ssc install dataex
clear
input float year str59 countryO str52 countryD double migrantstock float migrantsflows
1960 "Afghanistan" "Georgia"                            0     .
1960 "Afghanistan" "Equatorial Guinea"                  0     .
1960 "Afghanistan" "Nepal"                              0     .
1960 "Afghanistan" "San Marino"                         0     .
1960 "Afghanistan" "Lesotho"                            0     .
1960 "Afghanistan" "Liberia"                            0     .
1960 "Afghanistan" "Egypt, Arab Rep."                1450     .
1960 "Afghanistan" "Honduras"                           1     .
1960 "Afghanistan" "France"                          1767     .
1960 "Afghanistan" "Tonga"                              0     .
1960 "Afghanistan" "Cote d'Ivoire"                      0     .
1960 "Afghanistan" "Virgin Islands (U.S.)"              0     .
1960 "Afghanistan" "Myanmar"                           12     .
1960 "Afghanistan" "Luxembourg"                         0     .
1960 "Afghanistan" "Cameroon"                           0     .
1960 "Afghanistan" "Sierra Leone"                       0     .
1960 "Afghanistan" "Greece"                             9     .
1960 "Afghanistan" "Cyprus"                             3     .
1960 "Afghanistan" "Colombia"                           0     .
1960 "Afghanistan" "India"                           3827     .
1960 "Afghanistan" "Philippines"                       11     .
1960 "Afghanistan" "Togo"                               0     .
1960 "Afghanistan" "Gabon"                              0     .
1960 "Afghanistan" "Denmark"                           13     .
1960 "Afghanistan" "Cuba"                               0     .
1960 "Afghanistan" "Faeroe Islands"                     7     .
1960 "Afghanistan" "Libya"                             36     .
1960 "Afghanistan" "Tunisia"                           83     .
1960 "Afghanistan" "Dominica"                           0     .
1960 "Afghanistan" "Belize"                             0     .
1960 "Afghanistan" "Guinea"                             0     .
1960 "Afghanistan" "Russian Federation"                 0     .
1960 "Afghanistan" "Uganda"                             1     .
1960 "Afghanistan" "Aruba"                              0     .
1960 "Afghanistan" "Turks and Caicos Islands"           0     .
1960 "Afghanistan" "Malawi"                             0     .
1960 "Afghanistan" "Mali"                               0     .
1960 "Afghanistan" "Gambia, The"                        0     .
1960 "Afghanistan" "Sweden"                            17     .
1960 "Afghanistan" "Bosnia and Herzegovina"             0     .
1960 "Afghanistan" "Reunion"                            0     .
1960 "Afghanistan" "St. Kitts and Nevis"                0     .
1960 "Afghanistan" "Anguilla"                           0     .
1960 "Afghanistan" "El Salvador"                        7     .
1960 "Afghanistan" "Cook Islands"                       0     .
1960 "Afghanistan" "St. Vincent and the Grenadines"     0     .
1960 "Afghanistan" "Grenada"                            0     .
1960 "Afghanistan" "Tuvalu"                             0     .
1960 "Afghanistan" "Palau"                              0     .
1960 "Afghanistan" "Korea, Rep."                        0     .
1960 "Afghanistan" "Netherlands Antilles"               0     .
1960 "Afghanistan" "Seychelles"                         0     .
1960 "Afghanistan" "Estonia"                            0     .
1960 "Afghanistan" "Latvia"                             0     .
1960 "Afghanistan" "Mauritius"                          0     .
1960 "Afghanistan" "Bolivia"                            0     .
1960 "Afghanistan" "Jamaica"                            0     .
1960 "Afghanistan" "Samoa"                              0     .
1960 "Afghanistan" "Slovak Republic"                    0     .
1960 "Afghanistan" "Nicaragua"                         10     .
1960 "Afghanistan" "Eritrea"                            0     .
1960 "Afghanistan" "Naura"                              0     .
1960 "Afghanistan" "Bhutan"                             0     .
1960 "Afghanistan" "Bangladesh"                       151     .
1960 "Afghanistan" "Paraguay"                           0     .
1960 "Afghanistan" "Brazil"                             8     .
1960 "Afghanistan" "Bahrain"                           62     .
1960 "Afghanistan" "Montserrat"                         0     .
1960 "Afghanistan" "Rwanda"                             0     .
1960 "Afghanistan" "Hong Kong SAR, China"              67     .
1960 "Afghanistan" "Mauritania"                         0     .
1960 "Afghanistan" "Armenia"                            0     .
1960 "Afghanistan" "Romania"                            0     .
1960 "Afghanistan" "Bulgaria"                         168     .
1960 "Afghanistan" "Vietnam"                            0     .
1960 "Afghanistan" "Lao PDR"                            0     .
1960 "Afghanistan" "Chad"                               0     .
1960 "Afghanistan" "Morocco"                           63     .
1960 "Afghanistan" "West Bank and Gaza"                39     .
1960 "Afghanistan" "Cayman Islands"                     0     .
1960 "Afghanistan" "Spain"                              0     .
1960 "Afghanistan" "Kazakhstan"                         0     .
1960 "Afghanistan" "Zambia"                             0     .
1960 "Afghanistan" "Poland"                             3     .
1960 "Afghanistan" "Zimbabwe"                           1     .
1960 "Afghanistan" "Yemen, Rep."                     1283     .
1960 "Afghanistan" "Venezuela, RB"                      5     .
1960 "Afghanistan" "Kiribati"                           0     .
1960 "Afghanistan" "Uruguay"                            0     .
1960 "Afghanistan" "Qatar"                             27     .
1960 "Afghanistan" "Andorra"                            0     .
1960 "Afghanistan" "Trinidad and Tobago"                0     .
1960 "Afghanistan" "Maldives"                           3     .
1960 "Afghanistan" "Croatia"                            0     .
1960 "Afghanistan" "Timor-Leste"                        0     .
1960 "Afghanistan" "Comoros"                            0     .
1960 "Afghanistan" "Monaco"                             0     .
1960 "Afghanistan" "Thailand"                           2     .
1960 "Afghanistan" "Pakistan"                       28216     .
1960 "Afghanistan" "Syrian Arab Republic"             701     .
1960 "Afghanistan" "Lebanon"                           28     .
1960 "Afghanistan" "Tokelau"                            0     .
1960 "Afghanistan" "Burkina Faso"                       0     .
1960 "Afghanistan" "Djibouti"                           0     .
1960 "Afghanistan" "Martinique"                         0     .
1960 "Afghanistan" "Central African Republic"           0     .
1960 "Afghanistan" "Macedonia, FYR"                     0     .
1960 "Afghanistan" "Falkland Islands (Malvinas)"        0     .
1960 "Afghanistan" "French Guiana"                      0     .
1960 "Afghanistan" "Uzbekistan"                         0     .
1960 "Afghanistan" "Sudan"                              5     .
1960 "Afghanistan" "Dominican Republic"                 0     .
1960 "Afghanistan" "Greenland"                          0     .
1960 "Afghanistan" "Italy"                            180     .
1960 "Afghanistan" "Angola"                             0     .
1960 "Afghanistan" "Swaziland"                          0     .
1960 "Afghanistan" "St. Lucia"                          0     .
1960 "Afghanistan" "Costa Rica"                         0     .
1960 "Afghanistan" "Sri Lanka"                         14     .
1960 "Afghanistan" "Slovenia"                           0     .
1960 "Afghanistan" "Solomon Islands"                    0     .
1960 "Afghanistan" "Ecuador"                            0     .
1960 "Afghanistan" "United States"                    293     .
1960 "Afghanistan" "Niue"                               0     .
1960 "Afghanistan" "Guatemala"                          0     .
1960 "Afghanistan" "Northern Mariana Islands"           0     .
1960 "Afghanistan" "Antigua and Barbuda"                0     .
1960 "Afghanistan" "Somalia"                            0     .
1960 "Afghanistan" "Portugal"                           1     .
1960 "Afghanistan" "Mozambique"                         0     .
1960 "Afghanistan" "Malaysia"                           0     .
1960 "Afghanistan" "Saint Helena"                       0     .
1960 "Afghanistan" "Botswana"                           0     .
1960 "Afghanistan" "Czech Republic"                     2     .
1960 "Afghanistan" "Brunei Darussalam"                  0     .
1960 "Afghanistan" "Malta"                              0     .
1960 "Afghanistan" "Taiwan, China"                      0     .
1960 "Afghanistan" "Suriname"                           0     .
1960 "Afghanistan" "Tajikistan"                         0     .
1960 "Afghanistan" "Cambodia"                           5     .
1960 "Afghanistan" "Kyrgyz Republic"                    0     .
1960 "Afghanistan" "Vanuatu"                            3     .
1960 "Afghanistan" "Turkmenistan"                       0     .
1960 "Afghanistan" "Liechtenstein"                      0     .
1960 "Afghanistan" "New Caledonia"                      0     .
1960 "Afghanistan" "Jordan"                            21     .
1960 "Afghanistan" "Peru"                               1     .
1960 "Afghanistan" "Ukraine"                            0     .
1960 "Afghanistan" "Netherlands"                        0     .
1960 "Afghanistan" "Marshall Islands"                   0     .
1960 "Afghanistan" "Indonesia"                         24     .
1960 "Afghanistan" "Madagascar"                         0     .
1960 "Afghanistan" "Norfolk Island"                     0     .
1960 "Afghanistan" "Serbia and Montenegro"              3     .
1960 "Afghanistan" "Guyana"                             0     .
1960 "Afghanistan" "Belgium"                           16     .
1960 "Afghanistan" "Algeria"                           41     .
1960 "Afghanistan" "Israel"                            16     .
1960 "Afghanistan" "Ghana"                              0     .
1960 "Afghanistan" "Mexico"                            15     .
1960 "Afghanistan" "Lithuania"                          0     .
1960 "Afghanistan" "Namibia"                            0     .
1960 "Afghanistan" "Iraq"                             327     .
1960 "Afghanistan" "Bahamas, The"                       0     .
1960 "Afghanistan" "Australia"                         42     .
1960 "Afghanistan" "Fiji"                               9     .
1960 "Afghanistan" "Saudi Arabia"                     157     .
1960 "Afghanistan" "Canada"                           228     .
1960 "Afghanistan" "Japan"                              0     .
1960 "Afghanistan" "South Africa"                       2     .
1960 "Afghanistan" "Mongolia"                           0     .
1960 "Afghanistan" "United Arab Emirates"              25     .
1960 "Afghanistan" "Belarus"                            0     .
1960 "Afghanistan" "Switzerland"                      160     .
1960 "Afghanistan" "Korea, Dem. Rep."                   0     .
1960 "Afghanistan" "Ethiopia"                           0     .
1960 "Afghanistan" "Guam"                               2     .
1960 "Afghanistan" "Iceland"                            0     .
1960 "Afghanistan" "Cape Verde"                         0     .
1960 "Afghanistan" "Wallis and Futuna"                  0     .
1960 "Afghanistan" "Singapore"                          2     .
1960 "Afghanistan" "Macao SAR, China"                   3     .
1960 "Afghanistan" "Azerbaijan"                         0     .
1960 "Afghanistan" "American Samoa"                     0     .
1960 "Afghanistan" "Saint Pierre and Miquelon"          0     .
1960 "Afghanistan" "Bermuda"                            0     .
1960 "Afghanistan" "Ireland"                            6     .
1960 "Afghanistan" "Puerto Rico"                        0     .
1960 "Afghanistan" "Chile"                              0     .
1960 "Afghanistan" "United Kingdom"                   221     .
1960 "Afghanistan" "Sao Tome and Principe"              0     .
1960 "Afghanistan" "Benin"                              0     .
1960 "Afghanistan" "Micronesia, Fed. Sts."              0     .
1960 "Afghanistan" "Senegal"                            0     .
1960 "Afghanistan" "Oman"                              80     .
1960 "Afghanistan" "Barbados"                           0     .
1960 "Afghanistan" "Austria"                           47     .
1960 "Afghanistan" "Argentina"                          6     .
1960 "Afghanistan" "Panama"                             0     .
1960 "Afghanistan" "Congo, Rep."                        0     .
1960 "Afghanistan" "New Zealand"                       19     .
1960 "Afghanistan" "Tanzania"                          23     .
1960 "Afghanistan" "Virgin Islands, British"            0     .
1960 "Afghanistan" "Turkey"                           620     .
1960 "Afghanistan" "Burundi"                            1     .
1960 "Afghanistan" "Moldova"                            0     .
1960 "Afghanistan" "Mayotte"                            0     .
1960 "Afghanistan" "Hungary"                           99     .
1960 "Afghanistan" "Guadeloupe"                         0     .
1960 "Afghanistan" "Haiti"                              0     .
1960 "Afghanistan" "Albania"                            0     .
1960 "Afghanistan" "Nigeria"                            0     .
1960 "Afghanistan" "Gibraltar"                          0     .
1960 "Afghanistan" "French Polynesia"                   0     .
1960 "Afghanistan" "Guinea-Bissau"                      0     .
1960 "Afghanistan" "Kenya"                              6     .
1960 "Afghanistan" "Norway"                             2     .
1960 "Afghanistan" "Niger"                              0     .
1960 "Afghanistan" "Papua New Guinea"                   0     .
1960 "Afghanistan" "China"                             24     .
1960 "Afghanistan" "Congo, Dem. Rep."                  26     .
1960 "Afghanistan" "Iran, Islamic Rep."             42222     .
1960 "Afghanistan" "Finland"                            0     .
1960 "Afghanistan" "Kuwait"                           760     .
1960 "Afghanistan" "Germany"                          199     .
1970 "Afghanistan" "Paraguay"                           0     0
1970 "Afghanistan" "Indonesia"                         15     .
1970 "Afghanistan" "Namibia"                            0     0
1970 "Afghanistan" "Gibraltar"                          0     0
1970 "Afghanistan" "Turkmenistan"                       0     0
1970 "Afghanistan" "Malaysia"                           7     7
1970 "Afghanistan" "Cyprus"                            10     7
1970 "Afghanistan" "Vietnam"                            0     0
1970 "Afghanistan" "Saudi Arabia"                     887   730
1970 "Afghanistan" "Madagascar"                         0     0
1970 "Afghanistan" "Cook Islands"                       0     0
1970 "Afghanistan" "Sri Lanka"                         25    11
1970 "Afghanistan" "Greece"                            26    17
1970 "Afghanistan" "Czech Republic"                     2     0
1970 "Afghanistan" "Ghana"                              6     6
1970 "Afghanistan" "Lithuania"                          0     0
1970 "Afghanistan" "Romania"                            0     0
1970 "Afghanistan" "United Kingdom"                    50     .
1970 "Afghanistan" "Libya"                            225   189
1970 "Afghanistan" "Jamaica"                            0     0
1970 "Afghanistan" "Iran, Islamic Rep."             75075 32853
1970 "Afghanistan" "Mongolia"                           0     0
1970 "Afghanistan" "Malta"                              0     0
1970 "Afghanistan" "Kenya"                             66    60
1970 "Afghanistan" "Ethiopia"                           0     0
1970 "Afghanistan" "Martinique"                         0     0
1970 "Afghanistan" "Nicaragua"                         18     8
1970 "Afghanistan" "Monaco"                             0     0
1970 "Afghanistan" "Mauritania"                         0     0
1970 "Afghanistan" "Mauritius"                          0     0
1970 "Afghanistan" "Myanmar"                           11     .
1970 "Afghanistan" "Faeroe Islands"                     9     2
1970 "Afghanistan" "Greenland"                          0     0
1970 "Afghanistan" "Zimbabwe"                           1     0
1970 "Afghanistan" "Croatia"                            0     0
1970 "Afghanistan" "Syrian Arab Republic"            6777  6076
1970 "Afghanistan" "Kiribati"                           0     0
1970 "Afghanistan" "Oman"                             116    36
1970 "Afghanistan" "Swaziland"                          0     0
1970 "Afghanistan" "Belgium"                           91    75
1970 "Afghanistan" "France"                           177     .
1970 "Afghanistan" "Ecuador"                            0     0
1970 "Afghanistan" "Burkina Faso"                       0     0
1970 "Afghanistan" "West Bank and Gaza"                46     7
1970 "Afghanistan" "Virgin Islands, British"            0     0
1970 "Afghanistan" "Virgin Islands (U.S.)"              0     0
1970 "Afghanistan" "Morocco"                           20     .
1970 "Afghanistan" "Uruguay"                            0     0
1970 "Afghanistan" "United States"                    207     .
1970 "Afghanistan" "United Arab Emirates"             759   734
1970 "Afghanistan" "Guatemala"                          0     0
1970 "Afghanistan" "Norway"                             0     .
1970 "Afghanistan" "Pakistan"                       27957     .
1970 "Afghanistan" "Tunisia"                            9     .
1970 "Afghanistan" "Tonga"                              0     0
1970 "Afghanistan" "Ireland"                            7     1
1970 "Afghanistan" "Palau"                              0     0
1970 "Afghanistan" "Tanzania"                          21     .
1970 "Afghanistan" "Denmark"                           12     .
1970 "Afghanistan" "Puerto Rico"                        0     0
1970 "Afghanistan" "Serbia and Montenegro"              3     0
1970 "Afghanistan" "Montserrat"                         0     0
1970 "Afghanistan" "St. Kitts and Nevis"                0     0
1970 "Afghanistan" "Slovak Republic"                    0     0
1970 "Afghanistan" "French Polynesia"                   0     0
1970 "Afghanistan" "Venezuela, RB"                      0     .
1970 "Afghanistan" "Congo, Dem. Rep."                   9     .
1970 "Afghanistan" "Turkey"                          2573  1953
1970 "Afghanistan" "Georgia"                            0     0
1970 "Afghanistan" "Brunei Darussalam"                  0     0
1970 "Afghanistan" "Uganda"                             1     0
1970 "Afghanistan" "Tuvalu"                             0     0
1970 "Afghanistan" "Cape Verde"                         0     0
1970 "Afghanistan" "St. Vincent and the Grenadines"     0     0
1970 "Afghanistan" "Guyana"                             0     0
end

First thank you for your answer I've tried here the dataex command, sorry if not works properly, it's my first try.
So as I explained, I have years, countryO countryD migrantstock and migrants flows. I would like to obtain my variable network exactly as i explained before, namely by using migrantstock 10 years before. In that way, migrantstock for a country i to j at time t will correspond to network of country i to j at time t+10.

[Clyde Schechter]

I'm still not sure I understand what result you desire. But take a look at this and see if it does what you want.

egen dyad = group(countryO countryD)
xtset dyad year, delta(10)

foreach v of varlist migrant* {
    gen moved_`v' = L.`v'
}

[Killian Foubert]

It works perfectly Thanks a lot!

[Nick Cox]

See also http://www.stata-journal.com/sjpdf.h...iclenum=dm0043