Merge two datasets by date

[Leir Th]

Hi all,

I want to merge a dataset of Inflation imported to Stata using freduse (fantastic function!) with a dataset that contains stock prices.

The idea is to merge by date, as there is no other common variable.
The challenge is that date in the inflation dataset is reported on the first day of the month: 01jan2017, 01feb2017, whereas the date in the stock price dataset is reported on the first trading day of the month: 02jan2017.



I thought about removing the first to numbers from the date variable so that I get: jan2017, feb2017,...
However, using the function substr() does not work:

gen time = substr(date, 3,9)


Maybe there is another way to merge these datasets that are easier?

All help much appreciated!

Thomas

[Hemanshu Kumar]

Are the date variables numeric or string? If they are numeric (daily dates used by Stata), then you can convert them to months using the mofd() function, and then merge. See

help mofd()

[Clyde Schechter]

One thing you should never do on Statalist is say that something "does not work." Always say exactly what went wrong. There are many, many ways in which something can fail to produce the results you were hoping for, and the solution to the problem will depend exactly on that.

Also, when asking for help with code you should almost always provide example data using the -dataex- command so that those who want to help you can test out solutions on a faithful replica of your data set that includes all of the details of metadata that may be crucial to getting it right. If you are running version 17, 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.

All of that said, the following general comments may be helpful in resolving your problem:

1. If your date variable is a string, you will get nowhere slowly until you creat a Stata internal format numeric variable. to use instead The -daily()- function will do that for you. If you are not familiar with -daily()-, see -help daily()-.
2. After creating a Stata daily date variable, or if you had one to start with, now create a Stata internal format numerical monthly date variable by applying the -mofd()- function to that. Again, if this is unfamiliar, -help mofd()- will show you the details of its syntax.
3. Once you have Stata internal format numerical monthly date variable, you should be able to successfully -merge- the two data sets with that as the key.

Added: Crossed with #2.