Hi , you can try this one also as it will also help you to get 2nd highest sum by category also: DATA TEST; INPUT ID SAL; CARDS; 1 200 2 300 3 400 1 600 2 800 3 600 1 800 2 600 3 500 ; RUN; PROC SORT DATA =TEST ; BY ID; RUN; DATA TEST1; SET TEST; BY ID; RETAIN TOT 0 CN 0 ; IF FIRST.ID THEN TOT=SAL; ELSE TOT=SUM(TOT,SAL); IF FIRST.ID THEN CN=1; ELSE CN=CN+1; RUN; PROC SORT DATA =TEST1 ; BY ID; RUN; DATA TEST2; SET TEST1 ; BY ID ; if last.id then output test2; RENAME CN=CNT TOT=SUM_TOT; RUN; data test3(DROP=TOT CN); MERGE test2 test1; by id; tot_avg=SUM_TOT/cnt; run;
... View more
