want max of coulmn

[rkolupoti9001]

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

id_1	id_2	year_month
C123	97813	201209
C123	77980	201212
C234	56432	201212
C345	76556	201201
C345	97813	201209
C456	56432	201212

Want row only has max of year_month example:

id_1	id_2	year_month
c123	77980	201212
c234	56432	201212
c345	97813	201209
c456	56432	201212

[art297 · Accepted solution]

The following should work:

proc sql;

  create table want as

    select *

      from have

        group by id_1

          having year_month=max(year_month)

  ;

quit;

[sas_null_]

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;