BookmarkSubscribeRSS Feed
Arun_shSAS
Fluorite | Level 6
data a;
input id sal;
cards;
1 10
1 20 
1 40 
1 35 
1 5
2 3 
2 6
2 4 
2 78
;
run;

* I want within each distinct group of id I want 3rd largest sal value using monotonic();
6 REPLIES 6
ChrisNZ
Tourmaline | Level 20

Why monotonic()?

SQL is just not good at considering rows in order, unless ORDER BY is used.

This does what you want:

proc sql;
  select a.ID, a.SAL, count(*) as ORDER 
  from HAVE a
      ,HAVE b
  where a.ID=b.ID and a.SAL<=b.SAL
  group by 1,2
  having ORDER=3;
quit;
ID SAL ORDER
1 20 3
2 4 3

 

KachiM
Rhodochrosite | Level 12

If you want a Data Step Solution ...

Data Set A is assumed to be sorted by ID. Assumed that within ID there will not be  more than 100 SAL values. Used Array to store SALs for each ID. Use LARGEST function to get your Kth ORDERED Value from the Array. Clean the Array and process the next ID as above.

data a;
input id sal;
cards;
1 10
1 20 
1 40 
1 35 
1 5
2 3 
2 6
2 4 
2 78
;
run;

data want;
   array k[100] _temporary_;
   do i = 1 by 1 until(last.id);
      set a;
      by id notsorted;
      k[i] = sal;
   end;
   third_Largest = Largest(3, of k[*]);
   put third_largest =;
   call missing(of k[*]);
run;

Cheers.

DATASP

Kurt_Bremser
Super User

@Arun_shSAS wrote:
data a;
input id sal;
cards;
1 10
1 20 
1 40 
1 35 
1 5
2 3 
2 6
2 4 
2 78
;
run;

* I want within each distinct group of id I want 3rd largest sal value using monotonic();

Basic data step solution:

proc sort data=a;
by id descending sal;
run;

data want;
set a;
by id;
if first.id
then count = 1;
else count + 1;
if count = 3;
drop count;
run;
RW9
Diamond | Level 26 RW9
Diamond | Level 26
%let nth=3;

data a;
  input id sal;
cards;
1 10
1 20 
1 40 
1 35 
1 5
2 3 
2 6
2 4 
2 78
;
run;

proc sort data=a;
  by id descending sal;
run;

data want;
  set a;
  retain cnt;
  by id;
  cnt=ifn(first.id,1,cnt+1);
  if cnt=&nth.;
run;

Combine that with any other processing.

Patrick
Opal | Level 21

@Arun_shSAS

monotonic() is an undocumented and unsupported function which you shouldn't use at all.

mkeintz
PROC Star

You might use monotonic() to examine some of the behavior of sql, but I wouldn't recommend it for work you rely on.

 

Neither sql nor the data step is the most efficacious solution.  I'd recommend proc rank (assuming data are sorted by id):

 

proc rank data=have out=want (where=(salrank=3)) descending;
  by id;
  var sal;
  ranks salrank;
run;

 

If the data were already sorted by descending salary within id, then the data step would be easiest.  But if the within-id records have any other order, proc rank is a very direct solution.

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------

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
  • 6 replies
  • 2396 views
  • 4 likes
  • 7 in conversation