CODEWITHSUNDEEP
sas
Sinbad Sailor
Sinbad Sailor

Reputation: 57

SAS Keep maximum value by ID

Each ID has several instances, and each instance has a different value. I would like the final output to be the maximum value per ID. So the initial dataset is:

 ID     Value
 1      100
 1      7
 1      65
 2      12
 2      97
 3      82
 3      54

And the output will be:

 ID     Value
 1      100
 2      97
 3      82

I tried running proc sort twice thinking that the first sort would get things in the proper order so that nodupkey on the second sort would get rid of the right values. This did not work.

 proc sort work.data; by id value descending; run;
 proc sort work.data nodupkey; by id; run;

Thanks!

Upvotes: 2

Views: 7836

Answers (2)

Joe
Joe

Reputation: 63424

My preferred solution:

proc means data=have noprint;
  class id;
  var value;
  output out=want max(value)=;
run;

Should be a lot faster than two sorts.

Upvotes: 2

Robert Penridge
Robert Penridge

Reputation: 8513

Your approach should have worked fine but it looks like you have a syntax error - did you forget to check your log? The descending keyword needs to go before the variable you want to sort in descending order.

proc sort data=sashelp.class out=tmp;
  by sex descending height;
run;

proc sort data=tmp out=final nodupkey;
  by sex;
run;

Also - in case you're not familiar with SQL, I strongly suggest that you should learn it as it will simplify many data manipulation tasks. This can also be solved in a single SQL step:

proc sql noprint;
  create table want as
  select sex,
         max(height) as height
  from sashelp.class
  group by sex
  ;
quit;

Upvotes: 3

Related Questions