DATA Step, Macro, Functions and more

Compute maximum and minimum values for rows

Accepted Solution Solved
Reply
Contributor
Posts: 71
Accepted Solution

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
Respected Advisor
Posts: 3,156

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

View solution in original post


All Replies
PROC Star
Posts: 7,468

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
Respected Advisor
Posts: 3,156

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: 7,468

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: 117

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;

Trusted Advisor
Posts: 1,228

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: 7,468

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 342 views
  • 6 likes
  • 5 in conversation