Panel data correlation multiple variables

[Erik Larsson]

Hi,

I have six variables: time, country, panelid, hp_A_Austria, hp_A_Belgium, hp_A_Denmark and period1

I'm looking for a way to calculate the correlation (Pearson correlation coefficient) between hp_A_Austria, hp_A_Denmark and hp_A_Belgium for period1=1 and the same for period1=0. I have tested with

pwcorr hp_A_* if period1==1

Have copied in the data from excel. Do you have any suggestions how I can solve it?

Qtime	country	panelid	A	hp_A	period1	hp_A_Austria	hp_A_Belgium	hp_A_Denmark
1996Q1	Austria	A Austria	952.6	37.10776		37.10776
1996Q2	Austria	A Austria	964.1	52.04268		52.04268
1996Q3	Austria	A Austria	916	7.354406		7.354406
1996Q4	Austria	A Austria	855.8	-49.51278		-49.51278
1997Q1	Austria	A Austria	895.1	-7.01919		-7.01919
1997Q2	Austria	A Austria	940.5	41.4058		41.4058
1997Q3	Austria	A Austria	881.8	-14.46279		-14.46279
1997Q4	Austria	A Austria	842.1	-51.57582		-51.57582
1998Q1	Austria	A Austria	874.9	-16.47511		-16.47511
1998Q2	Austria	A Austria	901.9	12.52974		12.52974
1998Q3	Austria	A Austria	853.9	-33.76054		-33.76054
1998Q4	Austria	A Austria	853.7	-32.5531		-32.5531
1999Q1	Austria	A Austria	887	1.866066	1	1.866066
1999Q2	Austria	A Austria	916.5	32.23127	1	32.23127
1999Q3	Austria	A Austria	848.9	-34.72433	1	-34.72433
1999Q4	Austria	A Austria	842.5	-40.68773	1	-40.68773
2000Q1	Austria	A Austria	874.6	-8.324187	1	-8.324187
2000Q2	Austria	A Austria	867	-15.77358	1	-15.77358
2000Q3	Austria	A Austria	876.9	-5.770547	1	-5.770547
2000Q4	Austria	A Austria	907.6	25.06011	1	25.06011
2001Q1	Austria	A Austria	930.7	48.39721	1	48.39721
2001Q2	Austria	A Austria	947.7	65.80391	1	65.80391
2001Q3	Austria	A Austria	894.7	13.4131	1	13.4131
2001Q4	Austria	A Austria	901.4	20.91656	1	20.91656
2002Q1	Austria	A Austria	953.9	74.39769	1	74.39769
2002Q2	Austria	A Austria	815	-63.3732	1	-63.3732
2002Q3	Austria	A Austria	916.3	39.12772	1	39.12772
2002Q4	Austria	A Austria	882.9	6.96387	1	6.96387
2003Q1	Austria	A Austria	874.9	.1742271	1	.1742271
2003Q2	Austria	A Austria	851.1	-22.50659	1	-22.50659
2003Q3	Austria	A Austria	839.9	-32.74406	1	-32.74406
2003Q4	Austria	A Austria	904.1	32.2104	1	32.2104
2004Q1	Austria	A Austria	940.2	68.82581	1	68.82581
2004Q2	Austria	A Austria	970.9	99.75111	1	99.75111
2004Q3	Austria	A Austria	852.6	-18.70781	1	-18.70781
2004Q4	Austria	A Austria	779.2	-92.8074	1	-92.8074
2005Q1	Austria	A Austria	804.3	-69.09242	1	-69.09242
2005Q2	Austria	A Austria	817.2	-58.34962	1	-58.34962
2005Q3	Austria	A Austria	773.8	-104.7226	1	-104.7226
2005Q4	Austria	A Austria	800.2	-82.11834	1	-82.11834
2006Q1	Austria	A Austria	821.4	-65.47861	1	-65.47861
2006Q2	Austria	A Austria	852.8	-39.2937	1	-39.2937
2006Q3	Austria	A Austria	878.8	-19.01299	1	-19.01299
2006Q4	Austria	A Austria	950.5	46.63865	1	46.63865
2007Q1	Austria	A Austria	883.6	-26.45172	1	-26.45172
2007Q2	Austria	A Austria	889.9	-26.3262	1	-26.3262
2007Q3	Austria	A Austria	1053.2	130.9896	1	130.9896
2007Q4	Austria	A Austria	1256.1	328.2866	1	328.2866
2008Q1	Austria	A Austria	1029.3	96.37383	0	96.37383
2008Q2	Austria	A Austria	1035.9	98.25506	0	98.25506
2008Q3	Austria	A Austria	967.7	25.57392	0	25.57392
2008Q4	Austria	A Austria	884.3	-62.28741	0	-62.28741
2009Q1	Austria	A Austria	848.5	-102.7627	0	-102.7627
2009Q2	Austria	A Austria	820.2	-136.1469	0	-136.1469
2009Q3	Austria	A Austria	811.6	-150.3705	0	-150.3705
2009Q4	Austria	A Austria	827	-141.1792	0	-141.1792
2010Q1	Austria	A Austria	854.2	-120.7245	0	-120.7245
2010Q2	Austria	A Austria	760.9	-221.1697	0	-221.1697
2010Q3	Austria	A Austria	1028.2	38.79734	0	38.79734
2010Q4	Austria	A Austria	1187.1	190.5269	0	190.5269
2011Q1	Austria	A Austria	1104	100.7451	0	100.7451
2011Q2	Austria	A Austria	1131.4	122.159	0	122.159
2011Q3	Austria	A Austria	1073.3	58.91266	0	58.91266
2011Q4	Austria	A Austria	1017.7	-.9262266	0	-.9262266
2012Q1	Austria	A Austria	1031.1	9.173244	0	9.173244
2012Q2	Austria	A Austria	990.2	-34.05746	0	-34.05746
2012Q3	Austria	A Austria	1112.4	86.8074	0	86.8074
2012Q4	Austria	A Austria	1203.4	177.5148	0	177.5148
2013Q1	Austria	A Austria	1140.9	115.7577	0	115.7577
2013Q2	Austria	A Austria	962.6	-60.88236	0	-60.88236
2013Q3	Austria	A Austria	1002.8	-18.29572	0	-18.29572
2013Q4	Austria	A Austria	977.9	-40.23491	0	-40.23491
2014Q1	Austria	A Austria	1023.9	9.159049	0	9.159049
2014Q2	Austria	A Austria	1043.9	32.87025	0	32.87025
2014Q3	Austria	A Austria	981.9	-25.22292	0	-25.22292
2014Q4	Austria	A Austria	929.3	-73.86263	0	-73.86263
2015Q1	Austria	A Austria	931.1	-68.17526	0	-68.17526
2015Q2	Austria	A Austria	974	-21.54106	0	-21.54106
2015Q3	Austria	A Austria	958.4	-33.59764	0	-33.59764
2015Q4	Austria	A Austria	959.3	-29.36917	0	-29.36917
2016Q1	Austria	A Austria	965.6	-19.95881	0	-19.95881
2016Q2	Austria	A Austria	988.7	6.048631	0	6.048631
2016Q3	Austria	A Austria	971.8	-8.119169	0	-8.119169
2016Q4	Austria	A Austria	958.6	-18.73833	0	-18.73833
2017Q1	Austria	A Austria	986.2	11.32012	0	11.32012
2017Q2	Austria	A Austria	978.5	5.996842	0	5.996842
2017Q3	Austria	A Austria	988.7	18.52544	0	18.52544
2017Q4	Austria	A Austria	997.1	29.23577	0	29.23577
1995Q1	Belgium	A Belgium	736.9	8.233837			8.233837
1995Q2	Belgium	A Belgium	708	-20.25949			-20.25949
1995Q3	Belgium	A Belgium	708.9	-18.95796			-18.95796
1995Q4	Belgium	A Belgium	725.1	-2.354059			-2.354059
1996Q1	Belgium	A Belgium	738.3	11.27157			11.27157
1996Q2	Belgium	A Belgium	735.8	9.239779			9.239779
1996Q3	Belgium	A Belgium	746.1	20.06434			20.06434
1996Q4	Belgium	A Belgium	708.8	-16.64671			-16.64671
1997Q1	Belgium	A Belgium	715.8	-8.99791			-8.99791
1997Q2	Belgium	A Belgium	745.1	21.01662			21.01662
1997Q3	Belgium	A Belgium	762.3	39.0084			39.0084
1997Q4	Belgium	A Belgium	767.4	44.97578			44.97578
1998Q1	Belgium	A Belgium	727.8	6.292758			6.292758
1998Q2	Belgium	A Belgium	732.1	11.50521			11.50521
1998Q3	Belgium	A Belgium	719.2	-.5449146			-.5449146
1998Q4	Belgium	A Belgium	721.4	2.377135			2.377135
1999Q1	Belgium	A Belgium	699	-19.49354	1		-19.49354
1999Q2	Belgium	A Belgium	636	-82.22334	1		-82.22334
1999Q3	Belgium	A Belgium	615	-103.2665	1		-103.2665
1999Q4	Belgium	A Belgium	658	-60.62574	1		-60.62574
2000Q1	Belgium	A Belgium	689	-30.23942	1		-30.23942
2000Q2	Belgium	A Belgium	749	28.99209	1		28.99209
2000Q3	Belgium	A Belgium	797	76.18731	1		76.18731
2000Q4	Belgium	A Belgium	782	60.44664	1		60.44664
2001Q1	Belgium	A Belgium	763	40.82285	1		40.82285
2001Q2	Belgium	A Belgium	745	22.33095	1		22.33095
2001Q3	Belgium	A Belgium	704	-19.03959	1		-19.03959
2001Q4	Belgium	A Belgium	722	-1.31323	1		-1.31323
2002Q1	Belgium	A Belgium	729	5.497453	1		5.497453
2002Q2	Belgium	A Belgium	706	-17.61929	1		-17.61929
2002Q3	Belgium	A Belgium	680	-43.67866	1		-43.67866
2002Q4	Belgium	A Belgium	709	-14.68481	1		-14.68481
2003Q1	Belgium	A Belgium	685	-38.61464	1		-38.61464
2003Q2	Belgium	A Belgium	699	-24.43582	1		-24.43582
2003Q3	Belgium	A Belgium	762	38.90805	1		38.90805
2003Q4	Belgium	A Belgium	750	27.48871	1		27.48871
2004Q1	Belgium	A Belgium	787	65.35353	1		65.35353
2004Q2	Belgium	A Belgium	782	61.53273	1		61.53273
2004Q3	Belgium	A Belgium	744	25.01567	1		25.01567
2004Q4	Belgium	A Belgium	724	6.753255	1		6.753255
2005Q1	Belgium	A Belgium	645	-70.31924	1		-70.31924
2005Q2	Belgium	A Belgium	638	-75.27077	1		-75.27077
2005Q3	Belgium	A Belgium	665	-46.12632	1		-46.12632
2005Q4	Belgium	A Belgium	658	-50.86386	1		-50.86386
2006Q1	Belgium	A Belgium	694	-12.43252	1		-12.43252
2006Q2	Belgium	A Belgium	747	43.25038	1		43.25038
2006Q3	Belgium	A Belgium	766	65.27526	1		65.27526
2006Q4	Belgium	A Belgium	744	46.70552	1		46.70552
2007Q1	Belgium	A Belgium	804	110.5638	1		110.5638
2007Q2	Belgium	A Belgium	800	110.8434	1		110.8434
2007Q3	Belgium	A Belgium	728	43.46882	1		43.46882
2007Q4	Belgium	A Belgium	719	39.29494	1		39.29494
2008Q1	Belgium	A Belgium	683	8.149692	0		8.149692
2008Q2	Belgium	A Belgium	646	-24.16364	0		-24.16364
2008Q3	Belgium	A Belgium	611	-54.84683	0		-54.84683
2008Q4	Belgium	A Belgium	567	-95.08657	0		-95.08657
2009Q1	Belgium	A Belgium	565	-94.03526	0		-94.03526
2009Q2	Belgium	A Belgium	576	-80.78587	0		-80.78587
2009Q3	Belgium	A Belgium	550	-105.3726	0		-105.3726
2009Q4	Belgium	A Belgium	574	-80.77921	0		-80.77921
2010Q1	Belgium	A Belgium	663	8.076511	0		8.076511
2010Q2	Belgium	A Belgium	703	47.32716	0		47.32716
2010Q3	Belgium	A Belgium	730	73.10033	0		73.10033
2010Q4	Belgium	A Belgium	688	29.49402	0		29.49402
2011Q1	Belgium	A Belgium	589	-71.43944	0		-71.43944
2011Q2	Belgium	A Belgium	595	-67.66618	0		-67.66618
2011Q3	Belgium	A Belgium	591	-74.10766	0		-74.10766
2011Q4	Belgium	A Belgium	667	-.6430542	0		-.6430542
2012Q1	Belgium	A Belgium	743	72.89478	0		72.89478
2012Q2	Belgium	A Belgium	766	93.67337	0		93.67337
2012Q3	Belgium	A Belgium	768	93.81472	0		93.81472
2012Q4	Belgium	A Belgium	764	88.38226	0		88.38226
2013Q1	Belgium	A Belgium	751	74.38081	0		74.38081
2013Q2	Belgium	A Belgium	670	-7.240092	0		-7.240092
2013Q3	Belgium	A Belgium	627	-50.57735	0		-50.57735
2013Q4	Belgium	A Belgium	651	-26.72337	0		-26.72337
2014Q1	Belgium	A Belgium	616	-61.73893	0		-61.73893
2014Q2	Belgium	A Belgium	658	-19.66811	0		-19.66811
2014Q3	Belgium	A Belgium	647	-30.51643	0		-30.51643
2014Q4	Belgium	A Belgium	677	-.2770737	0		-.2770737
2015Q1	Belgium	A Belgium	710	33.07582	0		33.07582
2015Q2	Belgium	A Belgium	708	31.56829	0		31.56829
2015Q3	Belgium	A Belgium	740	64.2057	0		64.2057
2015Q4	Belgium	A Belgium	646	-29.0263	0		-29.0263
2016Q1	Belgium	A Belgium	656	-18.18221	0		-18.18221
2016Q2	Belgium	A Belgium	631	-42.29837	0		-42.29837
2016Q3	Belgium	A Belgium	680	7.600232	0		7.600232
2016Q4	Belgium	A Belgium	643	-28.48496	0		-28.48496
2017Q1	Belgium	A Belgium	716	45.44277	0		45.44277
2017Q2	Belgium	A Belgium	669	-.6020883	0		-.6020883
2017Q3	Belgium	A Belgium	647	-21.63342	0		-21.63342
1995Q1	Denmark	A Denmark	978.4	-50.4109				-50.4109
1995Q2	Denmark	A Denmark	1007.9	-14.65244				-14.65244
1995Q3	Denmark	A Denmark	1031	14.73754				14.73754
1995Q4	Denmark	A Denmark	1018	8.099685				8.099685
1996Q1	Denmark	A Denmark	1001	-2.434545				-2.434545
1996Q2	Denmark	A Denmark	1017.2	20.36124				20.36124
1996Q3	Denmark	A Denmark	1058.1	68.01496				68.01496
1996Q4	Denmark	A Denmark	1052.1	68.94179				68.94179
1997Q1	Denmark	A Denmark	1030.9	54.81442				54.81442
1997Q2	Denmark	A Denmark	1080.4	111.4624				111.4624
1997Q3	Denmark	A Denmark	1012.1	50.28115				50.28115
1997Q4	Denmark	A Denmark	916.4	-38.50375				-38.50375
1998Q1	Denmark	A Denmark	945.2	-3.198023				-3.198023
1998Q2	Denmark	A Denmark	811.3	-131.1834				-131.1834
1998Q3	Denmark	A Denmark	791.1	-146.2395				-146.2395
1998Q4	Denmark	A Denmark	843.3	-89.7641				-89.7641
1999Q1	Denmark	A Denmark	766.1	-163.5635	1			-163.5635
1999Q2	Denmark	A Denmark	756.1	-170.9879	1			-170.9879
1999Q3	Denmark	A Denmark	824.4	-100.7852	1			-100.7852
1999Q4	Denmark	A Denmark	894.2	-29.49659	1			-29.49659
2000Q1	Denmark	A Denmark	942	19.6998	1			19.6998
2000Q2	Denmark	A Denmark	978.2	57.54432	1			57.54432
2000Q3	Denmark	A Denmark	922.6	4.164972	1			4.164972
2000Q4	Denmark	A Denmark	987.8	72.45379	1			72.45379
2001Q1	Denmark	A Denmark	1100.4	189.3002	1			189.3002
2001Q2	Denmark	A Denmark	1118.3	212.8484	1			212.8484
2001Q3	Denmark	A Denmark	1084.8	186.5241	1			186.5241
2001Q4	Denmark	A Denmark	1002.2	112.6203	1			112.6203
2002Q1	Denmark	A Denmark	910.7	31.21312	1			31.21312
2002Q2	Denmark	A Denmark	874.2	6.00841	1			6.00841
2002Q3	Denmark	A Denmark	808.7	-47.20749	1			-47.20749
2002Q4	Denmark	A Denmark	786.9	-55.95199	1			-55.95199
2003Q1	Denmark	A Denmark	775.6	-53.61301	1			-53.61301
2003Q2	Denmark	A Denmark	785.1	-30.04349	1			-30.04349
2003Q3	Denmark	A Denmark	780.1	-20.66287	1			-20.66287
2003Q4	Denmark	A Denmark	773.8	-12.37181	1			-12.37181
2004Q1	Denmark	A Denmark	722.7	-48.75806	1			-48.75806
2004Q2	Denmark	A Denmark	854.7	97.99838	1			97.99838
2004Q3	Denmark	A Denmark	929.3	187.348	1			187.348
2004Q4	Denmark	A Denmark	777.6	50.2799	1			50.2799
2005Q1	Denmark	A Denmark	651.7	-61.33368	1			-61.33368
2005Q2	Denmark	A Denmark	512.7	-186.6521	1			-186.6521
2005Q3	Denmark	A Denmark	595.9	-90.59631	1			-90.59631
2005Q4	Denmark	A Denmark	677.9	3.329365	1			3.329365
2006Q1	Denmark	A Denmark	656.2	-7.42277	1			-7.42277
2006Q2	Denmark	A Denmark	682.6	28.89752	1			28.89752
2006Q3	Denmark	A Denmark	653	8.145092	1			8.145092
2006Q4	Denmark	A Denmark	635.9	-1.243239	1			-1.243239
2007Q1	Denmark	A Denmark	716	85.36424	1			85.36424
2007Q2	Denmark	A Denmark	692.2	66.80002	1			66.80002
2007Q3	Denmark	A Denmark	653.4	31.84324	1			31.84324
2007Q4	Denmark	A Denmark	679.3	60.03129	1			60.03129
2008Q1	Denmark	A Denmark	624.1	5.381663	0			5.381663
2008Q2	Denmark	A Denmark	472.3	-147.8257	0			-147.8257
2008Q3	Denmark	A Denmark	477	-146.7141	0			-146.7141
2008Q4	Denmark	A Denmark	499.2	-130.4147	0			-130.4147
2009Q1	Denmark	A Denmark	452.5	-185.3667	0			-185.3667
2009Q2	Denmark	A Denmark	439	-209.4279	0			-209.4279
2009Q3	Denmark	A Denmark	528.9	-132.2403	0			-132.2403
2009Q4	Denmark	A Denmark	507.8	-167.9149	0			-167.9149
2010Q1	Denmark	A Denmark	618.3	-73.48006	0			-73.48006
2010Q2	Denmark	A Denmark	807.2	98.34065	0			98.34065
2010Q3	Denmark	A Denmark	818.8	92.36976	0			92.36976
2010Q4	Denmark	A Denmark	678.4	-65.63173	0			-65.63173
2011Q1	Denmark	A Denmark	712.5	-48.76054	0			-48.76054
2011Q2	Denmark	A Denmark	793.6	15.92762	0			15.92762
2011Q3	Denmark	A Denmark	820.5	27.70752	0			27.70752
2011Q4	Denmark	A Denmark	926.2	120.044	0			120.044
2012Q1	Denmark	A Denmark	1014.3	196.9845	0			196.9845
2012Q2	Denmark	A Denmark	1047.5	221.6016	0			221.6016
2012Q3	Denmark	A Denmark	1064.8	233.1445	0			233.1445
2012Q4	Denmark	A Denmark	1085.9	251.4242	0			251.4242
2013Q1	Denmark	A Denmark	954.8	120.4057	0			120.4057
2013Q2	Denmark	A Denmark	837	5.397168	0			5.397168
2013Q3	Denmark	A Denmark	705.7	-120.6688	0			-120.6688
2013Q4	Denmark	A Denmark	871.7	52.73729	0			52.73729
2014Q1	Denmark	A Denmark	1011.7	202.1201	0			202.1201
2014Q2	Denmark	A Denmark	977.1	178.6516	0			178.6516
2014Q3	Denmark	A Denmark	836	50.07722	0			50.07722
2014Q4	Denmark	A Denmark	848.8	76.33079	0			76.33079
2015Q1	Denmark	A Denmark	627.1	-131.4851	0			-131.4851
2015Q2	Denmark	A Denmark	599	-145.8157	0			-145.8157
2015Q3	Denmark	A Denmark	529.1	-202.5238	0			-202.5238
2015Q4	Denmark	A Denmark	426.9	-292.4814	0			-292.4814
2016Q1	Denmark	A Denmark	497	-211.3338	0			-211.3338
2016Q2	Denmark	A Denmark	454.2	-244.3433	0			-244.3433
2016Q3	Denmark	A Denmark	595.6	-94.34043	0			-94.34043
2016Q4	Denmark	A Denmark	675.4	-6.902784	0			-6.902784
2017Q1	Denmark	A Denmark	725.7	50.35092	0			50.35092
2017Q2	Denmark	A Denmark	844.7	175.9063	0			175.9063
2017Q3	Denmark	A Denmark	893.1	230.7174	0			230.7174

[Clyde Schechter]

Well, the way your data is set up, every observation contains only a value for hp_a_austria, or only one for hp_a_belgium, or only one for hp_a_denmark. To calculate correlations between variables, each observation must give the corresponding values of all the variables you wish to correlate.

What you have is a deformed data set that seems to have resulted from putting together three separate data sets in a dysfunctional organization. On the one hand, there are separate observations for the three different data sets (corresponding to panelid = A Austria, A Belgium, and A Denmark, respectively), but on the other hand the values for hp_a_* are side by side in separate variables. So it is a hybrid of long and wide layout that won't work properly as either. So the first thing is to get the data into a usable layout. Since you want to correlate the hp_a_* variables with each other, this would be a wide layout.

keep qtime country hp_a period1
rename hp_a hp_a_
reshape wide hp_a_, i(qtime) j(country) string

by period1, sort: pwcorr hp_a_*

In the future, do not show data from a spreadsheet (unless your question is specifically about how to transfer the data between Stata and the spreadsheet). If you have not yet imported your data to Stata, it is premature to be asking for help with code. Show example data that comes from your Stata data set. Moreover, do that using the -dataex- command. If you are running version 15.1 or a fully updated version 14.2, it is already part of your official Stata installation. If not, run -ssc install dataex- to get it. Either way, run -help dataex- to read the simple instructions for using it. -dataex- will save you time; it is easier and quicker than typing out tables. It includes complete information about aspects of the data that are often critical to answering your question but cannot be seen from tabular displays or screenshots. It also makes it possible for those who want to help you to create a faithful representation of your example to try out their code, which in turn makes it more likely that their answer will actually work in your data.

When asking for help with code, always show example data. When showing example data, always use -dataex-.

[Erik Larsson]

Thank you for your reply and apologies for not posting the data incorrectly. We tried running the code you suggested but we get an error code: "Your data are currently long. You are performing a reshape wide. You specified i(Qtime) and j(country). There are observations within i(Qtime) with the same value of j(country). In the long data, variables i() and j() together must uniquely identify the observations." Any help would be appreciated. Below is the data using dataex.

* Example generated by -dataex-. To install: ssc install dataex
clear
input float Qtime str48 country float panelid double A float(hp_A period1 hp_A_Austria hp_A_Belgium hp_A_Denmark)
144 "Austria" 1  952.6   37.10776 .   37.10776          . .
145 "Austria" 1  964.1   52.04268 .   52.04268          . .
146 "Austria" 1    916   7.354406 .   7.354406          . .
147 "Austria" 1  855.8  -49.51278 .  -49.51278          . .
148 "Austria" 1  895.1   -7.01919 .   -7.01919          . .
149 "Austria" 1  940.5    41.4058 .    41.4058          . .
150 "Austria" 1  881.8 -14.462787 . -14.462787          . .
151 "Austria" 1  842.1  -51.57582 .  -51.57582          . .
152 "Austria" 1  874.9 -16.475111 . -16.475111          . .
153 "Austria" 1  901.9  12.529743 .  12.529743          . .
154 "Austria" 1  853.9 -33.760544 . -33.760544          . .
155 "Austria" 1  853.7 -32.553097 . -32.553097          . .
156 "Austria" 1    887   1.866066 1   1.866066          . .
157 "Austria" 1  916.5  32.231266 1  32.231266          . .
158 "Austria" 1  848.9 -34.724335 1 -34.724335          . .
159 "Austria" 1  842.5  -40.68773 1  -40.68773          . .
160 "Austria" 1  874.6  -8.324187 1  -8.324187          . .
161 "Austria" 1    867  -15.77358 1  -15.77358          . .
162 "Austria" 1  876.9  -5.770547 1  -5.770547          . .
163 "Austria" 1  907.6   25.06011 1   25.06011          . .
164 "Austria" 1  930.7   48.39721 1   48.39721          . .
165 "Austria" 1  947.7   65.80391 1   65.80391          . .
166 "Austria" 1  894.7    13.4131 1    13.4131          . .
167 "Austria" 1  901.4  20.916565 1  20.916565          . .
168 "Austria" 1  953.9   74.39769 1   74.39769          . .
169 "Austria" 1    815   -63.3732 1   -63.3732          . .
170 "Austria" 1  916.3   39.12772 1   39.12772          . .
171 "Austria" 1  882.9    6.96387 1    6.96387          . .
172 "Austria" 1  874.9  .17422715 1  .17422715          . .
173 "Austria" 1  851.1 -22.506586 1 -22.506586          . .
174 "Austria" 1  839.9 -32.744057 1 -32.744057          . .
175 "Austria" 1  904.1  32.210396 1  32.210396          . .
176 "Austria" 1  940.2   68.82581 1   68.82581          . .
177 "Austria" 1  970.9   99.75111 1   99.75111          . .
178 "Austria" 1  852.6 -18.707808 1 -18.707808          . .
179 "Austria" 1  779.2   -92.8074 1   -92.8074          . .
180 "Austria" 1  804.3  -69.09242 1  -69.09242          . .
181 "Austria" 1  817.2  -58.34962 1  -58.34962          . .
182 "Austria" 1  773.8 -104.72256 1 -104.72256          . .
183 "Austria" 1  800.2  -82.11834 1  -82.11834          . .
184 "Austria" 1  821.4  -65.47861 1  -65.47861          . .
185 "Austria" 1  852.8   -39.2937 1   -39.2937          . .
186 "Austria" 1  878.8 -19.012995 1 -19.012995          . .
187 "Austria" 1  950.5   46.63865 1   46.63865          . .
188 "Austria" 1  883.6 -26.451715 1 -26.451715          . .
189 "Austria" 1  889.9   -26.3262 1   -26.3262          . .
190 "Austria" 1 1053.2  130.98962 1  130.98962          . .
191 "Austria" 1 1256.1   328.2866 1   328.2866          . .
192 "Austria" 1 1029.3   96.37383 0   96.37383          . .
193 "Austria" 1 1035.9   98.25506 0   98.25506          . .
194 "Austria" 1  967.7   25.57392 0   25.57392          . .
195 "Austria" 1  884.3  -62.28741 0  -62.28741          . .
196 "Austria" 1  848.5 -102.76272 0 -102.76272          . .
197 "Austria" 1  820.2 -136.14688 0 -136.14688          . .
198 "Austria" 1  811.6 -150.37051 0 -150.37051          . .
199 "Austria" 1    827  -141.1792 0  -141.1792          . .
200 "Austria" 1  854.2 -120.72446 0 -120.72446          . .
201 "Austria" 1  760.9 -221.16966 0 -221.16966          . .
202 "Austria" 1 1028.2   38.79734 0   38.79734          . .
203 "Austria" 1 1187.1   190.5269 0   190.5269          . .
204 "Austria" 1   1104   100.7451 0   100.7451          . .
205 "Austria" 1 1131.4    122.159 0    122.159          . .
206 "Austria" 1 1073.3   58.91266 0   58.91266          . .
207 "Austria" 1 1017.7  -.9262266 0  -.9262266          . .
208 "Austria" 1 1031.1  9.1732435 0  9.1732435          . .
209 "Austria" 1  990.2  -34.05746 0  -34.05746          . .
210 "Austria" 1 1112.4    86.8074 0    86.8074          . .
211 "Austria" 1 1203.4  177.51485 0  177.51485          . .
212 "Austria" 1 1140.9  115.75765 0  115.75765          . .
213 "Austria" 1  962.6  -60.88236 0  -60.88236          . .
214 "Austria" 1 1002.8 -18.295723 0 -18.295723          . .
215 "Austria" 1  977.9  -40.23491 0  -40.23491          . .
216 "Austria" 1 1023.9   9.159049 0   9.159049          . .
217 "Austria" 1 1043.9   32.87025 0   32.87025          . .
218 "Austria" 1  981.9  -25.22292 0  -25.22292          . .
219 "Austria" 1  929.3 -73.862625 0 -73.862625          . .
220 "Austria" 1  931.1  -68.17526 0  -68.17526          . .
221 "Austria" 1    974  -21.54106 0  -21.54106          . .
222 "Austria" 1  958.4 -33.597645 0 -33.597645          . .
223 "Austria" 1  959.3 -29.369175 0 -29.369175          . .
224 "Austria" 1  965.6 -19.958815 0 -19.958815          . .
225 "Austria" 1  988.7   6.048631 0   6.048631          . .
226 "Austria" 1  971.8  -8.119169 0  -8.119169          . .
227 "Austria" 1  958.6 -18.738327 0 -18.738327          . .
228 "Austria" 1  986.2  11.320118 0  11.320118          . .
229 "Austria" 1  978.5   5.996842 0   5.996842          . .
230 "Austria" 1  988.7  18.525442 0  18.525442          . .
231 "Austria" 1  997.1   29.23577 0   29.23577          . .
140 "Belgium" 2  736.9   8.233837 .          .   8.233837 .
141 "Belgium" 2    708  -20.25949 .          .  -20.25949 .
142 "Belgium" 2  708.9 -18.957958 .          . -18.957958 .
143 "Belgium" 2  725.1 -2.3540595 .          . -2.3540595 .
144 "Belgium" 2  738.3  11.271574 .          .  11.271574 .
145 "Belgium" 2  735.8  9.2397785 .          .  9.2397785 .
146 "Belgium" 2  746.1  20.064344 .          .  20.064344 .
147 "Belgium" 2  708.8  -16.64671 .          .  -16.64671 .
148 "Belgium" 2  715.8 -8.9979105 .          . -8.9979105 .
149 "Belgium" 2  745.1  21.016624 .          .  21.016624 .
150 "Belgium" 2  762.3    39.0084 .          .    39.0084 .
151 "Belgium" 2  767.4   44.97578 .          .   44.97578 .
end
format %tq Qtime
label values panelid panelid
label def panelid 1 "A Austria", modify
label def panelid 2 "A Belgium", modify

[Clyde Schechter]

Thank you for posting your data with -dataex-.

I can't replicate your problem. The only difficulty I encountered using the code from #2 to your example data in #3 is that some of the variable names now have upper-case letters. But once I fix that, it runs as expected. (Subject to the observation that you have only observations for Austria when period1 is 0 or 1, so no correlations are possible with no data from Belgium there; the only place you have data from both countries is when period1 = .--there you get the correlations.)

I think you are doing something different from what was shown in #2. Here is what I get from this data and the code with variable names corrected:

. keep Qtime country hp_A period1

. rename hp_A hp_A_

. reshape wide hp_A_, i(Qtime) j(country) string
(note: j = Austria Belgium)

Data                               long   ->   wide
-----------------------------------------------------------------------------
Number of obs.                      100   ->      92
Number of variables                   4   ->       4
j variable (2 values)           country   ->   (dropped)
xij variables:
                                  hp_A_   ->   hp_A_Austria hp_A_Belgium
-----------------------------------------------------------------------------

. 
. by period1, sort: pwcorr hp_A_*

--------------------------------------------------------------------------------------------------------------------------------
-> period1 = 0
no observations

--------------------------------------------------------------------------------------------------------------------------------
-> period1 = 1
no observations

--------------------------------------------------------------------------------------------------------------------------------
-> period1 = .

             | hp_A_A~a hp_A_B~m
-------------+------------------
hp_A_Austria |   1.0000 
hp_A_Belgium |  -0.0520   1.0000