Solved
Contributor
Posts: 66

# Data step-Max Function

A data set is given below.

Name   Age

Jack    10

Joe       31

Tom     22

Roe     33

Rex    44

Tim     24

In a single  step   I am trying  to find the person with maximum age from the individuals named Tom , Row and Joe.

Thanks

Accepted Solutions
Solution
‎12-12-2012 01:13 PM
Posts: 3,167

## Re: Data step-Max Function

You don't have to involve Macro variables, because if you do, it generally means more than ONE steps.

data have;

input Name \$  Age;

cards;

Jack    10

Joe       31

Tom     22

Roe     33

Rex    44

Tim     24

;

/*OEN STEP SQL*/

proc sql;

select * from have where upcase(name) in ('TOM','ROE','JOE') HAVING AGE=MAX(AGE);

QUIT;

/*ONE STEP DATASTEP*/

DATA WANT;

SET HAVE (WHERE=(UPCASE(NAME) IN ('TOM','ROE','JOE'))) END=LAST;

LENGTH _N \$20.;

RETAIN _N  _A;

IF _A<AGE THEN DO; _A=AGE; _N=NAME;END;

IF LAST THEN DO; AGE=_A;NAME=_N;OUTPUT;END;

DROP _:;

RUN;

Haikuo

All Replies
PROC Star
Posts: 8,164

## Re: Data step-Max Function

Sounds like a homework assignment.  Try to solve it and, if you run into a problem, post your attempted code.

Contributor
Posts: 66

## Re: Data step-Max Function

Will be doing something like this for my project coming soon. Looking at my data, I am trying to figure out the candidate codes.

This works.

But I want to do this in a single step, these are my trails,

Thanks.

Posts: 5,529

## Re: Data step-Max Function

Your second attempt should have worked.

PG

PG
Super User
Posts: 8,114

## Re: Data step-Max Function

The data step does not understand the aggregation functions of SQL.  There is a separate MAX() function that will find the maximum from a list of values one the current observation only.  For example:  max(var1,var2,var3,var4)

To summarize across rows use PROC SUMMARY.

Super Contributor
Posts: 1,636

## Re: Data step-Max Function

Hi,

try the code below:

data have;

input Name\$   Age;

cards;

Jack    10

Joe       31

Tom     22

Roe     100

Rex    44

Tim     24

;

data _null_;

retain max_age 0;

set have (where=( upcase(name)in('TOM','ROE','REX')));

max_age=ifn(age>max_age,age,max_age);

call symputx('max_age',max_age);

run;

%put &max_age;

Super Contributor
Posts: 1,636

## Re: Data step-Max Function

or improved version:

data _null_;

retain max_age 0;

set have (where=( upcase(name)in('TOM','ROE','REX'))) end=last;

max_age=ifn(age>max_age,age,max_age);

if last then  call symputx('m_age',max_age);

run;

%put &m_age;

Super User
Posts: 6,776

## Re: Data step-Max Function

I like Hai.kuo's SQL solution.  But if you must have a one-step DATA step solution as well, here's one:

data want;

do until (done1);

set have end=done1;

where name in ('Tom', 'Roe', 'Rex');

max_age = max(age, max_age);

end;

do until (done2);

set have end=done2;

where name in ('Tom', 'Roe', 'Rex');

if age = max_age then output;

end;

run;

Solution
‎12-12-2012 01:13 PM
Posts: 3,167

## Re: Data step-Max Function

You don't have to involve Macro variables, because if you do, it generally means more than ONE steps.

data have;

input Name \$  Age;

cards;

Jack    10

Joe       31

Tom     22

Roe     33

Rex    44

Tim     24

;

/*OEN STEP SQL*/

proc sql;

select * from have where upcase(name) in ('TOM','ROE','JOE') HAVING AGE=MAX(AGE);

QUIT;

/*ONE STEP DATASTEP*/

DATA WANT;

SET HAVE (WHERE=(UPCASE(NAME) IN ('TOM','ROE','JOE'))) END=LAST;

LENGTH _N \$20.;

RETAIN _N  _A;

IF _A<AGE THEN DO; _A=AGE; _N=NAME;END;

IF LAST THEN DO; AGE=_A;NAME=_N;OUTPUT;END;

DROP _:;

RUN;

Haikuo

PROC Star
Posts: 8,164

## Re: Data step-Max Function

Your final datastep doesn't include a set statement so, minimally, it doesn't know what 'name' is and/or have any values to compare.

🔒 This topic is solved and locked.