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;
@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.
Suppose there is 100 records in a group?
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.