How to find out maximum and minimum value with group by condition ?

[Lidia1]

subjid	DOSE	NAM	VAL
3	120MG FeS	HEIGHTNO	167.2
7	120MG FeS	HEIGHTNO	170
2	120MG FeS	HEIGHTNO	172.7
10	120MG FeS	HEIGHTNO	173
6	120MG FeS	HEIGHTNO	176
9	120MG FeS	HEIGHTNO	53
11	240MG FeS	HEIGHTNO	159
14	240MG FeS	HEIGHTNO	164
13	240MG FeS	HEIGHTNO	169
1	240MG FeS	HEIGHTNO	175
17	240MG FeS	HEIGHTNO	182.8
12	240MG FeS	HEIGHTNO	74
22	360MG FeS	HEIGHTNO	160.5
20	360MG FeS	HEIGHTNO	166
25	360MG FeS	HEIGHTNO	166
18	360MG FeS	HEIGHTNO	171
19	360MG FeS	HEIGHTNO	177.8
15	360MG FeS	HEIGHTNO	193

My desired output is 

Dose	min	max
120MG FeS	54	167.2
240MG FeS	74	159
360MG FeS	160.5	193

Kindly help me to frame this. I am always getting the max value as a min value.

[novinosrin]

proc sql;
create table want as
select dose, min(val) as min, max(val) as max
from your_dataset
group by dose;
quit;

[Lidia1]

I have used your query --

proc sql;
create table want as
select exdose, min(qval) as min, max(qval) as max
from exhgt
group by exdose;
quit;

But again it is giving me the  below result which is not correct--

dose	min	max
120MG FeS	167.2	53
240MG FeS	159	74
360MG FeS	160.5	193

[PaigeMiller]

---

@Lidia1 wrote:

I have used your query --

proc sql;
create table want as
select exdose, min(qval) as min, max(qval) as max
from exhgt
group by exdose;
quit;

But again it is giving me the  below result which is not correct--

dose	min	max
120MG FeS	167.2	53
240MG FeS	159	74
360MG FeS	160.5	193

---

There's something wrong above, because I get the desired results using PROC SQL or PROC SUMMARY. Either your data set is not represented properly, or your code as shown is not the code you are running.

Can you please provide the data as a SAS data step as shown below, and also the EXACT unedited code you are using? Copy and paste (do not type) the EXACT code into a code box (click on the running man icon to open the code box).

data have;
infile cards;
input subjid DOSE &:$9.	  NAM $9.	   VAL;
cards;
3	120MG FeS	HEIGHTNO	167.2
7	120MG FeS	HEIGHTNO	170
2	120MG FeS	HEIGHTNO	172.7
10	120MG FeS	HEIGHTNO	173
6	120MG FeS	HEIGHTNO	176
9	120MG FeS	HEIGHTNO	53
11	240MG FeS	HEIGHTNO	159
14	240MG FeS	HEIGHTNO	164
13	240MG FeS	HEIGHTNO	169
1	240MG FeS	HEIGHTNO	175
17	240MG FeS	HEIGHTNO	182.8
12	240MG FeS	HEIGHTNO	74
22	360MG FeS	HEIGHTNO	160.5
20	360MG FeS	HEIGHTNO	166
25	360MG FeS	HEIGHTNO	166
18	360MG FeS	HEIGHTNO	171
19	360MG FeS	HEIGHTNO	177.8
15	360MG FeS	HEIGHTNO	193
run;

proc sql;
create table want as
select dose, min( val) as min, max( val) as max
from have
group by dose;
quit;

Also, does the variable VAL have some formatting??? Can you show us the PROC CONTENTS output for your SAS data set?

[Lidia1]

Yes. PFB the proc contents -

obs	Variable	Type	Len	Format	Informat	Label
2	EXDOSE	Char	9	$9.00	$9.00	EXDOSE
3	QNAM	Char	20	$18.00	$18.00	QNAM
4	QVAL	Char	24	$24.00	$24.00	QVAL
1	subjid	Num	8

I have also shared (below)the data which I am using.

Obs	subjid	EXDOSE	QNAM	QVAL
1	3	120MG FeS	HEIGHTNO	167.2
2	7	120MG FeS	HEIGHTNO	170
3	2	120MG FeS	HEIGHTNO	172.7
4	10	120MG FeS	HEIGHTNO	173
5	6	120MG FeS	HEIGHTNO	176
6	9	120MG FeS	HEIGHTNO	53
7	11	240MG FeS	HEIGHTNO	159
8	14	240MG FeS	HEIGHTNO	164
9	13	240MG FeS	HEIGHTNO	169
10	1	240MG FeS	HEIGHTNO	175
11	17	240MG FeS	HEIGHTNO	182.8
12	12	240MG FeS	HEIGHTNO	74
13	22	360MG FeS	HEIGHTNO	160.5
14	20	360MG FeS	HEIGHTNO	166
15	25	360MG FeS	HEIGHTNO	166
16	18	360MG FeS	HEIGHTNO	171
17	19	360MG FeS	HEIGHTNO	177.8
18	15	360MG FeS	HEIGHTNO	193

[ed_sas_member]

Hi @Lidia1 

According to your data, min and max values in each group are:

Obs	subjid	EXDOSE	QNAM	QVAL
1	3	120MG FeS	HEIGHTNO	167.2
2	7	120MG FeS	HEIGHTNO	170
3	2	120MG FeS	HEIGHTNO	172.7
4	10	120MG FeS	HEIGHTNO	173
5	6	120MG FeS	HEIGHTNO	176
6	9	120MG FeS	HEIGHTNO	53
7	11	240MG FeS	HEIGHTNO	159
8	14	240MG FeS	HEIGHTNO	164
9	13	240MG FeS	HEIGHTNO	169
10	1	240MG FeS	HEIGHTNO	175
11	17	240MG FeS	HEIGHTNO	182.8
12	12	240MG FeS	HEIGHTNO	74
13	22	360MG FeS	HEIGHTNO	160.5
14	20	360MG FeS	HEIGHTNO	166
15	25	360MG FeS	HEIGHTNO	166
16	18	360MG FeS	HEIGHTNO	171
17	19	360MG FeS	HEIGHTNO	177.8
18	15	360MG FeS	HEIGHTNO	193

So this doesn't correspond to your expected output ???

[Lidia1]

No. I am getting min value as max in my output dataset.

[brzcol]

This is probably because the val column is character. You will need this to be numeric. See the solution below another user made to go from character to numeric.

[FreelanceReinh]

---

@Lidia1 wrote:

No. I am getting min value as max in my output dataset.

---

This just seems to be the case for some groups because, e.g., "53">"176" alphabetically -- and alphabetical order is relevant when comparing character values (e.g. via MIN and MAX functions in PROC SQL). Please convert QVAL to numeric using the INPUT function as suggested earlier.

[PaigeMiller]

proc summary data=have nway;
    class dose;
    var val;
    output out=want min=min max=max;
run;

[brzcol]

One option is to use proc means:

proc means data=ExampleTable min max nonobs;
   class dose;
   var val;
run;

[data_null__]

I like PROC SUMMARY for this and it has features to ID the obs that is MIN and MAX.  In this example I use it find the subject with MIN/MAX in each group.

data sl;
   input subjid $ DOSE &$10. NAM :$32. val;
   cards;
3
120MG FeS
HEIGHTNO
167.2
7
120MG FeS
HEIGHTNO
170
2
120MG FeS
HEIGHTNO
172.7
10
120MG FeS
HEIGHTNO
173
6
120MG FeS
HEIGHTNO
176
9
120MG FeS
HEIGHTNO
53
11
240MG FeS
HEIGHTNO
159
14
240MG FeS
HEIGHTNO
164
13
240MG FeS
HEIGHTNO
169
1
240MG FeS
HEIGHTNO
175
17
240MG FeS
HEIGHTNO
182.8
12
240MG FeS
HEIGHTNO
74
22
360MG FeS
HEIGHTNO
160.5
20
360MG FeS
HEIGHTNO
166
25
360MG FeS
HEIGHTNO
166
18
360MG FeS
HEIGHTNO
171
19
360MG FeS
HEIGHTNO
177.8
15
360MG FeS
HEIGHTNO
193
;;;;
   run;
proc print;
   run;

proc summary data=sl nway missing;
   class nam dose;
   output out=stats(drop=_type_)
      idgroup(max(val) out(val subjid)=max maxsubj)
      idgroup(min(val) out(val subjid)=min minsubj)
      ;
   run;
proc print;
   run;

[FreelanceReinh · Accepted solution]

Hi @Lidia1,

Step 1: Create a numeric variable VAL from the existing character variable VAL (or QVAL), e.g., like this:

data have2;
set have(rename=(val=valc));
val=input(valc,32.);
run;

Step 2: Apply any of the suggested solutions (or possibly your existing code) to the new dataset (HAVE2 in the example above).

[PaigeMiller]

So @Lidia1 (and others), the lesson here is that you can't perform math (find a minimum or maximum or any other type of statistic or calculation) on character variables. Maxim 3 — know your data — I've heard that somewhere before.

Had you used my preference which is PROC SUMMARY (or PROC MEANS), you would have received an error in the log, alerting you to the problem, which would then have led to a fix. You would not have received incorrect results. PROC SQL doesn't produce an error; which is another reason why I usually don't use PROC SQL for math.

[Lidia1]

Yes. I tried your query. But I got Type mismatch (in by variable i.e. dose) error at my end.