BookmarkSubscribeRSS Feed
praveenkotte
Fluorite | Level 6

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

 

 

5 REPLIES 5
ballardw
Super User

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
Fluorite | Level 6

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
ballardw
Super User

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
Rhodochrosite | Level 12

 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
Super User
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;


sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 1591 views
  • 1 like
  • 4 in conversation