Correlation in Panel Data

[Alexander Lahmann]

Dear statlist,

i am trying to calculate correlation coefficients for a panel dataset.

Suppose you have the following dataset on IDs and years:

Year	ID	X
1992	10	2600
1992	11	1000
1993	10	800
1993	11	700
1994	10	6000
1994	11	2500

Obviously, variable X is an ID and Year specific item. I would like to calculate, let say for time t=1994 the correlation of variable X between ID 10 and 11 for the years 1992-1994.

I know this might be a very straightforward question. However, I tried several things but could not find a convenient solution.

Thanks in advance!

Best,

Alex L

[Scott Merryman]

reshaping the data will allow you to calculate the correlation

* Example generated by -dataex-. To install: ssc install dataex
clear
input float Year byte ID float X
1992 10 2600
1992 11 1000
1993 10  800
1993 11  700
1994 10 6000
1994 11 2500
end
reshape wide X, i(Year) j(ID)
l
corr X*

[Alexander Lahmann]

Well, thanks a lot for your answer. This yields somehow not my desired result. Probably I was not very clear with my question. To clarify

Based upon the above setting

Year	ID	X
1992	10	2600
1992	11	1000
1992	12	800
1993	10	700
1993	11	6000
1993	12	2500
1994	10	600
1994	11	500
1994	12	200

I would like to determine the correlations in a correlation matrix, returning than

ID/ID	10	11	12
10	1	correlation of var x between the IDs 10 and 11 over the years 1992-1994	correlation of var x between the IDs 10 and 12 over the years 1992-1994
11	correlation of var x between the IDs 11 and 10 over the years 1992-1994	1	...
12	correlation of var x between the IDs 12 and 10 over the years 1992-1994	correlation of var x between the IDs 12 and 11 over the years 1992-1994	1

In summary, my aim is to compute a correlation matrix yielding in each of the cells the correlation of variable x with respect to the IDs over a pre-specified time frame. Obviously, the first cell in the correlation table is the correlation of variable x between the IDs 10 and 10 over the time frame 1992 to 1994; the second cell yields the correlation of variable x between the IDs 10 and 11 over the time frame 1992 to 1994. It is like a standard portfolio correlation matrix.

Thanks again in advance for any help.

Best.

[Jesse Wursten]

I'm not entirely sure I understand what you're asking for, but this sounds like something that could be done using my -pwcorrf- command, available from SSC (ssc install pwcorrf). I still need to update it to include if and in options, but for now, the following should work?

keep if Year > 1991 & Year < 1995
xtset ID Year
pwcorrf X, reshape

[Scott Merryman]

It is not clear why the previous suggested solution would not work in this case with three IDs:

* Example generated by -dataex-. To install: ssc install dataex
clear
input float Year byte ID float X
1992    10    2600
1992    11    1000
1992    12    800
1993    10    700
1993    11    6000
1993    12    2500
1994    10    600
1994    11    500
1994    12    200
end
reshape wide X, i(Year) j(ID)
corr X*

. corr X*
(obs=3)

             |      X10      X11      X12
-------------+---------------------------
         X10 |   1.0000
         X11 |  -0.3866   1.0000
         X12 |  -0.2231   0.9853   1.0000

Of course, the correlation of (X11,X10) equals the correlation of (X10,X11)