DATA Step, Macro, Functions and more

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

Reply
Occasional Contributor
Posts: 13

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

[ Edited ]

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

 

 

Super User
Posts: 10,500

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

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.

Occasional Contributor
Posts: 13

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

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

 

Ronald

M1567133
JudyF1464.390

 

 

NameSexAgeHeightWeight
AliceF1356.584
BarbaraF1365.398
CarolF1462.8102.5
JaneF1259.884.5
JanetF1562.5112.5
JoyceF1151.350.5
JudyF1464.390
LouiseF1256.377
MaryF1566.5112
AlfredM1469112.5
HenryM1463.5102.5
JamesM1257.383
JeffreyM1362.584
JohnM125999.5
PhilipM1672150
RobertM1264.8128
RonaldM1567133
ThomasM1157.585
WilliamM1566.5112
Super User
Posts: 10,500

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

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.

Super Contributor
Posts: 254

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

 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;

 

 

 

Super User
Posts: 9,681

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

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;


Ask a Question
Discussion stats
  • 5 replies
  • 258 views
  • 1 like
  • 4 in conversation