export a set of correlation tables using command frame

Guest

export a set of correlation tables using command frame

14 Feb 2022, 10:21

Dear All,

I'm using Stata to investigate the bilateral trade at the sectoral level. I wanted to check whether correlation of variables is in line with the economic intuition (distance and trade-negative, GDP and trade-positive). I used the following code:

Code:

 frame create myresults
frame create holding
tempfile holding
local sectorname
xtset id year
local where 1
levelsof HSspecific, local(sectors)
foreach sector of local sectors{
    capture noisily{
        di "For sector `sector'"
        corr log_exp log_GDP log_EXCH log_distance cm_border eu if HSspecific==`sector'
        mat res= (r(table)[1..6, 1..6])'
        frame holding{
            clear
            set obs 6
            svmat res
            save `holding', replace
         }
        frame myresults{
            append using `holding'
            cap gen sector= "`sector'" in `where'    
            replace sector= "GDP CAP sector `sector'" in `where'
            local ++where
            replace sector= "EXCH sector `sector'" in `where'
            local ++where
            replace sector= "distance sector `sector'" in `where'
            local ++where
            replace sector= "cm_border sector `sector'" in `where'
            local ++where
            replace sector= "eu sector `sector'" in `where'
            local ++where
        }
    }
}
frame myresults{
    order sector
    rename  (res1- res6)  (log_exp log_GDP log_EXCH log_distance cm_border eu )
}
frame drop holding
frame myresults: browse
frame myresults: l, sep(0)

where HSspecific indicates sector number, log_exp is logarithm of export, log_GDP- logarithm of GDP, log_EXCH- log of exchange rate, log_distance- logarithm of distance, cm_border- dummy indicating whether countries share a common border, eu-dummy indicating whether both countries belong to the European Union.

Stata displayed the tables with correlation. However, I see error: invalid syntax. E.g:

Code:

invalid syntax
For sector 30243
(obs=45)

             |  log_exp  log_GDP log_EXCH log_di~e cm_bor~r       eu
-------------+------------------------------------------------------
     log_exp |   1.0000
     log_GDP |   0.2189   1.0000
    log_EXCH |  -0.6093   0.2566   1.0000
log_distance |  -0.6677  -0.4501   0.5004   1.0000
   cm_border |   0.3294   0.3943  -0.2145  -0.6568   1.0000
          eu |   0.2777  -0.3375  -0.1203  -0.0721  -0.1153   1.0000

The signs are as expected. However, Stata doesn't display matrix with results for each sector. I see an error:

Code:

frame myresults{
     order sector
no variables defined
r(111);
rename  (res1- res6)  (log_exp log_GDP log_EXCH log_distance cm_border eu ) }
r(111)

Here is a sample of my Stata output:
[CODE]

Code:

* Example generated by -dataex-. To install: ssc install dataex
clear
input float(log_exp log_GDP log_EXCH log_distance cm_border eu) long HSspecific int year float id
 21.41338  9.807273  .25675145 9.693803 0 0     0 1991  1
21.630123  9.859335  .13704698 9.693803 0 0     0 1992  1
   21.626  9.911574   .1171426 9.693803 0 0     0 1993  1
 21.79623   9.97103   .1709818 9.693803 0 0     0 1994  1
 22.02459 10.017065  .06047494 9.693803 0 0     0 1995  1
21.972624 10.054852   .1634534 9.693803 0 0     0 1996  1
 21.94359  10.10407   .2523011 9.693803 0 0     0 1997  1
 21.97477  10.15314  .10024168 9.693803 0 0     0 1998  1
21.960596 10.208075 -.50192624 9.693803 0 0     0 1999  1
 21.85747 10.250782  -.4656639 9.693803 0 0     0 2000  1
 21.88538 10.294225  -.5490737 9.693803 0 0     0 2001  1
22.030205 10.335284  -.5541078 9.693803 0 0     0 2002  1
 22.28353  10.38446 -.55626863 9.693803 0 0     0 2003  1
22.475513 10.432998 -.52555615 9.693803 0 0     0 2004  1
 22.55476 10.481292  -.4880296 9.693803 0 0     0 2005  1
22.654886 10.542543 -.51126665 9.693803 0 0     0 2006  1
 22.79919 10.588058  -.4933665 9.693803 0 0     0 2007  1
23.000784  10.59926 -.56155837 9.693803 0 0     0 2008  1
 22.89651 10.636732 -.58130705 9.693803 0 0     0 2009  1
23.066584 10.664525  -.3682772 9.693803 0 0     0 2010  1
23.173985  10.70166 -.29969665 9.693803 0 0     0 2011  1
23.214634  10.68929  -.2157968 9.693803 0 0     0 2012  1
23.160517 10.773984  -.3189786 9.693803 0 0     0 2013  1
 23.06461  10.77067  -.3878373 9.693803 0 0     0 2014  1
        . 10.335284  -.5541078 9.693803 0 0 10110 2002  2
        .  10.38446 -.55626863 9.693803 0 0 10110 2003  2
        . 10.432998 -.52555615 9.693803 0 0 10110 2004  2
        . 10.481292  -.4880296 9.693803 0 0 10110 2005  2
 8.294049 10.542543 -.51126665 9.693803 0 0 10110 2006  2
        . 10.588058  -.4933665 9.693803 0 0 10110 2007  2
10.645425 10.636732 -.58130705 9.693803 0 0 10110 2009  2
        . 10.664525  -.3682772 9.693803 0 0 10110 2010  2
        .  10.70166 -.29969665 9.693803 0 0 10110 2011  2
10.950807   9.97103   .1709818 9.693803 0 0 10111 1994  3
        . 10.054852   .1634534 9.693803 0 0 10111 1996  3
        .  10.10407   .2523011 9.693803 0 0 10111 1997  3
        . 10.208075 -.50192624 9.693803 0 0 10111 1999  3
        . 10.294225  -.5490737 9.693803 0 0 10111 2001  3
 10.83958  9.807273  .25675145 9.693803 0 0 10119 1991  4
        .  9.859335  .13704698 9.693803 0 0 10119 1992  4
        .  9.911574   .1171426 9.693803 0 0 10119 1993  4
        .   9.97103   .1709818 9.693803 0 0 10119 1994  4
        . 10.017065  .06047494 9.693803 0 0 10119 1995  4
        . 10.054852   .1634534 9.693803 0 0 10119 1996  4
        .  10.10407   .2523011 9.693803 0 0 10119 1997  4
        .  10.15314  .10024168 9.693803 0 0 10119 1998  4
        . 10.208075 -.50192624 9.693803 0 0 10119 1999  4
11.066638 10.250782  -.4656639 9.693803 0 0 10119 2000  4
11.198215 10.294225  -.5490737 9.693803 0 0 10119 2001  4
        .  10.68929  -.2157968 9.693803 0 0 10121 2012  5
        .  10.77067  -.3878373 9.693803 0 0 10121 2014  5
        . 10.822786  -.3981192 9.693803 0 0 10121 2016  5
        . 10.833665  -.3879531 9.693803 0 0 10121 2017  5
15.227152  10.68929  -.2157968 9.693803 0 0 10129 2012  6
15.211052 10.773984  -.3189786 9.693803 0 0 10129 2013  6
14.995152  10.77067  -.3878373 9.693803 0 0 10129 2014  6
14.288353  10.76284  -.3899194 9.693803 0 0 10129 2015  6
14.704036 10.822786  -.3981192 9.693803 0 0 10129 2016  6
14.532348 10.833665  -.3879531 9.693803 0 0 10129 2017  6
13.927883 10.886076   -.457807 9.693803 0 0 10129 2018  6
10.985292 10.335284  -.5541078 9.693803 0 0 10190 2002  7
 12.20106  10.38446 -.55626863 9.693803 0 0 10190 2003  7
 10.89674 10.432998 -.52555615 9.693803 0 0 10190 2004  7
 14.36767 10.542543 -.51126665 9.693803 0 0 10190 2006  7
13.299672 10.588058  -.4933665 9.693803 0 0 10190 2007  7
14.746887  10.59926 -.56155837 9.693803 0 0 10190 2008  7
15.009736 10.636732 -.58130705 9.693803 0 0 10190 2009  7
14.346915 10.664525  -.3682772 9.693803 0 0 10190 2010  7
14.692664  10.70166 -.29969665 9.693803 0 0 10190 2011  7
 6.907755 10.017065  .06047494 9.693803 0 0 10210 1995  8
 11.21182 10.588058  -.4933665 9.693803 0 0 10210 2007  8
 6.907755  10.15314  .10024168 9.693803 0 0 10310 1998  9
 8.798606 10.664525  -.3682772 9.693803 0 0 10511 2010 10
 8.699514  9.807273  .25675145 9.693803 0 0 10600 1991 11
 8.006368  9.859335  .13704698 9.693803 0 0 10600 1992 11
 8.006368  9.911574   .1171426 9.693803 0 0 10600 1993 11
        .   9.97103   .1709818 9.693803 0 0 10600 1994 11
        . 10.017065  .06047494 9.693803 0 0 10600 1995 11
        . 10.054852   .1634534 9.693803 0 0 10600 1996 11
 8.149024  10.10407   .2523011 9.693803 0 0 10600 1997 11
 8.784315 10.208075 -.50192624 9.693803 0 0 10600 1999 11
        . 10.250782  -.4656639 9.693803 0 0 10600 2000 11
9.1049795 10.294225  -.5490737 9.693803 0 0 10600 2001 11
        . 10.588058  -.4933665 9.693803 0 0 10611 2007 12
        .  10.76284  -.3899194 9.693803 0 0 10613 2015 13
        . 10.335284  -.5541078 9.693803 0 0 10619 2002 14
 6.907755  10.38446 -.55626863 9.693803 0 0 10619 2003 14
 6.907755 10.432998 -.52555615 9.693803 0 0 10619 2004 14
 6.907755 10.481292  -.4880296 9.693803 0 0 10619 2005 14
        . 10.542543 -.51126665 9.693803 0 0 10619 2006 14
        . 10.588058  -.4933665 9.693803 0 0 10619 2007 14
 6.907755  10.59926 -.56155837 9.693803 0 0 10619 2008 14
        . 10.636732 -.58130705 9.693803 0 0 10619 2009 14
        . 10.664525  -.3682772 9.693803 0 0 10619 2010 14
 5.627621  10.70166 -.29969665 9.693803 0 0 10619 2011 14
        .  10.68929  -.2157968 9.693803 0 0 10619 2012 14
        . 10.773984  -.3189786 9.693803 0 0 10619 2013 14
 7.487174  10.77067  -.3878373 9.693803 0 0 10619 2014 14
 10.28141  10.76284  -.3899194 9.693803 0 0 10619 2015 14
11.195554 10.822786  -.3981192 9.693803 0 0 10619 2016 14
end

I would be very grateful for your help.

Last edited by sladmin; 28 Aug 2023, 09:39. Reason: anonymize original poster

Tags: None

Andrew Musau

Join Date: Oct 2014
Posts: 9945

14 Feb 2022, 12:31

Your example dataset cannot generate all those correlations that you request. But you can get a \(3\times 3\) correlation matrix for most of the sectors. I would export these in CSV format using estout from SSC. If you want, you can subsequently import to Stata using import delimited.

Code:

* Example generated by -dataex-. For more info, type help dataex
clear
input float(log_exp log_GDP log_EXCH log_distance cm_border eu) long HSspecific int year float id
 21.41338  9.807273  .25675145 9.693803 0 0     0 1991  1
21.630123  9.859335  .13704698 9.693803 0 0     0 1992  1
   21.626  9.911574   .1171426 9.693803 0 0     0 1993  1
 21.79623   9.97103   .1709818 9.693803 0 0     0 1994  1
 22.02459 10.017065  .06047494 9.693803 0 0     0 1995  1
21.972624 10.054852   .1634534 9.693803 0 0     0 1996  1
 21.94359  10.10407   .2523011 9.693803 0 0     0 1997  1
 21.97477  10.15314  .10024168 9.693803 0 0     0 1998  1
21.960596 10.208075 -.50192624 9.693803 0 0     0 1999  1
 21.85747 10.250782  -.4656639 9.693803 0 0     0 2000  1
 21.88538 10.294225  -.5490737 9.693803 0 0     0 2001  1
22.030205 10.335284  -.5541078 9.693803 0 0     0 2002  1
 22.28353  10.38446 -.55626863 9.693803 0 0     0 2003  1
22.475513 10.432998 -.52555615 9.693803 0 0     0 2004  1
 22.55476 10.481292  -.4880296 9.693803 0 0     0 2005  1
22.654886 10.542543 -.51126665 9.693803 0 0     0 2006  1
 22.79919 10.588058  -.4933665 9.693803 0 0     0 2007  1
23.000784  10.59926 -.56155837 9.693803 0 0     0 2008  1
 22.89651 10.636732 -.58130705 9.693803 0 0     0 2009  1
23.066584 10.664525  -.3682772 9.693803 0 0     0 2010  1
23.173985  10.70166 -.29969665 9.693803 0 0     0 2011  1
23.214634  10.68929  -.2157968 9.693803 0 0     0 2012  1
23.160517 10.773984  -.3189786 9.693803 0 0     0 2013  1
 23.06461  10.77067  -.3878373 9.693803 0 0     0 2014  1
        . 10.335284  -.5541078 9.693803 0 0 10110 2002  2
        .  10.38446 -.55626863 9.693803 0 0 10110 2003  2
        . 10.432998 -.52555615 9.693803 0 0 10110 2004  2
        . 10.481292  -.4880296 9.693803 0 0 10110 2005  2
 8.294049 10.542543 -.51126665 9.693803 0 0 10110 2006  2
        . 10.588058  -.4933665 9.693803 0 0 10110 2007  2
10.645425 10.636732 -.58130705 9.693803 0 0 10110 2009  2
        . 10.664525  -.3682772 9.693803 0 0 10110 2010  2
        .  10.70166 -.29969665 9.693803 0 0 10110 2011  2
10.950807   9.97103   .1709818 9.693803 0 0 10111 1994  3
        . 10.054852   .1634534 9.693803 0 0 10111 1996  3
        .  10.10407   .2523011 9.693803 0 0 10111 1997  3
        . 10.208075 -.50192624 9.693803 0 0 10111 1999  3
        . 10.294225  -.5490737 9.693803 0 0 10111 2001  3
 10.83958  9.807273  .25675145 9.693803 0 0 10119 1991  4
        .  9.859335  .13704698 9.693803 0 0 10119 1992  4
        .  9.911574   .1171426 9.693803 0 0 10119 1993  4
        .   9.97103   .1709818 9.693803 0 0 10119 1994  4
        . 10.017065  .06047494 9.693803 0 0 10119 1995  4
        . 10.054852   .1634534 9.693803 0 0 10119 1996  4
        .  10.10407   .2523011 9.693803 0 0 10119 1997  4
        .  10.15314  .10024168 9.693803 0 0 10119 1998  4
        . 10.208075 -.50192624 9.693803 0 0 10119 1999  4
11.066638 10.250782  -.4656639 9.693803 0 0 10119 2000  4
11.198215 10.294225  -.5490737 9.693803 0 0 10119 2001  4
        .  10.68929  -.2157968 9.693803 0 0 10121 2012  5
        .  10.77067  -.3878373 9.693803 0 0 10121 2014  5
        . 10.822786  -.3981192 9.693803 0 0 10121 2016  5
        . 10.833665  -.3879531 9.693803 0 0 10121 2017  5
15.227152  10.68929  -.2157968 9.693803 0 0 10129 2012  6
15.211052 10.773984  -.3189786 9.693803 0 0 10129 2013  6
14.995152  10.77067  -.3878373 9.693803 0 0 10129 2014  6
14.288353  10.76284  -.3899194 9.693803 0 0 10129 2015  6
14.704036 10.822786  -.3981192 9.693803 0 0 10129 2016  6
14.532348 10.833665  -.3879531 9.693803 0 0 10129 2017  6
13.927883 10.886076   -.457807 9.693803 0 0 10129 2018  6
10.985292 10.335284  -.5541078 9.693803 0 0 10190 2002  7
 12.20106  10.38446 -.55626863 9.693803 0 0 10190 2003  7
 10.89674 10.432998 -.52555615 9.693803 0 0 10190 2004  7
 14.36767 10.542543 -.51126665 9.693803 0 0 10190 2006  7
13.299672 10.588058  -.4933665 9.693803 0 0 10190 2007  7
14.746887  10.59926 -.56155837 9.693803 0 0 10190 2008  7
15.009736 10.636732 -.58130705 9.693803 0 0 10190 2009  7
14.346915 10.664525  -.3682772 9.693803 0 0 10190 2010  7
14.692664  10.70166 -.29969665 9.693803 0 0 10190 2011  7
 6.907755 10.017065  .06047494 9.693803 0 0 10210 1995  8
 11.21182 10.588058  -.4933665 9.693803 0 0 10210 2007  8
 6.907755  10.15314  .10024168 9.693803 0 0 10310 1998  9
 8.798606 10.664525  -.3682772 9.693803 0 0 10511 2010 10
 8.699514  9.807273  .25675145 9.693803 0 0 10600 1991 11
 8.006368  9.859335  .13704698 9.693803 0 0 10600 1992 11
 8.006368  9.911574   .1171426 9.693803 0 0 10600 1993 11
        .   9.97103   .1709818 9.693803 0 0 10600 1994 11
        . 10.017065  .06047494 9.693803 0 0 10600 1995 11
        . 10.054852   .1634534 9.693803 0 0 10600 1996 11
 8.149024  10.10407   .2523011 9.693803 0 0 10600 1997 11
 8.784315 10.208075 -.50192624 9.693803 0 0 10600 1999 11
        . 10.250782  -.4656639 9.693803 0 0 10600 2000 11
9.1049795 10.294225  -.5490737 9.693803 0 0 10600 2001 11
        . 10.588058  -.4933665 9.693803 0 0 10611 2007 12
        .  10.76284  -.3899194 9.693803 0 0 10613 2015 13
        . 10.335284  -.5541078 9.693803 0 0 10619 2002 14
 6.907755  10.38446 -.55626863 9.693803 0 0 10619 2003 14
 6.907755 10.432998 -.52555615 9.693803 0 0 10619 2004 14
 6.907755 10.481292  -.4880296 9.693803 0 0 10619 2005 14
        . 10.542543 -.51126665 9.693803 0 0 10619 2006 14
        . 10.588058  -.4933665 9.693803 0 0 10619 2007 14
 6.907755  10.59926 -.56155837 9.693803 0 0 10619 2008 14
        . 10.636732 -.58130705 9.693803 0 0 10619 2009 14
        . 10.664525  -.3682772 9.693803 0 0 10619 2010 14
 5.627621  10.70166 -.29969665 9.693803 0 0 10619 2011 14
        .  10.68929  -.2157968 9.693803 0 0 10619 2012 14
        . 10.773984  -.3189786 9.693803 0 0 10619 2013 14
 7.487174  10.77067  -.3878373 9.693803 0 0 10619 2014 14
 10.28141  10.76284  -.3899194 9.693803 0 0 10619 2015 14
11.195554 10.822786  -.3981192 9.693803 0 0 10619 2016 14
end


local which 1
levelsof HSspecific, local(sectors)
foreach sector of local sectors{
    capture noisily{
        local opt= cond(`which'==1, "replace", "append")
        estpost corr log_exp log_GDP log_EXCH if HSspecific==`sector', matrix
        esttab . using myfile.csv, b(4)  unstack nonum nomtitle not noobs compress varwidth(18) modelwidth(4) `opt' title(For sector `sector')
        local ++which
    }
}

Res.:

For sector 0
	log_exp	log_GDP	log_EXCH
log_exp	1.0000
log_GDP	0.9555***	1.0000
log_EXCH	-0.5504**	-0.7261***	1.0000
="* p<0.05	** p<0.01	*** p<0.001"
For sector 10110
	log_exp	log_GDP	log_EXCH
log_exp	1.0000
log_GDP	1.0000	1.0000
log_EXCH	-1.0000	0.6682*	1.0000
="* p<0.05	** p<0.01	*** p<0.001"
For sector 10119
	log_exp	log_GDP	log_EXCH
log_exp	1.0000
log_GDP	0.9581	1.0000
log_EXCH	-0.9619	-0.7874**	1.0000
="* p<0.05	** p<0.01	*** p<0.001"
For sector 10129
	log_exp	log_GDP	log_EXCH
log_exp	1.0000
log_GDP	-0.7475	1.0000
log_EXCH	0.8035*	-0.8808**	1.0000
="* p<0.05	** p<0.01	*** p<0.001"
For sector 10190
	log_exp	log_GDP	log_EXCH
log_exp	1.0000
log_GDP	0.8889**	1.0000
log_EXCH	0.3327	0.5996	1.0000
="* p<0.05	** p<0.01	*** p<0.001"
For sector 10210
	log_exp	log_GDP	log_EXCH
log_exp	1.0000
log_GDP	1.0000	1.0000
log_EXCH	-1.0000	-1.0000	1.0000
="* p<0.05	** p<0.01	*** p<0.001"
For sector 10600
	log_exp	log_GDP	log_EXCH
log_exp	1.0000
log_GDP	0.6072	1.0000
log_EXCH	-0.7439	-0.8363**	1.0000
="* p<0.05	** p<0.01	*** p<0.001"
For sector 10619
	log_exp	log_GDP	log_EXCH
log_exp	1.0000
log_GDP	0.5887	1.0000
log_EXCH	0.2150	0.6843**	1.0000
="* p<0.05	** p<0.01	*** p<0.001"

Announcement

export a set of correlation tables using command frame

Comment