Compute and output group-level aggregates¶
Three approaches:
- Data step: By-Group Processing
- PROC SUMMARY
- PROC SQL
To start with, we create an example SAS dataset with repeated observations per ID (i.e., multiple records within a BY-group) and sort the data set by ID. The calorie intake data are for breakfast, lunch, and dinner for 4 persons (12 data points).
In the code snippet below,
- The OPTIONS statement controls output formatting (no date, left-aligned, no page numbers).
- Data Step creates the work.Have SAS dataset with two variables: - ID (character) - calorie_intake (numeric)
- DATALINES supplies the raw data.
In the code snippet below,
- The OPTIONS statement controls output formatting (no date, left-aligned, no page numbers).
- Data Step creates the work.Have SAS dataset with two variables: - ID (character) - calorie_intake (numeric)
DATALINES supplies the raw data.
*Ex10_first_var_last_var.sas (Part 1);
ods html close;
options nodate nocenter nonumber nosource;
DATA work.Have;
INPUT ID $ calorie_intake;
DATALINES;
A 200
A 800
A 500
C 250
C 850
C 550
B 300
B 900
B 600
D 260
D 900
D 800
;
The SAS System NOTE: Writing HTML5(SASPY_INTERNAL) Body file: _TOMODS1 NOTE: The data set WORK.HAVE has 12 observations and 2 variables. NOTE: DATA statement used (Total process time): real time 0.02 seconds cpu time 0.00 seconds The SAS System E3969440A681A2408885998500000006
PROC SORT
- sorts the work.Have dataset by ID (required for BY-group processing later)
- outputs a dataset named work.Sorted_have.
PROC SORT data=work.Have
out=work.Sorted_have;
BY ID;
run;
The SAS System 1141 ods listing close;ods html5 (id=saspy_internal) file=_tomods1 options(bitmap_mode='inline') device=svg style=HTMLBlue; 1141 ! ods graphics on / outputfmt=png; NOTE: Writing HTML5(SASPY_INTERNAL) Body file: _TOMODS1 1142 1143 PROC SORT data=work.Have 1144 out=work.Sorted_have; 1145 BY ID; 1146 run; NOTE: There were 12 observations read from the data set WORK.HAVE. NOTE: The data set WORK.SORTED_HAVE has 12 observations and 2 variables. NOTE: PROCEDURE SORT used (Total process time): real time 0.02 seconds cpu time 0.01 seconds 1147 1148 1149 1150 ods html5 (id=saspy_internal) close;ods listing; 1151 The SAS System 1152
The DATA NULL step below still processes each observation from work.sorted_have, sending the output to the log instead of creating a dataset. This kind of data step is useful for debugging, logging, or testing calculations.
By-Group Processing The BY statement in the DATA step enables SAS to process data in groups.
A BY statement in the DATA step below creates two temporary variables (First. /Last.Values) for each variable listed in the BY statement. See the log below.
The FIRST.variable is set to 1 when an observation is the first in a BY group. Otherwise it equals to 0.
The LAST.variable is set to 1 when an observation is the last in a BY group. Otherwise it equals to 0.
*Ex10_first_var_last_var.sas (Part 2);
DATA _NULL_;
SET work.sorted_have; BY ID;
PUTLOG ID= First.ID= LAST.ID= calorie_intake=;
run;
The SAS System 1291 ods listing close;ods html5 (id=saspy_internal) file=_tomods1 options(bitmap_mode='inline') device=svg style=HTMLBlue; 1291 ! ods graphics on / outputfmt=png; NOTE: Writing HTML5(SASPY_INTERNAL) Body file: _TOMODS1 1292 1293 *Ex10_first_var_last_var.sas (Part 2); 1294 DATA _NULL_; 1295 SET work.sorted_have; BY ID; 1296 PUTLOG ID= First.ID= LAST.ID= calorie_intake=; 1297 run; ID=A FIRST.ID=1 LAST.ID=0 calorie_intake=200 ID=A FIRST.ID=0 LAST.ID=0 calorie_intake=800 ID=A FIRST.ID=0 LAST.ID=1 calorie_intake=500 ID=B FIRST.ID=1 LAST.ID=0 calorie_intake=300 ID=B FIRST.ID=0 LAST.ID=0 calorie_intake=900 ID=B FIRST.ID=0 LAST.ID=1 calorie_intake=600 ID=C FIRST.ID=1 LAST.ID=0 calorie_intake=250 ID=C FIRST.ID=0 LAST.ID=0 calorie_intake=850 ID=C FIRST.ID=0 LAST.ID=1 calorie_intake=550 ID=D FIRST.ID=1 LAST.ID=0 calorie_intake=260 ID=D FIRST.ID=0 LAST.ID=0 calorie_intake=900 ID=D FIRST.ID=0 LAST.ID=1 calorie_intake=800 NOTE: There were 12 observations read from the data set WORK.SORTED_HAVE. NOTE: DATA statement used (Total process time): real time 0.01 seconds cpu time 0.00 seconds 1298 1299 1300 ods html5 (id=saspy_internal) close;ods listing; 1301 The SAS System 1302
*Ex10_first_var_last_var.sas (Part 3);
options nodate nocenter nonumber;
Data _Null_;
SET work.sorted_have;
BY ID;
file 'C:\Data\BY_VAR.txt';
if _n_=1 then put @10 "ID" +3 "Total_intake";
if first.id=1 then total_intake=0;
total_intake+calorie_intake;
if last.id then do;
put @11 id +6 total_intake;
putlog @11 id +6 total_intake;
end;
run;
The SAS System 1373 ods listing close;ods html5 (id=saspy_internal) file=_tomods1 options(bitmap_mode='inline') device=svg style=HTMLBlue; 1373 ! ods graphics on / outputfmt=png; NOTE: Writing HTML5(SASPY_INTERNAL) Body file: _TOMODS1 1374 1375 *Ex10_first_var_last_var.sas (Part 3); 1376 options nodate nocenter nonumber; 1377 Data _Null_; 1378 SET work.sorted_have; 1379 BY ID; 1380 file 'C:\Data\BY_VAR.txt'; 1381 if _n_=1 then put @10 "ID" +3 "Total_intake"; 1382 if first.id=1 then total_intake=0; 1383 total_intake+calorie_intake; 1384 if last.id then do; 1385 put @11 id +6 total_intake; 1386 putlog @11 id +6 total_intake; 1387 end; 1388 run; NOTE: The file 'C:\Data\BY_VAR.txt' is: Filename=C:\Data\BY_VAR.txt, RECFM=V,LRECL=32767,File Size (bytes)=0, Last Modified=01Mar2026:11:19:15, Create Time=26Feb2026:15:52:49 A 1500 B 1800 C 1650 D 1960 NOTE: 5 records were written to the file 'C:\Data\BY_VAR.txt'. The minimum record length was 22. The maximum record length was 26. NOTE: There were 12 observations read from the data set WORK.SORTED_HAVE. NOTE: DATA statement used (Total process time): real time 0.01 seconds cpu time 0.01 seconds 1389 1390 1391 ods html5 (id=saspy_internal) close;ods listing; 1392 The SAS System 1393
By-Group Processing The BY statement in the DATA step enables SAS to process data in groups.
A BY statement in the DATA step below creates two temporary variables (First. /Last.Values) for each variable listed in the BY statement. See the log below.
The FIRST.variable is set to 1 when an observation is the first in a BY group. Otherwise, it equals to 0.
The LAST.variable is set to 1 when an observation is the last in a BY group. Otherwise, it equals to 0.
The DATA step calculates total calorie intake per ID and writes the results both to a text file and the SAS log, using BY-group processing and the SUM statement. Only the last row of each group is output, so each ID appears once with its total.
Step-by-Step Explanation
1️⃣ NULL Dataset
- NULL tells SAS not to create a new dataset.
- The DATA step processes rows and writes output (to a file or log) instead of storing data.
2️⃣ SET work.sorted_have; BY ID;
- Reads work.sorted_have row by row.
- BY ID activates BY-group processing, creating automatic temporary variables:
FIRST.ID → 1 if this is the first row of a given ID group LAST.ID → 1 if this is the last row of a given ID group Important: The dataset must be sorted by ID (done previously).
3️⃣ file 'C:\Data\BY_VAR.txt';
- Specifies the external text file where output will be written.
- All PUT statements after this go to that file.
4️⃣ Print Header Once
- if n=1 then put @10 "ID" +3 "Total_intake";
- N=1 → Only for the first observation in the DATA step.
- @10 → Start printing at column 10.
- "ID" → Column header
- +3 "Total_intake" → Adds 3 spaces, then prints "Total_intake" as second column.
- Result: File will have a header like: ID Total_intake
5️⃣ Initialize SUM for Each ID
- if first.id=1 then total_intake=0;
- Resets the running total at the start of each ID group.
6️⃣ SUM Statement
- total_intake + calorie_intake;
A DATA step SUM statement:
- Automatically retains total_intake across rows in the group.
- Automatically treats missing values as zero.
- Adds calorie_intake to total_intake for each row of the ID.
7️⃣ Output at Last Row of Group
- Only executes at the last observation for each ID:
Key Points
- BY-group processing:
- FIRST.ID → resets sum
- LAST.ID → triggers output
NULL with FILE:
Avoids dataset creation
Writes results directly to a file
Only when LAST.ID=1, we output.
- Ensures one line per ID.
- PUT → writes to external file
- PUTLOG → writes to SAS log
PUTLOG:
- Writes the same output to the SAS log for verification
- Column positioning (@ and +):
- Controls alignment in the output file
PROC SUMMARY¶
options nodate nocenter nonumber;
proc summary data=work.sorted_have nway;
class ID;
var calorie_intake;
output out=want_summary (drop=_type_ _freq_)
sum=Total_intake;
run;
proc print data=want_summary noobs label;
label Total_intake = "Total_intake";
run;
| ID | Total_intake |
|---|---|
| A | 1500 |
| B | 1800 |
| C | 1650 |
| D | 1960 |
Explanation for selected portions of the SAS code snippet shown above¶
- PROC SUMMARY computes descriptive statistics.
- data=work.sorted_have specifies the input dataset.
- nway tells SAS to output only the highest level of grouping (i.e., one row per id). Without nway, additional summary levels (including overall totals) would be produced
- class ID; → Defines grouping variable.
- var calorie_intake; → Analysis variable.
- sum=Total_intake; → Creates summed variable.
- TYPE and FREQ are dropped for a clean dataset.
The PROC SQL step below performs a grouped aggregation equivalent to a BY-group accumulation in a DATA step.
options nodate nocenter nonumber;
proc sql;
select ID,
sum(calorie_intake) as Total_intake
from work.sorted_have
group by ID;
quit;
| ID | Total_intake |
|---|---|
| A | 1500 |
| B | 1800 |
| C | 1650 |
| D | 1960 |
Below is a precise breakdown of what it does and how it relates to SAS processing semantics.
- GROUP BY ID partitions the dataset into groups of identical ID values.
- sum(calorie_intake) computes the aggregate sum within each group.
- Important behavior:
- Missing values are ignored.
- If all values in a group are missing → result is missing.
- No need for explicit initialization (unlike DATA step).