Tuesday 19 January 2010


I was introduced to the WHEREUP data set option recently. In counterpoint to a WHERE data set option, WHEREUP allows you to control what records are written out (as opposed to WHERE's control of what's read in). WHEREUP can be set as YES or NO and it specifies whether or not records to be written out must match the WHERE clause.

The WHERE clause always applies to input observations, and the step reads only observations that meet the condition. With WHEREUP=YES, it does not allow the observations to be changed so that they no longer meet the condition. With WHEREUP=NO, it is possible to change data values so that observations no longer meet the WHERE condition.

The following code provides a very simple demonstration of WHEREUP=YES and the behaviour and  messages it can produce.

21 data a;
22   modify a(where=(x=1) whereup=yes);
23   x=3;
24   replace; /* Update does not match WHERE expression */
25   output; /* Add does not match WHERE expression */
26 run;

ERROR: The update to file WORK.A.DATA was rejected because it does not match the WHERE clause.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: The data set WORK.A has been updated. There were 0 observations rewritten, 0 observations added and 0 observations deleted.
NOTE: There were 1 rejected updates, 0 rejected adds, and 0 rejected deletes.

As a means of enforcing the rule(s) you have specified in your WHERE clause, the WHEREUP option can be very useful. WHEREUP can form a useful tool in your data integrity strategy.