The SQL Procedure

Example 11: Retrieving Values with the SOUNDS-LIKE Operator

Procedure features:

ORDER BY clause

SOUNDS-LIKE operator

Table:

PROCLIB.STAFF

This example returns rows based on the functionality of the SOUNDS-LIKE operator in a WHERE clause.

Note: 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 on the SOUNDEX algorithm, see SAS Language Reference: Dictionary. [cautionend]

Input Table

                                 PROCLIB.STAFF
                               First 10 Rows Only

  Id
  Num   Lname            Fname            City             State  Hphone
  ----------------------------------------------------------------------------
  1919  ADAMS            GERALD           STAMFORD         CT     203/781-1255
  1653  ALIBRANDI        MARIA            BRIDGEPORT       CT     203/675-7715
  1400  ALHERTANI        ABDULLAH         NEW YORK         NY     212/586-0808
  1350  ALVAREZ          MERCEDES         NEW YORK         NY     718/383-1549
  1401  ALVAREZ          CARLOS           PATERSON         NJ     201/732-8787
  1499  BAREFOOT         JOSEPH           PRINCETON        NJ     201/812-5665
  1101  BAUCOM           WALTER           NEW YORK         NY     212/586-8060
  1333  BANADYGA         JUSTIN           STAMFORD         CT     203/781-1777
  1402  BLALOCK          RALPH            NEW YORK         NY     718/384-2849
  1479  BALLETTI         MARIE            NEW YORK         NY     718/384-8816

Program to Select Names That Sound Like 'Johnson'

libname proclib 'SAS-library';

options nodate pageno=1 linesize=80 pagesize=60;

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

      where lname=*"Johnson"
      order by 2;

Output: Listing

                Employees Whose Last Name Sounds Like 'Johnson'                1

                     Id
                     Num                    Fname
                     --------------------------------------
                     1411  JOHNSEN          JACK           
                     1113  JOHNSON          LESLIE         
                     1369  JONSON           ANTHONY

Program to Select Names that Sound Like 'Sanders'

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

Output: Listing

                Employees Whose Last Name Sounds Like 'Sanders'                2

  Id
  Num   Lname            Fname            City             State  Hphone
  ----------------------------------------------------------------------------
  1561  SANDERS          RAYMOND          NEW YORK         NY     212/588-6615
  1414  SANDERSON        NATHAN           BRIDGEPORT       CT     203/675-1715
  1434  SANDERSON        EDITH            STAMFORD         CT     203/781-1333

Top of Page