SAS 9.4 SQL Procedure User's Guide, Third Edition, 3rd Edition

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