Max by group + keep variables

[fre]

Dear community,

I have this dataset to start from:

case  /   name   /    valueX

001  /  A   /   100

002  /  A   /    80

002  /  B   /    20

003  /  A   /    20

003  /  B   /    50

003  /  C   /    30

What I need is for each case, the maximum for valueX, but the output need also to have the column "name".

So, I want:

001 / A /  100

002 / B /  80

003 / B /  50

What I tried:

proc sql;

create table want as

select case, name, max(valueX) as max_value

from have

group by case;

quit;

but this results in:

001  /  A  /  100

002  /  A  /  80

002  /  B  /  80

003  /  A   /    50

003  /  B   /    50

003  /  C   /    50

What am I doing wrong?

[gamotte]

Hello,

GROUP BY only indicates which observations are taken into account when calculating the max

but you still keep all observations. You have to add a where close on the condition

valueX=max(valueX)

[RW9]

@gamotte has answered the SQL part, you could also just do a sort and datastep (an be included in other code):

proc sort data=have out=want;
  by case name descending valuex;
run;
data want;
  set have;
  by case name;
  if first.name;
run;

[FredrikE · Accepted solution]

Try the having clause:

data one;

length cas name $32 valueX 8;

input cas name valueX;

datalines;

001 A 100

002 A 80

002 B 20

003 A 20

003 B 50

003 C 30

;

quit;

proc sql;

create table two as

select cas, name, max(valueX) as max_valueX

from one

group by cas

having valueX = max_valueX

;

quit;

//Fredrik

[fre]

Hi FredrikE, gamotte, RW9,

thank you all very much for your answers.  There are more possibilities, but I can only mark 1 as the solution, so I picked the one closest to my original code.