Hello I want to select the rows having the maximum value of total score by secid.
I do this but do not works
proc sql;
create table max as
select distinct *
from CRSP_data3
having TOTAL_SCORE=max(TOTAL_SCORE)
order by secid;
run;
secid | Fund_name | Inception_date | CRSP_FUNDNO | FUND_NAME_CRSP | TOTAL_SCORE |
F000002KYY | JHancock Disciplined Value R4 | 2009-05-22 | 46807 | John Hancock Funds III: John Hancock Disciplined Value Fund; Class R3 Shares | 50 |
F000002KYY | JHancock Disciplined Value R4 | 2009-05-22 | 46808 | John Hancock Funds III: John Hancock Disciplined Value Fund; Class R4 Shares | 75 |
F000002KYY | JHancock Disciplined Value R4 | 2009-05-22 | 46809 | John Hancock Funds III: John Hancock Disciplined Value Fund; Class R5 Shares | 50 |
F000002KYY | JHancock Disciplined Value R4 | 2009-05-22 | 46811 | John Hancock Funds III: International Core Fund; Class R4 Shares | 25 |
F000002KYY | JHancock Disciplined Value R4 | 2009-05-22 | 46815 | John Hancock Investment Trust: John Hancock Fundamental Large Cap Core Fund; Class R4 Shares | 25 |
F000002KYY | JHancock Disciplined Value R4 | 2009-05-22 | 46815 | John Hancock Investment Trust: John Hancock Large Cap Equity Fund; Class R4 Shares | 25 |
F000002KYY | JHancock Disciplined Value R4 | 2009-05-22 | 46821 | John Hancock Funds III: US Core Fund; Class R4 Shares | 25 |
F000002KYY | JHancock Disciplined Value R4 | 2009-05-22 | 46823 | John Hancock Capital Series: John Hancock Classic Value Fund; Class R3 Shares | 25 |
F000002KYY | JHancock Disciplined Value R4 | 2009-05-22 | 46824 | John Hancock Capital Series: John Hancock Classic Value Fund; Class R4 Shares | 50 |
F000002KYY | JHancock Disciplined Value R4 | 2009-05-22 | 46825 | John Hancock Capital Series: John Hancock Classic Value Fund; Class R5 Shares | 25 |
You are missing GROUP BY
So-
proc sql;
create table max as
select *
from CRSP_data3
group by secid
having TOTAL_SCORE=max(TOTAL_SCORE)
order by secid;
quit;
You are missing GROUP BY
So-
proc sql;
create table max as
select *
from CRSP_data3
group by secid
having TOTAL_SCORE=max(TOTAL_SCORE)
order by secid;
quit;
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.