When a WHERE statement contains a function with a missing argument, the following errors occur:

   ERROR: Syntax error while parsing WHERE clause.
   
   ERROR 22-322: Syntax error, expecting one of the following: a name, a
   quoted string, a numeric constant, a datetime constant, a missing 
   value, INPUT, PUT.

   ERROR 76-322: Syntax error, statement will be ignored.

See an example under the Full Code tab.

The same function can be used with an IF statement without errors. WHERE statements follow SQL syntax conventions and are not able to handle missing arguments.

Operating System and Release Information

Product Family	Product	System	SAS Release
			Reported	Fixed*
SAS System	Base SAS	z/OS	9 TS M0
		Microsoft® Windows® for 64-Bit Itanium-based Systems	9 TS M0
		Microsoft Windows Server 2003 Datacenter 64-bit Edition	9 TS M0
		Microsoft Windows Server 2003 Enterprise 64-bit Edition	9 TS M0
		Microsoft Windows 2000 Advanced Server	9 TS M0
		Microsoft Windows 2000 Datacenter Server	9 TS M0
		Microsoft Windows 2000 Server	9 TS M0
		Microsoft Windows 2000 Professional	9 TS M0
		Microsoft Windows NT Workstation	9 TS M0
		Microsoft Windows Server 2003 Datacenter Edition	9 TS M0
		Microsoft Windows Server 2003 Enterprise Edition	9 TS M0
		Microsoft Windows Server 2003 Standard Edition	9 TS M0
		Microsoft Windows XP Professional	9 TS M0
		64-bit Enabled AIX	9 TS M0
		64-bit Enabled HP-UX	9 TS M0
		64-bit Enabled Solaris	9 TS M0
		HP-UX IPF	9 TS M0
		Linux	9 TS M0
		OpenVMS Alpha	9 TS M0
		Tru64 UNIX	9 TS M0

* For software releases that are not yet generally available, the Fixed Release is the software release in which the problem is planned to be fixed.

This example demonstrates that a function wih a missing argument can't be used in a WHERE statement. It is acceptable in an IF statement.

---

data a;
  city='NEW YORK';
run;

data b;
  set a;
  where index(compress(city, ,'adko'),'NEWYORK');
run;

proc print;
run;

/* The errors are produced from the WHERE statement */
/* Changing the WHERE to an IF causes correct output and the value */
/* NEW YORK is selected.                                           */

577    data b;
578   city='NEW YORK';
579   where index(compress(city, ,'adko'),'NEWYORK');
                                 -
                                 22
                                 76
ERROR: Syntax error while parsing WHERE clause.
ERROR: No input data sets available for WHERE statement.
ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string,
              a numeric constant, a datetime constant, a missing value, INPUT, PUT.

ERROR 76-322: Syntax error, statement will be ignored.

An IF statement produces correct results when an argument has a missing argument. However, a WHERE statement does not produce correct results.

Type:	Usage Note
Priority:
Topic:	SAS Reference ==> Statements ==> Action ==> IF, subsetting SAS Reference ==> Statements ==> Action ==> WHERE

Date Modified:	2010-12-03 10:42:45
Date Created:	2010-11-02 13:14:37