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 |
