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

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

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