BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
rnmishra
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
Haikuo
Onyx | Level 15

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

9 REPLIES 9
art297
Opal | Level 21

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

rnmishra
Calcite | Level 5

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.

PGStats
Opal | Level 21

Your second attempt should have worked.

PG

PG
Tom
Super User Tom
Super User

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.

Linlin
Lapis Lazuli | Level 10

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;

Linlin
Lapis Lazuli | Level 10

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;

Astounding
PROC Star

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;

Haikuo
Onyx | Level 15

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

art297
Opal | Level 21

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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