generate correlation from one variable across countries and groups

[Mina Sami]

Dear Stata Users,
I want to create a Code that creates a Correlate in the variable (mean_price) by pair of countries, industries, and quarters.
So I don't mind collapsing the data below to be by quarters, including pair of countries and one new variable correlate.
In other words, i need this data to be converted as in figure 2

figure 1

figure 2

[Ken Chui]

I don't have any clever solution other than brute forcing it by first using "cross" to produce all possible pairs. Then trim down to matching month and industry. Then with statsby it'd be straightforward. Notice that currently they are "duplicated" in a way that China-India is a pair, and India-China is a pair.

(Also, you've made 50 posts and should not be a new user. Please kindly follow the FAQ part 12 to provide sample data in future using command dataex. It's very time-consuming to create demonstration data and if you do not start doing that, it would be unlikely to get any quick help.)

* Set up sample data -----------------------------------------------------------
clear
input str30 country str10 date str10 industry
China 1992m1 Energy
China 1992m2 Energy
China 1992m3 Energy
China 1992m4 Energy
China 1992m5 Energy
China 1992m6 Energy
India 1992m1 Energy
India 1992m2 Energy
India 1992m3 Energy
India 1992m4 Energy
India 1992m5 Energy
India 1992m6 Energy
China 1992m1 Financials
China 1992m2 Financials
China 1992m3 Financials
China 1992m4 Financials
China 1992m5 Financials
China 1992m6 Financials
India 1992m1 Financials
India 1992m2 Financials
India 1992m3 Financials
India 1992m4 Financials
India 1992m5 Financials
India 1992m6 Financials
end
set seed 15746
gen mean_price = runiform() * 2

save file01, replace

rename * *_2
save file02, replace

* Cross ------------------------------------------------------------------------
use file01, clear
cross using file02
keep if industry == industry_2
drop industry_2
keep if date == date_2
drop date_2
drop if country == country_2

* Create quarter variable ------------------------------------------------------
gen qdate = qofd(dofm(monthly(date, "YM")))

bysort country country_2 qdate industry: corr mean_price mean_price_2

* Statsby ----------------------------------------------------------------------
statsby rho=r(rho), clear by(country country_2 industry qdate): corr mean_price mean_price_2
format qdate %tq

Results:

     +------------------------------------------------------+
     | country   countr~2     industry    qdate         rho |
     |------------------------------------------------------|
  1. |   China      India       Energy   1992q1   -.8074771 |
  2. |   China      India       Energy   1992q2    .5700118 |
     |------------------------------------------------------|
  3. |   China      India   Financials   1992q1      -.6192 |
  4. |   China      India   Financials   1992q2    .1423009 |
     |------------------------------------------------------|
  5. |   India      China       Energy   1992q1   -.8074771 |
  6. |   India      China       Energy   1992q2    .5700118 |
     |------------------------------------------------------|
  7. |   India      China   Financials   1992q1      -.6192 |
  8. |   India      China   Financials   1992q2    .1423009 |