Getting correlation coefficients for panel data

[Kate Pryce]

Hi all,

I'm working with some unbalanced panel data and am trying to find the correlation between two of my variables. I've read through: https://www.stata.com/manuals13/rcorrelate.pdf and feel like I'm using the right code, but my results are quite different than I would have expected so I wanted to make sure I'm using "correlate" properly.

I'm trying to find the correlation between mean_TenYRIncomeGR and mean_TenYRRevenueGR

What I'm doing:

xtset ID FiscalYear
correlate mean_TenYRIncomeGR mean_TenYRRevenueGR

Am I missing something? Is there another step for analyzing correlation within panel data?

* Example generated by -dataex-. For more info, type help dataex
clear
input float ID int FiscalYear double(mean_TenYRIncomeGR mean_TenYRRevenueGR)
  4 1990    .3013545546680689  .1400796439498663
  4 2011    .3711621031165123 .13184541333466768
  4 2012   .43131961226463317 .14137937258929015
  4 2013    .5921003580093384 .12775963488966227
  4 2014    .1650818407535553 .09139561150223016
  4 2015   .40518084168434143 .07569748815149069
  4 2016    .3568063959479332 .06374833416193723
 21 1985    1.244255561567843  .2437101311981678
 21 1986   1.2366038927808405  .2408012218773365
 21 1987   1.2610976701602339 .23165517672896385
 21 1988   1.2944470202550291 .23015092089772224
 21 1989   1.0794008700177073  .1814340077340603
 21 1990   1.0965539572760463 .17404749915003775
 21 1991   1.0949830915778875  .1771307408809662
 21 1992   1.0644168470054864 .15207069367170334
 21 1993   1.2067912612110376 .17425474748015404
 21 1994    .3053622055798769 .18208017572760582
 21 1995   .23387790210545062 .17015033289790155
 21 1996    .2403137255460024 .19634600058197976
 21 1997    .2372197363525629  .2208409421145916
 21 1998    .2609607372432947 .23153994381427764
 21 1999   .22393156699836253 .26201214641332626
 21 2000   1.0748906653374433  .3003494068980217
 21 2001     .830388767272234 .26032835692167283
 21 2002    .8238427482545376 .19622096717357634
 21 2003    .6800801061093807 .15434216558933259
 21 2004    .5351963885128498 .13321208162233233
 21 2005   1.0689509637653827 .15164889497682452
 34 1985   3.0778897956013678  .2229406602680683
 34 1986   3.3719642356038095 .21876103356480597
 34 1987   3.4794270031154158 .22572405710816384
 34 1988   .46810268685221673 .22003901600837708
 34 1989    5.411567752808333 .20391169488430022
141 1988  -1.4939886301755905  .4808107768651098
141 1989  -1.2084229916334153   .439282359695062
141 1990  -1.1108329325914383 .33850742927752436
141 1991   -.6809824913740158   .309461367642507
141 1992   -.6911552384495735  .2572598328348249
198 1985 -.024204089492559432  .2379065163433552
198 1986  -.22273791059851647 .22338848412036896
198 1987   -.8775786064565182 .23075008690357207
198 1988   -.8648223422467709 .23633310198783875
198 1989   -.9943342588841915 .19357213284820318
198 1994   -.4009999831517537  .3614867003634572
  8 2016   .18908351026475428 .06797020640224219
  8 2017    .1112497616559267 .07675650399178266
  8 2018  -2.7995246570557355 .07348728086799383
  9 1986 -.010615566186606884 .20615729335695504
  9 1987    .3997617932036519  .1702531086280942
  9 1989    .2902595134451985 .20562590844929218
  9 1990   .29645537342876194 .19770551100373268
  9 1991   .26502930242568257  .1808270938694477
  9 1992    .2706481020897627 .16773973330855368
 12 2012   .27764869555830957 .11015220507979392
 12 2013   .20500855520367622 .10307748913764954
 12 2014  .059668376296758655   .098741315305233
 12 2015   .06906864364864304 .08602351173758507
 12 2016   .05880515446187928  .0856175296008587
 12 2017   .02455853169085458 .07931783124804496
 12 2018  .026969211001414806 .07572528310120105
 12 2019    .0832687990856357 .09339170418679714
 12 2020    .1396146329236217 .10175368525087833
 12 2021   .19897814040305092 .09670307449996471
 12 2022   .09494880383135751 .08812491931021213
 16 1985    .8436818785965443  .3442229971289635
 16 1986  .014648372679948807  .2318948857486248
 18 1992  -.09864846626296639 .33592813011491673
 18 1993  -.01568692484870553  .3742471694597043
 18 1994   .11458832612261176 .37839570190990346
 18 1995   .11059418907389044  .4272702693589963
 18 1996    .4700551800429821  .5563840806134976
 18 1997   .45371257364749906  .5874514609575272
 18 1998    .3947015356272459  .5832711368799209
 18 1999    .5848141100257636  .6538862228393555
 18 2000     .626978075504303  .7067772522568703
 18 2001   .47843788787722585  .6650876395404339
 18 2002   .21418369933962822 .25498939082026484
 18 2003   .39284896180033685  .2026827149093151
 18 2004   .46256498619914055  .2315781332552433
 18 2005    .6268990032374859 .21489492133259774
 18 2006   .22583327516913415 .12983618974685668
 18 2008   .21341869831085206 .17327015474438667
 18 2013   .27009166926145556 .13234935738146306
 18 2014  .022778378427028657 .11688304282724857
 18 2022  -.22397081702947616 .10193254090845585
 19 1985    .4118266612291336 .25282262489199636
 19 1986   .29660564959049224 .17708603739738465
 26 1985   -3.065638390183449  .3460938915610313
 26 1986  -3.1970169126987456  .3766698449850082
 26 1987  -3.0945453464984896 .37579168677330016
 26 1988  -3.9928260266780855 .35889510363340377
 26 1989   -4.565485352277756  .2845915824174881
 26 1990   -4.549769192934036  .2505698949098587
 26 1991   -4.548397824168205 .20533928610384464
 26 1992   -4.490296213328838 .17459032349288464
 26 1993   -4.229204164445401 .19018477462232114
 26 1994    -1.34039254039526 .14327390752732755
 37 1994   -.6573722299188376 .13781240973621606
 37 1995 .0015500430017709732 .15443487074226142
 37 1996   .27829552032053473 .16083965506404638
end
label values ID ID
label def ID 4 "AAR CORP", modify
label def ID 8 "ACETO CORP", modify
label def ID 9 "ACMAT CORP  -CL A", modify
label def ID 12 "ACME UNITED CORP", modify
label def ID 16 "ACTON CORP  -OLD", modify
label def ID 18 "ADAMS RESOURCES & ENERGY INC", modify
label def ID 19 "ADAMS RUSSELL", modify
label def ID 21 "ADC TELECOMMUNICATIONS INC", modify
label def ID 26 "ADVANCE CIRCUITS INC", modify
label def ID 34 "AFG INDUSTRIES INC", modify
label def ID 37 "AIR EXPRESS INTERNATIONAL CP", modify
label def ID 141 "APL CORP", modify
label def ID 198 "AVX CORP", modify

[Carlo Lazzaro]

Kate:
if you are interested in the correlation of two variables incidentally included in a panel dataset -xtset- is not necessary.
What I get using your excerp is:

. correlate mean_TenYRIncomeGR mean_TenYRRevenueGR
(obs=100)

             | mea~meGR mea~ueGR
-------------+------------------
mean_Te~meGR |   1.0000
mean_Te~ueGR |  -0.0854   1.0000


. sum mean_TenYRIncomeGR mean_TenYRRevenueGR

    Variable |        Obs        Mean    Std. dev.       Min        Max
-------------+---------------------------------------------------------
mean_Te~meGR |        100   -.0335379    1.586489  -4.565485   5.411568
mean_Te~ueGR |        100    .2279375    .1358519   .0637483   .7067773

.

Why does your result make no sense to you?