finding a maximum of 10 variables using proc sql

Question

I am using a proc sql query to extract id and marks of 5 subjects. I would like to create a new column as maximum marks for that id. i tried to use the below query. but it is giving syntax error.

proc sql;
select id, m1, m2,m3,m4,m5, max(m1-m5) as max_marks from data1;
quit;

Edit your question and provide sample data and desired results. — Gordon Linoff, Oct 09 '17 at 11:05

score 1 · Answer 1 · answered Oct 09 '17 at 11:09

1

I think you can use largest():

proc sql;
    select id, m1, m2, m3, m4, m5,
           largest(m1, m2, m3, m4, m5) as max_marks
    from data1;
quit;

If not in proc sql, you can definitely do this with a data step.

answered Oct 09 '17 at 11:09

Gordon Linoff

1,242,037
58
646
786

In SAS the `LARGEST()` function requires another parameter. `MAX(m1,m2,m3)` is the same as `LARGEST(1,m1,m2,m3)`. – Tom Oct 09 '17 at 15:24

Longfish · Answer 2 · 2017-10-09T11:59:54.170

As @Gordon points out, it's easier if you post the syntax error you're getting. The syntax in your post is correct, but what it is doing is subtracting m5 from m1 and taking the max of that 1 value, clearly not what is wanted.

To get the maximum value across variables using the shortcut - then you need to add the keyword of.

max(of m1-m5)

* note that this method only works in a data step, not using proc sql *

Tom · Answer 3 · 2017-10-09T15:37:36.513

Your syntax for passing a variable list to a function is wrong. To pass a variable list to a function in SAS you need to use the of keyword. MAX(of m1-m5).

Your code is actually calculating the difference between M1 and M5 and since MAX() only has one parameter SAS sees this as a call to the SQL aggregate function MAX() and not a call to the SAS function MAX(,) for finding the max of two or more numbers.

But you cannot use variable lists in PROC SQL, so you will need to list the individual variables.

proc sql;
select id,m1,m2,m3,m4,m5
     , max(m1,m2,m3,m4,m5) as max_marks
from data1
;
quit;

finding a maximum of 10 variables using proc sql

3 Answers3