Previous Page | Next Page

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.

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.

Sample Input Table for Creating a Summary Report

                   Sample Data to Create Summary Sales Report

            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.


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,
                      when substr(InvoiceDate,3,2)='01' then
                         InvoiceAmount end as Jan,
                      when substr(InvoiceDate,3,2)='02' then 
                         InvoiceAmount end as Feb,
                      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

The first, or outer, SELECT statement in the query

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:

   when month(InvoiceDate)=1 then
      InvoiceAmount end as Jan,
   when month(InvoiceDate)=2 then
      InvoiceAmount end as Feb,
   when month(InvoiceDate)=3 then
      InvoiceAmount end as Mar

Previous Page | Next Page | Top of Page