After you specify an option, it remains in effect until you change it, or you re-invoke PROC SQL. You can use the RESET statement to add, drop, or change PROC SQL options without re-invoking the SQL procedure.

Options are additive. For example, you can specify the NOPRINT option in a PROC SQL statement, submit a query, and submit the RESET statement with the NUMBER option, without affecting the NOPRINT option.

Suppose you want to submit two PROC SQL queries in a single PROC SQL step. You want

In the following PROC SQL step, the PROC SQL statement specifies the OUTOBS= option to restrict the number of rows that is displayed in the output. After the first SELECT statement, the RESET statement adds the NUMBER option to display row numbers in the result set.

proc sql outobs=5;
   select flightnumber, destination 
      from sasuser.internationalflights;
reset number;
   select flightnumber, destination 
      from sasuser.internationalflights
      where boarded gt 200;

The output, which contains two result sets, is shown below. The result set from the first SELECT statement reflects only by the OUTOBS= option. The result set from the second SELECT statement reflects both the OUTOBS= option and the NUMBER option that is specified in the RESET statement.

Now suppose you want to modify the PROC SQL step so that the result set from only the first SELECT statement is restricted to five rows of output. In the modified PROC SQL step, the OUTOBS= option is added to the RESET statement to change (reset) the OUTOBS= option that is specified in the PROC SQL statement. The modified step follows:

 proc sql outobs=5;
    select flightnumber, destination 
       from sasuser.internationalflights;
 reset outobs= number;
    select flightnumber, destination 
       from sasuser.internationalflights
       where boarded gt 200;

In the output, the result set from the second SELECT statement now contains all the rows that are generated by the query.