Correlations: one variable, many groups

crsp2021 — Mon, 29 Nov 2021 04:15:18 GMT

I am trying to compute correlations between returns to many firms. A piece of data in SAS format is in the table below (permno is a firm identifier) - of course, I have more years and lots of firms (permno's) that enter and exit the sample.

What I am trying to do is to create a correlation matrix of firm returns for each year. The obvious solution is just to create a new return variable for each firm (return1 for return to permno=10001, return2 for permno=10002); then merge tiny files (year, month, returnX) by year month; then do proc corr with a by year clause. The downside is that the loop to do that runs for a long time and, like I said, firms enter and exit the sample.

Is there a short-cut? I tried proc mixed, but could not figure out how to make it deliver just a plain correlation matrix I described above, without filtering out any fixed/random effects.

permno	year	month	return
10001	2000	1	-4.412
10001	2000	2	1.538
10001	2000	3	-1.576
10001	2000	4	1.172
10001	2000	5	-2.317
10001	2000	6	2.767
10001	2000	7	-1.563
10001	2000	8	4.762
10001	2000	9	7.576
10001	2000	10	2.857
10001	2000	11	6.25
10001	2000	12	3.268
10002	2000	1	-2.564
10002	2000	2	-16.316
10002	2000	3	2.893
10002	2000	4	1.235
10002	2000	5	-2.439
10002	2000	6	4.125
10002	2000	7	-1.212
10002	2000	8	-1.84
10002	2000	9	-25.25
10002	2000	10	16.949
10002	2000	11	1.812
10002	2000	12	5.765

topic Correlations: one variable, many groups in Statistical Procedures

Correlations: one variable, many groups