|Valid:||in a DATA step or a PROC step|
|How SAS Identifies the Beginning and End of a BY Group|
|In a DATA Step|
|In a PROC Step|
|With SAS Views|
|Processing BY Groups|
|Example 1: Specifying One or More BY Variables|
|Example 2: Specifying Sort Order|
|Example 3: BY-Group Processing with Nonsorted Data|
|Example 4: Grouping Observations By Using Formatted Values|
|Example 5: Combining Multiple Observations and Grouping Them Based on One BY Value|
BY <DESCENDING> variable-1
<...<DESCENDING> variable-n > <NOTSORTED><GROUPFORMAT>;
|Restriction:||You cannot use the DESCENDING option with data sets that are indexed because indexes are always stored in ascending order.|
|Featured in:||Specifying Sort Order|
uses the formatted values, instead of the internal values, of the BY variables to determine where BY groups begin and end, and therefore how FIRST.variable and LAST.variable are assigned. Although the GROUPFORMAT option can appear anywhere in the BY statement, the option applies to all variables in the BY statement.
|Restriction:||You must sort the observations in a data set based on the value of the BY variables before using the GROUPFORMAT option in the BY statement.|
|Restriction:||You can use the GROUPFORMAT option in a BY statement only in a DATA step.|
|Interaction:||If you also use the NOTSORTED option, you can group the observations in a data set by the formatted value of the BY variables without requiring that the data set be sorted or indexed.|
|Tip:||Using the GROUPFORMAT option is useful when you define your own formats to display data that is grouped.|
|Tip:||Using the GROUPFORMAT option in the DATA step ensures that BY groups that you use to create a data set match the BY groups in PROC steps that report grouped, formatted data.|
|Comparison:||BY-group processing in the DATA step using the GROUPFORMAT option is the same as BY-group processing with formatted values in SAS procedures.|
|See Also:||By-Group Processing in the DATA Step in SAS Language Reference: Concepts|
|Featured in:||Grouping Observations By Using Formatted Values|
|Tip:||The data set can be sorted or indexed by more than one variable.|
|Featured in:||Specifying One or More BY Variables, Specifying Sort Order, BY-Group Processing with Nonsorted Data, and Grouping Observations By Using Formatted Values|
|Restriction:||You cannot use the NOTSORTED option with the MERGE and UPDATE statements.|
|Tip:||The NOTSORTED option can appear anywhere in the BY statement.|
|Tip:||Using the NOTSORTED option is useful if you have data that falls into other logical groupings such as chronological order or categories.|
|Featured in:||BY-Group Processing with Nonsorted Data|
SAS identifies the beginning and end of a BY group by creating two temporary variables for each BY variable: FIRST.variable and LAST.variable. The value of these variables is either 0 or 1. SAS sets the value of FIRST.variable to 1 when it reads the first observation in a BY group, and sets the value of LAST.variable to 1 when it reads the last observation in a BY group. These temporary variables are available for DATA step programming but are not added to the output data set.
For a complete explanation of how SAS processes grouped data and of how to prepare your data, see By-Group Processing in the DATA Step in SAS Language Reference: Concepts.
The data sets that are listed in the SET, MERGE, or UPDATE statements must be sorted by the values of the variables that are listed in the BY statement or have an appropriate index. As a default, SAS expects the data sets to be arranged in ascending numeric order or in alphabetical order. The observations can be arranged by one of the following methods:
Note: MODIFY does not require sorted data, but sorting can improve performance.
Note: The BY statement honors the linguistic collation of data that is sorted by using the SORT procedure with the SORTSEQ=LINGUISTIC option.
For more information, see How to Prepare Your Data Sets in SAS Language Reference: Concepts.
You can specify the BY statement with some SAS procedures to modify their action. Refer to the individual procedure in the Base SAS Procedures Guide for a discussion of how the BY statement affects processing for SAS procedures.
If you create a DATA step view by reading from a DBMS and the SET, MERGE, UPDATE, or MODIFY statement is followed by a BY statement, the BY statement might cause the DBMS to sort the data in order to return the data in sorted order. Sorting the data could increase execution time.
proc format; value range low -55 = 'Under 55' 55-60 = '55 to 60' 60-65 = '60 to 65' 65-70 = '65 to 70' other = 'Over 70'; run; proc sort data=class out=sorted_class; by height; run; data _null_; format height range.; set sorted_class; by height groupformat; if first.height then put 'Shortest in ' height 'measures ' height:best12.; run;
Shortest in Under 55 measures 51.3 Shortest in 55 to 60 measures 56.3 Shortest in 60 to 65 measures 62.5 Shortest in 65 to 70 measures 65.3 Shortest in Over 70 measures 72
options pageno=1 nodate ls=80 ps=64; data Inventory; length RecordID 8 Invoice $ 30 ItemLine $ 50; infile datalines; input RecordID Invoice ItemLine &; drop RecordID; datalines; A74 A5296 Highlighters A75 A5296 Lot # 7603 A76 A5296 Yellow Blue Green A77 A5296 24 per box A78 A5297 Paper Clips A79 A5297 Lot # 7423 A80 A5297 Small Medium Large A81 A5298 Gluestick A82 A5298 Lot # 4422 A83 A5298 New item A84 A5299 Rubber bands A85 A5299 Lot # 7892 A86 A5299 Wide width, Narrow width A87 A5299 1000 per box ; data combined; array Line $ 60 ; retain Line1-Line4; keep Invoice Line1-Line4; set Inventory; by Invoice; if first.Invoice then do; call missing(of Line1-Line4); records = 0; end; records + 1; Line[records]=ItemLine; if last.Invoice then output; run; proc print data=combined; title 'Office Supply Inventory'; run;
Office Supply Inventory 1 Obs Line1 Line2 Line3 Line4 Invoice 1 Highlighters Lot # 7603 Yellow Blue Green 24 per box A5296 2 Paper Clips Lot # 7423 Small Medium Large A5297 3 Gluestick Lot # 4422 New item A5298 4 Rubber bands Lot # 7892 Wide width, Narrow width 1000 per box A5299