Average pairwise correlation

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 17
Accepted Solution

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.

CompanyYearSegment Cash Flow
A19971200
A19972-250
A19973120
A19981400
A19982-111
A19983-20
B19971100
B19972-320
B19973120
B19974-200
B19981120
B19982-200
B19983360
B19984-210
C19971700
C19972230
C19973400

Accepted Solutions
Solution
‎09-01-2012 10:38 AM
Respected Advisor
Posts: 4,646

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

View solution in original post


All Replies
Solution
‎09-01-2012 10:38 AM
Respected Advisor
Posts: 4,646

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

10340.629787
10450.470612
1056-0.03342
10780.183483
1161.
12090.115599
12300.498804
12390.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

CompMeanCorrYear
10340.6291997
10340.4561998
10450.47061997
10450.5561998
Respected Advisor
Posts: 4,646

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!

Respected Advisor
Posts: 4,646

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 668 views
  • 0 likes
  • 2 in conversation