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.
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
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
Another option:
proc sql;
create table want as
select patient, max(value) from have
group by patient;
quit;
Tom
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.