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

the sum of each month's sales, and then uses the SUM function a second time to total the

monthly sums into one grand total.

sum(calculated JanTotal, calculated FebTotal,

calculated MarTotal) as GrandTotal format=dollar10.

An alternative way to code the grand total calculation is to use nested functions:

sum(sum(January), sum(February), sum(March))

as GrandTotal format=dollar10.

Creating a Summary Report

Problem

You have a table that contains detailed sales information. You want to produce a

summary report from the detail table.

Background Information

There is one input table, called Sales, that contains detailed sales information. There is

one record for each sale for the first quarter that shows the site, product, invoice number,

invoice amount, and invoice date.

data sales;

input Site $ Product $ Invoice $ InvoiceAmount InvoiceDate $;

datalines;

V1009 VID010 V7679 598.5 980126

V1019 VID010 V7688 598.5 980126

V1032 VID005 V7771 1070 980309

V1043 VID014 V7780 1070 980309

V421 VID003 V7831 2000 980330

V421 VID010 V7832 750 980330

V570 VID003 V7762 2000 980302

V659 VID003 V7730 1000 980223

V783 VID003 V7815 750 980323

V985 VID003 V7733 2500 980223

V966 VID001 V5020 1167 980215

V98 VID003 V7750 2000 980223

;

proc sql;

title 'Sample Data to Create Summary Sales Report';

select * from sales;

quit;

194 Chapter 6 • Practical Problem-Solving with PROC SQL

Output 6.15 Sample Input Table for Creating a Summary Report

You want to use this table to create a summary report that shows the sales for each

product for each month of the quarter.

Solution

Use the following PROC SQL code to create a column for each month of the quarter,

and use the summary function SUM in combination with the GROUP BY statement to

accumulate the monthly sales for each product:

proc sql;

title 'First Quarter Sales by Product';

select Product,

sum(Jan) label='Jan',

sum(Feb) label='Feb',

sum(Mar) label='Mar'

from (select Product,

case

when substr(InvoiceDate,3,2)='01' then

InvoiceAmount end as Jan,

case

when substr(InvoiceDate,3,2)='02' then

InvoiceAmount end as Feb,

case

when substr(InvoiceDate,3,2)='03' then

InvoiceAmount end as Mar

from work.sales)

group by Product;

Creating a Summary Report 195

Output 6.16 PROC SQL Output for a Summary Report

Note: Missing values in the matrix indicate that no sales occurred for that given product

in that month.

How It Works

This solution uses an in-line view to create three temporary columns, Jan, Feb, and Mar,

based on the month part of the invoice date column. The in-line view is a query that

performs the following:

• selects the product column

• uses a CASE expression to assign the value of invoice amount to one of three

columns, Jan, Feb, or Mar, depending on the value of the month part of the invoice

date column

case

when substr(InvoiceDate,3,2)='01' then

InvoiceAmount end as Jan,

case

when substr(InvoiceSate,3,2)='02' then

InvoiceAmount end as Feb,

case

when substr(InvoiceDate,3,2)='03' then

InvoiceAmount end as Mar

The first, or outer, SELECT statement in the query performs the following:

• selects the product

• uses the summary function SUM to accumulate the Jan, Feb, and Mar amounts

• uses the GROUP BY statement to produce a line in the table for each product

Notice that dates are stored in the input table as strings. If the dates were stored as SAS

dates, then the CASE expression could be written as follows:

case

when month(InvoiceDate)=1 then

InvoiceAmount end as Jan,

case

when month(InvoiceDate)=2 then

InvoiceAmount end as Feb,

case

196 Chapter 6 • Practical Problem-Solving with PROC SQL