When you are using PROC SQL, you might find that the data in a table is not formatted as you would like it to appear. Fortunately, with PROC SQL you can use enhancements, such as the following, to improve the appearance of your query output:

You know how to use the first two enhancements with other SAS procedures. You can also enhance PROC SQL query output by working with the following query:

  proc sql outobs=15;
     select empid, jobcode, salary,
            salary * .10 as Bonus
        from sasuser.payrollmaster
        where salary>75000
        order by salary desc;

This query limits output to 15 observations. The SELECT clause selects three existing columns from the table Sasuser.Payrollmaster, and calculates a fourth (Bonus). The WHERE clause retrieves only rows in which salary is greater than 75,000. The ORDER BY clause sorts by the Salary column and uses the keyword DESC to sort in descending order.

Here is the output from this query.

Look closely at this output and you see that improvements can be made. You learn how to enhance this output in the following ways:

By default, PROC SQL formats output using column attributes that are already saved in the table or, if none are saved, the default attributes. To control the formatting of columns in output, you can specify column modifiers, such as LABEL= and FORMAT=, after any column name specified in the SELECT clause. When you define a new column in the SELECT clause, you can assign a label rather than an alias, if you prefer.

select hiredate
       label='Date of Hire'

select hiredate
       format=date9.

Your first task is to specify column labels for the first two columns. Below, the LABEL= option has been added after both EmpID and JobCode, and the text of each label is enclosed in quotation marks. For easier reading, each of the four columns in the SELECT clause is now listed on its own line.

proc sql outobs=15;
   select empid label='Employee ID',
          jobcode label='Job Code',
          salary,
          salary * .10 as Bonus
      from sasuser.payrollmaster
      where salary>75000
      order by salary desc;

Next, you add a format for the Bonus column. Because the Bonus values are dollar amounts, you use the format Dollar12.2. The FORMAT= modifier has been added to the SELECT clause, below, immediately following the column alias Bonus:

proc sql outobs=15;
   select empid label='Employee ID',
          jobcode label='Job Code',
          salary,
          salary * .10 as Bonus
          format=dollar12.2
   from sasuser.payrollmaster
   where salary>75000
   order by salary desc;

Now that column formats and labels have been specified, you can add a title to this PROC SQL query.

You should already know how to specify and cancel titles and footnotes with other SAS procedures. When you specify titles and footnotes with a PROC SQL query, you must place the TITLE and FOOTNOTE statements in either of the following locations:

In the following PROC SQL query, two title lines have been added between the PROC SQL statement and the SELECT statement:

 proc sql outobs=15;
 title 'Current Bonus Information';
 title2 'Employees with Salaries > $75,000';
    select empid label='Employee ID',
           jobcode label='Job Code',
           salary,
           salary * .10 as Bonus
           format=dollar12.2
       from sasuser.payrollmaster
       where salary>75000
       order by salary desc;

Now that these changes have been made, you can look at the enhanced query output.

The first two columns have new labels, the Bonus values are consistently formatted, and two title lines are displayed at the top of the output.

Another way of enhancing PROC SQL query output is to define a column that contains a character constant. To do this, you include a text string in quotation marks in the SELECT clause.

You can look at the preceding PROC SQL query output again and determine where you can add a text string.

You can remove the column label Bonus and display the text bonus is: in a new column to the left of the Bonus column. This is how you want the columns and rows to appear in the query output.

To specify a new column that contains a character constant, you include the text string in quotation marks in the SELECT clause list. Your modified PROC SQL query is shown below:

proc sql outobs=15;
title 'Current Bonus Information';
title2 'Employees with Salaries > $75,000';
   select empid label='Employee ID',
          jobcode label='Job Code',
          salary,
          'bonus is:',
          salary * .10 format=dollar12.2
      from sasuser.payrollmaster
      where salary>75000
      order by salary desc;

In the SELECT clause list, the text string bonus is: has been added between Salary and Bonus.

Note that the code as Bonus has been removed from the last line of the SELECT clause. Now that the character constant has been added, the column alias Bonus is no longer needed.