SAS - BY Statement

BY Statement 

BY 语句用于规定分组变量，和set, merge, update或modify语句搭配使用。可以用于 DATA step 或 PROC step 中。参考链接：Statements: BY Statement - 9.2 (sas.com)

 

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.

(简译：BY 通过自动生成临时变量 FIRST.variable 和 LAST.variable 来识别数据集按照 BY group 中的变量分组后，每组的首行和末行)

 

Use BY In a DATA Step

The BY statement applies only to the SET, MERGE, MODIFY, or UPDATE statement that precedes it in the DATA step, and 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 statements must be sorted by the values of the variables that are listed in the BY statement or have an appropriate index. 

(简译：DATA步中，BY 语句只能在 SET, MERGE, MODIFY, or UPDATE statements 使用，并且每个statement中只能用一次，使用前，数据集必须先按照BY group 的变量排序。默认按照升序或字母顺序排序 [ascending numeric order or in alphabetical order])

如果要降序排序，在变量前加 descending ：by descending var1 var2. 按照 var1 降序, var2 升序排序。

 

Use BY In a PROC Step

一些 SAS procedures 中也可以使用 BY 语句，such as PROC SORT. 

 

Example 

This example creates a new data set, CLASS.BESTSCORES, which contains one observation for each ID value.

proc sort data=class.allscores;
   by id;
run;

data class.bestscores;
   drop grade;
   set class.allscores;
   by id;
      /* Prevents HIGHEST from being reset to missing for each iteration. */
   retain highest;
      /* Sets HIGHEST to missing for each different ID value. */
   if first.id then highest=.;
      /* Compares HIGHEST to GRADE in current iteration and resets value if GRADE is higher. */
   highest=max(highest,grade);
   if last.id then output;
run;