Date: Thu, 22 May 2003 13:28:34 -0400
Reply-To: Ian Whitlock <WHITLOI1@WESTAT.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Ian Whitlock <WHITLOI1@WESTAT.COM>
Subject: Re: Tabulate,ODS and Row/Column totals background colors
Content-Type: text/plain
Richard,
Thanks for setting up executable code and clarifying the example.
With a little luck the solution might be a traffic lighting format. At
least it works for your random data and with the addition of error trapping,
one could in principle know those cases where it failed without looking at
the output. However this trick does depend on the fact that total numbers
are not likely to also be cell numbers.
%let nProdCat = 3;
%let maxProdPerProdCat = 2;
data foo;
i = ranuni(5);
array npdesc [&nProdCat] _temporary_;
array pdesc [&nProdCat, &maxProdPerProdCat] $1 _temporary_;
do i = 1 to &nProdCat;
npdesc[i] = 1 + int ( &maxProdPerProdCat * ranuni(0));
do j = 1 to npdesc[i];
pdesc [ i, j ] = byte (rank('A') + 26 * ranuni(0));
end;
end;
nevents = 5000;
do eventid = 1 to nevents;
prodcategory = 1 + int ( &nProdCat * ranuni(0));
x = 1 + (npdesc[prodcategory] * ranuni(0));
proddesc = pdesc [ prodcategory, x ] ;
start = 1 + int(3 * ranuni(0));
category = byte (rank('A') + 3 * ranuni(0));
amount = 20 + int(20*ranuni(0));
code = 1 + int(3*ranuni(0));
_freq_ = 1 + int(3 * ranuni(0));
output;
end;
format _numeric_ 6.;
keep eventid prodcategory proddesc start category amount code _freq_; run;
ods listing close;
ods html body='c:\temp\foo.html' style=sasweb;
options nocenter;
title "Want white cells under Total to be a different color"; footnote;
%let bcolor = TAN;
%let bnzblue = DMBLUE;
%let cellback = CXE0E0E0;
%let allrow =
{ label = 'Total '
STYLE = [ background = &bcolor
font_size = 2
font_weight = bold]
}
*
[ style = [ background = &bcolor
font_size = 2
font_weight=bold
]
]
;
/* set up format code for trafic lighting */
proc summary data = foo ;
class prodcategory proddesc start ;
types prodcategory * proddesc
prodcategory * proddesc * start
;
var amount _freq_ ;
output out = summary sum = totamt nopps ;
run ;
data fmtdata ( keep = fmtname start label hlo ) ;
length fmtname $ 8 label $ 8 ;
if eof then
do ;
hlo = "O" ;
fmtname = "freq" ;
label = "&cellback" ;
output ;
fmtname = "sum" ;
label = "&cellback" ;
output ;
end ;
set summary end = eof ;
fmtname = "freq" ;
start = nopps ;
label = "&bcolor" ;
output ;
fmtname = "sum" ;
start = totamt ;
label = "&bcolor" ;
output ;
run ;
proc sort data = fmtdata nodupkey ;
by fmtname hlo start ;
run ;
proc format cntlin = fmtdata ;
/**********************************************
old starting code more like a sledgehammer, but it works
value freq
400 - high = "&bcolor"
other = "&cellback"
;
value sum
4500 - high = "&bcolor"
other = "&cellback"
;
**********************************************/
run ;
proc tabulate data=foo
;
class prodcategory proddesc start category code;
var _freq_ ;
var amount ;
table
prodcategory = { label = ' ' }
* proddesc = { label = ' ' }
,
start = ' '
* ( category = ' ' all = &allrow )
all = &allrow
,
all = { label = 'Total '
STYLE = [ foreground = white
background = &bnzblue]
}
/*
* [ style = [ background = &cellback ] ]
*/
* ( _freq_ = ' ' * [ style = [ background = freq. ] ] /* iw */
* sum = 'N Opps'
* f = comma8.
amount = ' ' * [ style = [ background = sum. ] ] /* iw */
* sum = 'Total $'
* f = dollar18.
)
code = ' '
* ( _freq_ = ' '
* sum = 'N Opps'
* f = comma8.
amount = ' '
* sum = 'Total $'
* f = dollar18.
)
/
box = [ style = [ foreground = white
font_face = helvetica
Background = &bnzblue
]
]
;
run;
quit;
ods html close;
I am not sure either way, but Ray Pass may have discussed this use of
traffic lighting in one of his presentations. In any case thanks Ray.
I didn't preserve the bold font for totals in the problem columns, but that
can be done by adding more formats for traffic bolding.
IanWhitlock@westat.com
-----Original Message-----
From: Richard A. DeVenezia [mailto:radevenz@IX.NETCOM.COM]
Sent: Thursday, May 22, 2003 10:39 AM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Re: Tabulate,ODS and Row/Column totals background colors
"Don Stanley & Susann Ryan" <don_stanley@PARADISE.NET.NZ> wrote in message
news:3ECCA833.31A53C3@paradise.net.nz...
> The follow PROC TABULATE table statement does pretty well what I want
> except one small problem ...
>
> table
> prodcategory={label=''}
> * proddesc={label=''},
>
> start='' * (category='' all=&allrow)
> all=&allrow,
>
> all={label='Total ' STYLE=[foreground=white
background=&bnzblue]}*
> [style=[background=&cellback]]*(_freq_=''*sum='N
> Opps'*f=comma8. sale_opp_oth_amt=''*sum='Total $'*f=dollar18.)
> sale_stage_code=''*(_freq_=''*sum='N Opps'*f=comma8.
> sale_opp_oth_amt=''*sum='Total $'*f=dollar18.)
>
> / box=[style=[foreground=white font_face=helvetica
> Background=&bnzblue]]
> ;
>
> where
>
> &allrow=
> {label='Total ' STYLE=[background=&bcolor font_size=2
> font_weight=bold]}*
> [style=[background=&bcolor font_size=2 font_weight=bold]]
>
> The intent is that the cross totals, ie totals derived from
>
> start='' * (category='' all=&allrow)
> all=&allrow,
>
> should be background coloured whatever &bcolor is. This works fine,
> except where they intersect with the column total defined by
>
> all={label='Total ' STYLE=[foreground=white background=&bnzblue]}*
>
> In that case the background color on the column total overrides the
> background color on the row total.
>
> How can I set the row total line background to be &bcolor when the row
> total intersects the column total?
>
> Thanks
> Don
> --
Here is a sample program demonstrating what I think is Don's problem. The
output is ok, except he wants the first two white background columns to be a
different background color (except for the total lines). If you uncomment
the
/*
* [ style = [ background = &cellback ] ]
*/
you will see the first two data columns background changes, but the total
lines are no longer separately colored.
If there is a solution, it might involve <PARENT> somewhere.
===== tested 8.2 =====
%let nProdCat = 3;
%let maxProdPerProdCat = 2;
data foo;
i = ranuni(5);
array npdesc [&nProdCat] _temporary_;
array pdesc [&nProdCat, &maxProdPerProdCat] $1 _temporary_;
do i = 1 to &nProdCat;
npdesc[i] = 1 + int ( &maxProdPerProdCat * ranuni(0));
do j = 1 to npdesc[i];
pdesc [ i, j ] = byte (rank('A') + 26 * ranuni(0));
end;
end;
nevents = 5000;
do eventid = 1 to nevents;
prodcategory = 1 + int ( &nProdCat * ranuni(0));
x = 1 + (npdesc[prodcategory] * ranuni(0));
proddesc = pdesc [ prodcategory, x ] ;
start = 1 + int(3 * ranuni(0));
category = byte (rank('A') + 3 * ranuni(0));
amount = 20 + int(20*ranuni(0));
code = 1 + int(3*ranuni(0));
_freq_ = 1 + int(3 * ranuni(0));
output;
end;
format _numeric_ 6.;
keep eventid prodcategory proddesc start category amount code _freq_; run;
ods listing close;
ods html body='c:\temp\foo.html' style=sasweb;
options nocenter;
title "Want white cells under Total to be a different color"; footnote;
%let bcolor = TAN;
%let bnzblue = DMBLUE;
%let cellback = CXE0E0E0;
%let allrow =
{ label = 'Total '
STYLE = [ background = &bcolor
font_size = 2
font_weight = bold]
}
*
[ style = [ background = &bcolor
font_size = 2
font_weight=bold
]
]
;
proc tabulate data=foo
;
class prodcategory proddesc start category code;
var _freq_ ;
var amount ;
table
prodcategory = { label = ' ' }
* proddesc = { label = ' ' }
,
start = ' '
* ( category = ' ' all = &allrow )
all = &allrow
,
all = { label = 'Total '
STYLE = [ foreground = white
background = &bnzblue]
}
/*
* [ style = [ background = &cellback ] ]
*/
* ( _freq_ = ' '
* sum = 'N Opps'
* f = comma8.
amount = ' '
* sum = 'Total $'
* f = dollar18.
)
code = ' '
* ( _freq_ = ' '
* sum = 'N Opps'
* f = comma8.
amount = ' '
* sum = 'Total $'
* f = dollar18.
)
/
box = [ style = [ foreground = white
font_face = helvetica
Background = &bnzblue
]
]
;
run;
quit;
ods html close;
=====
Richard A. DeVenezia, http://www.devenezia.com
