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
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
Sounds like a homework assignment. Try to solve it and, if you run into a problem, post your attempted code.
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.
Your second attempt should have worked.
PG
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.
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;
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;
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;
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
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.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.