proc sql - how to get a "top" value per [variable]?

[ucdcrush]

Greetings all,

dataset:

patient  value

1          99

1          100

2          300

2          200

2          100

3          500

3          200

would like to get the following, using proc sql:

patient   value

1           100

2           300

3           500

Normally if I wanted to do this, I would use proc sort by patient then descending value, then use a "if first.patient" then output, in order to get the max value. I'm thinking there may be an easier way to do this in one swoop using proc SQL.

Any help is appreciated, thank you.

[Jagadishkatam · Accepted solution]

Please try the below code

proc sql;

  create table want as select a.* from have as a left join have as b on (a.patient=b.patient and a.value le b.value) group by a.patient,a.value

  having count(*) <=1 order by a.patient,  a.value desc;

quit;

Output:

Thanks,

Jag

[TomKari]

Another option:

proc sql;

create table want as

select patient, max(value) from have

group by patient;

quit;

Tom