Keeping observations from the using data in a 1:1 merging

[Ikechukwu Mpama]

I am trying to merge the results of students in different subjects over two years using 1:1, however the merge result only contains observations from the using data. I tried using keepusing(varlist) to no avail. Here is my code

use "result18.dta" merge 1:1 Student using "result17.dta", keepusing (English TechDraw Year)
save mergedresult.dta, replace

[Clyde Schechter]

Your question cannot be answered without seeing representative example data from both data sets. Use -dataex- to show those. If you are running version 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.

In selecting your example data to show, please be sure to include observations from each data set that you expect will match with observations in the other.

[Ikechukwu Mpama]

Thanks Clyde. This is the master data "result17.dta"

input int Year str16 Student byte(Maths English Science Art TechDraw) int Total str1 TotalGrade
2017 "Adejoh Blessing"  73  86 97 11  59 326 "B"
2017 "Mustapha Idris"   25  88 15 87  84 299 "C"
2017 "Patience Mudiaga" 45  47 98 88  97 375 "B"
2017 "Arowole Ajogun"   14  87 47 36  14 198 "E"
2017 "Mojisola Akanbi"  60  28 46 20  46 200 "E"
2017 "Okon Idongesit"   15  38 95 38 100 286 "C"
2017 "Grace Ukpabi"     91  69 88 41  62 351 "B"
2017 "Guzo Nnamdi"      79  54 41 47  98 319 "C"
2017 "Kezie Ogbonna"    40  15 51 59  72 237 "D"
2017 "Kelvin Efe"       76  26 20 12  60 194 "E"
2017 "Onos Edosio"      19  47 94 33  83 276 "C"
2017 "Kome Idibie"      60  85 11 69  36 261 "D"
2017 "Best Godwin"      23  30 40 46  87 226 "D"
2017 "Tess Braithwaith" 54 100 99 96  82 431 "A"

And the using data "result18"

input int Year str16 Student byte(Maths English Science Art TechDraw) int Total str1 TotalGrade
2018 "Adejoh Blessing"  99  19 50 64 17 249 "D"
2018 "Mustapha Idris"   61  76 61 16 10 224 "E"
2018 "Patience Mudiaga" 14  75 64 74 38 265 "D"
2018 "Arowole Ajogun"   87  73 65 90 74 389 "A"
2018 "Mojisola Akanbi"  70  56 83 49 13 271 "D"
2018 "Okon Idongesit"   84  33 65 27 72 281 "C"
2018 "Grace Ukpabi"     40  32 63 89 32 256 "D"
2018 "Guzo Nnamdi"      90  27 64 29 26 236 "D"
2018 "Kezie Ogbonna"    19  54 37 36 68 214 "E"
2018 "Kelvin Efe"       30  46 86 49 62 273 "D"
2018 "Onos Edosio"      31  90 60 62 68 311 "C"
2018 "Kome Idibie"      56 100 94 43 35 328 "B"
2018 "Best Godwin"      31  79 38 56 77 281 "C"
2018 "Tess Braithwaith" 53  32 83 21 59 248 "D"

[Rich Goldstein]

I am confused - first you say you are losing observations then you use the "keepusing" option which affects variables but not observations; secondly, these seem to be the same people but different years - are you sure you don't want to -append-, giving a file in "long" format, rather than -merge- (which gives a file in "wide" format)??? third, using your command withOUT the keepusing option, keeps all 14 observations and all variables and merges the files apparently correctly

[Ikechukwu Mpama]

Okay let me rephrase. I need to merge both files such that the result will contain observations from both files. That is grades for the student for 2017 and 2018 for each of the subject side by side. How do I do that?

[FernandoRios]

By default, you cant do that.
If a variable already exist on the "master data", the version from the "using data" will not be used.
An easy way to go around that problem is to rename the variables, either in the using or the master data. Perhaps adding the "year" to the name?
HTH

[Igor Paploski]

Echoing Rich Goldstein on #4, you can append both datasets instead of merging then. You will essentially get a dataset that is in long format (repeated observations for each student). Then you can reshape the dataset. Using Stata terminology, you have i (student) and j (year). After you append both data (for help in that, type help append), you will obtain this data:

* Example generated by -dataex-. To install: ssc install dataex
clear
input int year str16 student byte(maths english science art techdraw) int total str1 totalgrade
2017 "Adejoh Blessing"  73  86 97 11  59 326 "B"
2017 "Mustapha Idris"   25  88 15 87  84 299 "C"
2017 "Patience Mudiaga" 45  47 98 88  97 375 "B"
2017 "Arowole Ajogun"   14  87 47 36  14 198 "E"
2017 "Mojisola Akanbi"  60  28 46 20  46 200 "E"
2017 "Okon Idongesit"   15  38 95 38 100 286 "C"
2017 "Grace Ukpabi"     91  69 88 41  62 351 "B"
2017 "Guzo Nnamdi"      79  54 41 47  98 319 "C"
2017 "Kezie Ogbonna"    40  15 51 59  72 237 "D"
2017 "Kelvin Efe"       76  26 20 12  60 194 "E"
2017 "Onos Edosio"      19  47 94 33  83 276 "C"
2017 "Kome Idibie"      60  85 11 69  36 261 "D"
2017 "Best Godwin"      23  30 40 46  87 226 "D"
2017 "Tess Braithwaith" 54 100 99 96  82 431 "A"
2018 "Adejoh Blessing"  99  19 50 64  17 249 "D"
2018 "Mustapha Idris"   61  76 61 16  10 224 "E"
2018 "Patience Mudiaga" 14  75 64 74  38 265 "D"
2018 "Arowole Ajogun"   87  73 65 90  74 389 "A"
2018 "Mojisola Akanbi"  70  56 83 49  13 271 "D"
2018 "Okon Idongesit"   84  33 65 27  72 281 "C"
2018 "Grace Ukpabi"     40  32 63 89  32 256 "D"
2018 "Guzo Nnamdi"      90  27 64 29  26 236 "D"
2018 "Kezie Ogbonna"    19  54 37 36  68 214 "E"
2018 "Kelvin Efe"       30  46 86 49  62 273 "D"
2018 "Onos Edosio"      31  90 60 62  68 311 "C"
2018 "Kome Idibie"      56 100 94 43  35 328 "B"
2018 "Best Godwin"      31  79 38 56  77 281 "C"
2018 "Tess Braithwaith" 53  32 83 21  59 248 "D"
end

And then the reshape...

reshape wide maths english science art techdraw total totalgrade, i(student) j(year)

[Ikechukwu Mpama]

Thanks FernandoRios and Igor Pavposki. You've been of help. Thanks.