SQL Procedure

Example 11: Retrieving Values with the SOUNDS-LIKE Operator

Features:

ORDER BY clause

SOUNDS-LIKE operator

Table name:

This example returns rows based on the functionality of the SOUNDS-LIKE operator in a WHERE clause. The SOUNDS-LIKE operator is based on the SOUNDEX algorithm for identifying words that sound alike. The SOUNDEX algorithm is English-biased and is less useful for languages other than English. For more information about the “SOUNDEX Function” in SAS Functions and CALL Routines: Reference algorithm, see SAS Functions and CALL Routines: Reference.

Details

proc sql outobs=10;
   title 'PROCLIB.STAFF';
   title2 'First 10 Rows Only';
   select * from proclib.staff;
   title;

PROCLIB.STAFF

Program to Select Names That Sound like 'Johnson'

libname proclib 'SAS-library';

proc sql;
   title "Employees Whose Last Name Sounds Like 'Johnson'";
   select idnum, upcase(lname), fname
      from proclib.staff

where lname=*"Johnson"
      order by 2;

Program Description

Declare the PROCLIB library.The PROCLIB library is used in these examples to store created tables.

libname proclib 'SAS-library';

Select the columns and the table from which the data is obtained.The SELECT clause selects all columns from the table in the FROM clause, PROCLIB.STAFF.

proc sql;
   title "Employees Whose Last Name Sounds Like 'Johnson'";
   select idnum, upcase(lname), fname
      from proclib.staff

Subset the query and sort the output.The WHERE clause uses the SOUNDS-LIKE operator to subset the table by those employees whose last name sounds like Johnson. The ORDER BY clause orders the output by the second column.

where lname=*"Johnson"
      order by 2;

Output for Names That Sound like 'Johnson'

Employees Whose Last Name Sounds like 'Johnson'

Program to Select Names That Sound like 'Sanders'

SOUNDS-LIKE is useful, but there might be instances where it does not return every row that seems to satisfy the condition. PROCLIB.STAFF has an employee with the last name SANDERS and an employee with the last name SANYERS. The algorithm does not find SANYERS, but it does find SANDERS and SANDERSON.

proc sql;
title "Employees Whose Last Name Sounds Like 'Sanders'";
   select *
      from proclib.staff
      where lname=*"Sanders"
      order by 2;

Output for Names That Sound like 'Sanders'

Employees Whose Last Name Sounds like 'Sanders'