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;
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;
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;
Thanks a huge. Anyway to do a list of variables, and save out the means into a dataset?!
Yes, you can use variable lists in both PROC RANK and PROC MEANS. PROC MEANS also allows you to create output data sets.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.