Practical Problem-Solving with PROC SQL |

Problem |

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

Background Information |

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.

Copyright © 2008 by SAS Institute Inc., Cary, NC, USA. All rights reserved.