:smileygrin: funny. There was a mistake in my decision, but it still works for your problem. Here step by step (so it'll be easier to check the correctness of all steps): Just copypaste and run it. DATA return; INPUT portid firmid myear return; DATALINES; 1 100 200301 2.11 1 120 200301 3.12 1 130 200301 2.13 1 140 200301 3.67 1 100 200302 5.23 1 120 200302 6.81 1 130 200302 2.55 1 140 200302 4.21 1 150 200302 3.21 2 300 200301 1.11 2 320 200301 0.12 2 330 200301 -2.13 2 340 200301 -1.67 2 300 200302 4.23 2 320 200302 3.81 2 330 200302 1.55 2 340 200302 2.21 2 350 200302 4.23 2 100 200302 5.23 3 56 200301 3.11 3 100 200301 2.11 3 763 200301 5.13 3 412 200301 4.67 3 124 200302 3.23 3 97 200302 3.81 3 195 200302 1.55 4 740 200302 0.21 4 250 200302 -1.21 4 812 200301 -1.11 4 823 200301 7.12 5 835 200301 -3.33 5 947 200301 -4.67 5 980 200302 4.23 6 620 200302 3.81 ; run; proc sql; create table portfolio_averages as select portid, myear, ( sum(S_portid) - S_portid ) as SUM_ret_other_portfolios, ( sum(N_portid) - N_portid ) as N_other_portid from ( select portid, myear, sum(return) as S_portid, count(return) as N_portid from return group by portid, myear ) group by myear order by myear, portid; quit; proc sql; create table Firms_averages as select firmid, myear, SUM(return) as SUM_firmid, count(return) as N_firmid from return group by myear, firmid order by myear, firmid; quit; /* here we add the return to the sum, because we extract it (it's used in SUM_firmid, while it shouldn't be used at all) */ proc sql; create table RETURN as select r.firmid, r.portid, r.myear, r.return, (p.SUM_ret_other_portfolios - f.SUM_firmid + r.return) / (p.N_other_portid - f.N_firmid + 1) as return_estim from return r left join Firms_averages f on r.firmid = f.firmid and r.myear = f.myear left join portfolio_averages p on p.myear = r.myear and p.portid = r.portid order by portid, myear, firmid; quit; If you wish so, as proposed by DanielSantos, you can integrate everything in one sql query. Just substitute the ( sql code ) of a certain table instead of it's alias in a final query, but as I mentioned above - it will be hard to check the correctness, while maximum momentary hard-disk space consumption would be the same. I checked it manually for several firmids, portfolios and years and still think - that this will solve your task.
... View more
