select max value by id

sasphd — Wed, 04 Nov 2020 18:39:15 GMT

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

Re: select max value by id

novinosrin — Wed, 04 Nov 2020 18:43:36 GMT

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;