Merging daily data with monthly data

[Jahnvi Patel]

Hello,
I am currently dealing with mutual fund data. In this I have daily return dataset which I need to merge with dataset that has ESG scores of the portfolio but the data for them is monthly. When I am trying to merge them the data is getting reduced. How do I merge the two dataset in a way that for each date's observation for each firm takes the lagged value of ESG score of that month. I would appreciate all the help as I am very new to stata.

[Clyde Schechter]

You do not show example data. So I will imagine that your mutal fund data set has a fund_id variable and a date variable that is a Stata internal format daily date variable called ddate. I will also imagine that your ESG data set has a fund_id variable that is matchable with that of the mutual fund data set, and that it has a Stata internal format monthly date variable called mdate. I'll assume that the esg score variable in it is called esg_score. I assume both data sets are in long layout.

use mutual_fund_data_set, clear
gen mdate = mofd(ddate)
format mdate %tm
preserve

use esg_data_set, clear
xtset fund_id mdate
gen lagged_esg_score = L1.esg_score
tempfile holding
save `holding'

restore
merge m:1 fund_id mdate using `holding', keep(master match)

Now this is obviously based on several assumptions about your data. Each of them is plausible, but there is a good chance that at least one of them is not true. Also because there was no example data provided, this code could not be tested. So it may contain typos or other errors. But it has the gist of a solution. If you are unable to adapt it to your real data set, when posting back, be sure to show example data from both data sets (and make sure the examples you show contain some observations that should match).

When showing the example data, to make sure it is both usable and contains all the necessary metadata to write code that is suitable for it, use the -dataex- command. If you are running version 18, 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.