How to merge these two?

[Loekie Casteleijn]

Hi guys,

I am trying to merge country-level data with exchange rate data. Because in the end I need to merge my country-level dataset with a firm-level dataset and I need to use the exchange rate to convert financial data into USD.

I want to merge the two on countryid/countryid2 but all the time I get errors/values not lining up in the final dataset.

I also tried to change the rows and the columns in the country-level data set, but because many of the variables are string variables they change into a dot when trying to transpose it.

Anyone who has a clue/tip?

This is the layout of my exchange rate data

and this is the layout of my country-level data

[Nick Cox]

Please see 12.5 in particular and the entire entry at https://www.statalist.org/forums/help#stata

Long-term active members who answer lots of questions here tend to draw the line at trying to read dense screenshots, let alone work out code by copying variable names. Sorry if that seems unhelpful, but help us to help you by reading and acting on the advice that grows out of nearly 28 years of Statalist and hundreds of thousands of posts.

[Loekie Casteleijn]

Hey Nick,

Thanks for your reply. Didn't know it worked that way! I'll try and rephrase my question for the next time!

[William Lisowski]

Briefly, you need to

0) drop the countryid and countryid2 variables from both datasets, because they are nonsense - the coding in the two datasets do not agree (2=AUS and 2=AFG, for example.

help encode

1) reshape your country level data to a long layout, with separate observations for each combination of countrycode and year, as you have in your exchange data.

help reshape

2) merge your datasets matching on countrycode and year

help merge

3) continue to retain and use countrycode for merging until all your merging is complete, then and only then use encode to create a numeric coding for analysis

help encode

4) retain your data in the long layout.

I'm sympathetic to you as a new user of Stata - there is quite a lot to absorb. Nevertheless, I'd like to encourage you to take a step back from your immediate tasks.

When I began using Stata in a serious way, I started, as have others here, by reading my way through the Getting Started with Stata manual relevant to my setup. Chapter 18 then gives suggested further reading, much of which is in the Stata User's Guide, and I worked my way through much of that reading as well. All of these manuals are included as PDFs in the Stata installation and are accessible from within Stata - for example, through the PDF Documentation section of Stata's Help menu.

The objective in doing the reading was not so much to master Stata - I'm still far from that goal - as to be sure I'd become familiar with a wide variety of important basic techniques, so that when the time came that I needed them, I might recall their existence, if not the full syntax, and know how to find out more about them in the help files and PDF manuals.

Stata supplies exceptionally good documentation that amply repays the time spent studying it - there's just a lot of it. The path I followed surfaces the things you need to know to get started in a hurry and to work effectively.

Stata also supples YouTube videos, if that's your thing.