MODAL Function

The MODAL function provides a measure of central tendency that identifies the element or elements of a data set that repeats most frequently, and more frequently than another element of the data set. Unlike AVERAGE and MEDIAN, the MODAL function can be applied to non-numeric data. The following table shows how MODAL works on various data sets:

Data Set	AVERAGE	MEDIAN	MODAL
{1, 2, 3}	2	2	None
{1, 1}	1	1	None
{1,1,2}	1.333	1	1
{1,1,2,10}	3.5	1.5	1
{1, 1, 2, 2}	1.5	1.5	None
{1, 1, 2, 2, 3}	1.8	2	1 and 2
{'John', 'John', 'Mary'}	N/A	N/A	'John'
{'1-jan-2003','1-jan-2003', '2-jan-2003'}	N/A	N/A	'1-jan-2003'

Arguments	Data Type	Description
list	Array of Object	List of any of the following data types: FloatObject, IntegerObject, MoneyObject, DecimalObject, DateObject, LongVcharObject, StringObject. All elements must be of the same type.
modecount	Integer	Returns the number of instances of the modal value or values in the data set.
ignorecase	Integer	Valid values are FALSE and TRUE. This parameter is ignored unless the data type is StringObject. If set to TRUE, comparisons are case-insensitive. Default: FALSE

• Array of Object—Array containing the modal element or elements of the list. The array must be of the same type as the array passed to MODAL.

/*
** Identify the most frequently occurring surname in an HR database
*/
initialize()=
declare
    array_name           = array of stringobject;
    array_results        = array of stringobject;
    dbso                 = DBSessionObject;
    msg                  = varchar(256) not null;
    n                    = integer not null;
    ret                  = integer not null;
enddeclare

{
    /*
    ** Connect to the database
    */
    ret = dbso.Connect(database = 'xxxxxxxx::testdb');
    CurFrame.DBSession = dbso;

    /*
    ** Load the surnames in the roster into an array
    */

    n = 1;

    select lastname as array_name[n].Value
    from hr_table
    {
        n = n + 1;
    };

    /*
    ** Pass the array of surnames to the Modal function
    */

    array_results = modal(list = array_name);

    /*
    ** Loop through the results to report all modal values
    */

    if array_results.AllRows = 0 then
        msg = 'No modal value';
    else
        msg = 'The modal value(s) is/are: ';

        for n = 1 to array_results.LastRow do
            msg = msg + HC_NEWLINE;
            msg = msg + array_results[n].Value;
        endfor;
    endif;

    /*
    ** Report the results
    */

    message msg;

    /*
    ** Disconnect from the database
    */

    dbso.Disconnect();
}