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
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
data want (keep=num max);
set a1;
max=max(of maths--chemis);
run;
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
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
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.
I forgot this Mr.Arthur. I will correct and keep this in mind.
Hi,
Below is the proc sql code.
proc sql;
select num,max(maths,phys,chemis) as max from a1;
quit;
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;
I dont want to use Proc transpose. I got the answer. Thanks for answer.
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.