topic Re: get only last 3rd record from last record based on by group variable sex from sashelp.class dat in SAS Programming

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

praveenkotte — Wed, 27 Jul 2016 15:27:03 GMT

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

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

ballardw — Wed, 27 Jul 2016 15:42:01 GMT

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.

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

praveenkotte — Wed, 27 Jul 2016 16:07:08 GMT

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

Ronald

133

Judy

64.3

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

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

ballardw — Wed, 27 Jul 2016 16:25:29 GMT

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.

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

KachiM — Wed, 27 Jul 2016 16:45:04 GMT

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;

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

Ksharp — Thu, 28 Jul 2016 03:17:53 GMT

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;