SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

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

Accepted Solution Solved
Reply
Contributor
Posts: 38
Accepted Solution

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

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.


Accepted Solutions
Solution
‎02-13-2014 09:16 PM
Trusted Advisor
Posts: 1,137

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

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

Thanks,
Jag

View solution in original post


All Replies
Solution
‎02-13-2014 09:16 PM
Trusted Advisor
Posts: 1,137

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

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

Thanks,
Jag
PROC Star
Posts: 1,167

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

Another option:

proc sql;

create table want as

select patient, max(value) from have

group by patient;

quit;

Tom

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 323 views
  • 3 likes
  • 3 in conversation