DATA Step, Macro, Functions and more

Select row based on max value

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 8
Accepted Solution

Select row based on max value

DATA TEST;
INPUT CUST A B C;
CARDS;
1 12 13 14
1 12 13 11
2 12 10 14
2 12 17 14
3 10 16 11
3 10 13 14
4 12 11 14
4 12 11 14
;
RUN;

 

/*the objective is to get aggregated max values per cust, and resulting one column which have max of a,b and c*/

 

/*this codes gives var wise aggregated max values but when i try to find for all a,b,c it gives error*/

PROC SQL;
CREATE TABLE ANA AS
SELECT CUST
,MAX(A) as max_a
,MAX(B) as max_b
,MAX(C) as max_c
from test
group by 1
;quit;

 

PROC SQL;
CREATE TABLE ANA AS
SELECT CUST
,MAX(max_a,max_b,max_c) as max_abc
from test
;quit;


Accepted Solutions
Solution
‎09-01-2016 05:32 AM
SAS Super FREQ
Posts: 708

Re: Select row based on max value

In order to reference new columns in SQL you can use the CALCULATED keyword

 

See sample below:

PROC SQL;
  CREATE TABLE ANA AS
    SELECT CUST
      ,MAX(A) as max_a
      ,MAX(B) as max_b
      ,MAX(C) as max_c
      , max(calculated max_a,calculated max_b,calculated max_c) as maxValue 
    from test
      group by 1
  ;
quit;

But one could run another query on the ANA table to create the max column

 

Bruno

View solution in original post


All Replies
Solution
‎09-01-2016 05:32 AM
SAS Super FREQ
Posts: 708

Re: Select row based on max value

In order to reference new columns in SQL you can use the CALCULATED keyword

 

See sample below:

PROC SQL;
  CREATE TABLE ANA AS
    SELECT CUST
      ,MAX(A) as max_a
      ,MAX(B) as max_b
      ,MAX(C) as max_c
      , max(calculated max_a,calculated max_b,calculated max_c) as maxValue 
    from test
      group by 1
  ;
quit;

But one could run another query on the ANA table to create the max column

 

Bruno

Super User
Posts: 19,770

Re: Select row based on max value

Select cust, max(a) as maxA, max(b) as maxB, max(C) as MaxC, max( calculated maxA, calculated maxB, calculated maxC) as overallMax

from have

group by cust;

 

☑ This topic is solved.

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

Discussion stats
  • 2 replies
  • 595 views
  • 2 likes
  • 3 in conversation