Lesson 6, Part 5: Combining the Summary Data with the Detailed Data

Combine the summary of the data (average) with the detailed dataset, then calculate the deviation of each individual weight from the mean.

• PROC MEANS and Data Step
• PROC SUMMARY and Data Step
• PROC SQL

Creating Summary Data Set (Method 1)

PROC MEANS generates descriptive statistics. The OUTPUT statement with OUT= option creates a SAS data set with the summary statistics

proc means data=sashelp.class noprint;
var weight;
output out=summary_data_m (drop=_TYPE_ _FREQ_) mean=avg_weight;
run;

title1 'Summarized values from PROC MEANS output data set';
proc print data=summary_data_m noobs; run;

Summarized values from PROC MEANS output data set

avg_weight
100.026

Creating Summary Data Set (Method 2)

PROC SUMMARY generates descriptive statistics. The OUTPUT statement with OUT= option creates a SAS data set with the summary statistics

*Ex11_summary_detail.sas (Part 2);
options nocenter nodate nonumber;
*** Summary value using PROC SUMMARY;
proc summary data=sashelp.class;
     var weight;
      output out=summary_data_s (drop=_TYPE_ _FREQ_)
      mean(weight)=s_avg_weight;
run;
title1 'Summarized values from PROC SUMMARY output data set';
proc print data=summary_data_s noobs; 
run;

Summarized values from PROC SUMMARY output data set

s_avg_weight
100.026

Combining the Summary and Detailed Data

Use two SET statements in the DATA step to combine the summary and detailed data.

*Ex11_summary_detail.sas (Part 3);
options nocenter nodate nonumber nosource;
data class;
        if _n_=1 then  set summary_data_m;
        set sashelp.class (keep=name weight);
        weight_deviation=1-(weight/avg_weight);
run;
title1 'Combine the summary data with the detailed data using two SET statements';
proc print data=class;
var name weight avg_weight weight_deviation;
format avg_weight weight  5.1  weight_deviation percent8.2;
run;

Combine the summary data with the detailed data using two SET statements

Obs	Name	Weight	avg_weight	weight_deviation
1	Alfred	112.5	100.0	(12.47%)
2	Alice	84.0	100.0	16.02%
3	Barbara	98.0	100.0	2.03%
4	Carol	102.5	100.0	( 2.47%)
5	Henry	102.5	100.0	( 2.47%)
6	James	83.0	100.0	17.02%
7	Jane	84.5	100.0	15.52%
8	Janet	112.5	100.0	(12.47%)
9	Jeffrey	84.0	100.0	16.02%
10	John	99.5	100.0	0.53%
11	Joyce	50.5	100.0	49.51%
12	Judy	90.0	100.0	10.02%
13	Louise	77.0	100.0	23.02%
14	Mary	112.0	100.0	(11.97%)
15	Philip	150.0	100.0	(49.96%)
16	Robert	128.0	100.0	(27.97%)
17	Ronald	133.0	100.0	(32.97%)
18	Thomas	85.0	100.0	15.02%
19	William	112.0	100.0	(11.97%)

Combining the Summary and Detailed Data Using the SQL Procedure

Calculate the summary data and merge it with the detailed data in a single PROC SQL step.

*Ex11_summary_detail.sas (Part 4);
title1 'Combine the summary data with the detailed data using PROC SQL';
PROC SQL;
select  name 
       ,weight format=5.1
       ,mean(weight) as avg_weight format=5.1
       ,1-(weight/calculated avg_weight) 
          as weight_deviation format=percent8.2
  from sashelp.class;
 quit;

Combine the summary data with the detailed data using PROC SQL

Name	Weight	avg_weight	weight_deviation
Alfred	112.5	100.0	(12.47%)
Alice	84.0	100.0	16.02%
Barbara	98.0	100.0	2.03%
Carol	102.5	100.0	( 2.47%)
Henry	102.5	100.0	( 2.47%)
James	83.0	100.0	17.02%
Jane	84.5	100.0	15.52%
Janet	112.5	100.0	(12.47%)
Jeffrey	84.0	100.0	16.02%
John	99.5	100.0	0.53%
Joyce	50.5	100.0	49.51%
Judy	90.0	100.0	10.02%
Louise	77.0	100.0	23.02%
Mary	112.0	100.0	(11.97%)
Philip	150.0	100.0	(49.96%)
Robert	128.0	100.0	(27.97%)
Ronald	133.0	100.0	(32.97%)
Thomas	85.0	100.0	15.02%
William	112.0	100.0	(11.97%)

*Ex11_summary_detail.sas (Part 5);
* DATA Step Approach  ;
data detail_class;
 length new_var $1;
 set sashelp.class;
  new_var='C';
 run;
 data x_summary_data_m;
   length new_var $1;
   set summary_data_m;
     new_var='C';
 run;

data mclass (drop=new_var);
 merge detail_class 
        x_summary_data_m;
 by new_var;
 weight_deviation=1-(weight/avg_weight);
 run;

 title1 'DATA step using the MERGE statement';
proc print data=mclass;
format weight avg_weight 5.1 
       weight_deviation percent8.2;
run;

DATA step using the MERGE statement

Obs	Name	Sex	Age	Height	Weight	avg_weight	weight_deviation
1	Alfred	M	14	69.0	112.5	100.0	(12.47%)
2	Alice	F	13	56.5	84.0	100.0	16.02%
3	Barbara	F	13	65.3	98.0	100.0	2.03%
4	Carol	F	14	62.8	102.5	100.0	( 2.47%)
5	Henry	M	14	63.5	102.5	100.0	( 2.47%)
6	James	M	12	57.3	83.0	100.0	17.02%
7	Jane	F	12	59.8	84.5	100.0	15.52%
8	Janet	F	15	62.5	112.5	100.0	(12.47%)
9	Jeffrey	M	13	62.5	84.0	100.0	16.02%
10	John	M	12	59.0	99.5	100.0	0.53%
11	Joyce	F	11	51.3	50.5	100.0	49.51%
12	Judy	F	14	64.3	90.0	100.0	10.02%
13	Louise	F	12	56.3	77.0	100.0	23.02%
14	Mary	F	15	66.5	112.0	100.0	(11.97%)
15	Philip	M	16	72.0	150.0	100.0	(49.96%)
16	Robert	M	12	64.8	128.0	100.0	(27.97%)
17	Ronald	M	15	67.0	133.0	100.0	(32.97%)
18	Thomas	M	11	57.5	85.0	100.0	15.02%
19	William	M	15	66.5	112.0	100.0	(11.97%)

*Ex11_summary_detail.sas (Part 6);
*** PROC Step, CALL SYMPUTX, DATA Step;
Options nocenter nodate nonumber;
proc means data=sashelp.class noprint;
var weight;
output out=mystats mean=ave_weight;
run;

data _null_;
  set mystats;
  call symputx('AverageWeight',ave_weight);
run;

data x_class;
        set sashelp.class (keep=name weight);
        weight_deviation=1-(weight/&AverageWeight);
run;
title1 "PROC Step, CALL SymputX, and DATA Step";
title2 "Mean weight: %sysfunc(putn(&AverageWeight, 5.1)) lbs";
proc print data=x_class;
var name weight weight_deviation;
format weight 5.1  weight_deviation   percent8.2;
run;

PROC Step, CALL SymputX, and DATA Step

Mean weight: 100.0 lbs

Obs	Name	Weight	weight_deviation
1	Alfred	112.5	(12.47%)
2	Alice	84.0	16.02%
3	Barbara	98.0	2.03%
4	Carol	102.5	( 2.47%)
5	Henry	102.5	( 2.47%)
6	James	83.0	17.02%
7	Jane	84.5	15.52%
8	Janet	112.5	(12.47%)
9	Jeffrey	84.0	16.02%
10	John	99.5	0.53%
11	Joyce	50.5	49.51%
12	Judy	90.0	10.02%
13	Louise	77.0	23.02%
14	Mary	112.0	(11.97%)
15	Philip	150.0	(49.96%)
16	Robert	128.0	(27.97%)
17	Ronald	133.0	(32.97%)
18	Thomas	85.0	15.02%
19	William	112.0	(11.97%)

*Ex12_SUM_Statement_vs_2_SETs.sas (Part 1);
options nocenter nodate nonumber;
DATA sale_by_mon ;
  INPUT mon $  sale @@;
  cum_sale+sale;
  DATALINES;
  Jan 164083 Feb 164260 Mar 163747 Apr 164759 
  May 165617 Jun 166098 Jul 167305 Aug 167797 
  Sep 169407 Oct 170681 Nov 171025 Dec 172995
  ;
run;
title1 'Example Data Set';
PROC PRINT DATA=sale_by_mon ; 
  FORMAT sale cum_sale dollar12.;
   SUM sale ;
  run;

Example Data Set

Obs	mon	sale	cum_sale
1	Jan	$164,083	$164,083
2	Feb	$164,260	$328,343
3	Mar	$163,747	$492,090
4	Apr	$164,759	$656,849
5	May	$165,617	$822,466
6	Jun	$166,098	$988,564
7	Jul	$167,305	$1,155,869
8	Aug	$167,797	$1,323,666
9	Sep	$169,407	$1,493,073
10	Oct	$170,681	$1,663,754
11	Nov	$171,025	$1,834,779
12	Dec	$172,995	$2,007,774
		$2,007,774

*Ex12_SUM_Statement_vs_2_SETs.sas (Part 2);
DATA xsale;
  SET sale_by_mon(keep=cum_sale)
       POINT=last nobs=last;
  SET sale_by_mon (drop=cum_sale);
  Percent_sale = sale/cum_sale;
run;
title1 'Combining the summary data with the detailed data';
PROC PRINT DATA=xsale; 
  VAR mon sale Percent_sale;
  SUM sale Percent_sale;
  FORMAT sale dollar10. Percent_sale percent12.2;
run;

Combining the summary data with the detailed data

Obs	mon	sale	Percent_sale
1	Jan	$164,083	8.17%
2	Feb	$164,260	8.18%
3	Mar	$163,747	8.16%
4	Apr	$164,759	8.21%
5	May	$165,617	8.25%
6	Jun	$166,098	8.27%
7	Jul	$167,305	8.33%
8	Aug	$167,797	8.36%
9	Sep	$169,407	8.44%
10	Oct	$170,681	8.50%
11	Nov	$171,025	8.52%
12	Dec	$172,995	8.62%
		$2,007,774	100.00%

Another Example: Combine the summary data with the detailed data using PROC SQL

Calculate group means by sex and then append an overall (Total) mean as an additional row.

proc sql;
   select sex length=5,
          mean(weight) as mean_weight format=5.1
   from sashelp.class
   group by sex 

   union all

   /* Grand total row */
   select "TOTAL" as sex length=5,
          mean(weight) as mean_weight format=5.1
   from sashelp.class ;
quit;

Combining the summary data with the detailed data

sex	mean_weight
F	90.1
M	109.0
TOTAL	100.0

Explanation of the above code

• PROC SQL; Starts an SQL procedure in SAS.
• First SELECT (Grouped Means)
• sex length=5 → Expands the character length of sex to 5 (prevents truncation in the UNION).
• mean(weight) as mean_weight format=5.1
• Computes the average weight within each sex group.
• Names the result mean_weight.
• Displays it with one decimal place.
• group by sex → Produces one row for F and one for M.
• UNION ALL appends another row without removing duplicates.
• Second SELECT (Grand Total Row)
• "TOTAL" replaces the sex value.
• Computes the overall mean weight for all observations.
• Uses the same column structure to match the first query.
• QUIT; Ends the SQL procedure.

Comparison between UNION ALL vs UNION

UNION ALL combines result sets and keeps duplicates.

• No deduplication
• No sorting for distinctness
• Faster and more efficient
• Preserves row counts exactly

In contrast, UNION (not in the code above) combines result sets and removes duplicate rows.

• Performs an implicit DISTINCT
• Requires sorting or comparison
• Slower than UNION ALL

Norman, Rod. 2012. A Mean Way to Count, Enumerating the Values of Multiple Variables Using Formats with the Means Procedure. PharmaSUG 2012 – CC30