SUGI 29 Data Presentation
Paper 085-29
PROC TABULATE: Doin’ It in Style!
, Consulting, Hartsdale, NY Sandy McNeill, SAS, Cary, NC
Copyright By PowCoder代写 加微信 powcoder
The advent of the SAS® Output Delivery System (ODS to its friends) has turned SAS reports from machine-generated, black & white monospace bores into people-produced, productive and reader-friendly information displays. One of the main principles underlying ODS is the use of Table and Style definitions (also known as Table and Style templates). Most procedures have a standard output layout structure and rely on their Table and Style definitions to govern the cosmetic or stylistic appearance of their tables. Certain procedures (REPORT, TABULATE, etc), however, by the very nature of their complete structural customizability, do not rely on fixed external table definitions. For these procedures, stylistic customizations are performed through the use of the STYLE option, an ODS concept which is integrated into the heart of the procedures’ syntax. This presentation will demonstrate the use of STYLEs in the TABULATE procedure.
Introduction
Prior to Version 8 (actually Version 7) of the SAS System, the only form of output available from PROC TABULATE was the listing file in the Output Window. The output was produced in SAS Monospace font with form characters (usually dashes) used for overlining and underlining. This was the acceptable (and in fact the only) way to bring attention to summary or total lines. There was no way to highlight any of the rows, columns or cells of the output. As Version 8 was released, HTML output was gaining huge popularity as the choice of medium for sharing information – reports, documents, charts. In the HTML world, monospace, fixed fonts were no longer preferred and it was now possible to use proportional fonts, colors, different font sizes, bolding and italics to bring attention to areas of reports that needed more attention from the reader. Version 8 contained the first production release of the Output Delivery System (ODS). One of the main features of ODS is the ability to produce output from all BASE procedures in alternate formats (known in ODS as destinations). One of the original ODS destinations, in addition to the default LISTING destination, was HTML. Output sent to this destination was rendered as HTML-tagged output suitable for viewing in HTML browsers.
Most BASE procedures follow certain fairly rigid structural guidelines in terms of the overall layout of the results, and the design of the layout is fairly consistent from run to run. All UNIVARIATE output for example follows a basic blueprint. This was not, however, possible with certain reporting procedures (REPORT, TABULATE, PRINT, etc) because of the infinite amounts of final data layouts that could be created depending on many data factors including variables used and reporting statistics chosen, as well as other design considerations. Therefore, while standard codified aspects of most procedure output could be individually customized via ODS and its accompanying TEMPLATE procedure, this was not possible for the reporting procedures because of the lack of standard replicable design features. To compensate for this lack of individual customizability, a system of STYLE formatting was made available for use in REPORT and TABULATE coding (now available in PRINT as well) which provided the ability to individually customize almost all design aspects of the procedure output.
This presentation will illustrate some of the many different features of ODS STYLEs as implemented in PROC TABULATE. This will be done through a series of examples, each using the same source data set. This data set, a subset of SASHELP.PRDSAL3, contains fictitious actual and predicted sales for the years 1997 and 1998 for the states or provinces of three countries – Mexico, U.S.A., and Canada. The examples will start off very simply and then build upon each other by adding or changing features until the final example, which will be a culmination of these features. Let’s get started with the code needed to create the data set used throughout.
data tabhow;
set sashelp.prdsal3(keep=country state quarter year month actual predict);
country=’U.S.A.’
and ( state=’North Carolina’
or state=’ ‘
or state=’California’))
or country=’Canada’
or country=’Mexico’;
MakeData Code
SUGI 29 Data Presentation
Example 1 – Plain old HTML
This first example creates the basic table that we will be working with throughout the rest of the paper. The code for the report is as follows, with comments after the code. The output for all examples can be found at the end of the paper.
title1 ‘Example 1 – Simple Tabulate HTML’; *—————————————————————————————-;
ods listing close;
ods html file = “tabhow1.htm”; *—————————————————————————————-; proc tabulate data=tabhow;
class country state year;
var actual predict;
table country * (state=’State/Province’
all =’** Whole Country **’),
year=’Sales per Year’ * (actual =’Actual’
predict=’Predicted’) * sum=’ ‘ * f=comma9.0
/ box=’Country by State by Year’ ;
*—————————————————————————————-; ods html close;
ods listing;
Example 1 Code
In addition to the actual and predicted sales values for each of the years for the countries, we also have an ALL row which captures the total actual and predicted sales values. The output is sent to the ODS HTML destination via two simple ODS statements:
1) ODS HTML FILE = ‘tabhow1.htm’; – this statement defines the HTML output file to which the output will be written,
2) ODS HTML CLOSE; – this statement closes the output file and is necessary before the output is available for browsing.
The ODS LISTING CLOSE; and ODS LISTING; statements, while not necessary for the functionality of the ODS HTML destination routing, are usually an excellent addition to all ODS coding. They simply turn off, and then turn back on, the default ODS LISTING destination to conserve resources.
A few things should be noticed as you look at the report output: 1) the report is rather plain, 2) it is difficult to discern the ALL rows from the rest of the rows, 3) it is easy to confuse one country’s values with another . These concerns will be addressed in the following examples.
Example 2 – STYLE on the HTML statement
In this example we start working on the appearance of the report. The only thing we will do here is to change the overall style that is used for the output.
title ‘Example 2 – Add Style to ODS HTML Statement’; *—————————————————————————————-;
ods listing close;
ods html file = “tabhow2.htm”
style = sasweb; *—————————————————————————————-; proc tabulate data=tabhow;
class country state year;
var actual predict;
table country * (state=’State/Province’
all =’** Whole Country **’),
year=’Sales per Year’ * (actual =’Actual’
predict=’Predicted’) * sum=’ ‘ * f=comma9.0
/ box =’Country by State by Year’;
*—————————————————————————————-; ods html close;
ods listing;
Example 2 Code
SUGI 29 Data Presentation
The only difference between this example and Example 1 is the STYLE option on the HTML statement. When a STYLE option is used on a global ODS statement which opens a destination, the style specified (and its elements) are used until the destination is closed.
In the first example, we were actually using the DEFAULT style even though we did not explicitly code it. When using the HTML destination, this is the STYLE which is used by default.
In Example 2, we use the SASWEB style. This style corresponds to the SASWEB style template which is located in the STYLES directory of the first readable itemstore that contains this style template. As long as the style resides in the STYLES directory, you can omit specifying “STYLES.” before the style name, since that is the default. It’s just like specifying/not specifying “WORK” as the default libref when referring to temporary SAS data sets. If this is unclear at the moment, don’t worry. It’s a base concept in ODS and TEMPLATES, but is not critical to where we are going.
Notice when comparing the output from Example 1 to that for Example 2 that the SASWEB style not only affects the color of different areas of the report, but also changes the font and font size that is used throughout the report. If you want stylistic changes to be in effect for many reports, the best place to put those changes is in a style template which you can then simply call from report to report. But once again, this is a bit peripheral to the topic at hand.
Example 3 – STYLE on the TABULATE statement
Now that we have seen how to apply a style to an entire report (and one that could be used for many reports), the next task is to see how we can make changes to colors, fonts, or other stylistic attributes for just one report. The method to use is the STYLE option, but not the same STYLE option as we saw on the ODS global statement in Example 2. The goal of the rest of this paper is to examine different ways that the STYLE option can be used within a specific PROC TABULATE. Let’s take a look at the code for Example 3 and then we’ll discuss the first use of the STYLE option, namely on the TABULATE statement.
title ‘Example 3 – Add Style to TABULATE Statement’; *—————————————————————————————-;
ods listing close;
ods html file = “tabhow3.htm”
style = sasweb; *—————————————————————————————-; proc tabulate data=tabhow
style=[background=beige];
class country state year;
var actual predict;
table country * (state=’State/Province’
all =’** Whole Country **’),
year=’Sales per Year’ * (actual =’Actual’
predict=’Predicted’) * sum=’ ‘ * f=comma9.0
/ box =’Country by State by Year’;
*—————————————————————————————-; ods html close;
ods listing;
Example 3 Code
The STYLE= option has the following general syntax:
STYLE<(location(s)>=
Note: You can use either square brackets ([ and ]) or braces ({ and }).
The optional LOCATION identifier tells where in the report the style should be applied. Since PROC TABULATE does not use LOCATION in the STYLE option, we won’t discuss it here. PROC REPORT and PROC PRINT however do use this feature and this is discussed in their documentation chapters.
The STYLE-ELEMENT-NAME refers to the name of a style element from the current style template in use. This is beyond 3
SUGI 29 Data Presentation
the scope of this paper, but if you want to learn more about style elements, the place to go is the SAS Procedures Guide, Version 8 (online or hardcopy.)
The last part(s) of the STYLE option are pairings of attribute names for those style attributes that you want to set, and the values that you want to assign to them. The PROC TEMPLATE documentation (in The Complete Guide to the SAS Output Delivery System, Version 8), as well as the aforementioned V8 SAS Procedures Guide, contain lists of attributes and their possible values.
When a FORMAT option is used on the TABULATE statement, the format specified is applied to the data cells, as opposed to row or column headers. This same rule also applies when a STYLE option resides on the TABULATE statement – the named style is applied to the data cells of the report.
On the TABULATE statement, we see the code STYLE=[BACKGROUND=BEIGE]. There is no style element name specified and only one style attribute and value. Since the STYLE option on the TABULATE statement affects data cells, the background color of data cells will be the color beige.
Example 4 – STYLE on CLASS and CLASSLEV statements
Now that we are familiar with the basic syntax and operation of the STYLE option, let’s use this option with other PROC TABULATE statements. In this example, we will use the STYLE option on both the CLASS and CLASSLEV statements. Let’s take a look at the code first.
title ‘Example 4 – Add Style to CLASS and CLASSLEV Statements’; *—————————————————————————————-; ods listing close;
ods html file = “tabhow4.htm”
style = sasweb; *—————————————————————————————-; proc tabulate data=tabhow style=[background=beige];
class country state;
class year / style=[foreground=black];
classlev year / style=[foreground=black];
var actual predict;
table country * (state=’State/Province’
all=’** Whole Country **’),
year=’Sales per Year’ * (actual =’Actual’
predict=’Predicted’) * sum=’ ‘ *f=comma9.0
/ box =’Country by State by Year’;
*—————————————————————————————-; ods html close;
ods listing;
Example 4 Code
In this example we chose to have the column spanning header for the YEAR variable (‘Sales Per Year’) and the actual values of the YEAR variable (1997, 1998) displayed in black instead of the default white (this default comes from the style element HEADER in the style template that we are using which is STYLES.SASWEB). The first thing to understand is that we need to use the FOREGROUND attribute since we want to change the color of the text (we would use the BACKGROUND attribute to change the background color of the cell.) The second thing to figure out is where to place the STYLE options. Since we want to change the label of the YEAR variable, we place a STYLE option on the CLASS statement for YEAR. Notice that by using PROC TABULATE’s support of multiple CLASS statements for different categorizing variables, we can apply different styles (or none at all) to different class variables. In this example, COUNTRY and STATE are not given styles, and the text color for the YEAR variable label is set to black.
OK, so we’ve changed the style on the CLASS statement for the YEAR variable, but that only affects the label for the variable itself (‘Sales Per Year’), and not the actual values of the variable, 1997 and 1998. To do this, we need to use the STYLE option on the CLASSLEV statement for YEAR. This CLASSLEV statement was created for just this purpose – to access the class level values for a particular class variable. Just as we can have multiple CLASS statements, we can also have multiple CLASSLEV statements to allow different class variables to be styled differently. In this example, however, we are only stylizing the class levels for the variable YEAR, so we have one CLASSLEV statement for that variable, with the same style option as we had on the CLASS statement for YEAR. Unlike CLASS statements which must be present for
SUGI 29 Data Presentation
every categorizing variable in the run, CLASSLEV statements are only needed for those variables for which you wish to make variable-level style changes.
Example 5 – STYLE on the BOX option
One area that is slightly removed from the variables and their statistics in the output is the BOX area. This area can nevertheless be stylized. Here is the code:
title ‘Example 5 – Add Style to BOX option on TABLE Statement’; *—————————————————————————————-; ods listing close;
ods html file = “tabhow5.htm”
style = sasweb; *—————————————————————————————-; proc tabulate data=tabhow style=[background=beige];
class country state;
class year / style=[foreground=black];
classlev year / style=[foreground=black];
var actual predict;
table country * (state=’State/Province’
all =’** Whole Country **’),
year=’Sales per Year’ * (actual =’Actual’
predict=’Predicted’) * sum=’ ‘ * f=comma9.0
/ box=[label=’Country by State by Year’
style=[background=beige
foreground=black
font_size=3]];
run; *—————————————————————————————-; ods html close;
ods listing;
Example 5 Code
Setting a style for the BOX area only slightly complicates the process of just setting a string to appear there. Instead of just specifying the string, as in BOX=’string’, you have to enclose the string (LABEL=) in brackets as well as the style. Remember that the STYLE= is enclosed in brackets, within the outer BOX brackets. In our example, we set a label as well as a style with three attributes: BACKGROUND, FOREGROUND and FONT_SIZE.
Example 6 – STYLE on VAR statements
OK, we’ve colored CLASS variable column headers with STYLE on CLASS and CLASSLEV statements (the same can obviously be done with row CLASS variables) and we’ve done the BOX area. Now we come to the rest of the table, the guts, the data cells. The task at hand in this example and the next is to differentially color the ACTUAL and PREDICT columns so that they can be more readily separated by eye when viewing the table. We’ll do just the headers (‘Actual’, ‘Predicted’) in Example 6 and get to the data cells for these columns in Example 7. Here is the code for Example 6:
title ‘Example 6 – Add Style to VAR Statement’; *—————————————————————————————-; ods listing close;
ods html file = “tabhow6.htm”
style = sasweb; *—————————————————————————————-; proc tabulate data=tabhow style=[background=beige];
class country state;
class year / style=[foreground=black];
classlev year / style=[foreground=black];
var actual / style=[background=lightgreen
foreground=black];
var predict / style=[background=lightcyan
foreground=black];
table country * (state=’State/Province’
all=’** Whole Country **’),
year=’Sales per Year’ * (actual =’Actual’
predict=’Predicted’) * sum=’ ‘ * f=comma9.0
SUGI 29 Data Presentation
/ box=[label=’Country by State by Year’
style=[background=beige
foreground=black
font_size=3]];
run; *—————————————————————————————-; ods html close;
ods listing;
Example 6 Code
Just as we can have separate CLASS and CLASSLEV statements for separate CLASS variables or sets of variables, we can also have separate VAR statements. The structure of the STYLE syntax on the VAR statement is just like that of the CLASS or CLASSLEV statement. In our example, we are giving a black foreground to both ACTUAL and PREDICT, but we are giving them different background colors, lightgreen for ACTUAL and lightcyan for PREDICT. This is the first step towards color-coding these columns. Let’s go on to Example 7 and see the next step.
Example 7 – STYLE on TABLE statements
In Example 1, we saw that placing a STYLE option on the PROC TABULATE statement affects the style of all of the data cells. The task is now to style the cells differentially, depending on the VARs tha
程序代写 CS代考 加微信: powcoder QQ: 1823890830 Email: powcoder@163.com