topic Re: Compute maximum and minimum values for rows in SAS Programming

Compute maximum and minimum values for rows

pallis — Sun, 07 Dec 2014 13:52:05 GMT

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

Re: Compute maximum and minimum values for rows

art297 — Sun, 07 Dec 2014 15:12:16 GMT

data want (keep=num max);

set a1;

max=max(of maths--chemis);

run;

Re: Compute maximum and minimum values for rows

pallis — Sun, 07 Dec 2014 16:07:25 GMT

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

Re: Compute maximum and minimum values for rows

Haikuo — Sun, 07 Dec 2014 16:23:13 GMT

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

Re: Compute maximum and minimum values for rows

Vish33 — Sun, 07 Dec 2014 16:24:02 GMT

Hi,

Below is the proc sql code.

proc sql;

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

quit;

Re: Compute maximum and minimum values for rows

art297 — Sun, 07 Dec 2014 16:27:47 GMT

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.

Re: Compute maximum and minimum values for rows

stat_sas — Sun, 07 Dec 2014 16:32:00 GMT

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;

Re: Compute maximum and minimum values for rows

pallis — Sun, 07 Dec 2014 16:33:15 GMT

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

Re: Compute maximum and minimum values for rows

pallis — Sun, 07 Dec 2014 16:34:23 GMT

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

Re: Compute maximum and minimum values for rows

art297 — Sun, 07 Dec 2014 16:44:16 GMT

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;