Sample 25401: Demonstrate the use of banding in PROC TABULATE
 |  |  |  |  |  |
The sample code on the Full Code tab illustrates the use of banding in PROC TABULATE output.
This logic is applicable in any of the non-listing destinations as well, such as PDF, RTF, and so on.
These sample files and code examples are provided by SAS Institute Inc. "as is" without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and fitness for a particular purpose. Recipients acknowledge and agree that SAS Institute shall not be liable for any damages whatsoever arising out of their use of this material. In addition, SAS Institute will provide no support for the materials contained herein.
This sample is from the SAS Sample Library. For additional information, refer to "SAS Output Delivery System: User's Guide".
/****************************************************************/
/* S A S S A M P L E L I B R A R Y */
/* */
/* NAME: ODSTAB10 */
/* TITLE: Demonstrates Use of Banding in PROC TABULATE */
/* PRODUCT: BASE */
/* SYSTEM: ALL */
/* KEYS: ODS HTML BODY= */
/* PROCS: TABULATE */
/* DATA: */
/* SUPPORT: UPDATE: */
/* REF: */
/* MISC: */
/* */
/****************************************************************/
/* Point ODS in the right direction. */
ods html body="odstab10.htm";
ods listing close;
data;
input region $ citysize $ pop product $ saletype $
quantity amount;
cards;
NC S 25000 A100 R 150 3750.00
NE S 37000 A100 R 200 5000.00
SO S 48000 A100 R 410 10250.00
WE S 32000 A100 R 180 4500.00
NC M 125000 A100 R 350 8750.00
NE M 237000 A100 R 600 15000.00
SO M 348000 A100 R 710 17750.00
WE M 432000 A100 R 780 19500.00
NE L 837000 A100 R 800 20000.00
SO L 748000 A100 R 760 19000.00
WE L 932000 A100 R 880 22000.00
NC S 25000 A100 W 150 3000.00
NE S 37000 A100 W 200 4000.00
WE S 32000 A100 W 180 3600.00
NC M 125000 A100 W 350 7000.00
NE M 237000 A100 W 600 12000.00
SO M 348000 A100 W 710 14200.00
WE M 432000 A100 W 780 15600.00
NC L 625000 A100 W 750 15000.00
NE L 837000 A100 W 800 16000.00
SO L 748000 A100 W 760 15200.00
WE L 932000 A100 W 880 17600.00
NC S 25000 A200 R 165 4125.00
NE S 37000 A200 R 215 5375.00
SO S 48000 A200 R 425 10425.00
WE S 32000 A200 R 195 4875.00
NC M 125000 A200 R 365 9125.00
NE M 237000 A200 R 615 15375.00
SO M 348000 A200 R 725 19125.00
WE M 432000 A200 R 795 19875.00
NE L 837000 A200 R 815 20375.00
SO L 748000 A200 R 775 19375.00
WE L 932000 A200 R 895 22375.00
NC S 25000 A200 W 165 3300.00
NE S 37000 A200 W 215 4300.00
WE S 32000 A200 W 195 3900.00
NC M 125000 A200 W 365 7300.00
NE M 237000 A200 W 615 12300.00
SO M 348000 A200 W 725 14500.00
WE M 432000 A200 W 795 15900.00
NC L 625000 A200 W 765 15300.00
NE L 837000 A200 W 815 16300.00
SO L 748000 A200 W 775 15500.00
WE L 932000 A200 W 895 17900.00
NC S 25000 A300 R 157 3925.00
NE S 37000 A300 R 208 5200.00
SO S 48000 A300 R 419 10475.00
WE S 32000 A300 R 186 4650.00
NC M 125000 A300 R 351 8725.00
NE M 237000 A300 R 610 15250.00
SO M 348000 A300 R 714 17850.00
WE M 432000 A300 R 785 19625.00
NE L 837000 A300 R 806 20150.00
SO L 748000 A300 R 768 19200.00
WE L 932000 A300 R 880 22000.00
NC S 25000 A300 W 157 3140.00
NE S 37000 A300 W 208 4160.00
WE S 32000 A300 W 186 3720.00
NC M 125000 A300 W 351 7020.00
NE M 237000 A300 W 610 12200.00
SO M 348000 A300 W 714 14280.00
WE M 432000 A300 W 785 15700.00
NC L 625000 A300 W 757 15140.00
NE L 837000 A300 W 806 16120.00
SO L 748000 A300 W 768 15360.00
WE L 932000 A300 W 880 17600.00
;
run;
proc format;
value $salefmt 'R'='Retail'
'W'='Wholesale';
value $salecol 'R'='red'
'W'='yellow';
value $regcol 'NC'='CX00C400'
'NE'='white'
'SO'='CX00C400'
'WE'='white';
value cellcol 0-40000 = 'blue'
40001-90000 = 'purple'
other = 'brown';
run;
/* The user-defined format REGCOL on the Classlev statement */
/* determines the colors to be applied to the class level */
/* values for the variable REGION. In this example, 'NC' */
/* and 'SO' are formatted to green and 'NE' and 'WE' are */
/* formatted to white. */
/* < parent > on the Classlev statement for Citysize says to */
/* apply same style as Citysize's parent, which is Region. */
/* Region influences or dominates Citysize since it precedes */
/* Citysize in the dimension expression. */
proc tabulate s = {foreground=cellcol.};
class region citysize saletype;
classlev region / s={background=$regcol.};
classlev citysize / s=< parent >;
var quantity amount;
keyword all sum;
format saletype $salefmt.;
label region="Region" citysize="Citysize" saletype="Saletype";
label quantity="Quantity" amount="Amount";
keylabel all="Total";
/* Indirectly, foreground=black overrides foreground=cellcol */
/* on the proc statement. This determines the foreground */
/* color for the data cells in the table. < parent > says */
/* follow the same rules as that of the parent. The parent */
/* is Region. */
table region*citysize*
{s=< parent >{foreground=black}},
(saletype=' ' all)*(quantity amount) /
/* The table option style attribute Background= determines */
/* the color to be applied to the table grid. In this case */
/* the table grid is black. */
s={background=black}
misstext="Missing";
run;
/* The user-defined format SALECOL on the Classlev statement */
/* determines the colors to be applied to the class level */
/* values for the variable Saletype. In this example, 'R' */
/* is formatted to red and 'W' is formatted to white. */
/* Keyword variable Sum have parents Quantity and Amount and */
/* these in turn have parent Saletype. */
proc tabulate s = {foreground=cellcol.};
class region citysize saletype;
classlev saletype / s={background=$salecol.};
var quantity amount / s=< parent >;
keyword sum / s=< parent >;
format saletype $salefmt.;
label region="Region" citysize="Citysize";
label quantity="Quantity" amount="Amount";
keylabel all="Total";
/* Indirectly, foreground=black overrides Foreground=cellcol */
/* on the proc statement. This determines the foreground */
/* color for the data cells in the table. says */
/* follow the same rules as that of the parent. The parent */
/* is Saletype, which precedes Quantity and Amount in the */
/* dimension expression. */
table region*citysize,
saletype=' '*
(quantity*f=COMMA6. amount*f=dollar10.)*
{s=< parent >{foreground=black}}
all*
(quantity*f=COMMA6. amount*f=dollar10.) /
/* The table option style attribute Background= determines */
/* the color to be applied to the table grid. In this case */
/* the table grid is black. Also, when a cell has a missing */
/* value, tabulate places the word 'Missing' within that */
/* cell. */
s={background=black}
misstext="Missing";
run;
/* The user-defined formats REGCOL and SALECOL on the */
/* Classlev statements determine the colors to be applied */
/* to the class level values for the variables Region and */
/* Saletype. According to the table statement, Region is the */
/* parent of Citysize (row dimension) and Saletype is the */
/* parent of Quantity and Amount, which in turn is the */
/* parent of Sum (column dimension). */
proc tabulate s = {foreground=cellcol.};
class region citysize saletype;
classlev region / s={background=$regcol.};
classlev citysize / s=< parent >;
classlev saletype / s={background=$salecol.};
var quantity amount / s=< parent >;
keyword sum / s=< parent >;
format saletype $salefmt.;
label region="Region" citysize="Citysize";
label quantity="Quantity" amount="Amount";
keylabel all="Total";
/* Table statement attributes override Proc attributes! */
/* Column style attributes override Row style attributes. */
/* Style attributes are applied for the row dimension first */
/* then applied for the columns. */
/* The total column 'ALL' has no style attributes, therefore */
/* the Column Header is the default gray and the rows colors */
/* are determined by the row dimension styles. */
table region*citysize*{s=< parent >{foreground=black}}
all,
/* Quantity has 2 style attributes defined. Foreground=purple */
/* and Background=$salecol given from < parent >. */
/* Amount has the default foreground attribute (black) only */
/* Notice the background does not change from green or white */
/* The 'Total' row has no style attributes, therefore the */
/* Row background is the default gray except where column */
/* colors intersect. The foreground colors are determined by */
/* the CELLCOL format because it is specified on the proc */
/* statement. */
saletype=' '*
(quantity*f=COMMA6.*{s=< parent >{foreground=black}}
amount*f=dollar10.)
all*(quantity*f=COMMA6. amount*f=dollar10.) /
/* The table option style attribute Background= determines */
/* the color to be applied to the table grid. In this case */
/* the table grid is black. */
s={background=black}
misstext="Missing";
/* Since Citysize has no parent listed on the table stmt, */
/* no data cells are affected by the green/white background */
/* colors. The foreground colors are determined from the */
/* foreground=cellcol. from the proc statement. */
table region*citysize,
/* Quantity has 2 style attributes defined. Foreground=black */
/* and Background=$salecol given indirectly from < parent >. */
/* Amount has the default foreground attribute (black) only */
/* Notice the background does not change from gray. */
saletype=' '*
(quantity*f=COMMA6.*{s=< parent >{foreground=black}}
amount*f=dollar10.) /
/* The table option style attribute Background= determines */
/* the color to be applied to the table grid. In this case */
/* the table grid is black. Also, when a cell has a missing */
/* value, tabulate places the word 'Missing' within that */
/* cell and makes the background of that cell orange. */
/* Also note, that the dimension expression styles override */
/* missing styles. This explains the missing cell with the */
/* red background and white foreground. */
s={background=black}
misstext={label="Missing" s={background=orange}};
run;
/* The user-defined format REGCOL on the Classlev statement */
/* determines the colors to be applied to the class level */
/* values for the variable REGION. In this example, 'NC' */
/* and 'SO' are formatted to green and 'NE' and 'WE' are */
/* formatted to white. */
/* < parent > on the Classlev statement for Citysize says to */
/* apply same style as Citysize's parent, which is Region. */
/* Region influences or dominates Citysize since it precedes */
/* Citysize in the dimension expression. */
proc tabulate;
class region citysize saletype;
classlev region / s={background=$regcol.};
classlev citysize / s=< parent >;
var quantity amount;
keyword all sum;
format saletype $salefmt.;
label region="Region" citysize="Citysize" saletype="Saletype";
label quantity="Quantity" amount="Amount";
keylabel all="Total";
/* Foreground=black determines the foreground color for the */
/* data cells in the table. < parent > says follow the same */
/* rules as that of the parent. The parent of Citysize is */
/* Region. */
table region=' '*citysize=' '*
{s=< parent >{foreground=black}},
(saletype=' ' all)*(quantity*f=COMMA6. amount*f=dollar10.) /
/* The table option style attribute Background= determines */
/* the color to be applied to the table grid. In this case */
/* the table grid is black. */
s={background=black}
misstext="Missing";
/* Add indent=0 option to table statement. When you use the */
/* indent= option, CLASS variable names and labels are not */
/* included in the row titles. Indention of class values */
/* only occurs with monospace output. */
table region*citysize*
{s=< parent >{foreground=black}},
(saletype=' ' all)*(quantity*f=COMMA6. amount*f=dollar10.) /
s={background=black}
misstext="Missing"
indent=0;
/* 3-dimensional table where the Page dimension has a very */
/* dark green background and yellow foreground. < parent > */
/* implies that all cells in the report inherit the style of */
/* the page dimension expression, which in turn is the style */
/* of the first element in the expression, i.e. Region. */
table region={s={foreground=yellow
background=very dark green}}*{s=< parent >},
citysize,
(saletype=' ' all)*(quantity*f=COMMA6. amount*f=dollar10.) /
/* The table option style attribute Background= determines */
/* the color to be applied to the table grid. In this case */
/* the table grid is black. */
s={background=black}
misstext="Missing";
run;
data tabulate;
input dept acct qtr mon expense @@;
cards;
1 1345 1 1 12980 1 1674 1 3 13135 3 4138 1 1 29930
1 1345 1 1 9475 1 1674 1 3 21672 3 4138 1 2 22530
1 1345 1 1 15633 1 1674 1 3 3847 3 4138 1 2 16446
1 1345 1 2 14009 1 1674 1 3 2808 3 4138 1 2 27135
1 1345 1 2 10226 1 1674 1 3 4633 3 4138 1 3 24399
1 1345 1 2 16872 2 2134 1 1 34520 3 4138 1 3 17811
1 1345 1 2 17800 2 2134 1 1 25199 3 4138 1 3 29388
1 1345 1 2 12994 2 2134 1 1 41578 3 4138 1 3 16592
1 1345 1 2 21440 2 2134 1 2 26560 3 4138 1 3 12112
1 1345 1 3 35300 2 2134 1 2 19388 3 4138 1 3 19984
1 1345 1 3 25769 2 2134 1 2 31990 3 4279 1 1 9984
1 1345 1 3 42518 2 2134 1 3 24399 3 4279 1 1 7288
1 1578 1 1 8000 2 2134 1 3 17811 3 4279 1 1 12025
1 1578 1 1 5840 2 2134 1 3 29388 3 4279 1 2 14209
1 1578 1 1 9636 2 2403 1 1 25464 3 4279 1 2 10372
1 1578 1 2 7900 2 2403 1 1 18588 3 4279 1 2 17113
1 1578 1 2 5767 2 2403 1 1 30670 3 4279 1 3 13500
1 1578 1 2 9515 2 2403 1 2 15494 3 4279 1 3 9855
1 1578 1 3 4500 2 2403 1 2 11310 3 4279 1 3 16260
1 1578 1 3 3285 2 2403 1 2 18661 3 4290 1 1 10948
1 1578 1 3 5420 2 2403 1 2 1482 3 4290 1 1 7992
1 1674 1 1 11950 2 2403 1 2 1081 3 4290 1 1 13186
1 1674 1 1 8723 2 2403 1 2 1783 3 4290 1 2 14539
1 1674 1 1 14392 2 2403 1 3 10009 3 4290 1 2 10613
1 1674 1 2 13534 2 2403 1 3 7306 3 4290 1 2 17511
1 1674 1 2 9879 2 2403 1 3 12054 3 4290 1 3 11459
1 1674 1 2 16300 3 4138 1 1 24850 3 4290 1 3 8365
1 1674 1 3 17994 3 4138 1 1 18140 3 4290 1 3 13802
;
proc format;
value qtrfmt 1 = 'FIRST QUARTER'
2 = 'SECOND QUARTER'
3 = 'THIRD QUARTER'
4 = 'FOURTH QUARTER';
value monfmt 1 = 'JANUARY'
2 = 'FEBRUARY'
3 = 'MARCH'
4 = 'APRIL'
5 = 'MAY'
6 = 'JUNE'
7 = 'JULY'
8 = 'AUGUST'
9 = 'SEPTEMBER'
10 = 'OCTOBER'
11 = 'NOVEMBER'
12 = 'DECEMBER';
value moncol 1 = 'yellow'
2 = 'red'
3 = 'dark green'
other = 'orange';
value dept 1 = 'ACCOUNTING'
2 = 'HUMAN RESOURCES'
3 = 'SYSTEMS';
run;
/* The user-defined format MONCOL on the Classlev statement */
/* determines the colors to be applied to the class level */
/* values for the variable MON. In this example, MON=1 is */
/* formatted to yellow, MON=2 is red and MON=3 is green. */
/* Any other month would be formatted to orange. */
/* Keyword ALL: will always have a background of purple */
/* Keywords SUM and MEAN: will follow the attributes of */
/* their parents because of the < parent > attribute. In */
/* this example, the parent is Expense. */
/* Var EXPENSE: will follow the attributes of its */
/* Mon. Also, the foreground of the data cells will be */
/* white. */
proc tabulate format=11.2;
class qtr mon dept acct;
classlev mon / s={background=moncol.};
var expense / s=< parent > {foreground=white};
keyword sum mean / s=< parent >;
keyword all / s={background=purple};
table (qtr*(mon all) all)*expense*(sum mean) /
s={background=black};
table (qtr*(mon all) all)*(expense*sum expense*mean) /
s={background=black};
table (qtr*(mon all) all)*expense*sum
(qtr*(mon all) all)*expense*mean / s={background=black};
table qtr*(mon all)*expense*sum
qtr*(mon all)*expense*mean
all*expense*sum
all*expense*mean / s={background=black};
table qtr*mon*expense*sum
qtr*all*expense*sum
qtr*mon*expense*mean
qtr*all*expense*mean
all*expense*sum
all*expense*mean / s={background=black};
run;
/* All done, let us take a look. */
ods html close;
These sample files and code examples are provided by SAS Institute Inc. "as is" without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and fitness for a particular purpose. Recipients acknowledge and agree that SAS Institute shall not be liable for any damages whatsoever arising out of their use of this material. In addition, SAS Institute will provide no support for the materials contained herein.
This code produces an HTML file.Click here to see the output Results for this sample were generated in SAS Release 9.1.3 Service Pack 2.
Color code rows of output in the HTML destination.
| Type: | Sample |
| Topic: | Third Party ==> Output ==> HTML SAS Reference ==> Procedures ==> TABULATE SAS Reference ==> ODS (Output Delivery System) |
| Date Modified: | 2008-05-28 09:22:51 |
| Date Created: | 2005-05-23 13:51:21 |
Operating System and Release Information
| Product Family | Product | Host | SAS Release | |
| Starting | Ending | |||
| SAS System | Base SAS | All | 9.1 TS1M3 | n/a |