SAS provides a variety of techniques for querying data that enable you to create the results that you want in different ways. In this chapter, you learn to select the most efficient query techniques from those listed below, based on comparisons of resource usage.

In this chapter, you learn to

Before beginning this chapter, you should completethe following chapters:

Part 1: SQL Processing with SAS

Part 3: Advanced SAS Programming Techniques

Part 4: Optimizing SAS Programs

When accessing observations sequentially, SAS must search through all observations in the order in which they are stored in the data file.

Suppose you want to create a new data set, Company.D02jul2000, that contains a subset of observations from the data set Company.Dates. The following DATA step uses a WHERE statement to select all observations in which the value of Date_ID is 02JUL2000:

data company.d02jul2000;
   set company.dates;
   where date_id='02JUL2000'd; 
run;

The data set Company.Dates does not have an index defined on the variable Date_ID, so SAS must use sequential access to process the WHERE statement.

When using an index for WHERE processing, SAS goes straight to each observation that contains the value without having to read every observation in the data set.

Suppose you have defined an index on the variable Date_ID in the Company.Dates data set. This time, when you submit the following DATA step, SAS uses the index to process the WHERE statement:

data company.d02jul2000;
   set company.dates;
   where date_id='02JUL2000'd; 
run;

The process of retrieving data via an index (direct access) is more complicated than sequentially processing data, so direct access requires more CPU time per observation retrieved than sequential access. However, for a small subset, using an index can decrease the number of pages that SAS has to load into input buffers, which reduces the number of I/O operations.

As the preceding examples show, both benefits and costs are associated with using an index. Weighing these benefits and costs is an important part of deciding whether using an index is efficient.

The main benefits of using an index include the following:

The main costs of using an index include the following:

When SAS processes a WHERE expression, it first determines whether to use direct access or sequential access by performing the following steps:

In the next few sections, each step of this process is explained in detail.

Suppose you submit a program that contains the following WHERE statement, and suppose that the data set has one index, as shown below:

This WHERE expression has one condition, and the variable in that condition ( Delivery_Date) is the key variable in the simple index. If all other requirements for optimization are met in later steps, then SAS can use this index to optimize the WHERE expression.

Likewise, if the only available index is a composite index in which Delivery_Date is the first key variable, then SAS can use the index if all other requirements for optimization are met.

Even if a WHERE statement has multiple conditions, SAS can use either a simple index or a composite index to optimize just one of the conditions. For example, suppose your program contains a WHERE statement that has two conditions, and suppose that the data set has one index, as shown below:

Assuming that all other requirements for optimization are met, SAS can use this index to optimize the second condition in this WHERE expression.

Suppose your program contains a WHERE statement with two conditions, and suppose that each condition references a key variable in a different index, as shown below:

Although two indexes are available, SAS can use only one index to optimize a WHERE statement. In a later step of the process, SAS will try to select the index that satisfies the most conditions and that selects the smallest subset of observations.

SAS usually uses an index to process just one condition, no matter how many conditions and variables a WHERE expression contains. However, in a process called compound optimization, SAS can use a composite index to optimize multiple conditions on multiple variables, which are joined with a logical operator such as AND. Constructing your WHERE expression to take advantage of multiple key variables in a single index can greatly improve performance.

In order for compound optimization to occur, at least the first two key variables in the composite index must be used in the WHERE conditions. Later in this chapter, you will learn about other requirements that must be met in order for compound optimization to occur.

Suppose your program contains a WHERE statement that has two conditions, and suppose that each condition references one of the first two key variables in a composite index:

Because the two variables that are referenced in the WHERE expression are the first two key variables in the composite index, SAS can use the composite index for compound optimization if the WHERE conditions meet all other requirements for optimization.

The following WHERE statement also contains two conditions, and each condition references one of the variables in the composite index:

As in the previous WHERE statement, Order_Date is the first key variable in the index. However, in this situation, the composite index can be used to optimize only the first condition. The second condition references the third key variable, Product_ID, but the WHERE expression does not reference the second key variable, Delivery_Date. Without a reference to both the first and second key variables, compound optimization cannot occur.

Now suppose your program contains a WHERE statement that references only the second and third key variables in the composite index, as shown below:

In this situation, SAS cannot use the index for optimization at all because the WHERE statement does not reference the first key variable.

SAS considers using an index for a WHERE condition that contains any of the following operators and functions:

where quarter = '1998Q1'; 
where date_id < '03JUL2000'd; 
where quarter in ('1998Q2','1998Q3'), 

where quarter ne '1999Q1'; 
where quarter not in ('1999Q1','1999Q4'), 

where quarter =: '1998'; 

where quarter contains 'Q4'; 

where '01Jan1999'd < date_id 
      < '31Dec1999'd; 
where date_id between '01Jan1999'd 
              and '31Dec1999'd 

where quarter like '%Q%'; 

where quarter is null; 
where quarter is missing; 

where trim(region) = 'Queensland'; 

where substr(quarter,1,4) = '1998'; 

SAS does not use an index to process a WHERE condition that contains any of the elements listed below.

where weekday(date_id)=2; 

where substr(quarter,6,1)='1'; 

where quarter=*'1900Q0'; 

where quantity=quantity+1; 

where quantity gt threshold; 

Most of the same operators that are acceptable for optimizing a single condition are also acceptable for compound optimization. However, compound optimization has special requirements for the operators that appear in the WHERE expression:

Also, SAS cannot perform compound optimization for WHERE conditions that include any of the following:

Suppose your program contains the following WHERE statement, which selects all people whose name is John Smith. The WHERE statement contains two conditions, each of which references a different variable:

where lastname eq 'Smith' and 
      frstname eq 'John'; 

Suppose Lastname is the first key variable and Frstname is the second key variable in a compound index. This WHERE statement meets all requirements for compound optimization:

If the two conditions in the WHERE statement are reversed, as shown below, the statement still meets all requirements for compound optimization. The order in which the key variables appear does not matter.

where frstname eq 'John' and 
      lastname eq 'Smith'; 

Now suppose that the conditions in the WHERE statement are joined by the operator OR instead of AND:

where frstname eq 'John' or 
      lastname eq 'Smith';

These conditions cannot be optimized because they are joined by OR but they do not reference the same variable.

To help SAS estimate the number of observations that would be selected by a WHERE expression, each index stores 21 statistics called cumulative percentiles, or centiles. Centiles provide information about the distribution of values for the indexed variable.

Understanding the distribution of values in a data set can help you improve the efficiency of WHERE processing in your programs. You can print centile information for an indexed data file by specifying the CENTILES option in either of these places:

PROC CONTENTS < options>;     PROC DATASETS < options>;

RUN;                                                         CONTENTS < options>;

                                                         QUIT;

The following SAS program prints centile information for the data set Company.Organization:

proc contents data=company.organization centiles; 
run;

Partial output from this program is shown below. As indicated on the left, an index is defined on the variable Employee_ID. The 21 centile values are listed on the right.

Figure 24.1. Partial PROC CONTENTS Output

The 21 centile values consist of the following:

To compare resource usage, SAS performs the following steps:

Several factors affect the number of I/O operations that are required for WHERE processing, including the following:

These factors are discussed in more detail below.

As explained earlier in this chapter, SAS is more likely to use an index to access a small subset of observations. The process of retrieving data with an index is inherently more complicated than sequentially processing the data. This is why using an index requires more I/O operations and CPU time when a large subset is read.

For small subsets, however, the benefit of reading only a few observations outweighs the cost of the complex processing. The smaller the subset, the larger the performance gains. Remember that SAS will use an index if the subset is less than 3% of the data set, and SAS will probably use an index if the subset is between 3% and 33% of the data set.

For a small data file, sequential processing is often just as efficient as index processing. If the data file's page count is less than three pages, then sequential access is faster even if the subset is less than 3% of the entire data set.

The order of the data (sort order) affects the number of I/O operations as described below:

When SAS reads a compressed data file, SAS automatically uncompresses the observations as they are read into the program data vector. This requires additional CPU resources, but fewer I/O operations are required because there are fewer data set pages. When performing a sequential read of a compressed data file, SAS must uncompress all observations in the file. However, when using direct access, SAS must uncompress only the qualified observations. Therefore, the resource cost of uncompressing observations is greater for a sequential read than for direct access.

Data type and length are two other factors that can affect index efficiency. Numeric key variables typically result in more CPU usage than character key variables, because numeric variables must be converted to collatable formats (formats that can be sequenced) when values are read into the index or retrieved from the index. Character values are already intrinsically collatable, so they do not have to be converted. A page cannot contain as many values if the values are longer. When the values are longer, the index structure is not as efficient, which might lead to more I/O and search time.

Consider three sample queries to see how you can apply the guidelines that are listed in the previous section. These queries illustrate the effect of one factor—the size of the subset relative to the size of the data set—on the choice of an access method. For each query, you will learn:

Suppose you are working with the following two data sets, each of which contains information about a company's orders:

You want to create queries to generate three subset detail reports, one for each of the following types of subsets:

In all three queries, the WHERE expression specifies the variable Order_Date.You know that this variable will be used frequently in queries for the company, and that it is a discriminating variable. According to the guidelines in the previous section, these are both criteria for creating an index on the variable. However, there is currently no index defined on this variable in either data set.

The first report that you want to generate shows all orders in Company.Orders_large that were made on January 10, 1998. Your query is shown below, along with the subset size that you have estimated:

data _null_;
   set company.orders_large;
   where order_date='10JAN1998'd;
run;

Because the subset is less than 3% of the entire data set, using an index on Order_Date should be more efficient than using sequential access. SAS will use an index for WHERE processing, if an index is available. To improve performance, you should create an index on Order_Date before running this program.

The second report shows all orders in Company.Orders_large that were made before January 1, 1998. Your query and the estimated subset size are shown below:

data _null_;
   set company.orders_large;
   where order_date<'01JAN2000'd;
run;

Because the subset is more than 33% of the entire data set, using the index is probably less efficient than using sequential access. SAS will probably not use the index for WHERE processing.

The third report shows all orders in the smaller data set Company.Orders_small that were made on June 30, 1998. Your query and the estimated subset size are shown below:

data _null_;
   set company.orders_small;
   where order_date'30JUN1998'd;
run;

Because the subset is less than 3% of the entire data set, SAS will use the index for WHERE processing. However, the data file's page count is less than three pages, so it is more efficient to use sequential access. In this situation, it is best not to create an index.

In most situations, it is best to let SAS determine whether or not to use an index for WHERE processing. However, sometimes you might want to control whether or not SAS uses an existing index. For example, if you know that your query will select a large subset and that indexed access will therefore not be efficient, you can tell SAS to ignore any index and to satisfy the conditions of the WHERE expression with a sequential search of the data set. Or, if your query will select a small subset and there are multiple available indexes, you can make sure that SAS uses a particular index to process your WHERE statement. Finally, you might want to force SAS to use (or not use) an index when you are benchmarking.

You should be familiar with the data set options IDXWHERE= and IDXNAME=, which you can use to control index usage:

To determine whether SAS is using an index to process a WHERE expression, you can specify I as the value of the MSGLEVEL= system option. Using MSGLEVEL=I causes SAS to display information about index usage in the SAS log.

Suppose you write the following query, which lists all employees who work in the Sales department of a company:

proc print data=company.organization;
   where department='Sales';
run;

Now suppose an index is defined on the variable Department in the data set Company.Organization. You know that Department has the value Sales in 65% of the observations, so it is not efficient for SAS to use an index for WHERE processing. To ensure that SAS does not use an index, specify IDXWHERE=NO after the data set name. At the beginning of the program, you can also add an OPTIONS statement that specifies MSGLEVEL=I to display a message about index usage in the SAS log. The revised program is shown below:

options msglevel=i; 
proc print data=company.organization (idxwhere=no); 
   where department='Sales';
run;

When you run this program, the SAS log indicates that the index was not used for processing.

Table 24.1. SAS Log

Suppose you are working with the data set Company.Products and that you want to generate four types of detail reports:

For the first three reports, the PROC PRINT program is likely to use fewer resources than the PROC SQL program. For the last report, the resource usage for the two programs is likely to be about the same.

The simple detail report lists the product ID, product name, and supplier name for all products. The PROC PRINT program and PROC SQL program for producing this report are shown below:

PROC PRINT	PROC SQL

In this situation, the PROC PRINT program is likely to use fewer CPU and memory resources than the PROC SQL program. The I/O resource usage should be approximately the same.

The subset detail report lists the product ID, product name, and supplier name for all products that come from Sweden (SE). The PROC PRINT program and PROC SQL program for producing this report are shown below:

PROC PRINT	PROC SQL

Both steps use WHERE processing to subset the data. In this situation, the PROC PRINT program is likely to use fewer CPU and memory resources than the PROC SQL program. The I/O resource usage should be approximately the same.

The sorted detail report lists the product ID, product name, and supplier name for all products, with observations sorted by the supplier country. The PROC PRINT program and PROC SQL program for producing this report are shown below:

PROC PRINT	PROC SQL

To sort the data, a PROC SORT step has been added to the PROC PRINT program, and an ORDER BY clause has been added to the PROC SQL program. In this situation, the PROC PRINT program is likely to use fewer CPU and memory resources than the PROC SQL program. The I/O resource usage should be approximately the same.

The sorted subset detail report lists the product ID, product name, and supplier name for all products that come from Sweden (SE), with observations sorted by the supplier name. The PROC PRINT program and PROC SQL program for producing this report are shown below:

PROC PRINT	PROC SQL

To sort the data, a PROC SORT step has been added to the PROC PRINT program. The PROC SORT step uses the KEEP= option to subset the observations, which improves efficiency. The PROC SQL step uses an ORDER BY clause for sorting and a WHERE clause for subsetting. In this situation, the CPU and memory usage for the PROC PRINT program and the PROC SQL program are about the same.

When summarizing data for one or more class variables, the tools in each of the following groups are similar in resource usage:

However, the relative efficiency of the two groups of tools varies according to the shape of the data, as shown below:

Suppose you want to summarize the data set Retail.Orders by calculating the average quantity of products sold for each order type (each value of the class variable Order_Type). You can use the following techniques to produce the summary report:

The following programs show each of these techniques. You can use these samples as models for creating benchmark programs in your own environment. Your results might vary depending on the structure of your data, your operating environment, and the resources that are available at your site. You can also view general recommendations for summarizing data for one class variable.

Programming Techniques

This PROC MEANS step creates an output report that displays the mean quantity of products sold (the analysis variable Quantity) for each order type (the class variable Order_Type).

This PROC REPORT step creates an output report that displays the mean quantity of products sold (the analysis variable Quantity) for each order type (the class variable Order_Type).

This program uses a PROC SORT step and a DATA step to create an output report. The PROC SORT step specifies the variables to be included in the report, sorts the observations by the values of the variable Order_Type, and generates the temporary output data set Orders. The DATA step calculates the mean quantity of products sold (the analysis variable Quantity) for each order type (the class variable Order_Type) and displays these values in the temporary output data set.

This PROC SQL step creates an output report that displays the mean quantity of products sold (the analysis variable Quantity) for each order type (the class variable Order_Type).

This PROC TABULATE step creates an output report that displays the mean quantity of products sold (the analysis variable Quantity) for each order type (the class variable Order_Type).

To produce summary statistics for combinations of class variables, you can use PROC MEANS in the following ways. These techniques differ in resource usage.

Combinations of Class Variables	Technique	Example
all possible combinations: c b b*c a a* c a* b a* b *c	basic PROC MEANS step
specific combinations: a * b and a * c	TYPES statement in PROC MEANS
specific combinations: a* b and a* c	NWAY option in multiple PROC MEANS steps
specific combinations: a* b and a* c	WHERE= option in the OUTPUT statement in PROC MEANS

The three techniques for summarizing data for specific combinations of class variables (all but the basic PROC MEANS step) differ in resource usage as follows:

We will learn how to use a basic PROC MEANS step and the three other techniques that are listed above.

By default, PROC MEANS (or PROC SUMMARY) creates the following:

Suppose you want to calculate average employee salaries and to group results for the combination of the three class variables Employee_Country, Department, and Employee_Gender.

The following PROC MEANS program creates both a report and a SAS data set:

proc means data=company.organization mean;
   class employee_country department 
         employee_gender; 
   var salary;
   output out=summary mean=average;
run;

The report groups data and displays summary statistics for the combination of the three class variables. A partial view of the output report is shown below:

The output data set groups data and displays summary statistics for both of the following:

A partial view of the output data set is shown below:

Each combination of class variables that is used to calculate and group statistics for PROC MEANS is called a type.

For example, the following basic PROC MEANS step specifies the three class variables a, b, and c:

proc means data=lib.dataset mean;
   class a b c; 
   var salary;
   output out=summary1
          mean=average;
run;

This PROC MEANS step generates seven possible types (combinations of the three variables):

SAS uses the _TYPE_ variable to specify the combination of class variables that PROC MEANS uses to calculate the statistics for each observation in the output data set. The _TYPE_ variable has a unique value for each combination. SAS always combines the class variables in a particular sequence, based on the order in which they are listed in the CLASS statement, in order to assign the _TYPE_ values. For example, for each of the seven types (seven possible combinations of three class variables) shown above, SAS assigns a value to _TYPE_ as follows:

As the number of class variables increases, so does the number of types. However, the highest _TYPE_ value (7, in this example) always indicates the combination of all class variables.

SAS includes the _TYPE_ variable in the output data set that is generated by PROC MEANS. In the output from the basic PROC MEANS step that was shown in the previous section, you can see that the observations are listed in order of increasing values of the _TYPE_ variable:

The first observation in the output data set has a _TYPE_ value of 0, which indicates that the statistics are generated for the entire data set.

By default, the output data set that is generated by PROC MEANS contains a separate observation for each unique combination of values of the class variables for each type. Each unique combination of values within a type is called a level of that type. In the output data set linked above (for example, there are 17 levels for type 2, 17 observations that have a _TYPE_ value of 2).

The output report that is generated by the basic PROC MEANS step contains only the observations that represent a combination of all of the class variables (the observations for which _TYPE_= 7). The _TYPE_ variable is not displayed in the report.

You can use the TYPES statement in PROC MEANS to specify which combinations of the class variables are used for grouping data and for calculating statistics. The CLASS statement is required in order to use the TYPES statement.

To request combinations of class variables more concisely, you can use a grouping syntax by placing parentheses around several variables and joining other variables or variable combinations. The following examples of TYPES statements illustrate the use of grouping syntax:

Suppose you want to calculate average employee salaries, as in the previous example. This time, you want to group results for the two combinations of class variables shown below:

To do this, you can add a TYPES statement to the PROC MEANS step:

proc means data=company.organization mean;
   class employee_country department
         employee_gender;
   var salary;
   types employee_country*department 

          
         employee_country*employee_gender; 
   output out=summary mean=average;
run;

This PROC MEANS step generates both an output report and an output data set. The report, shown below, has a separate table for each of the two combinations that were specified in the TYPES statement:

The output data set summarizes and reports data for only the combinations (types) that are specified in the TYPES statement. A partial view of the output data set is shown below:

Another way to specify a combination of class variables is to use the NWAY option in PROC MEANS:

The NWAY option enables you to generate summary statistics for one particular combination of class variables— all of the class variables—in a single PROC MEANS step. Therefore, to generate statistics for different combinations of class variables, you can specify a separate PROC MEANS step that contains the NWAY option for each combination.

Suppose you want to calculate average employee salaries and to group results for the following combinations of class variables:

You can use two PROC MEANS steps, each containing the NWAY option, as shown below. The first PROC MEANS step generates statistics for the first combination of class variables, and the second PROC MEANS steps generates statistics for the second combination of class variables.

proc means data=company.organization nway; 
   class employee_country department; 
   var salary;
   output out=summary1
          n=employees
          mean=average;
run;


proc means data=company.organization nway; 
   class employee_country employee_gender; 
   var salary;
   output out=summary2
          n=employees
          mean=average;
run;

When processing this program, SAS must read the data set once for each PROC MEANS step.

This program generates an output report and two output data sets. The report, shown in part below, has a separate table for each PROC MEANS step:

Each output data set summarizes and reports data for one of the types that are specified in the TYPES statement. A partial view of each output data set is shown below:

Figure 24.2. SAS Data Set Work.Summary1

Figure 24.3. SAS Data Set Work.Summary2

Yet another way to specify a combination of class variables is to use the WHERE= data set option in the OUTPUT statement:

When you use the WHERE= option in the OUTPUT statement, SAS must calculate all possible combinations of class variables, and subsetting does not occur until the results are written to output.

Suppose you want to calculate average employee salaries and to group results for two 2-way combinations of the three class variables Employee_Country, Department, and Employee_Gender. All possible combinations of these variables are listed below, and the two combinations that you want are highlighted:

To specify the types by _TYPE_ value, you can use the WHERE= option in the OUTPUT statement as shown below:

proc means data=company.organization;
   class employee_country department
         employee_gender;
   var salary;
   output out=summary
          (where=(_type_ in (5,6))) 
          n=employees
          mean=average;
run;

A partial view of the output report is shown below:

A partial view of the output data set Work.Summary is shown below:

Next, compare the resources that are used by these summarization techniques:

Suppose you want to summarize the data set Retail.Organization by calculating average employee salaries for two 3-way combinations of four class variables:

You can use the following techniques to produce an output report and one or more output data sets:

The following programs show each of these techniques. You can use these samples as models for creating benchmark programs in your own environment. Your results might vary depending on the structure of your data, your operating environment, and the resources that are available at your site. You can also view general recommendations for summarizing data for combinations of class variables.

Programming Techniques

This program calculates the average employee salary for two 3-way combinations of the class variables Employee_Country, Department, Employee_Gender, and Section. The TYPES statement specifies the two combinations. The program generates an output report and an output data set named Summary.

Each of the two PROC MEANS steps in this program calculates the average employee salary for a combination of three of the four class variables Employee_Country, Department, Employee_Gender, and Section. In each step, the NWAY option specifies that all three variables that are specified in the CLASS statement should be combined. The program generates an output report and two output data sets named Summary1 and Summary2.

This program calculates the average employee salary for two 3-way combinations of the class variables Employee_Country, Department, Employee_Gender, and Section. The WHERE= data set option in the OUTPUT statement specifies the two combinations by their _TYPE_ values. The program generates an output report and an output data set named Summary3.

The WAYS statement in PROC MEANS provides yet another way to display summary statistics for combinations of class variables. In the WAYS statement, you specify one or more integers that define the number of class variables to combine in order to form all the unique combinations of class variables.

For example, the following program uses the WAYS statement to create summary statistics for the following combinations of the three class variables Employee_Country, Department, and Employee_Gender:

The WAYS statement can be used instead of or in addition to the TYPES statement.