Reeza, I used your code for my real data which contains 2717655 observations. I got "ERROR SORT EXECUTION FAILURE" my guess is sas ran out of memory. my thought is to break the data into smaller sets by industry id, then run your code, after that, merge all the data back into a big dataset. Do you have an efficient way of doing this? this is the part of the code I got error - my guess is sas ran out of memory, it look over an hour for sas to run. * note: id=substr(put(sic,4.),1,3); PROC SQL; CREATE TABLE OUT AS SELECT A.FIRM_ID, A.SIC, A.ID, A.YEAR, A.RETURN, AVG(B.RETURN) AS AVG_RETURN FROM HAVE AS A LEFT JOIN HAVE AS B ON A.ID=B.ID AND A.YEAR=B.YEAR AND A.FIRM_ID NE B.FIRM_ID GROUP BY A.FIRM_ID, A.SIC, A.ID, A.YEAR, A.RETURN; QUIT; thanks very much, -Lan
... View more