Solved
Contributor
Posts: 21

# want max of coulmn

Hi i have a data format as below and all are characters

id_1id_2year_month
C12397813201209
C12377980201212
C23456432201212
C34576556201201
C34597813201209
C45656432201212

Want row only has max of year_month example:

id_1id_2year_month
c12377980201212
c23456432201212
c34597813201209
c45656432201212

Accepted Solutions
Solution
‎11-21-2014 11:27 AM
PROC Star
Posts: 7,686

## Re: want max of coulmn

The following should work:

proc sql;

create table want as

select *

from have

group by id_1

having year_month=max(year_month)

;

quit;

All Replies
Solution
‎11-21-2014 11:27 AM
PROC Star
Posts: 7,686

## Re: want max of coulmn

The following should work:

proc sql;

create table want as

select *

from have

group by id_1

having year_month=max(year_month)

;

quit;

Occasional Contributor
Posts: 14

## Re: want max of coulmn

You can also try this approach

PROC SQL;

CREATE TABLE WANT AS

SELECT t1.id_1,

t2.id_2,

t2.year_month

FROM (SELECT id_1,

/* max_year */

(MAX(INPUT(year_month,6.))) AS max_year_month

FROM HAVE

GROUP BY id_1) t1, (SELECT id_1,

id_2,

year_month,

/* year_month numeric */

(INPUT(year_month,6.)) AS year_month_num

FROM HAVE) t2

WHERE (t1.id_1 = t2.id_1 AND t1.max_year_month = t2.year_month_num);

QUIT;

🔒 This topic is solved and locked.