BY Statement

data ShoesByRegion;
   set sashelp.shoes; 
   by descending Region 
      descending Subsidiary;
run;
 

data sedanTypes;
   set cars;
   by 'Sedan Types'n;
   if 'first.Sedan Types'n then type=1;
run;

Details

• How SAS Identifies the Beginning and End of a BY Group
• Using a BY Statement in a DATA Step
• Using a BY Statement in a PROC Step
• Using a BY Statement with SAS Views
• How SAS Processes BY Groups

How SAS Identifies the Beginning and End of a BY Group

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 how to prepare your data, see BY-Group Processing in the DATA Step in SAS Language Reference: Concepts.

Using a BY Statement in a DATA Step

The BY statement applies only to the SET, MERGE, MODIFY, or UPDATE statement that precedes it in the DATA step. Only one BY statement can accompany each of these statements in a DATA step.

The data sets that are listed in the SET, MERGE, or UPDATE statement must be sorted by the values of the variables that are listed in the BY statement or that 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 using one of these methods:

• sort the data set
• create an index for the variables
• read in the observations in order

For more information, see How to Prepare Your Data Sets in SAS Language Reference: Concepts.

Using a BY Statement in a PROC Step

You can specify the BY statement with some SAS procedures to modify their action. For more information, see the individual procedures in Base SAS Procedures Guide for a discussion of how the BY statement affects processing for SAS procedures.

Using a BY Statement with SAS Views

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.

How SAS Processes BY Groups

SAS assigns these values to FIRST.variable and LAST.variable:

• FIRST.variable has a value of 1 under these conditions:
  • when the current observation is the first observation that is read from the data set.
  • when you do not use the GROUPFORMAT option and the internal value of the variable in the current observation differs from the internal value in the previous observation.

    If you use the GROUPFORMAT option, FIRST.variable has a value of 1 when the formatted value of the variable in the current observation differs from the formatted value in the previous observation.

  • FIRST.variable has a value of 1 for any preceding variable in the BY statement.

    In all other cases, FIRST.variable has a value of 0.

• LAST.variable has a value of 1 under these conditions:
  • when the current observation is the last observation that is read from the data set.
  • when you use the GROUPFORMAT option and the internal value of the variable in the current observation differs from the internal value in the next observation.

    If you use the GROUPFORMAT option, LAST.variable has a value of 1 when the formatted value of the variable in the current observation differs from the formatted value in the next observation.

  • LAST.variable has a value of 1 for any preceding variable in the BY statement.

    In all other cases, LAST.variable has a value of 0.

by month notsorted;

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=sashelp.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

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{4} $ 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;