O'Reilly logo

SAS 9.4 SQL Procedure User's Guide, Fourth Edition, 4th Edition by SAS Institute

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Summarizing Data in Multiple Columns
Problem
You want to produce a grand total of multiple columns in a table.
Background Information
There is one input table, called Sales, that contains the following data:
data Sales;
input Salesperson $ January February March;
datalines;
Smith 1000 650 800
Johnson 0 900 900
Reed 1200 700 850
Davis 1050 900 1000
Thompson 750 850 1000
Peterson 900 600 500
Jones 800 900 1200
Murphy 700 800 700
Garcia 400 1200 1150
;
proc print data=Sales;
title 'Sample Data for Summarizing Data from Multiple Columns';
run;
192 Chapter 6 Practical Problem-Solving with PROC SQL
Output 6.13 Sample Input Table for Summarizing Data from Multiple Columns
You want to create output that shows the total sales for each month and the total sales for
all three months.
Solution
Use the following PROC SQL code to produce the monthly totals and grand total:
proc sql;
title 'Total First Quarter Sales';
select sum(January) as JanTotal,
sum(February) as FebTotal,
sum(March) as MarTotal,
sum(calculated JanTotal, calculated FebTotal,
calculated MarTotal) as GrandTotal format=dollar10.
from Sales;
Output 6.14 PROC SQL Output for Summarizing Data from Multiple Columns
How It Works
Recall that when you specify one column as the argument to an aggregate function, the
values in that column are calculated. When you specify multiple columns, the values in
each row of the columns are calculated. This solution uses the SUM function to calculate
Summarizing Data in Multiple Columns 193

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required