topic Re: Select all observations that their weight=Second largest weight in SAS Programming

Select all observations that their weight=Second largest weight

Ronein — Sat, 28 Oct 2023 14:35:31 GMT

Hello

The following query select one observation that its weight value is second highest.

Data class;
input Cust_ID  weight age;
1 59 20
2 61 25
3 95 40
4 92 25
5 51 18
6 92 21
7 50 43
8 60 23
9 70 71
10 92 62
;
Run;

Data class;
set sashelp.class;
Run;

proc sql outobs = 1;
    select name, weight 
    from class
    where weight not in (select max(weight) from class)
    order by weight desc
;quit;

My question-

Let's say that there are multiple observations with weight=second highest value and I want to see these observations. What is the way to do it in one step? (So in this example I will see 3 observations since there are 3 observations with weight=92)

Re: Select all observations that their weight=Second largest weight

PaigeMiller — Sat, 28 Oct 2023 14:56:41 GMT

Use PROC RANK.

Re: Select all observations that their weight=Second largest weight

Ronein — Sat, 28 Oct 2023 15:50:12 GMT

May you please show the full code

Re: Select all observations that their weight=Second largest weight

PaigeMiller — Sat, 28 Oct 2023 16:33:40 GMT

Please look at the examples at the link I gave. One of the examples is analogous to your problem, only trivial changes are needed to use that example in your situation.

Re: Select all observations that their weight=Second largest weight

Ksharp — Sun, 29 Oct 2023 08:59:34 GMT

/*
It is not right tool by using SQL.
Why not try data step ?
*/
Data class;
input Cust_ID  weight age;
cards;
1 59 20
2 61 25
3 95 40
4 92 25
5 51 18
6 92 21
7 50 43
8 60 23
9 70 71
10 92 62
;
Run;

proc sql;
select * 
 from (select * from class except select * from class having weight= max(weight))
   having weight= max(weight)
;quit;

Re: Select all observations that their weight=Second largest weight

mkeintz — Sun, 29 Oct 2023 22:32:52 GMT

A data step can pass through the data once to establish the MAX and 2ND highest values. It can then re-read the data and keep those obs that match the second.

data want  (drop=_:);
  set have (in=firstpass)  have (in=secondpass);

  retain _2ND _MAX;
  array  w{2} _2ND _MAX; 
  
  if firstpass then do;      /*Update array W, if needed*/
    if weight^=_MAX and weight>_2ND then do;
      _2ND=weight;
      call sortn(of w{*});
    end;
  end;
  if secondpass and weight=_2ND;
run;

Re: Select all observations that their weight=Second largest weight

Patrick — Sun, 29 Oct 2023 23:46:20 GMT

As proposed by @PaigeMiller

data have;
  input Cust_ID  weight age;
  datalines;
1 59 20
2 61 25
3 95 40
4 92 25
5 51 18
6 92 21
7 50 43
8 60 23
9 70 71
10 92 62
;

proc rank data=have ties=dense descending out=want(where=(rank=2));
  ranks rank;
  var weight;
run;

proc print data=want;
run;

Re: Select all observations that their weight=Second largest weight

PaigeMiller — Mon, 30 Oct 2023 09:51:13 GMT

In the long run, I think SAS users are better off using built-in SAS capabilities (like PROC RANK) rather than writing their own DATA step code or SQL code to achieve the same thing. SAS has done the hard job of writing this code, making it robust to different possible problems including handling of missing values, debugging their code and testing it, and then SAS's code is proved to work in many real-world applications. In addition, if the problem changes to keeping the 5th largest ... trivial changes are needed to the PROC RANK code; I'm not sure the changes are trivial to DATA step or SQL solutions.

Re: Select all observations that their weight=Second largest weight

mkeintz — Mon, 30 Oct 2023 22:27:02 GMT

@PaigeMiller wrote:

In the long run, I think SAS users are better off using built-in SAS capabilities (like PROC RANK) rather than writing their own DATA step code or SQL code

...

In addition, if the problem changes to keeping the 5th largest ... trivial changes are needed to the PROC RANK code; I'm not sure the changes are trivial to DATA step or SQL solutions.

In general (and in this case), I agree with this sentiment. But with my clients/customers, I often find it's a good idea to anticipate a sort of mission creep, where the initial simple request (as in this topic) is expanded to include other objectives that won't be satisfied by the purpose-built SAS PROC.

In this particular case, changing the problem to the N'th largest would need only a trivial modification of the DATA step solution, but (I believe) a real headache for the PROC SQL approach.

Re: Select all observations that their weight=Second largest weight

PaigeMiller — Tue, 31 Oct 2023 11:43:50 GMT

Yes, "mission creep" can affect the programming that you do, its often a good thing to plan for it. On the other hand, for programmers who just want to learn how to do some task in SAS, that's not an issue.