Skip to main Content

LARGEST and SMALLEST Functions

The MAX and MIN functions allow you to easily identify the maximum and minimum values from a selection of variables. However in order to find the second highest value or the bottom 3 values you will need to make use of the SAS 9 functions LARGEST and SMALLEST.

The following examples are based on the data listed below:

data balances; 
  input custid $ bal1 bal2 bal3 bal4 bal5 bal6;
  datalines;
12345  911.00  863.30  896.98 1672.26 1675.20  602.42
12346   62.27   10.28 1377.79 1683.42 1290.92  553.13
12347 1996.54   74.68  253.94  374.02  691.90  593.75
12348 1494.83 1114.74  332.76  505.95     .    963.98
12349  336.61  295.78     .a  1965.40  592.76     .b
;
run;

Maximum and Minimum

The syntax for the maximum and minimum functions is as follows.

     function(list of variables);


Largest and Smallest

The largest and smallest functions have similar syntax.

     function(value, list);

Where value is the nth item you want (e.g. 2 for the second largest/smallest) and
list      is a list of variables that will be processed.

The following code demonstrates the use of the MAX, MIN, LARGEST and SMALLEST functions. 

data balances2;
   set balances;
   maxbal=max(of bal1-bal6);
   minbal=min(of bal1-bal6);
   large1=largest(1,of bal1-bal6);
   large2=largest(2,of bal1-bal6);
   small1=smallest(1,of bal1-bal6);
   small2=smallest(2,of bal1-bal6);
   small3=smallest(3,of bal1-bal6);
   drop bal:;
run;

  

 

The following results are produced: 

Max, Min, Smallest and Largest Functions

 maxbal    minbal     large1     large2

1675.20    602.42    1675.20    1672.26
1683.42     10.28    1683.42    1377.79
1996.54     74.68    1996.54     691.90
1494.83    332.76    1494.83    1114.74
1965.40    295.78    1965.40     592.76


Max, Min, Smallest and Largest Functions

 small1    small2    small3

602.42    863.30    896.98
 10.28     62.27    553.13
 74.68    253.94    374.02
332.76    505.95    963.98
295.78    336.61    592.76 

 

Note that where the first argument in the largest and smallest functions is set to '1', the result is the same as the max and min functions. Also note that in this example when evaluating the minimum, the missing values have been ignored.

Handling of missing values

The max and min functions completely exclude missing data from the evaluation however the LARGEST and SMALLEST functions handle missing values in the data by placing any missing values present at the end of the sorted list of variables.

So the LARGEST function will return the maximum data value where a '1' is specified in the first argument, the second largest for a '2' and so on. Once all of the non missing data is referenced the missing values will be returned.

The SMALLEST function will return the lowest value when '1' is specified the second lowest for a '2' and so on.  Again once all the non missing values have been referenced the missing values will be returned. The last non missing value in this case will be the highest data value.

The following example demonstrates how the largest and smallest functions deal with missing data values.

data balances3;
   set balances;
   large1=largest(1,of bal1-bal6);
   large6=largest(6,of bal1-bal6);
   small1=smallest(1,of bal1-bal6);
   small6=smallest(6,of bal1-bal6);
   drop bal:;
run;

  

Generates the following data:

Missing Values with the LARGEST and SMALLEST Functions 
  large1    large6    small1     small6
1675.20    602.42    602.42    1675.20
1683.42     10.28     10.28    1683.42
1996.54     74.68     74.68    1996.54
1494.83       .      332.76        .
1965.40       .      295.78        .