How to get the average, excluding top 10%, by group with SAS/SQL?!

[hellohere]

Sample data below, say, here is 100 rows/group, need to get the average of X in each group with excluding

the top 10 values?! 

data temp; do i=1 to 1000; grp=floor((i-1)/100)+1; x=ranuni(i); output; end; run;quit;

[PaigeMiller · Accepted solution]

Don't use SQL for this, use PROC RANK (Maxim 14: Use the right tool). The benefit is that PROC RANK makes this simple, and furthermore gives you several options for handling ties.

proc rank data=temp groups=10 out=deciles;
    by grp;
    var x;
    ranks decile;
run;

proc means data=deciles;
    by grp;
    where decile < 9;
    var x;
run;

[hellohere]

Thanks a huge. Anyway to do a list of variables, and save out the means into a dataset?!

[PaigeMiller]

Yes, you can use variable lists in both PROC RANK and PROC MEANS. PROC MEANS also allows you to create output data sets.