07-27-2016 11:25 AM - edited 07-27-2016 11:27 AM
get only last 3rd record from last record based on by group variable sex from sashelp.class dataset?
07-27-2016 11:42 AM
Since BY group processing depends on sort order you need to be more explicit. Sorted by what variable(s)?
And show exactly which record(s) you want.
07-27-2016 12:07 PM
i sorted sashelp.class based on sex variable.i want to get last 3 record each by group wise?
07-27-2016 12:25 PM
Here's one approach, not terribly fancy but realtively easy to understand.
proc sort data=sashelp.class out =tempclass; by sex; run; data temp; set tempclass; by sex; retain order .; if first.sex then order=1; else order+1; run; proc sort data=temp; by sex descending order; run; data want; set temp; by sex ; if first.sex then count=1; else count+1; if count le 3; run;
If you need the Want to retain the original order then sort by the included order variable.
Note: if the original input data set is ever re-sorted by other variables the results may not be reproducible.
07-27-2016 12:45 PM
My earlier post is messed up. The correct one is presenetd.
 The first solution is relatively easy. Use your sorted data and find the number of Females and Males(9,10) and the number of observations(19). It is better to create CLASS data set from SASHELP.CLASS for playing with it.
 From these the positions of 3rd for Females and Males are 7 and 17.
data class; set sashelp.class; run;
proc sort data = class; by sex; run; /** If Number of males and females are known in advance */ data _null_; do p = 7, 17; set class point = p; put _all_; end; stop; run;
If these numbers are not known then we need to find them by program.
data _null_; do count = 1 by 1 until(last.sex); set class; by sex; end; if sex = 'M' then call symputx('M_count', count); else call symputx('F_count', count); run; %put &M_count; %put &F_count;
Hope you are comfortable with Macro variables.
Then we are ready to go:
data _null_; total = &F_count + &M_count; M_pos = total - 2; do p = &F_count - 2, M_pos; set class point = p; put _all_; end; stop; run;
07-27-2016 11:17 PM
DOW + index skill: proc sort data=sashelp.class out=class; by sex; run; data want; do i=1 by 1 until(last.sex); set class; by sex; end; do j=1 by 1 until(last.sex); set class; by sex; if j=i-2 then output; end; drop i j; run;