How to use MAX IF in SAS EG

[AmberLOL]

Hi All,

I do not know how to use MAX IF function in SAS EG so that I can get the following output.

Given the left two columns, how can I get the output table (table only, not report) like below? Prefer using EG Query Builder, if cannot, code is also good for me. Thank you so much!

School	Score	Max Score of the School	Min Score of the School
IT	98	98	70
Biz	80	99	80
Biz	88	99	80
Law	97	97	84
IT	70	98	70
Law	84	97	84
IT	78	98	70
Biz	99	99	80
Law	86	97	84

[ChrisHemedinger · Accepted solution]

The easiest point-and-click method is two queries: first to calc the Min/Max for each school group, then the second to join that result with the original detail.

In screen shots, with this data:

data scores;
infile datalines dsd delimiter=' ';
length school $ 3 score 8;
input school score;
datalines;
IT 98
Biz 80
Biz 88
Law 97
IT 70
Law 84
IT 78
Biz 99
Law 86
run;

STEP 1:

STEP 2:

The flow:

Result:

Chris

[AmberLOL]

Hi Chirs,

Thank you very much. I found that actually just the second step is enough. MAX(score) Group by school.

Thanks