Practical Problem-Solving with PROC SQL

Summarizing Data in Multiple Columns

You want to produce a grand total of multiple columns in a table.

There is one input table, called SALES, that contains the following data:

Sample Input Table for Summarizing Data from Multiple Columns

             Sample Data for Summarizing Data from Multiple Columns

               Obs    Salesperson    January    February    March

                1      Smith           1000        650        800
                2      Johnson            0        900        900
                3      Reed            1200        700        850
                4      Davis           1050        900       1000
                5      Thompson         750        850       1000
                6      Peterson         900        600        500
                7      Jones            800        900       1200
                8      Murphy           700        800        700
                9      Garcia           400       1200       1150

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;

PROC SQL Output for Summarizing Data from Multiple Columns

                           Total First Quarter Sales

                    JanTotal  FebTotal  MarTotal  GrandTotal
                    ----------------------------------------
                        6800      7500      8100     $22,400

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

Top of Page