Solved
Contributor
Posts: 71

# Compute maximum and minimum values for rows

Hi ,

Can you let me know how to get Max  & Min value  for rows in proc sql

data a1;

input num maths phys chemis;

datalines;

1 19 23 45

2 33 45 55

3 56 88 56

;

run;

Looking for Output

num max

1       45

2        55

3        88

Regards,

Vnky

Accepted Solutions
Solution
‎12-07-2014 11:23 AM
Posts: 3,167

## Re: Compute maximum and minimum values for rows

data a1;

input num maths phys chemis;

datalines;

1 19 23 45

2 33 45 55

3 56 88 56

;

run;

/*Proc SQL requires you to spell out variable names explicitly, so

if you don't have many many variables*/

proc sql;

create table want as

select num, max(maths, phys, chemis) as max_score from a1;

quit;

/*or add another step to obtain the var names*/

proc sql noprint;

select name INTO :vname separated by ',' from dictionary.columns where libname='WORK' AND MEMNAME='A1' AND VARNUM>1

;

quit;

proc sql;

create table want_1 as

select num, max(&vname) as max_score from a1;

quit;

Haikuo

All Replies
PROC Star
Posts: 8,164

## Re: Compute maximum and minimum values for rows

data want (keep=num max);

set a1;

max=max(of maths--chemis);

run;

Contributor
Posts: 71

## Re: Compute maximum and minimum values for rows

Arthur,

Thanks for the reply. Looking in Proc sql Method;

Data step Method-2:

data Want;

set A1;

array x{*} _numeric_;

max=max(of x{*});

run;

Regards,

Vnky

Solution
‎12-07-2014 11:23 AM
Posts: 3,167

## Re: Compute maximum and minimum values for rows

data a1;

input num maths phys chemis;

datalines;

1 19 23 45

2 33 45 55

3 56 88 56

;

run;

/*Proc SQL requires you to spell out variable names explicitly, so

if you don't have many many variables*/

proc sql;

create table want as

select num, max(maths, phys, chemis) as max_score from a1;

quit;

/*or add another step to obtain the var names*/

proc sql noprint;

select name INTO :vname separated by ',' from dictionary.columns where libname='WORK' AND MEMNAME='A1' AND VARNUM>1

;

quit;

proc sql;

create table want_1 as

select num, max(&vname) as max_score from a1;

quit;

Haikuo

PROC Star
Posts: 8,164

## Re: Compute maximum and minimum values for rows

sql takes a bit more code:

proc sql;

create table want as

select num,max(maths,phys,chemis) as max

from a1

;

quit;

and the array approach you posted, if num is numeric, num will also be included in finding max.

Contributor
Posts: 71

## Re: Compute maximum and minimum values for rows

I forgot this Mr.Arthur. I will correct and keep this in mind.

Frequent Contributor
Posts: 142

## Re: Compute maximum and minimum values for rows

Hi,

Below is the proc sql code.

proc sql;

select num,max(maths,phys,chemis) as max from a1;

quit;

Posts: 1,270

## Re: Compute maximum and minimum values for rows

proc transpose data=a1 out=want;

by num;

run;

proc sql;

select num,min(col1) as min, max(col1) as max from want

group by num;

quit;

Contributor
Posts: 71

## Re: Compute maximum and minimum values for rows

I dont want to use Proc transpose. I got the answer. Thanks for answer.

PROC Star
Posts: 8,164

## Re: Compute maximum and minimum values for rows

Just two last things concerning 's suggested code:

(1) you can incorporate everything in one proc sql run and

(2) you can isolate numeric variables and omit variables by name

e.g.:

proc sql;

select name

into :vars separated by ','

from dictionary.columns

where libname='WORK' and

memname='A1' and

type='num' and

name ne 'num'

;

create table want as

select num,max(&vars.) as max

from a1

;

quit;

🔒 This topic is solved and locked.