Search for identifier associated with maximum in SAS

Question

Let's say I have data such as:

ID_A    ID_B    ID_C    NUM_1
123     124     0       72
123     124     1       90
123     124     1       80

I would like to add two columns to my data. The first will select the maximum NUM_1 (grouped by ID_A, ID_B, ID_C), and the second will select the ID_C corresponding to the maximum NUM_1.

For this data, the answer will be

ID_A    ID_B    ID_C    NUM_1   MAX_1   MAX_ID
123     124     0       72      72      0
123     124     1       90      90      1
123     124     1       80      90      1

Using something like

PROC SQL;
CREATE TABLE WORK.TEST1 AS 
SELECT t1.ID_A, 
      t1.ID_B, 
      t1.ID_C, 
      t1.NUM_1,
      /* Max_1 */
        (MAX(t1.NUM_1)) FORMAT=13.2 AS MAX_1

  FROM WORK.TEST t1
  GROUP BY t1.ID_A, 
           t1.ID_B, 
           t1.ID_C 
  HAVING MAX(t1.NUM_1)
  ORDER BY t1.ID_A, 
           t1.ID_B, 
           t1.ID_C, 
           t1.NUM_1;
QUIT;

I can get the maximum repeating on the right lines, but it does not return me MAX_ID repeating on the right lines that I want.

I am new to SAS, but I have been trying to get this for some time, and I apologize if it is described elsewhere.

I use SAS EG by the way, so if you know how to do this using the data steps, I agree with that.

Thanks in advance for your help.

Answer 1

SAS having :

proc sql noprint;
  create table tallest as
  select name, height
  from sashelp.class
  having height = max(height)
  ;
quit;

Answer 2

SAS - PROC MEANS idgroup.

proc means data=sashelp.class noprint;
var height;
output out=tallest  idgroup(max(height) out (name height)=);
run;

, , ( , id).

Answer 3

This can be done using a correlated subquery .

The line below works with your sample data. It may need to be changed if there are special cases in your complete data. He will go after the sentence defining MAX_1.

(select distinct t2.ID_C from test as t2 where t1.NUM_1 = t2.NUM_1) as MAX_ID