Adding an Extra Year in Dataset

[Cansu Oymak]

Dear Statalist Members,

I have a dataset at year-country level. Variable year stands for election years and it spans from 1961 to 2021 in the original dataset. However, since some years do not have election, these years do not appear in my dataset. These years are 1990, 1998, and 2010. I will merge this dataset (this one is my master dataset) with another dataset where data on 1990, 1992, and 2010 are available. I wish to generate years 1990, 1998 and 2010 for all countries where for example GADM_1 (i.e., region variable) is empty. By the way, I used the following command the expand the years: fillin yr ctr_n to fill the missing years. However, since 1990, 1992 and 2010 do not appear in the original dataset, even if I used "fillin" code, I still dont get 1990, 1992, and 2010.

Can you please help me?

Thank you very much.

* Example generated by -dataex-. To install: ssc install dataex
clear
input str30 ctr_n double yr str26 GADM_1
"Angola" 2008 "Bengo"  
"Angola" 2008 "Bengo"  
"Angola" 2012 "Bengo"  
"Angola" 2012 "Bengo"  
"Angola" 2012 "Bengo"  
"Angola" 2008 "Bengo"  
"Angola" 2008 "Bengo"  
"Angola" 2012 "Bengo"  
"Angola" 2008 "Bengo"  
"Angola" 2008 "Bengo"  
"Angola" 2008 "Bengo"  
"Angola" 2008 "Bengo"  
"Angola" 2012 "Bengo"  
"Angola" 2008 "Bengo"  
"Angola" 2008 "Bengo"  
"Angola" 2012 "Bengo"  
"Angola" 2008 "Bengo"  
"Angola" 2012 "Bengo"  
"Angola" 2012 "Bengo"  
"Angola" 2012 "Bengo"  
"Angola" 2008 "Bengo"  
"Angola" 2008 "Bengo"  
"Angola" 2008 "Bengo"  
"Angola" 2012 "Benguela"
"Angola" 2008 "Benguela"
"Angola" 2008 "Benguela"
"Angola" 2008 "Benguela"
"Angola" 2008 "Benguela"
"Angola" 2008 "Benguela"
"Angola" 2012 "Benguela"
"Angola" 2008 "Benguela"
"Angola" 2008 "Benguela"
"Angola" 2012 "Benguela"
"Angola" 2012 "Benguela"
"Angola" 2012 "Benguela"
"Angola" 2008 "Benguela"
"Angola" 2012 "Benguela"
"Angola" 2012 "Benguela"
"Angola" 2008 "Benguela"
"Angola" 2008 "Benguela"
"Angola" 2008 "Benguela"
"Angola" 2008 "Benguela"
"Angola" 2012 "Benguela"
"Angola" 2008 "Benguela"
"Angola" 2012 "Benguela"
"Angola" 2008 "Benguela"
"Angola" 2008 "Bie"    
"Angola" 2008 "Bie"    
"Angola" 2008 "Bie"    
"Angola" 2012 "Bie"    
"Angola" 2012 "Bie"    
"Angola" 2008 "Bie"    
"Angola" 2012 "Bie"    
"Angola" 2012 "Bie"    
"Angola" 2012 "Bie"    
"Angola" 2008 "Bie"    
"Angola" 2012 "Bie"    
"Angola" 2008 "Bie"    
"Angola" 2008 "Bie"    
"Angola" 2008 "Bie"    
"Angola" 2012 "Bie"    
"Angola" 2008 "Bie"    
"Angola" 2008 "Bie"    
"Angola" 2008 "Bie"    
"Angola" 2012 "Bie"    
"Angola" 2008 "Bie"    
"Angola" 2012 "Bie"    
"Angola" 2008 "Bie"    
"Angola" 2008 "Bie"    
"Angola" 1992 "Bengo"  
"Angola" 1992 "Bengo"  
"Angola" 1992 "Bengo"  
"Angola" 1992 "Bengo"  
"Angola" 1992 "Bengo"  
"Angola" 1992 "Bengo"  
"Angola" 1992 "Bengo"  
"Angola" 2017 "Bengo"  
"Angola" 2017 "Bengo"  
"Angola" 1992 "Bengo"  
"Angola" 1992 "Bengo"  
"Angola" 1992 "Bengo"  
"Angola" 1992 "Bengo"  
"Angola" 1992 "Bengo"  
"Angola" 2017 "Bengo"  
"Angola" 1992 "Bengo"  
"Angola" 1992 "Bengo"  
"Angola" 1992 "Bengo"  
"Angola" 1992 "Bengo"  
"Angola" 1992 "Bengo"  
"Angola" 1992 "Bengo"  
"Angola" 2017 "Bengo"  
"Angola" 2017 "Bengo"  
"Angola" 2017 "Bengo"  
"Angola" 2017 "Bengo"  
"Angola" 1992 "Benguela"
"Angola" 2017 "Benguela"
"Angola" 1992 "Benguela"
"Angola" 1992 "Benguela"
"Angola" 1992 "Benguela"
"Angola" 1992 "Benguela"
end

[Nick Cox]

The merge should take care of the gaps. How to populate them with non-missing values, or at all, is the issue.

[Cansu Oymak]

The merge should take care of the gaps. How to populate them with non-missing values, or at all, is the issue.

Dear Nick,

Thank you very much for your reply. However, probably because of my misunderstanding but, "merge" did not solve my problem. And, it does not propertly work.
Below, I provided examples from my master dataset (first one) and the other dataset. I used the following code to merge "merge m:1 ctr_n yr GADM_1 using "C:\Users\cansu\Dropbox\PC\Desktop\refugee 2.dta". However, for example for Angola with its Bengo region, there are 545 refugees in year 2000. When I merge, I expected to see 545 refugees in year 2000 in Bengo region in my master dataset. However, I cannot see it. Also, the missing years (i.e.,1990, 1992 and 2010) are still missing. Can you please help me when spotting my mistake?

* Example generated by -dataex-. To install: ssc install dataex
clear
input double yr str30 ctr_n str26 GADM_1
2000 "Angola"       ""             
2000 "Benin"        ""             
2000 "Botswana"     ""             
2000 "Burkina Faso" ""             
2000 "Cameroon"     ""             
2000 "Djibouti"     ""             
2000 "Gambia"       ""             
2000 "Ghana"        "Greater Accra"
2000 "Ghana"        "Greater Accra"
2000 "Ghana"        "Northern"     
2000 "Ghana"        "Brong Ahafo"  
2000 "Ghana"        "Northern"     
2000 "Ghana"        "Greater Accra"
2000 "Ghana"        "Brong Ahafo"  
2000 "Ghana"        "Eastern"      
2000 "Ghana"        "Central"      
2000 "Ghana"        "Upper West"   
2000 "Ghana"        "Western"      
2000 "Ghana"        "Brong Ahafo"  
2000 "Ghana"        "Ashanti"      
2000 "Ghana"        "Ashanti"      
2000 "Ghana"        "Northern"     
2000 "Ghana"        "Ashanti"      
2000 "Ghana"        "Upper West"   
2000 "Ghana"        "Northern"     
2000 "Ghana"        "Northern"     
2000 "Ghana"        "Brong Ahafo"  
2000 "Ghana"        "Eastern"      
2000 "Ghana"        "Upper East"   
2000 "Ghana"        "Northern"     
2000 "Ghana"        "Eastern"      
2000 "Ghana"        "Volta"        
2000 "Ghana"        "Eastern"      
2000 "Ghana"        "Eastern"      
2000 "Ghana"        "Brong Ahafo"  
2000 "Ghana"        "Central"      
2000 "Ghana"        "Northern"     
2000 "Ghana"        "Greater Accra"
2000 "Ghana"        "Upper East"   
2000 "Ghana"        "Volta"        
2000 "Ghana"        "Upper East"   
2000 "Ghana"        "Ashanti"      
2000 "Ghana"        "Ashanti"      
2000 "Ghana"        "Central"      
2000 "Ghana"        "Volta"        
2000 "Ghana"        "Volta"        
2000 "Ghana"        "Greater Accra"
2000 "Ghana"        "Brong Ahafo"  
2000 "Ghana"        "Northern"     
2000 "Ghana"        "Upper East"   
2000 "Ghana"        "Ashanti"      
2000 "Ghana"        "Ashanti"      
2000 "Ghana"        "Volta"        
2000 "Ghana"        "Eastern"      
2000 "Ghana"        "Western"      
2000 "Ghana"        "Brong Ahafo"  
2000 "Ghana"        "Greater Accra"
2000 "Ghana"        "Greater Accra"
2000 "Ghana"        "Upper East"   
2000 "Ghana"        "Volta"        
2000 "Ghana"        "Greater Accra"
2000 "Ghana"        "Volta"        
2000 "Ghana"        "Ashanti"      
2000 "Ghana"        "Eastern"      
2000 "Ghana"        "Ashanti"      
2000 "Ghana"        "Volta"        
2000 "Ghana"        "Greater Accra"
2000 "Ghana"        "Ashanti"      
2000 "Ghana"        "Northern"     
2000 "Ghana"        "Ashanti"      
2000 "Ghana"        "Brong Ahafo"  
2000 "Ghana"        "Volta"        
2000 "Ghana"        "Ashanti"      
2000 "Ghana"        "Central"      
2000 "Ghana"        "Brong Ahafo"  
2000 "Ghana"        "Northern"     
2000 "Ghana"        "Brong Ahafo"  
2000 "Ghana"        "Upper East"   
2000 "Ghana"        "Central"      
2000 "Ghana"        "Ashanti"      
2000 "Ghana"        "Central"      
2000 "Ghana"        "Northern"     
2000 "Ghana"        "Northern"     
2000 "Ghana"        "Northern"     
2000 "Ghana"        "Eastern"      
2000 "Ghana"        "Ashanti"      
2000 "Ghana"        "Volta"        
2000 "Ghana"        "Eastern"      
2000 "Ghana"        "Northern"     
2000 "Ghana"        "Central"      
2000 "Ghana"        "Western"      
2000 "Ghana"        "Ashanti"      
2000 "Ghana"        "Greater Accra"
2000 "Ghana"        "Eastern"      
2000 "Ghana"        "Northern"     
2000 "Ghana"        "Eastern"      
2000 "Ghana"        "Brong Ahafo"  
2000 "Ghana"        "Western"      
2000 "Ghana"        "Western"      
2000 "Ghana"        "Volta"        
end

This is an exmaple from my second dataset:

* Example generated by -dataex-. To install: ssc install dataex
clear
input int yr str32 ctr_n str19 GADM_1 double grandtotal
2000 "Angola"        "Bengo"            545
2000 "Angola"        "Cunene"            27
2000 "Angola"        "Luanda"          1396
2000 "Angola"        "Malanje"         6082
2000 "Angola"        "Moxico"            79
2000 "Botswana"      "Central"         3486
2000 "Djibouti"      "Ali Sabieh"     21124
2000 "Gambia"        "Upper River"     1060
2000 "Ghana"         "Central"         9300
2000 "Ghana"         "Western"         1603
2000 "Guinea-Bissau" "Cacheu"           554
2000 "Ivory Coast"   "Bas-Sassandra" 117749
2000 "Ivory Coast"   "Montagnes"       1779
2000 "Kenya"         "Garissa"       127993
2000 "Kenya"         "Turkana"        69749
2000 "Malawi"        "Dowa"            3900
2000 "Tanzania"      "Dodoma"        132523
2000 "Tanzania"      "Kigoma"        251119
2000 "Uganda"        "Adjumani"       73486
2000 "Uganda"        "Arua"           49007
2000 "Uganda"        "Hoima"           6530
2000 "Uganda"        "Kitgum"         25686
2000 "Uganda"        "Kyenjojo"        2473
2000 "Uganda"        "Masindi"        11772
2000 "Uganda"        "Mbarara"        15788
2000 "Uganda"        "Moyo"           31187
2000 "Zambia"        "Luapula"        11290
2000 "Zambia"        "North-Western"  71750
2000 "Zambia"        "Western"        31123
2000 "Zimbabwe"      "Manicaland"       908
2001 "Angola"        "Cunene"             5
2001 "Angola"        "Luanda"          1086
2001 "Angola"        "Malanje"         7311
2001 "Benin"         "Atlantique"      1169
2001 "Botswana"      "Central"         3520
2001 "Djibouti"      "Ali Sabieh"     21048
2001 "Gambia"        "Upper River"     1060
2001 "Ghana"         "Central"         8719
2001 "Ghana"         "Western"         1870
2001 "Guinea-Bissau" "Cacheu"          7332
2001 "Kenya"         "Garissa"       133998
2001 "Kenya"         "Turkana"        84552
2001 "Malawi"        "Dowa"            6200
2001 "Tanzania"      "Dodoma"        122122
2001 "Tanzania"      "Kagera"           179
2001 "Tanzania"      "Kigoma"        244652
2001 "Togo"          "Centre"          6288
2001 "Togo"          "Kara"            4920
2001 "Uganda"        "Adjumani"       57644
2001 "Uganda"        "Arua"           37233
2001 "Uganda"        "Hoima"           6544
2001 "Uganda"        "Kitgum"         23410
2001 "Uganda"        "Kyenjojo"        2729
2001 "Uganda"        "Masindi"        13340
2001 "Uganda"        "Mbarara"        14632
2001 "Uganda"        "Moyo"           23930
2001 "Zambia"        "Luapula"        24411
2001 "Zambia"        "North-Western"  70218
2001 "Zambia"        "Western"        47218
2001 "Zimbabwe"      "Manicaland"      1391
2002 "Angola"        "Luanda"          1086
2002 "Angola"        "Malanje"         7311
2002 "Benin"         "Atlantique"      1159
2002 "Botswana"      "Central"         2744
2002 "Djibouti"      "Ali Sabieh"     21316
2002 "Gambia"        "Upper River"     1060
2002 "Ghana"         "Central"        27001
2002 "Ghana"         "Western"         1674
2002 "Guinea-Bissau" "Cacheu"          7322
2002 "Kenya"         "Garissa"       136455
2002 "Kenya"         "Turkana"        82216
2002 "Sierra Leone"  "Eastern"         3725
2002 "Sierra Leone"  "Southern"       39375
2002 "Tanzania"      "Dodoma"        110887
2002 "Tanzania"      "Kagera"           113
2002 "Tanzania"      "Kigoma"        278512
2002 "Togo"          "Centre"          6288
2002 "Togo"          "Kara"            4920
2002 "Uganda"        "Adjumani"       60658
2002 "Uganda"        "Arua"           39956
2002 "Uganda"        "Hoima"          16224
2002 "Uganda"        "Kitgum"         15382
2002 "Uganda"        "Kyenjojo"        3159
2002 "Uganda"        "Masindi"        13841
2002 "Uganda"        "Mbarara"        19372
2002 "Uganda"        "Moyo"           28240
2002 "Zambia"        "Luapula"        18285
2002 "Zambia"        "North-Western"  70755
2002 "Zambia"        "Western"        47000
2002 "Zimbabwe"      "Manicaland"      1558
2003 "Angola"        "Malanje"         7066
2003 "Benin"         "Atlantique"      1135
2003 "Djibouti"      "Ali Sabieh"     22490
2003 "Gambia"        "Upper River"     1051
2003 "Ghana"         "Central"        41769
2003 "Ghana"         "Western"         1915
2003 "Guinea-Bissau" "Cacheu"          7317
2003 "Ivory Coast"   "Bas-Sassandra"   3500
2003 "Kenya"         "Garissa"       134628
2003 "Kenya"         "Turkana"        88994
end

[Nick Cox]

This works for me.

* Example generated by -dataex-. To install: ssc install dataex
clear
input double yr str30 ctr_n str26 GADM_1
2000 "Angola"       ""             
end

save A 

clear
input int yr str32 ctr_n str19 GADM_1 double grandtotal
2000 "Angola"        "Bengo"            545
2000 "Angola"        "Cunene"            27
2000 "Angola"        "Luanda"          1396
2000 "Angola"        "Malanje"         6082
2000 "Angola"        "Moxico"            79
end 

merge 1:1 yr ctr_n GADM_1 using A

I notice that there are duplicates in your first dataset. I don't know if that is a problem, but it doesn't help anything so far as I can see. See

help duplicates

for how to remove them.