Getting Mean Correlations

[ericdrosano]

My data looks like:

I'm trying to do the following:

"The correlations of OX of each member firm are determined with every other member firm in the same SIC3 group, by firm, over the five-year period."

I have tried pairwise matching by SIC3YEAR, and this successfully gives me the following :

BUT I can't seem to get correlations between each individual firm-year and their SIC3 peers, which I believe I need in order to sum the SIC3 total, before then dividing by n.

I have used so many variations of proc corr with ox and p_ox and even done the grouping before pairwise matching, but I am doing something wrong.

Code help is greatly appreciated; thank you!

[PaigeMiller]

To get correlations from PROC CORR, each firm would have to have their data in its own column, rather than the layout you have shown where (I assume) all the data is in one great big long data set (but you really show us only one time period, so how would I know?) If so, you could use PROC TRANSPOSE to get the data in the desired format. Of course, we can't work from screen captures, please provide a portion of your data (with at least 3 time periods) as SAS data step code (instructions).

I'm not really a good person to talk about time-series analyses, but I think there might be a SAS/ETS procedure that can compute correlations with your long data sets. But I can't be more specific.

[ericdrosano]

The dataset in the first image does contain each firm's data is in their own columns. The dataset in the second image is the pairwise combination of each firm, each year with each of their SIC3 peers in that year. Thus, all the data in the dataset the first image is what I use to pairwise match to create the dataset in the second image.
As far as uploading data, I may be able to once SAS has completed running my current code, which is already about 10 minutes in and I expect will take much longer.

[PaigeMiller]

The dataset in the first image does contain each firm's data is in their own columns.

Show us. And provide the data in a usable form, as I described.