Calcite | Level 5

## SAS Equivalent of Excel Max if Statement

I have

Data ABC;

X   Y

2    a

1    a

4    b

3    b

;

I want maximum of X for each of Y (maximum based on criteria).

so output would be

NEW

X  Y

2  a

4  b

1 ACCEPTED SOLUTION

Accepted Solutions
Quartz | Level 8

## Re: SAS Equivalent of Excel Max if Statement

Try this:

``````DATA ABC;
x=2; y='a'; output;
x=1; y='a'; output;
x=4; y='b'; output;
x=3; y='b'; output;
RUN;

PROC SQL;
CREATE TABLE want AS
SELECT max(x) as max_x, y
FROM ABC
GROUP BY y;
QUIT;``````
4 REPLIES 4
Quartz | Level 8

## Re: SAS Equivalent of Excel Max if Statement

Try this:

``````DATA ABC;
x=2; y='a'; output;
x=1; y='a'; output;
x=4; y='b'; output;
x=3; y='b'; output;
RUN;

PROC SQL;
CREATE TABLE want AS
SELECT max(x) as max_x, y
FROM ABC
GROUP BY y;
QUIT;``````
Calcite | Level 5

## Re: SAS Equivalent of Excel Max if Statement

Thank you all! I just used the first solution given and it worked, I appreciate your help!

Opal | Level 21

## Re: SAS Equivalent of Excel Max if Statement

The simplest way is:

``````proc sql;
create table NEW as
select max(X) as X, Y
from ABC
group by Y;
quit;``````
PG
Super User

## Re: SAS Equivalent of Excel Max if Statement

If result not appearance is acceptable:

```proc means data=have max;
class y;
var x;
run;```

I would recommend becoming familiar with the procs such as means as I can add large number of statistics such a min, median, mean, standard deviation, range, skewness, kurtosis, a number of percentiles and such by adding them to the list where MAX appears above.

Discussion stats
• 4 replies
• 1796 views
• 0 likes
• 4 in conversation