turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- General Programming
- /
- Average pairwise correlation

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

08-31-2012 11:29 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

09-01-2012 10:38 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

09-01-2012 10:38 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

09-04-2012 12:27 AM

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 |

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

09-04-2012 09:51 AM

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

PG

PG

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

09-04-2012 10:26 AM

Hi

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

09-04-2012 04:15 PM

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

PG

PG