Hello Statalisters!

I have a firm-day panel dataset with firms, dates and stock returns. It looks like that:


Code:
* Example generated by -dataex-. To install: ssc install dataex
clear
input str32 company float date str10 var4 float return
"IBM"  17898 "2009-01-01"   .0951049
"IBM"  17899 "2009-01-02" -.46948355
"IBM"  17900 "2009-01-03"  .18530998
"AAPL" 17898 "2009-01-01"  3.0555556
"AAPL" 17899 "2009-01-02"  4.2048516
"AAPL" 17900 "2009-01-03"   5.614035
end
format %td date
Now, I have another database that shows the revenue that each firm earns for 250 countries and a third database that shows the daily COVID-19 cases for that 250 countries. I want to merge everything to one database for analyzation. That means, in case of IBM, I would have to get 250 rows per day with all the same date and return but differing revenue for each country and differing cases for each country and date. The revenue value for each firm in each country is fix over time because it´s measured yearly (see below).

I tried various merge functions but nothing worked. In the revenue database I have the firm name and the year as identifyer, in the cases database the country and the date.

I thought of having this as a result:


Code:
* Example generated by -dataex-. To install: ssc install dataex
clear
input str32 company float date str10 var6 str3 country float(return revenue_percentage var7)
"IBM" 17898 "2009-01-01" "A"     .0951049  5  2400
"IBM" 17898 "2009-01-01" "GER"   .0951049 17 10000
"IBM" 17898 "2009-01-01" "US"    .0951049 40 55000
"IBM" 17899 "2009-01-02" "A"   -.46948355  5  2800
"IBM" 17899 "2009-01-02" "GER" -.46948355 17 12000
"IBM" 17899 "2009-01-02" "US"  -.46948355 40 57000
"IBM" 17900 "2009-01-03" "A"    .18530998  5  3000
"IBM" 17900 "2009-01-03" "GER"  .18530998 17 14000
"IBM" 17900 "2009-01-03" "US"   .18530998 40 60000
end
format %td date
The goal is to weight the cases with the percentage of revenue and regress is on the returns - so on firm-country-day level.

Does anyone have a clue how this could be done? Or do I have a problem in my way of reasoning this?


Thank you so much for your help in advance!!!

Best, Christian