Practical Problem-Solving with PROC SQL

Creating a Summary Report

You have a table that contains detailed sales information. You want to produce a summary report from the detail table.

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.

Sample Input Table for Creating a Summary Report

                   Sample Data to Create Summary Sales Report

                                               Invoice
            Site      Product   Invoice         Amount  InvoiceDate
            -------------------------------------------------------
            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

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;

PROC SQL Output for a Summary Report

                        First Quarter Sales by Product                       


                    Product        Jan       Feb       Mar
                    --------------------------------------
                    VID001           .      1167         .
                    VID003           .      5500      4750
                    VID005           .         .      1070
                    VID010        1197         .       750
                    VID014           .         .      1070

Note: Missing values in the matrix indicate that no sales occurred for that given product in that month. [cautionend]

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

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 upon 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

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
   when month(InvoiceDate)=3 then
      InvoiceAmount end as Mar

Top of Page