## Select row based on max value

Solved
Occasional Contributor
Posts: 11

# 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: 822

## 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

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

## 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: 23,700

## 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.