Sometimes you need to group observations by the values of a particular variable or order the observations in a particular way, such as alphabetically, in order to

The SORT procedure is one technique that can be used to group or order data. However, the SORT procedure can use a high volume of resources. When an uncompressed data file is sorted using the SORT procedure, SAS requires enough space in the data library for two copies of the data file, plus a workspace that is approximately two to four times the size of the data file.

In some cases, you might be able to use techniques other than the SORT procedure to group or order observations. In other cases, you might be able to use options or techniques with the SORT procedure that enable you to minimize resource usage.

In this chapter, you learn to

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

Part 1: SQL Processing with SAS

Part 3: Advanced SAS Programming Techniques

Part 4: Optimizing SAS Programs

BY-group processing is a method of processing observations from one or more SAS data sets that are grouped or ordered by the values of one or more common variables. You can use BY-group processing in both DATA steps and PROC steps.

The most common use of BY-group processing in the DATA step is to combine two or more SAS data sets by using the BY statement with a SET, MERGE, UPDATE, or MODIFY statement. When you use a SET, MERGE, or UPDATE statement, the data sets must first be ordered on the values of the BY variable unless you index the data sets. You can also use the NOTSORTED option in the BY statement with a SET statement.

When BY-group processing is used with an index that is based on one of the BY variables, the data can be sequenced without using the SORT procedure. The data can be sequenced by different variables if multiple indexes are used. Because indexes are updated automatically, there is no need to re-sort a data set when observations are modified or added.

However, using BY-group processing with an index has two disadvantages:

Suppose you want to use an existing data set, Retail.Order_fact, to create a new SAS data set that is ordered by the variable Order_Date. You could accomplish this task using

The following sample 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 BY-group processing with an index or sort.

Programming Techniques

In this example, the SAS data set Retail.Order_fact is indexed on the variable Order_Date. The data in Retail.Order_fact is in random order.

In this example, the SAS data set Retail.Order_fact is sorted on the variable Order_Date before it is read using the DATA step.

In this example, the SAS data set Retail.Order_fact is sorted using the SORT procedure. The data is then read using the DATA step.

You can also use the NOTSORTED option with a BY statement to create ordered or grouped reports without sorting the data. The NOTSORTED option specifies that observations that have the same BY value are grouped together but are not necessarily sorted in alphabetical or numeric order.

The NOTSORTED option can appear anywhere in the BY statement and is useful if you have data that is in logical categories or groupings such as chronological order. The NOTSORTED option works best when observations that have the same BY value are stored together.

Suppose you want to use the PRINT procedure to print the contents of the data set Retail.Europe, which contains data about European customers. The data set includes values for country names (Country_Name) as well as two-character country codes (Country_Code).

The data is grouped and sorted by the values of Country_Code. However, you want the observations in the output to be grouped by the values of Country_Name.

You can use the NOTSORTED option with a BY statement to accomplish this task without using the SORT procedure.

PROC PRINT output shows that the data is grouped but is not in sorted order. For example, observations in which the value of Country_Name is Germany are followed by observations in which the value of Country_Name is Denmark.

Table 23.3. PROC PRINT Output, Selected Observations

The NOTSORTED option can be used with FIRST. variable and LAST. variable, which are temporary automatic variables in the PDV that identify the first and last observations in each BY group.

These temporary variables are available for DATA step programming but are not added to the output data set. Their values indicate whether an observation is

You can take actions conditionally, based on whether you are processing the first observation of a BY group or the last.

When an observation is the first in a BY group, SAS sets the value of FIRST.variable to 1. For all other observations in the BY group, the value of FIRST.variable is 0. Likewise, if an observation is the last in a BY group, SAS sets the value of LAST. variable to 1. For all other observations in the BY group, the value of LAST. variable is 0.

The following program creates a new SAS data set Work.New. In the input data set, observations that have the same value for Ordername (Retail, Catalog, or Internet) are grouped together.

data work.new;
   set company.sales
   by ordername notsorted;
run;

When the program is submitted, SAS creates the temporary variables FIRST.Ordername and LAST.Ordername. These variables can be used during the DATA step, but they do not become variables in the new data set. The value 1 flags the beginning and end of each value in the BY group:

The GROUPFORMAT option uses the formatted values of a variable instead of the internal values to determine where a BY group begins and ends, and how FIRST.variable and LAST. variable are computed.

The GROUPFORMAT option

When the GROUPFORMAT option is used, the data set must be sorted by the GROUPFORMAT variable or grouped by the formatted values of the GROUPFORMAT variable.

Suppose you want to create a summary report that includes the number of orders for each quarter in 2002. The data for the report is stored in the SAS data set Company.Orders.

By creating a format for the data and using the GROUPFORMAT and NOTSORTED options, you cause SAS to create the variables FIRST.Order_Date and LAST.Order_Date based on the formatted values, not the internal values. This groups the data without requiring the creation of a new variable.

You can also use a CLASS statement to avoid a sort. Unlike the BY statement, the CLASS statement does not require the data to be presorted using the CLASS values, nor does it require an index that is based on the CLASS variables.

If the data cannot be sorted, the CLASS statement is more efficient than the BY statement in terms of CPU time, memory, and I/O usage. However, unlike using the BY statement, presorting the data for use with a CLASS statement does not provide a significant benefit.

Remember that a CLASS statement specifies the variables whose values define the subgroup combinations for an analysis by a SAS procedure. You can use the CLASS statement with the following Base SAS procedures:

Variables in a CLASS statement are referred to as class variables. Class variables can be numeric or character. Class variables can have continuous values, but they typically have a few discrete values that define the classifications of the variable.

The Company.Orders data set contains the variable Order_Type, which has three discrete values:

Suppose you want to show the average retail price, cost per unit, and discount for each value of Order_Type. You could use the MEANS procedure with either a BY statement or a CLASS statement to complete this task. The statistics created with either of these techniques are the same. However, the report layouts differ.

When the BY statement is used, SAS creates a report for each value of the BY variable. The statistics for each value of Order_Type appear in a separate tabular report.

proc sort data=company.order_fact(keep=order_type
          quantity total_retail_price
          costprice_per_unit discount) out=company.orders;
   by order_type; 
run;


proc means data=company.orders mean;

   by order_type; 
   var total_retail_price -- discount;
   freq quantity;
run;

Table 23.5. Output, PROC MEANS with a BY Statement

When the CLASS statement is used, only one report is created. The statistics for each value of Order_Type are consolidated into one tabular report.

Suppose you want to create a summary report that shows the average retail price, cost per unit, and discount for each type of order in the Retail.Order_fact data set.

Among the techniques you could use are

The following sample 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 using a BY or CLASS statement to avoid a sort.

Programming Techniques

The following program creates a report for each value of the BY variable Order_Type. Each report contains the mean value for the variables Total_Retail_Price, CostPrice_Per_Unit, and Discount. The input data is presorted by the value of Order_Type.

The following program uses a CLASS statement to create a single, tabular report that includes the mean value of Total_Retail_Price, CostPrice_Per_Unit, and Discount for each category of Order_Type.

In the following program, the input data set Retail.Order_fact is presorted by the value of Order_Type. The PROC MEANS step uses a CLASS statement to create a single, tabular report that includes the mean value of Total_Retail_Price, CostPrice_Per_Unit, and Discount for each category of Order_Type.

In the following program, the PROC SORT step first sorts the input data set Retail.Order_fact by the value of Order_Type. The PROC MEANS step then creates a report for each value of Order_Type. Each report contains the mean value for the variables Total_Retail_Price, CostPrice_Per_Unit, and Discount.

If you are working with input data that is already sorted, you can specify how the data is ordered by using the SORTEDBY= data set option.

Although the SORTEDBY= option does not sort a data set, it sets the value of the Sorted flag. It does not set the value of the Validated sort flag. (PROC SORT sets the Validated sort flag.) To see the values of these flags (YES or NO), use PROC CONTENTS.

proc contents;
   data=company.transactions;
run;

Figure 23.1. Partial PROC CONTENTS Output

Suppose you want to create a sorted SAS data set from an external file that contains invoice information. The external file is already sorted by invoice number.

You can use the SORTEDBY= data set option to sort the data by the value of Invoice.

data company.transactions (sortedby=invoice); 
     infile extdata;
     input Invoice 1-4 Item $6-20 Amount comma 6.;
   run;

When the Company.Transactions data set is created, the sort information is stored with it. PROC SORT checks the sort information before it sorts a data set so that data is not re-sorted unnecessarily. If you attempt to re-sort the data, the log indicates that the data set is already sorted and that no additional sorting occurred.

proc sort data=company.transactions;
   by invoice;
run;

Table 23.6. SAS Log

The performance of threaded sorting is affected by the value of the CPUCOUNT= system option. CPUCOUNT= specifies the number of processors that thread-enabled applications should assume will be available for concurrent processing. SAS uses this information to determine how many threads to start, not to restrict the number of CPUs that will be used.

When data is sorted, SAS requires enough space in the data library for two copies of the data file that is being sorted as well as additional workspace.

In releases before SAS 9, the workspace required for an uncompressed data file is approximately three to four times the size of the data file. Beginning with SAS 9, the workspace required for an uncompressed data file is approximately twice the size of the data file. The workspace can be allocated in memory and/or on disk as a utility file, depending on the sort utility and on the options chosen.

You can use the following formula to calculate the amount of workspace that the SORT procedure requires:

Suppose you want to submit the following program under SAS 9:

proc sort data=company.customers;
   by customer_group customer_lastname;
run;

You can use the CONTENTS procedure or the DATASETS procedure to obtain the information that is required for the calculation.

Figure 23.2. Partial PROC CONTENTS Output

In this case, the amount of workspace needed to sort the data set is 48,575,160 bytes.

48,575,160 bytes=(70+200)*89954*2

The SORTSIZE= system option or procedure option specifies how much memory is available to the SORT procedure. Specifying the SORTSIZE= option in the PROC SORT statement temporarily overrides the SAS system option SORTSIZE=.

Generally, the value of SORTSIZE= should be less than the physical memory that is available to your process.

If the required workspace is less than or equal to the value specified in the SORTSIZE= system option or procedure option, then the entire sort can take place in memory, which reduces processing time.

If the actual required workspace is greater than the value specified in the SORTSIZE= system option or procedure option, then processing time is increased because the SORT procedure must

This process is repeated until all of the data is sorted. The SORT procedure then interleaves the data in the utility files to create the final data set.

PROC SORT attempts to adapt to the constraint that is imposed by the SORTSIZE= option. Because PROC SORT uses memory as much as possible,

A data set is too large to sort when there is insufficient room in the data library for a second copy of the data set or when there is insufficient disk space for three to four temporary copies of the data set.

One approach to this situation is to divide the large data set into smaller data sets. The smaller data sets can then be sorted and combined to re-create the large data set. This approach is similar to the process that is used in a threaded sort.

Techniques for dividing and sorting a large data set include

Techniques that can be used to rebuild a large data from smaller, sorted data sets set include

Suppose you want to sort the SAS data set Retail.Order_fact by the value of Order_Date. The data set is too large to sort using a single SORT procedure. You could accomplish this task by

The following sample 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 dividing and sorting a large data set.

Programming Techniques

This program segments the data set Retail.Order_fact into three smaller data sets by observation number. The three smaller data sets, Work.One, Work.Two, and Work.Three, are sorted by the value of Order_Date. The large data set is then re-created by interleaving the three smaller, sorted data sets.

This program segments the data set Retail.Order_fact into three smaller data sets by using a subsetting IF statement and the YEAR function. The three smaller data sets, Work.One, Work.Two, and Work.Three, are then sorted by the value of Order_Date. The large data set is then re-created by concatenating the three smaller, sorted data sets. Interleaving is not required because the smaller data sets do not overlap each other on the sort key Order_Date.

This program segments the data set Retail.Order_fact into three smaller data sets by using a subsetting IF statement and a date constant. The three smaller data sets, Work.One, Work.Two, and Work.Three, are then sorted by the value of Order_Date. The large data set is then re-created by concatenating the three smaller, sorted data sets. Interleaving is not required because the smaller data sets do not overlap each other on the sort key Order_Date.

This program segments the data set Retail.Order_fact into three smaller data sets by using a WHERE statement and the YEAR function. The three smaller data sets, Work.One, Work.Two, and Work.Three, are sorted by the value of Order_Date. The large data set is then re-created by concatenating the three smaller, sorted data sets. Interleaving is not required because the smaller data sets do not overlap each other on the sort key Order_Date.

This program segments the data set Retail.Order_fact into three smaller data sets by using a WHERE statement with a date constant. The three smaller data sets, Work.One, Work.Two, and Work.Three, are sorted by the value of Order_Date. The large data set is then re-created by concatenating the three smaller, sorted data sets. Interleaving is not required because the smaller data sets do not overlap each other on the sort key Order_Date.

Like the programs shown in the previous section, each of the following programs illustrates a method for dividing the large data set Retail.Order_fact into smaller data sets for sorting. However, in this example, the smaller data sets, Work.One, Work.Two, and Work.Three, are combined using the APPEND procedure rather than a DATA step in programs 2, 3, 4, and 5.

The following sample 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 dividing and sorting a large data set.

Programming Techniques

This program segments the data set Retail.Order_fact into three smaller data sets by observation number. The three smaller data sets, Work.One, Work.Two, and Work.Three, are sorted by the value of Order_Date. PROC APPEND cannot be used to re-create a large data set that was segmented using FIRSTOBS= and OBS=. Therefore, the large data set is re-created by interleaving the three smaller, sorted data sets.

This program segments the data set Retail.Order_fact into three smaller data sets by using a subsetting IF statement and the YEAR function. The three smaller data sets, Work.One, Work.Two, and Work.Three, are then sorted by the value of Order_Date. The large data set is then re-created using the APPEND procedure.

This program segments the data set Retail.Order_fact into three smaller data sets by using a subsetting IF statement and a date constant. The three smaller data sets, Work.One, Work.Two, and Work.Three, are then sorted by the value of Order_Date. The large data set is then re-created using the APPEND procedure.

This program segments the data set Retail.Order_fact into three smaller data sets by using a WHERE statement and the YEAR function. The three smaller data sets, Work.One, Work.Two, and Work.Three, are sorted by the value of Order_Date. The large data set is then re-created using the APPEND procedure.

This program segments the data set Retail.Order_fact into three smaller data sets by using a WHERE statement with a date constant. The three smaller data sets, Work.One, Work.Two, and Work.Three, are sorted by the value of Order_Date. The large data set is then re-created using the APPEND procedure.

You can also use the TAGSORT option to sort a large data set. The TAGSORT option stores only the BY variables and the observation numbers in temporary files. The BY variables and the observation numbers are called tags. At the completion of the sorting process, PROC SORT uses the tags to retrieve records from the input data set in sorted order.

When the total length of the BY variables is small compared to the record length, TAGSORT reduces temporary disk usage considerably because sorting just the BY variables means sorting much less data. However, processing time is usually higher than if a regular sort is used because TAGSORT increases CPU time and I/O usage in order to save memory and disk space. TAGSORT

In the following program, only the BY variables, Customer_ID and Order_Date, and the tags are stored in temporary files within the sort workspace. SAS then

proc sort data=company.orders  tagsort;
   by customer_id order_date;
run;

The NODUPKEY option checks for and eliminates observations that have duplicate BY-variable values. If you specify this option, then PROC SORT compares all BY-variable values for each observation to those for the previous observation that was written to the output data set. If an exact match is found, then the observation is not written to the output data set.

The SAS data set Company.Reorder contains two duplicated observations. Observation 9 is a duplicate of observation 1, and observation 7 is a duplicate of observation 2. The duplicate observations are removed when the data is sorted by the values of Product_Line and Product_Name and when the NODUPKEY option is used.

proc sort data=company.reorder  nodupkey; 
   by product_line product_name; 
run;

The NODUPRECS option also checks for and eliminates duplicate observations. However, unlike the NODUPKEY option, the NODUPRECS option compares all of the variable values for each observation to those for the previous observation that was written to the output data set. If an exact match is found, then the observation is not written to the output data set.

Because NODUPRECS checks only consecutive observations, some nonconsecutive duplicate observations might remain in the output data set. You can remove all duplicates with this option by sorting on all variables.

When Company.Reorder is sorted using the BY variable Product_Line with the NODUPRECS option, the two observations that contain the product name Kids Children's Fleece Hat do not become consecutive duplicates. They are written to the output data set. The duplicate observation that contains the product name Ski Jacket w/Removable Fleece become consecutive duplicates. Only one of them is written to the output data set.

proc sort data=company.reorder noduprecs; 
   by product_line; 
run;

Both duplicate observations are removed when Company.Reorder is sorted by both Product_Line and Product_Name and when the NODUPRECS option is used.

proc sort data=company.reorder  noduprecs; 
      by product_line product_name; 
run;

EQUALS | NOEQUALS is a SORT procedure option that helps to determine the order of observations in the output data set. When you use NODUPRECS or NODUPKEY to remove observations from the output data set, the choice of EQUALS or NOEQUALS can have an effect on which observations are removed.

EQUALS is the default. For observations that have identical BY-variable values, EQUALS maintains the order from the input data set in the output data set. NOEQUALS does not necessarily preserve this order in the output data set. NOEQUALS can save CPU time and memory resources.

The following program uses PROC SORT with the NODUPKEY option and the NOEQUALS option to create an output data set that contains only the first observation in each BY group. Notice that the output data set Work.New contains different observations when the EQUALS option is used.

proc sort data=company.products out=work.new
     nodupkey noequals; 
      by product_line;
run;

FIRST. LAST. processing in the DATA step can also be used to remove duplicate observations from a SAS data set.

In the data set Company.Onorder, the fourth observation contains a duplicate value for Product_Name. The following program removes the observation that contains the duplicate value by first sorting the input data set, Company.Onorder, by the value of Product_Name. The DATA step then selects only the first observation in the BY group.

proc sort data=company.onorder 
          out=work.sorted;
      by product_name; 
run;
data work.onorder2;
   set work.sorted;
   by product_name; 
   if first.product_name; 
run;

Suppose you want to remove observations from the data set Retail.Order_fact in which the value of Order_Date is duplicated. Among the techniques you could use are

The following sample 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 recommendation for eliminating duplicates.

Programming Techniques

This program uses the NODUPKEY option and the EQUALS option to check for and eliminate observations that have duplicate BY-variable values. For observations that have identical BY-variable values, the EQUALS option maintains the order from the input data set in the output data set.

This program uses the NODUPKEY option and the NOEQUALS option to check for and eliminate observations that have duplicate BY-variable values. For observations that have identical BY-variable values, the NOEQUALS option does not necessarily maintain the order from the input data set in the output data set, which can save CPU time and memory.

In this program, the input data set is first sorted using the SORT procedure. Duplicate observations are then removed using BY-group and FIRST. processing.

Host sort utilities are third-party sort packages that are available in some operating environments. In some cases, using a host sort utility with PROC SORT might be more efficient than using the SAS sort utility with PROC SORT.

The following table lists the host sort utilities that might be available at your site. SAS uses the values that are set for the SORTPGM=, SORTCUT=, SORTCUTP=, and SORTNAME= system options to determine which sort to use.

The value specified in the SORTPGM= system option tells SAS whether to use the SAS sort, to use the host sort, or to determine which sort utility is best for the data set.

The SORTCUTP= system option specifies the number of bytes above which the host sort utility is used instead of the SAS sort utility.

The following table lists the default values for SORTCUTP= in the z/OS, UNIX, and Windows operating environments.

Beginning with SAS 9, the SORTCUT= system option can be used to specify the number of observations above which the host sort utility is used instead of the SAS sort utility.

The default value of the SORTCUT= system option is 0.

The SORTNAME= option specifies the host sort utility that will be used if the value of SORTPGM= is BEST or HOST.

When you specify SORTPGM= BEST, SAS uses the value of the SORTCUT= and SORTCUTP= options to determine whether to use the host sort or the SAS sort. If you specify values for both the SORTCUT= and SORTCUTP= options, and if either condition is true, SAS chooses the host sort.

In the program below, if the size of the SAS data set Company.Orders is larger than 10,000 bytes, the host sort utility, Syncsort, will be used instead of the SAS sort utility.

options sortpgm=best sortcutp=10000
        sortname=syncsort;


proc sort data=company.orders out=company.deliveries;
   by delivery_date;
run;