Max of a column

Reply
Occasional Contributor
Posts: 14

Max of a column

Hi How do I know which record has the max amount in a specific column?

Thanks.

- Matt

Trusted Advisor
Posts: 1,932

Re: Max of a column

PROC MEANS with the MAXID output statistic tells you.

See Example 11 of the PROC MEANS documentation.

Super Contributor
Posts: 307

Re: Max of a column

Posted in reply to PaigeMiller

Proc Univariate also works.

Other approaches that spring to mind:

* sort by amount descending -- the first record will be the max

* match merge in a data step

* proc sql

Respected Advisor
Posts: 3,156

Re: Max of a column

Add another two: Hash() and Proc Rank .

Respected Advisor
Posts: 3,799

Re: Max of a column

Posted in reply to PaigeMiller

To expand on suggestion to use the features of PROC MEANS/SUMMARY the "new" IDGROUP OUTPUT statement option takes MINID|MAXID to a whole new level.

Trusted Advisor
Posts: 1,228

Re: Max of a column

Hi Matt,

Try this one for columns A and B. I've tried this for column C.

data have;

input A B C;

cards;

1 23 12

3 2 21

7 5 25

;

proc sort data=have out=col_sort;

by desending C;

run;

data max_amount;

set col_sort;

if _n_=1 then output;

run;

Respected Advisor
Posts: 3,156

Re: Max of a column

In term of your approach:

data max_amount;

set col_sort;

if _n_=1 then output;

run;

I would do the following instead for the sake of efficiency:

data max_amount;

set col_sort (obs=1);

run;

or : add a stop statement:

data max_amount;

set col_sort;

if _n_=1 then output;

stop;

run;

Otherwise, even though you only output one obs, all obs will still be read through.

Haikuo

Super Contributor
Posts: 297

Re: Max of a column

PROC SORT DATA=SASHELP.CLASS OUT=HAVE;

BY HEIGHT;

RUN;

%LET DSID   = %SYSFUNC(OPEN(HAVE));

%LET NOBS   = %SYSFUNC(ATTRN(&DSID.,NOBS));

%LET VARNUM = %SYSFUNC(VARNUM(&DSID,HEIGHT));

%LET RC = %SYSFUNC(FETCHOBS(&DSID,&NOBS.));

%LET MAXVAL = %SYSFUNC(GETVARN(&DSID.,&VARNUM.));

%LET CLOSE  = %SYSFUNC(CLOSE(&DSID.));

%PUT MAXHEIGHT = &MAXVAL;

Ask a Question
Discussion stats
  • 7 replies
  • 326 views
  • 0 likes
  • 7 in conversation