## Average pairwise correlation

Solved
Occasional Contributor
Posts: 17

# Average pairwise correlation

Hi,

I'm trying to find the average pairwise correlation of the cash flows between all the segments in each company for each year.

For example, i would like to first find the correlation of cash flow between segment 1&2, 1&3, 2&3 for company A in 1997. I would then like to find the average of this 3 correlations.

I would like to repeat this step for each company each year. However, I have no idea as to which SAS code should I be using. Your help would be deeply appreciated. Thank You.

 Company Year Segment Cash Flow A 1997 1 200 A 1997 2 -250 A 1997 3 120 A 1998 1 400 A 1998 2 -111 A 1998 3 -20 B 1997 1 100 B 1997 2 -320 B 1997 3 120 B 1997 4 -200 B 1998 1 120 B 1998 2 -200 B 1998 3 360 B 1998 4 -210 C 1997 1 700 C 1997 2 230 C 1997 3 400

Accepted Solutions
Solution
‎09-01-2012 10:38 AM
Posts: 5,523

## Re: Average pairwise correlation

That can be done this way :

data have;
input Company \$ Year Segment CashFlow;
datalines;
A 1997 1 200
A 1997 2 -250
A 1997 3 120
A 1998 1 400
A 1998 2 -111
A 1998 3 -20
B 1997 1 100
B 1997 2 -320
B 1997 3 120
B 1997 4 -200
B 1998 1 120
B 1998 2 -200
B 1998 3 360
B 1998 4 -210
C 1997 1 700
C 1997 2 230
C 1997 3 400
;

proc sql;
create table comp as
select a.company, a.segment as s1, b.segment as s2, a.year,
a.cashFlow as c1, b.cashFlow as c2
from have as a
inner join have as b
on a.company=b.company and a.year=b.year and a.segment < b.segment
order by company, s1, s2, year;
quit;

proc corr data=comp pearson outp=pear(where=(_TYPE_="CORR")) noprint;
by company s1 s2;
var c1; with c2;
run;

proc sql;
create table want as
select company, mean(C1) as meanCorr
from pear
group by company;
drop table comp, pear;
select * from want;
quit;

PG

PG

All Replies
Solution
‎09-01-2012 10:38 AM
Posts: 5,523

## Re: Average pairwise correlation

That can be done this way :

data have;
input Company \$ Year Segment CashFlow;
datalines;
A 1997 1 200
A 1997 2 -250
A 1997 3 120
A 1998 1 400
A 1998 2 -111
A 1998 3 -20
B 1997 1 100
B 1997 2 -320
B 1997 3 120
B 1997 4 -200
B 1998 1 120
B 1998 2 -200
B 1998 3 360
B 1998 4 -210
C 1997 1 700
C 1997 2 230
C 1997 3 400
;

proc sql;
create table comp as
select a.company, a.segment as s1, b.segment as s2, a.year,
a.cashFlow as c1, b.cashFlow as c2
from have as a
inner join have as b
on a.company=b.company and a.year=b.year and a.segment < b.segment
order by company, s1, s2, year;
quit;

proc corr data=comp pearson outp=pear(where=(_TYPE_="CORR")) noprint;
by company s1 s2;
var c1; with c2;
run;

proc sql;
create table want as
select company, mean(C1) as meanCorr
from pear
group by company;
drop table comp, pear;
select * from want;
quit;

PG

PG
Occasional Contributor
Posts: 17

## Re: Average pairwise correlation

Hi PGStats,

Thanks for the help.

Comp  MeanCorr

 1034 0.629787 1045 0.470612 1056 -0.03342 1078 0.183483 1161 . 1209 0.115599 1230 0.498804 1239 0.535944

I tried your code on my data and the results look something like this. But i need to know the meancorr for each company each year (Below is an example). Is it possible? Thanks

 Comp MeanCorr Year 1034 0.629 1997 1034 0.456 1998 1045 0.4706 1997 1045 0.556 1998
Posts: 5,523

## Re: Average pairwise correlation

With a single observation per segment pair per year, I don't understand the concept of mean correlation per year.

PG

PG
Occasional Contributor
Posts: 17

## Re: Average pairwise correlation

Hi

please forgive my ignorance. you are right. thanks for the help!

Posts: 5,523

## Re: Average pairwise correlation

Perhaps, you could start a new discussion stating your goal in general terms. Someone might suggest an appropriate statistic for your problem.

PG

PG
🔒 This topic is solved and locked.