BY-Group Processing

When a DATA step reads sorted data with a BY statement, SAS creates two temporary flags for each BY variable:

• FIRST.var — 1 on the first row of each group.
• LAST.var — 1 on the last row of each group.

Combined with variables that keep their values between rows, they let you compute per-group results in a single pass.

RETAIN and the sum statement

Normally, variables you create are reset to missing at the start of each iteration. Two ways to keep them:

• retain total 0; — keep total across rows, starting at 0.
• total + amount; — a sum statement: it retains total, starts it at 0, and treats a missing amount as 0.

data running;
   input day amount;
   total + amount;
   datalines;
1 10
2 .
3 25
;
run;

proc print data=running noobs;
run;

day    amount    total

 1       10        10
 2        .        10
 3       25        35

total = total + amount; would not work: total would be reset to missing each row.

Per-group summaries

proc sort data=sashelp.class out=cls;
   by sex;
run;

data by_sex;
   set cls;
   by sex;
   if first.sex then do;
      n = 0;
      total = 0;
   end;
   n + 1;
   total + weight;
   if last.sex then do;
      avg = total / n;
      output;
   end;
   keep sex n total avg;
   format avg 6.2;
run;

proc print data=by_sex noobs;
run;

Sex     n     total       avg

 F      9     811.0     90.11
 M     10    1089.5    108.95

1. On the first row of a group, reset the counters.
2. On every row, accumulate.
3. On the last row, compute and OUTPUT one summary row.

Counting within groups

Number the rows within each group — for example, to keep each patient's first visit:

data visits;
   input patient $ visit_date :date9.;
   format visit_date date9.;
   datalines;
P1 03JAN2024
P1 17JAN2024
P2 05JAN2024
P1 30JAN2024
P2 20FEB2024
;
run;

proc sort data=visits;
   by patient visit_date;
run;

data numbered;
   set visits;
   by patient;
   if first.patient then visit_no = 0;
   visit_no + 1;
run;

proc print data=numbered noobs;
run;

patient    visit_date    visit_no

  P1        03JAN2024        1
  P1        17JAN2024        2
  P1        30JAN2024        3
  P2        05JAN2024        1
  P2        20FEB2024        2

Add if first.patient; to keep only first visits, or if last.patient; for the latest.

Common mistakes

• Forgetting to sort — ERROR: BY variables are not properly sorted on data set WORK.VISITS.
• With by a b;, first.b is also 1 whenever a changes — nested groups behave the way you'd hope.

Practice

Using cls from above, find the tallest student of each sex.

proc sort data=sashelp.class out=cls2;
   by sex height;
run;

data tallest;
   set cls2;
   by sex;
   if last.sex;
   keep sex name height;
run;

proc print data=tallest noobs;
run;

Name      Sex    Height

Mary       F      66.5
Philip     M      72.0