我现在有两张表,一张是N只股票分行业的收入数据,一张是SAS计算的各行业两两的相关系数,现在我想按照行业收入占比加权计算出一个加权相关系数,比如:股票000002有两个行业A和B,收入占比分别为0.8和0.2,而A与B的相关系数是0.6,则加权相关系数是0.8*0.2*0.6, 因为数据很多,所以觉得用程序做应该会比较简单,求指导~~~
附Excel
| stkcd | year | industry | sales_percent |
000002 | 2000/12/31 | 行业B | 0.64 |
000002 | 2000/12/31 | 行业A | 0.36 |
| 000002 | 2001/12/31 | 行业B | 0.78 |
000002 | 2001/12/31 | 行业A | 0.22 |
| 000002 | 2002/12/31 | 行业B | 0.97 |
000002 | 2002/12/31 | 行业C | 0.03 |
| 000004 | 2000/12/31 | 行业D | 0.03 |
000004 | 2000/12/31 | 行业E | 0.97 |
000004 | 2001/12/31 | 行业D | 1.00 |
000004 | 2002/12/31 | 行业E | 1.00 |
year | _TYPE_ | _NAME_ | 行业A | 行业B | 行业C | 行业D | 行业E |
2001 | MEAN | | -0.057818833 | -0.051693 | -0.0511015 | -0.037942833 | -0.050080333 |
2001 | STD | | 0.080045178 | 0.084213884 | 0.058385552 | 0.053219241 | 0.051143235 |
2001 | N | | 6 | 6 | 6 | 6 | 6 |
2001 | CORR | 行业A | 1 | 0.894106487 | 0.944918184 | 0.779096711 | 0.805572161 |
2001 | CORR | 行业B | 0.894106487 | 1 | 0.830976359 | 0.644248051 | 0.731728807 |
2001 | CORR | 行业C | 0.944918184 | 0.830976359 | 1 | 0.826699784 | 0.940412722 |
2001 | CORR | 行业D | 0.779096711 | 0.644248051 | 0.826699784 | 1 | 0.813968663 |
2001 | CORR | 行业E | 0.805572161 | 0.731728807 | 0.940412722 | 0.813968663 | 1 |
| | | | | | | |
2002 | N | | 12 | 12 | 12 | 12 | 12 |
2002 | CORR | 行业A | 1 | 0.937351508 | 0.958910062 | 0.966488781 | 0.963866254 |
2002 | CORR | 行业B | 0.937351508 | 1 | 0.932497985 | 0.934583321 | 0.902044024 |
2002 | CORR | 行业C | 0.958910062 | 0.932497985 | 1 | 0.982581724 | 0.987307329 |
2002 | CORR | 行业D | 0.966488781 | 0.934583321 | 0.982581724 | 1 | 0.973683992 |
2002 | CORR | 行业E | 0.963866254 | 0.902044024 | 0.987307329 | 0.973683992 | 1 |