find the bottom 20% of each by group

[GreggB]

OBJECTIVE: Find the bottom 20% in each grade at each school based on the elavss score. 

I sorted the data by school and grade and then ranked within each by group (not sure if ranking is helpful). I know that if east school has 80 students in grade 3, then I want to keep 80*20% = 16 records. Further, I know it will be the first 16 records since they are sorted low to high by elavss score. 

QUESTION: How do I capture those 16 records and then do the same for east school grade 4, west school grade 3, etc?

DATA

school  grade elavss ela_rank

east        3         100         1

east        3          121        2

east        4           200       1

east        4           222       2

west        3          101       1

west        3           105      2

west        3           143      3

west         4          167      1

west         4          189      2

CODE

proc sort data=ejoin;
by school grade_level;

proc rank data=ejoin ties=low out=ela_rank;
by school grade_level;
var elavss;
ranks ela_rank;
run;

proc sort data=ela_rank;
by school grade_level ela_rank;
run;

[PaigeMiller]

---

@GreggB wrote:

OBJECTIVE: Find the bottom 20% in each grade at each school based on the elavss score. 

I sorted the data by school and grade and then ranked within each by group (not sure if ranking is helpful). I know that if east school has 80 students in grade 3, then I want to keep 80*20% = 16 records. Further, I know it will be the first 16 records since they are sorted low to high by elavss score. 

QUESTION: How do I capture those 16 records and then do the same for east school grade 4, west school grade 3, etc?

---

This method of counting the first 16 records ignores ties, but if that doesn't bother you, and the data is already sorted, then

data want;
    set have;
    by school grade_level;
    if first.grade_level then count=0;
    count+1;
    if count<=16 then output;
run;

More generally, even if you don't know the total number of records in a group, and you want to control how to handle ties, then you could use PROC RANK with GROUPS=5, and then select the records where the rank = 1.

[GreggB]

Suppose there is 100 records in a group?

[Reeza · Accepted solution]

Add GROUPS = 5 to your PROC RANK statement to get the 20/40/60/80/100th percentiles. Take the group=0 as your lowest group.