topic Re: proc sql - how to get a "top" value per [variable]? in SAS Data Management

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

ucdcrush — Thu, 13 Feb 2014 23:55:23 GMT

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.

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

Jagadishkatam — Fri, 14 Feb 2014 02:16:29 GMT

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

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

TomKari — Fri, 14 Feb 2014 11:18:59 GMT

Another option:

proc sql;

create table want as

select patient, max(value) from have

group by patient;

quit;

Tom