Here's the scheme of calculation which could be useful.
The key idea is that calculating averages is easier than producing cartesian join.
Example:
firmid=100 in portid=2 and myear=200302 (observation # 19)=(6.81 + 2.55 + 4.21 + 3.21)/4=
here you can first calculate average for ret for all firmids.
Let us assume, that we have firmid = 100 for portid = 1 and myear = 200302 with some ret value R.
A = (6.81 + 2.55 + 4.21 + 3.21 + R)/5
N = 5 (amount of values used)
Now, as we now that R shouldn't be used for calculating real average (let's denote it B) we can do such a thing
B = (A * N - R) / (N - 1);
Now the real task is harder, since we can have more that 2 portids (but at the same time we assume that there is no entries with different ret for the same firmid portid myear), and we need to calc averages over different portids. But it's quite solveable in the same manner.
So my proposal is to use this scheme:
1. Calculate averages and count (or sum and count) for each group of
proc sql;
create table Averages as
select
portid as pid,
myear,
avg(ret) as A_pid,
count(ret) as N_pid
from
return
group by
portid, myear
order by
portid, myear;
quit;
/* Now! if we need to have an average over different portid which is not equal to source (for example for firmid=100 and portid=1 there are different firms for portid = 2,3,4,5,... ; but we need the result without grouping by this portid's, then we need averages over all portid's within the same year */
proc sql;
create table Averages as
select
pid,
myear,
A_pid,
N_pid,
sum( A_pid * N_pid ) / sum (N_pid) as total_average /* that is just total average of all ret's; you don't need it, just to point the meaning */,
(sum( A_pid * N_pid ) - A_pid * N_pid ) / ( sum (N_pid) - N_pid ) as AVG_RET_for_other_pids_same_year,
sum (N_pid) - N_pid as NUM_of_terms_in_avg
from
Averages
group by
myear
order by
pid, myear;
quit;
/* yes, the grouping is only within myear and it will work, and quite fast */
2. Now, to get ret2 for each firmid we need to make "an exclusion list" for each firmid.
As by calculating the target ret2 we don't need values of firmid at all, we calculate SUM (this will help to simplify formulas) and amount:
proc sql;
create table Firmid_averages as
select
firmid as fid,
myear,
SUM(ret) as R_fid,
count(*) as N_fid
from
return
group by
firmid, myear;
quit;
3. Finally, get ret2 (which you can LEFT join to source table after that, in order to compare ret and ret2)
proc sql;
create table RETURN2 as
select
f.fid as firmid,
p.pid as potid,
f.myear,
(p.AVG_RET_for_other_pids_same_year * p.NUM_of_terms_in_avg - f.R_fid) / (p.NUM_of_terms_in_avg - f.N_fid) as RET2
from
Firmid_averages f left join
Averages p on
f.myear = p.myear
order by
portid, myear, firmid;
quit;
4. Left join this to your source if you need to see the difference between ret and ret2.
... well, the code is a bit entangled. Actually, everything could be slightly simplified if you will use sums instead of averages. But I was afraid that the idea will not be clear from the simple code. So... I used this.
