Compute maximum and minimum values for rows

[pallis]

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

[art297]

data want (keep=num max);

  set a1;

  max=max(of maths--chemis);

run;

[pallis]

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

[Haikuo · Accepted solution]

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

[art297]

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.

[pallis]

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

[Vish33]

Hi,

Below is the proc sql code.

proc sql;

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

quit;

[stat_sas]

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;

[pallis]

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

[art297]

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;