Please supply example data against which we can test code. See my footnotes for hints about posting data and code.
If this is some kind of homework, you should be able to solve the problem yourself, if your teacher is worth the money.
Again, please present a full question, with text in the body of the post. Present test data in the form of a datastep, and show what you want as output.
If you have the answer as datastep, please clarify the need for sql. It is a far simpler problem in datastep, than in sql.
If you already have a working data step solution, why do you want to do it in PROC SQL?
SQL is not right tool to do it .
data have;
set sashelp.class;
score=weight;
keep sex score;
run;
proc sql;
create table want as
select *
from (
select * from have group by sex having score ne max(score)
)
group by sex
having score=max(score);
quit;
Ksharp,
I agree it's not a right tool. But your SQL code still gets there - under two conditions. First, unless the specs say "I want all the records with the second highest score", a DISTINCT would be needed in the outer query to weed out possible ties. Second, if a key group has but a single record, your code will filter it out. Again, it may be just what's required, but a different set of specs may say that it should be in the output. In this case, a good SQL head may still be able to adjust for it at the expense of more convoluted code, but then it would only reinforce your point about choosing an appropriate tool.
Best
Paul D.
data have;
set sashelp.class;
score=weight;
keep sex score;
run;
proc sql;
create table want as
select *,(select count(*) from (select distinct sex,score from have)
where sex=a.sex and score>=a.score) as rank
from have as a
where calculated rank=2;
quit;
The SAS code you recommend working perfectly. Thank you so much. I just wanna say that this one resolved my important problem.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.