Programming the statistical procedures from SAS

want max of coulmn

Accepted Solution Solved
Reply
Contributor
Posts: 21
Accepted Solution

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,416

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;

View solution in original post


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

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 202 views
  • 0 likes
  • 3 in conversation