%******************************************************************************;
%** http://www.info-net.nl                                                   **;
%******************************************************************************;
%* Topic         : SAS Procedures                                             *;
%* Program       : Proc SQL Where Clause                                      *;
%* Author        : Raymond Ebben                                              *;
%* Location      : http://www.info-net.nl/index/sas-coding/sas-procedures     *;
%* Date          : January 2006                                               *;
%* Version       : 1.0                                                        *;
%* Description   : This program demonstrates selecting records with proc SQL  *;
%*                 The program will use the sashelp.class dataset             *;
%*                 - Exact match                                              *;
%*                 - Fold option (upcase,lowcase)                             *;
%*                 - In operator                                              *;
%*                 - Missing operator                                         *;
%*                 - Fuzzy match                                              *;
%*                 - Numeric equasion                                         *;
%******************************************************************************;
%* In datasets   : None                                                       *;
%* In macrovars  : None                                                       *;
%* In files      : None                                                       *;
%* Out datasets  : work.example1 work.example2                                *;
%* Out macrovars : None                                                       *;
%* Out files     : None                                                       *;
%******************************************************************************;
%** Version control                                                          **;
%******************************************************************************;
%* Mod * Ver. * Date      *  Description                                      *;
%******************************************************************************;
%******************************************************************************;
 
proc sql;
 
  %****************************************************************************;
  %* Select records using the exact match                                     *;
  %****************************************************************************;
  title1 'Exact match for name="Alfred"';
  select *   /* All variables from sashelp.class*/
    from sashelp.class
    where name='Alfred';
 
  %****************************************************************************;
  %* Select records using the fold option (upcase,lowcase)                    *;
  %****************************************************************************;
  title1 'Fold option (upcase,lowcase) for lowcase(name)="alfred"';
  select *   /* All variables from sashelp.class*/
    from sashelp.class
    where lowcase(name)='alfred';
 
  %****************************************************************************;
  %* Select records using in operator (within a specified collection)         *;
  %****************************************************************************;
  title1 'In operator; search within a specified collection ';
  title2 'for name in ("Alfred","Mary")';
  select *   /* All variables from sashelp.class*/
    from sashelp.class
    where name in ('Alfred','Mary');
 
  %****************************************************************************;
  %* Select records using the missing operator (SAS v8 or higher)             *;
  %****************************************************************************;
  title1 '<NOT> missing operator for name is not missing ';
  select *   /* All variables from sashelp.class*/
    from sashelp.class
    where name is not missing;  /*keyword IS, is mandatory */
 
  %****************************************************************************;
  %* Select records using the missing operator (SAS v8 or higher)             *;
  %****************************************************************************;
  title1 'Fuzzy match for name like "_lfr__" or name like "%hil%"';
  select *   /* All variables from sashelp.class*/
    from sashelp.class
    where name like '_lfr__' /*_ (underscore) matches any single character */
      or name like '%hil%'; /*% (percent sign) matches any sequence of
                                 zero or more characters */
 
  %****************************************************************************;
  %* Select records using numeric equasion                                    *;
  %****************************************************************************;
  title 'Numeric equasions for age > 10 and age < 13 and age between 11 and 13';
  select *   /* All variables from sashelp.class*/
    from sashelp.class
    where age > 10 and age  < 13  /* greater than 10 and lesser than 13 */
      and age between 11 and 13; /* greater than 1 and lesser than  */
 
quit;
 

1 
2 
3 
4 
5 
6 
7 
8 
9 
10 
11 
12 
13 
14 
15 
16 
17 
18 
19 
20 
21 
22 
23 
24 
25 
26 
27 
28 
29 
30 
31 
32 
33 
34 
35 
36 
37 
38 
39 
40 
41 
42 
43 
44 
45 
46 
47 
48 
49 
50 
51 
52 
53 
54 
55 
56 
57 
58 
59 
60 
61 
62 
63 
64 
65 
66 
67 
68 
69 
70 
71 
72 
73 
74 
75 
76 
77 
78 
79 
80 
81 
82 
83 
84 
85 
86 
87