Lesson 6, Part 8: Aggregating Data Using PROC SUMMARY vs. PROC SQL

Question 1: In PROC SUMMARY, when should we use a CLASS statement instead of a BY statement?¶

Below are the responses.

1a. Use a CLASS statement when

• You want grouped summary statistics in one table (e.g., MEAN by sex and smoking_status). Syntax is not sensitive to sort order; PROC SUMMARY handles grouping internally.

• You want interaction levels (e.g., CLASS sex and smoking_status) to see combinations such as all combinations of sex × smoking_status in the output.

• The code below is meant to resemble a SQL GROUP BY style summary, where categories are part of the same stratum.

proc summary data=sashelp.heart 
     (where=(AgeCHDdiag ne . and smoking_status ne ' ' )) nway ;
  class sex smoking_status;
  var AgeCHDdiag;
  output out=work.means_data mean=;
run;
proc print data=means_data;
sum _FREQ_;
format _FREQ_ comma7. AgeCHDdiag 6.1;;
run;

Obs	Sex	Smoking_Status	_TYPE_	_FREQ_	AgeCHDdiag
1	Female	Heavy (16-25)	3	57	62.7
2	Female	Light (1-5)	3	76	65.2
3	Female	Moderate (6-15)	3	63	63.1
4	Female	Non-smoker	3	402	66.7
5	Female	Very Heavy (> 25)	3	15	62.1
6	Male	Heavy (16-25)	3	245	59.2
7	Male	Light (1-5)	3	60	63.0
8	Male	Moderate (6-15)	3	87	62.1
9	Male	Non-smoker	3	283	65.1
10	Male	Very Heavy (> 25)	3	152	58.2
				1,440

1b. Use PROC SUMMARY with CLASS to get multiple levels

/* Level 1: sex only */
proc summary data=sashelp.heart (where=(AgeCHDdiag ne . and smoking_status ne ' ' ));
  class sex;
  var AgeCHDdiag;
  output out=by_sex mean= / autoname;
run;

/* Level 2: smoking_status only */
proc summary data=sashelp.heart (where=(AgeCHDdiag ne . and smoking_status ne ' ' ));
  class smoking_status;
  var AgeCHDdiag;
  output out=by_smoking mean= / autoname;
run;

/* Level 3: sex × smoking_status (your current level) */
proc summary data=sashelp.heart (where=(AgeCHDdiag ne . and smoking_status ne ' ' ));
  class sex smoking_status;
  var AgeCHDdiag;
  output out=by_sex_smoke mean= / autoname;
run;

/* Append all three levels */
data work.all_levels;
  set by_sex by_smoking by_sex_smoke;
run;

proc print data=work.all_levels;
format AgeCHDdiag_Mean 6.1;
run;

Obs	Sex	_TYPE_	_FREQ_	AgeCHDdiag_Mean	Smoking_Status
1		0	1440	63.3
2	Female	1	613	65.6
3	Male	1	827	61.6
4		0	1440	63.3
5		1	302	59.9	Heavy (16-25)
6		1	136	64.2	Light (1-5)
7		1	150	62.5	Moderate (6-15)
8		1	685	66.0	Non-smoker
9		1	167	58.5	Very Heavy (> 25)
10		0	1440	63.3
11		1	302	59.9	Heavy (16-25)
12		1	136	64.2	Light (1-5)
13		1	150	62.5	Moderate (6-15)
14		1	685	66.0	Non-smoker
15		1	167	58.5	Very Heavy (> 25)
16	Female	2	613	65.6
17	Male	2	827	61.6
18	Female	3	57	62.7	Heavy (16-25)
19	Female	3	76	65.2	Light (1-5)
20	Female	3	63	63.1	Moderate (6-15)
21	Female	3	402	66.7	Non-smoker
22	Female	3	15	62.1	Very Heavy (> 25)
23	Male	3	245	59.2	Heavy (16-25)
24	Male	3	60	63.0	Light (1-5)
25	Male	3	87	62.1	Moderate (6-15)
26	Male	3	283	65.1	Non-smoker
27	Male	3	152	58.2	Very Heavy (> 25)

1c. Use a BY statement in PROC SUMMARY when

• One or more variables already sort the data, and you want PROC SUMMARY to treat each group as an independent stratum (e.g., one summary per sex, one per smoking_status, etc.).

• You want OUTPUT to produce one row per BY group, and you plan to merge or stack results by those groups.

proc sort data=sashelp.heart (where=(AgeCHDdiag ne . and smoking_status ne ' ' ))
   out=work.sorted_heart;
  by sex smoking_status;
run;

proc summary data=sorted_heart;
  by sex smoking_status;
  var AgeCHDdiag;
  output out=work.means_data2 mean=;
run;
proc print data=means_data2;
  by sex smoking_status;
  sum _FREQ_;
  format _FREQ_ comma7. AgeCHDdiag 6.1;
run;

Sex=Female Smoking Status=Heavy (16-25)

Obs	_TYPE_	_FREQ_	AgeCHDdiag
1	0	57	62.7

Sex=Female Smoking Status=Light (1-5)

Obs	_TYPE_	_FREQ_	AgeCHDdiag
2	0	76	65.2

Sex=Female Smoking Status=Moderate (6-15)

Obs	_TYPE_	_FREQ_	AgeCHDdiag
3	0	63	63.1

Sex=Female Smoking Status=Non-smoker

Obs	_TYPE_	_FREQ_	AgeCHDdiag
4	0	402	66.7

Sex=Female Smoking Status=Very Heavy (> 25)

Obs	_TYPE_	_FREQ_	AgeCHDdiag
5	0	15	62.1
Sex		613

Sex=Male Smoking Status=Heavy (16-25)

Obs	_TYPE_	_FREQ_	AgeCHDdiag
6	0	245	59.2

Sex=Male Smoking Status=Light (1-5)

Obs	_TYPE_	_FREQ_	AgeCHDdiag
7	0	60	63.0

Sex=Male Smoking Status=Moderate (6-15)

Obs	_TYPE_	_FREQ_	AgeCHDdiag
8	0	87	62.1

Sex=Male Smoking Status=Non-smoker

Obs	_TYPE_	_FREQ_	AgeCHDdiag
9	0	283	65.1

Sex=Male Smoking Status=Very Heavy (> 25)

Obs	_TYPE_	_FREQ_	AgeCHDdiag
10	0	152	58.2
Sex		827
		1,440

Question 2: How does COUNT behave when used with a GROUP BY clause in PROC SQL?

Below are the responses.

2a. When you group by more than one variable, COUNT applies to each combination of the group‑by levels in PROC SQL.

proc sql;
  create table want as

  /* Grouped rows */
  select 
    sex, 
    smoking_status,
    count(*) as total_n,
    count(AgeCHDdiag) as n_age,
    avg(AgeCHDdiag) as mean_age_CHD_diagnosis format=6.1
  from sashelp.heart
  where smoking_status ne ' '
  group by sex, smoking_status

  union all

  /* Total row */
  select 
    'Overall' as sex,
    'Overall' as smoking_status,
    count(*) as total_n,
    count(AgeCHDdiag) as n_age,
    avg(AgeCHDdiag) as mean_age_CHD_diagnosis format=6.1
  from sashelp.heart
  where AgeCHDdiag ne . and smoking_status ne ' ';
quit;

pro print data=want;
run;

Obs	Sex	Smoking_Status	total_n	n_age	mean_age_CHD_diagnosis
1	Female	Heavy (16-25)	339	57	62.7
2	Female	Light (1-5)	422	76	65.2
3	Female	Moderate (6-15)	340	63	63.1
4	Female	Non-smoker	1682	402	66.7
5	Female	Very Heavy (> 25)	73	15	62.1
6	Male	Heavy (16-25)	707	245	59.2
7	Male	Light (1-5)	157	60	63.0
8	Male	Moderate (6-15)	236	87	62.1
9	Male	Non-smoker	819	283	65.1
10	Male	Very Heavy (> 25)	398	152	58.2
11	Overall	Overall	1440	1440	63.3

The above table gives one row per (sex, smoking_status) pair, with n_age as the number of observations and mean_age_CHD_diagnosis in that group; it also appends one row for the "total".

2b. PROC TABULATE Solutions - An alternative solution

Question 3: When counting observations or distinct values, is PROC SQL preferred over PROC SUMMARY or the DATA step?

Response: PROC SQL in many situations.

Question 4: Should accumulators be initialized using RETAIN statements?

Response: Yes. See the SAS code examples and code explanations for additional information in the links below.

• The sum statement is equivalent to using the SUM function and the RETAIN statement in a DATA step.

• Writing an Observation Only After All Observations Have Been Read in a DATA step.