Data step-Max Function

Accepted Solution Solved
Reply
Contributor
Posts: 66
Accepted Solution

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
Respected Advisor
Posts: 3,156

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

View solution in original post


All Replies
PROC Star
Posts: 7,492

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.

Respected Advisor
Posts: 4,932

Re: Data step-Max Function

Your second attempt should have worked.

PG

PG
Super User
Super User
Posts: 7,076

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: 5,516

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
Respected Advisor
Posts: 3,156

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: 7,492

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.

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

Discussion stats
  • 9 replies
  • 14413 views
  • 6 likes
  • 7 in conversation