topic Re: find the bottom 20% of each by group in SAS Programming

find the bottom 20% of each by group

GreggB — Tue, 15 Oct 2019 16:58:56 GMT

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;

Re: find the bottom 20% of each by group

PaigeMiller — Tue, 15 Oct 2019 17:10:00 GMT

@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.

Re: find the bottom 20% of each by group

GreggB — Tue, 15 Oct 2019 17:14:40 GMT

Suppose there is 100 records in a group?

Re: find the bottom 20% of each by group

Reeza — Tue, 15 Oct 2019 17:20:01 GMT

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.