get only last 3rd record from last record based on by group variable sex from sashelp.class dataset

[praveenkotte]

get only last 3rd record from last record based on by group variable  sex from sashelp.class dataset?

[ballardw]

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.

[praveenkotte]

i sorted sashelp.class based on sex variable.i want to get  last 3 record each by group wise?

Ronald

M

15

67

133

Judy

F

14

64.3

90

Name	Sex	Age	Height	Weight
Alice	F	13	56.5	84
Barbara	F	13	65.3	98
Carol	F	14	62.8	102.5
Jane	F	12	59.8	84.5
Janet	F	15	62.5	112.5
Joyce	F	11	51.3	50.5
Judy	F	14	64.3	90
Louise	F	12	56.3	77
Mary	F	15	66.5	112
Alfred	M	14	69	112.5
Henry	M	14	63.5	102.5
James	M	12	57.3	83
Jeffrey	M	13	62.5	84
John	M	12	59	99.5
Philip	M	16	72	150
Robert	M	12	64.8	128
Ronald	M	15	67	133
Thomas	M	11	57.5	85
William	M	15	66.5	112

[ballardw]

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.

[KachiM]

 My earlier post is messed up. The correct one is presenetd.

[1] 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.

[2] 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;

[Ksharp]

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;