BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
pallis
Fluorite | Level 6

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

1 ACCEPTED SOLUTION

Accepted Solutions
Haikuo
Onyx | Level 15

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

9 REPLIES 9
art297
Opal | Level 21

data want (keep=num max);

  set a1;

  max=max(of maths--chemis);

run;

pallis
Fluorite | Level 6

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
Onyx | Level 15

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
Opal | Level 21

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
Fluorite | Level 6

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

Vish33
Lapis Lazuli | Level 10

Hi,

Below is the proc sql code.

proc sql;

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

quit;

stat_sas
Ammonite | Level 13

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
Fluorite | Level 6

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

art297
Opal | Level 21

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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