How to use joinby to create a new panel data

[yanqing zzz]

Dear community,

I want to merge panel data for my master thesis, but my knowledge of econometrics is limited and cannot solve this problem. My panel data set are attached. I want to maintain all the data of subsidy.dta and add some additional data from export.dta.

In subsidy.dta, the country's subsidy to the world is annually. In export.dta, the country's export to another country is annually too. So, i hope the final data set is order by:

year country country2 export subsidy subsidy1 subsidy2...
2018 1 1 999 0 0 0
2018 1 2 988 0 0 0
2018 1 0 0 99 99 99
2017 1 1 999 0 0 0
2017 1 2 988 0 0 0
2017 1 0 0 90 99 99

could you please give me some advice? Thank you in advance for your time and help!

[Clyde Schechter]

Please read the Forum FAQ for excellent advice about how to get the most out of your Statalist posts. The use of screenshots is not helpful. The useful way to show example data is with the -dataex- command. 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.

Also, in this case, it is important to state what version of Stata you are running if it is not the current one (16.1), as this particular problem would be approached differently.

Please post back with the requested information in order to get concrete advice.

[yanqing zzz]

Please read the Forum FAQ for excellent advice about how to get the most out of your Statalist posts. The use of screenshots is not helpful. The useful way to show example data is with the -dataex- command. 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.

Also, in this case, it is important to state what version of Stata you are running if it is not the current one (16.1), as this particular problem would be approached differently.

Please post back with the requested information in order to get concrete advice.

Dear Clyde Schechter,

I am sorry that i didn't follow the forums manual to upload my question. I tried to use dataex to upload my data and it seems like incomplete.


[CODE]
* Example generated by -dataex-. To install: ssc install dataex
clear
input int time str6 cou str49 country str3 par str49 country2 float(dist export) long(agdp subsidy)
2008 "FRA" "France" "ABW" "Aruba" 7685.884 98.90105 . .
2006 "FRA" "France" "ABW" "Aruba" 7685.884 66.52964 . .
2017 "FRA" "France" "ABW" "Aruba" 7685.884 53.847 . .
2011 "FRA" "France" "ABW" "Aruba" 7685.884 64 . .
2015 "FRA" "France" "ABW" "Aruba" 7685.884 47.446 . .
2010 "FRA" "France" "ABW" "Aruba" 7685.884 54.34799 . .
2018 "FRA" "France" "ABW" "Aruba" 7685.884 .499 . .
2016 "FRA" "France" "ABW" "Aruba" 7685.884 43.663 . .
2009 "FRA" "France" "ABW" "Aruba" 7685.884 91.69545 . .
2004 "FRA" "France" "ABW" "Aruba" 7685.884 50.99029 . .
2007 "FRA" "France" "ABW" "Aruba" 7685.884 76.79265 . .
2005 "FRA" "France" "ABW" "Aruba" 7685.884 63.46668 . .
2014 "FRA" "France" "ABW" "Aruba" 7685.884 29.496 . .
2012 "FRA" "France" "ABW" "Aruba" 7685.884 69.493 . .
2019 "FRA" "France" "ABW" "Aruba" 7685.884 .479 . .
2013 "FRA" "France" "ABW" "Aruba" 7685.884 60.034 .






I combined my 2 datasets as the document attached. When i use command tsset for further regression, the state system show "repeated time values within panel". I cannot use duplicate to delete the repeated variables, because the dataset contains different variables, subsidy variable is from exporter country to the world, bilateral export value is from exporter to importer. Both variables are important. Could you please give me some advice? Thank you in advance for your time and help!

[Clyde Schechter]

Thanks for trying. I don't download files from people I don't know, so I haven't made use of your export.dta attachment. Your start at -dataex- looks more or less OK. It seems the final line got cut off a bit, but otherwise it looks OK. It's more likely that you just didn't get the copy/paste right than anything else. Anyway, please try -dataex- again.

But when I compare it to the screenshots you showed in #1, I can't figure out which data set your -dataex- in #3 is from. It looks like a hybrid of the two data sets: it has country and country2 and export in it (so that looks like the file you called export.dta in #1. But it also contains the a subsidy variable, which looks like it comes from what you called subsidy.dta in #1. So I can't figure out what you've sent me here. Have you already figured out how to combine these data sets in the way you need to? If so, please just show how you did it and we can consider the thread closed. If not, please post back with -dataex- output from both data sets and I'll try to help you piece them together.

[yanqing zzz]

Thanks for trying. I don't download files from people I don't know, so I haven't made use of your export.dta attachment. Your start at -dataex- looks more or less OK. It seems the final line got cut off a bit, but otherwise it looks OK. It's more likely that you just didn't get the copy/paste right than anything else. Anyway, please try -dataex- again.

But when I compare it to the screenshots you showed in #1, I can't figure out which data set your -dataex- in #3 is from. It looks like a hybrid of the two data sets: it has country and country2 and export in it (so that looks like the file you called export.dta in #1. But it also contains the a subsidy variable, which looks like it comes from what you called subsidy.dta in #1. So I can't figure out what you've sent me here. Have you already figured out how to combine these data sets in the way you need to? If so, please just show how you did it and we can consider the thread closed. If not, please post back with -dataex- output from both data sets and I'll try to help you piece them together.

Hi Prof.Clyde Schechter,

Thank you for your reply! I already combine my dataset from the previous post in forums(it help a lot! thanks!). I am sorry I really need to learn how to use -dataex-.

Solution of combine 2 dataset (2 different panel data)
My export.dta contains variables: export year country1code country2code country1 country2 distance border

The subsidy.dta contains variables: subsidy subsidy-program year country1 country1code country2code country2

I open export.dta as master document.

merge m:1 country1code country2code year using "C:\Users\HP\Desktop\data\backup\subsidy.dta"

Hope it is right because i lost some observation with unknown reasons.