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

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

hellohere — Tue, 08 Mar 2022 13:57:16 GMT

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;

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

PaigeMiller — Tue, 08 Mar 2022 14:02:07 GMT

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;

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

hellohere — Tue, 08 Mar 2022 14:11:44 GMT

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

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

PaigeMiller — Tue, 08 Mar 2022 14:32:24 GMT

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