The SQL procedure offers a variety of options that control processing. Some options control execution. For example, you can limit the number of rows read or written during a query. Other options control output. For example, you can control the appearance of long character columns, double-space output, or (as shown below) number your rows. Options are also available for testing and evaluating performance.

Metadata is a description or definition of data or information. SAS session metadata is stored in Dictionary tables, which are special, read-only SAS tables that contain information about SAS libraries, SAS data sets, SAS macros, and external files that are available in the current SAS session. Dictionary tables also contain the settings for SAS system options and SAS titles and footnotes that are currently in effect. You can use the SQL procedure to access the metadata stored in Dictionary tables. For example, you can query a Dictionary table to find out which tables in a SAS library contain a specified column.

In this chapter, you learn to

Before beginning this chapter, you should complete the following chapters:

When you are developing queries against large tables, you can reduce the amount of time that it takes for the queries to run by reducing the number of rows that PROC SQL processes. Subsetting the tables with WHERE clauses is one way to do this. Using the INOBS= and OUTOBS= options in PROC SQL is another way.

You already know that you can use the OUTOBS= option to restrict the number of rows that PROC SQL displays or writes to a table. However, the OUTOBS= option does not restrict the rows that are read. The INOBS= option restricts the number of rows that PROC SQL takes as input from any single source. The INOBS= option is similar to the SAS system option OBS= and is useful for debugging queries on large tables.

In the following PROC SQL set operation, INOBS= 5 is specified. As indicated in the log, only five rows from each source table, Sasuser.Mechanicslevel1 and Sasuser.Mechanicslevel2, are used. The resulting table contains 10 rows.

proc sql inobs=5;
      select *
         from sasuser.mechanicslevel1
      outer union corr
      select *
         from sasuser.mechanicslevel2;

Table 8.4. SAS Log

The NUMBER | NONUMBER option specifies whether the output from a query should include a column named ROW, which displays row numbers. NONUMBER is the default. The option is similar to the OBS | NOOBS option in the PRINT procedure.

The following PROC SQL step specifies the NUMBER option. Output from the step includes a column named Row, which contains row numbers.

proc sql inobs=10 number; 
      select flightnumber, destination
         from sasuser.internationalflights;

In some cases, double-spacing your output can make it easier to read. The DOUBLE | NODOUBLE option specifies whether PROC SQL output is double-spaced. The default is NODOUBLE.

The following PROC SQL step specifies the DOUBLE option. The listing output from this step is double spaced. The HTML output from this step remains single-spaced.

proc sql inobs=10  double; 
      select flightnumber, destination
      from sasuser.internationalflights;

Figure 8.1. Listing Output

Figure 8.2. HTML Output

The FLOW | NOFLOW | FLOW=n | FLOW=n m option controls the appearance of wide character columns in listing output. The FLOW option causes text to be flowed in its column instead of wrapping the entire row. n sets the width of the flowed column. Specifying n and m floats the width of the column between limits to achieve a balanced layout.

The following PROC SQL step does not specify the FLOW option. Notice that in the output the name and values for the column ZipCode appear under the name and values for the column FFID due to the wide character columns.

proc sql inobs=5;
      select ffid, membertype, name, address, city,
             state, zipcode
         from sasuser.frequentflyers
         order by pointsused;

Figure 8.3. Output from PROC SQL Step without FLOW Option

Specifying flow=10 15 causes the text within each character column to float between 10 and 15 spaces, which prevents the ZipCode column from wrapping underneath the FFID column.

proc sql inobs=5  flow=10 15; 
   select ffid, membertype, name, address, city,
             state, zipcode
      from sasuser.frequentflyers
      order by pointsused;

Figure 8.4. Output from PROC SQL Step with FLOW Option

The PROC SQL option STIMER | NOSTIMER specifies whether PROC SQL writes timing information for each statement to the SAS log, instead of writing a cumulative value for the entire procedure. NOSTIMER is the default.

In order to use the STIMER option in PROC SQL, the SAS system option STIMER (the default) must also be in effect. Some host operating environments require that you specify the SAS system option STIMER when you invoke SAS. The STIMER system option controls the printing of performance statistics in the SAS log. If you use the system option alone, the results will contain timing information for the entire procedure, not on a statement-by-statement basis.

You can use the OPTIONS procedure to list the current settings of SAS system options. To find out if the SAS system STIMER option is enabled on your operating environment, submit the following program:

proc options option=stimer value;
run;

Table 8.5. SAS Log

Both of the queries in the following PROC SQL step list the name, address, city, state, and ZIP code of customers listed in the Sasuser.FrequentFlyers table. However, the second query only lists this information for customers who have earned more than 7000 points and used less than 3000 points.

When the PROC SQL statement is submitted without the STIMER option, timing information for both queries is written to the SAS log as a cumulative value for the entire procedure.

proc sql;
   select name, address, city, state, zipcode
      from sasuser.frequentflyers;
   select name, address, city, state, zipcode
      from sasuser.frequentflyers
      where pointsearned gt 7000 and pointsused lt 3000;
quit;

Table 8.6. SAS Log

When the PROC SQL statement is submitted with the STIMER option, timing information is written to the SAS log for each SELECT statement.

proc sql stimer; 
   select name, address, city, state, zipcode
         from sasuser.frequentflyers;
   select name, address, city, state, zipcode
         from sasuser.frequentflyers
         where pointsearned gt 7000 and pointsused lt 3000;
quit;

Table 8.7. SAS Log

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 will be 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.

You can query Dictionary tables the same way that you query any other table, including subsetting with a WHERE clause, ordering the results, creating tables, and creating PROC SQL views. Because Dictionary tables are read-only objects, you cannot insert rows or columns, alter column attributes, or add integrity constraints to them.

To see how each Dictionary table is defined, submit a DESCRIBE TABLE statement. The DESCRIBE TABLE statement writes a CREATE TABLE statement to the SAS log for the table specified in the DESCRIBE TABLE statement. After you know how a table is defined, you can use its column names in a subsetting WHERE clause in order to retrieve specific information.

The Dictionary.Tables table contains detailed information about tables. The following DESCRIBE TABLE statement displays information about the Dictionary.Tables table in the log window. The information includes the names of the columns stored in the table.

proc sql;
      describe table dictionary.tables;

Table 8.8. SAS Log

To display information about the files in a specific library, specify the column names in a SELECT statement and the Dictionary table name in the FROM clause.

For example, the following PROC SQL step displays the columns

The Dictionary column names are specified in the SELECT statement and the Dictionary table name, Dictionary.Tables, is specified in the FROM clause. The library name, Sasuser, is specified in the WHERE clause.

proc sql;
   select memname format=$20., nobs, nvar, crdate
      from dictionary.tables
      where libname='SASUSER';

Partial output is shown below.

You can also use Dictionary tables to determine more specific information such as which tables in a SAS library contain a specific column.

The Dictionary.Columns table contains detailed information about variables and their attributes. As in Dictionary.Tables, the Dictionary.Columns table contains a column that is titled Memname, which lists the name of each table within a library.

proc sql;
      describe table dictionary.columns;

Table 8.9. SAS Log

The following PROC SQL step lists all the tables in the Sasuser library that contain a column named EmpID. The dictionary column name, Memname, is specified in the SELECT statement. The Dictionary table, Dictionary.Columns, is specified in the FROM clause. The library name, Sasuser, and the column name, EmpID, are specified in the WHERE clause.

proc sql;
   select memname

    from dictionary.columns
    where libname='SASUSER'
          and name='EmpID';

Partial output is shown below.

Remember that you can also access a Dictionary table by referring to the PROC SQL view of the table that is stored in the Sashelp library. In the following PROC SQL step, the Sashelp view Vcolumn is specified in the FROM clause. The results of the query are identical to the preceding output.

proc sql;
   select memname
      from sashelp.vcolumn 
      where libname='SASUSER'
            and name='EmpID';

The LOOPS= option restricts the number of iterations of the inner loop in PROC SQL. By setting a limit, you can prevent queries from consuming excessive resources.

For example, joining three large tables without meeting the join-matching conditions could create a huge internal table that would be inefficient to process. Use the LOOPS= option to prevent this from happening.

You can use the PROMPT | NOPROMPT option to modify the effect of the LOOPS= option so that you are prompted to stop or continue processing when the limit set by the LOOPS= option is reached.

You already know that you can use the EXEC | NOEXEC option to specify whether a statement should be executed after its syntax is checked for accuracy. If the EXEC option is in effect, SAS checks the PROC SQL syntax for accuracy and, if no error is found, executes the SQL statement.

The ERRORSTOP | NOERRORSTOP option specifies whether PROC SQL stops executing if it encounters an error. This option is useful only when the EXEC option is in effect. The default is ERRORSTOP in batch or in a noninteractive session and NOERRORSTOP in an interactive SAS session.

ERRORSTOP instructs PROC SQL to stop executing the statements but to continue checking the syntax after it has encountered an error. ERRORSTOP has an effect only when SAS is running in batch or in noninteractive execution mode.

NOERRORSTOP instructs PROC SQL to execute the statements and to continue checking the syntax after an error occurs. NOERRORSTOP is useful if you want a batch job to continue executing SQL procedure statements after an error is encountered.

  PROC SQL <option(s)>;

    DESCRIBE TABLE table-name <, …table-name>;

    SELECT column-1<, …column-n>

      FROM table-1 | view-1<, …table-n | view-n>

      <WHERE expression>;

  RESET <option(s)>;

  QUIT;