Find the Min and Max value of a variable.

[Sathish_jammy]

Hi community,

Here I have a sample data which I want to fetch from the table only the min and max value of grade.

data have;
input ID Comments:$10. Grade;
cards;
1 abcdefg 2
1 lkjhgff 3
1 poiuytr 1
10 mnbvcxa 1
10 mbdfrte 4
10 qwertyu 2
;
run;
proc print;
run;

Output should be like:

ID	Comments1	Grade1	Comments2	Grade2
1	poiuytr	1	lkjhgff	3
10	mnbvcxa	1	mbdfrte	4

Please help me resolve the case.

Thanks in advance!

[PeterClemmensen · Accepted solution]

Like this?

data have;
input ID Comments:$10. Grade;
cards;
1 abcdefg 2
1 lkjhgff 3
1 poiuytr 1
10 mnbvcxa 1
10 mbdfrte 4
10 qwertyu 2
;
run;

proc sql;
   create table want as 
   select ID
          ,(select Comments from have where a.ID=ID group by ID having Grade=min(Grade)) as Comment1
          ,min(Grade) as Grade1
          ,(select Comments from have where a.ID=ID group by ID having Grade=max(Grade)) as Comment2
          ,max(Grade) as Grade2
   from have as a
   group by ID;
quit;

proc print data=want;
run;

[ballardw]

And an alternate approach using Proc Means:

Proc means data=have nway;
  class id;
  var grade;
  id comments;
  output out=want (drop= _type_ _freq_)
      minid=comments1 min=Grade1 maxid=Comments2 max=grade2
  ;
run;

The NWAY is so the result only includes values with the actual combinations of CLASS variables present. If you leave that off you get an additional row with the overall (regardless of ID) min, max and the identifiers. The Drop removes variables, the variable _type_ can be used to indentify which combination of CLASS is represented. This could be of interest if you had students (?) from different topics or schools. Include those on the class, remove the NWAY (good idea to leave the _type_ in ) and you would have an overall max min across schools, topic and Id, plus a summary with school only, topic only, school and topic, school and id, topic and id as well as the combination of school, topic and id.

If you were to want all of the above the SQL solution gets quite convoluted, with Proc Means (or summary) drop 2 keywords (nway _type_) and add 1 2 3 or as many grouping variables as you might have an interest in.