topic Re: Select row based on max value in SAS Programming

Select row based on max value

farrukh — Thu, 01 Sep 2016 09:19:10 GMT

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;

Re: Select row based on max value

BrunoMueller — Thu, 01 Sep 2016 09:28:04 GMT

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

Re: Select row based on max value

Reeza — Thu, 01 Sep 2016 09:28:26 GMT

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;