topic Re: ODS HTML background colours in columns and rows in ODS and Base Reporting

ODS HTML background colours in columns and rows

Bill — Wed, 15 Oct 2014 19:04:27 GMT

Using the code below to produce an html file to be read by excel. An example of the output I'm trying to create is in the attached capture.png. The code below produces the column backgrounds, but I would also like to have row group backgrounds. How is that accomplished?

Thank you,

Bill

proc tabulate
data=ChrgLossYldAll order=data style=[foreground=black font_face='arial
narrow' font_size=12pt];

format Process_Op_Cd $OpName. month monyy5. CorpCd $CorpNam.;

class CorpCd Process_Op_Cd Month;

classlev Process_Op_Cd;

var MY ExitCoilScrapY TotalScrapY
YldPct ExitCoilScrapPct TotalScrapPct

ChrgWt ScrapWt TotalScrapWt

/style=[background=cxfef0d9];

var ToolScrapPct ToolScrap

QScrapWt QScrapPct

ToolScrapY QScrapY ;

table CorpCd=''*process_op_cd='',

(MY='Metal Yield (units/1000)'*f=9.0*[style=[background=cxfef0d9]]
ToolScrapY='Tool Scrap (units/1000)'*f=9.2

        ExitCoilScrapY='Exit
         Scrap Coils (units/1000)'*f=9.2*[style=[background=cxfef0d9]]
         QScrapY='Downgrade Scrap Coils (units/1000)'*f=9.2

TotalScrapY='Total Scrap Loss (units/1000)'*f=9.2*[style=[background=cxfef0d9]])*sum=''

*month=' '/rts=25 ;

Re: ODS HTML background colours in columns and rows

Cynthia_sas — Wed, 15 Oct 2014 19:25:44 GMT

Hi: You didn't post enough of a capture to explain what you meant by "row group backgrounds" -- do you mean the backgrounds for CorpCD and process_op_cd that seem to be cut off in your screen shot???

If you want to style the background for the LEVELS of CorpCD and process_op_cd, then you need to have a user-defined format and use the <parent> style for inheritance. See this example, using SASHELP.PRDSALE. The syntax you need will be

style=<parent> -- but I am not entirely sure that the forum posting system will allow me to post the < and > in code. So if the code looks messed up, the syntax is:

style=< parent > but without any spaces in the string.

Cynthia

proc format;
value $ctry 'CANADA'='pink'
'GERMANY'='yellow'
'U.S.A.'='cyan';
run;

ods html file='c:\temp\row_bands.html' style=sasweb;
ods msoffice2k file='c:\temp\row_bands_ht.xls' style=sasweb;
ods tagsets.excelxp file='c:\temp\row_bands_xp.xml' style=sasweb;
proc tabulate data=sashelp.prdsale f=comma8.;
title '1) Changing Style for Country and Division in Bands';
title2 'The parent for DIVISION classlev is COUNTRY classlev';
title3 'The COUNTRY classlev color is determined by the STYLE override';

** this will change COUNTRY and DIVISION levels to their respective colors;
class country division/ style={background=green foreground=black};
classlev country / style={background=$ctry. foreground=black};
classlev division / style=<parent>;

** this will change all PRODTYPE levels to CYAN;
class prodtype / style={background=cyan foreground=black};
classlev prodtype /style=<parent>{foreground=black};
var actual /style=<parent>{foreground=black};

table country=' '*division=' ',
prodtype*actual='Actual'*(sum='Total' max='Max')
/ box={label="Banding Row Colors for Country and Division"};

run;

proc tabulate data=sashelp.prdsale f=comma8.;
title '2) Changing Style for Country and Division in Bands';
title2 'The parent for DIVISION classlev is COUNTRY classlev';
title3 'The COUNTRY classlev color is determined by the STYLE override';

** this will change COUNTRY and DIVISION levels to their respective colors;
class country division/ style={background=green foreground=black};
classlev country / style={background=$ctry. foreground=black};
classlev division / style=<parent>;

** note use of style=<parent> in row dimension;
table country=' '*division=' '*{style=<parent>},
prodtype*actual='Actual'*(sum='Total' max='Max')
/ box={label="Banding Row Colors for Country and Division"} style_precedence=row;

run;
ods _all_ close;

Re: ODS HTML background colours in columns and rows

Bill — Thu, 16 Oct 2014 13:25:24 GMT

Thanks Cynthia;

I'm a little further. What I'm looking for is some way to have row and column banding. I don't think that's quite possible, so I've tried to use font colour as the the vertical banding method in the example that you sent. My changes are included below. I'm not able to make the row bands extend across the entire row. Please see the Capture.png file attached. I'd appreciate more of your great help.

Thank you,

Bill

proc format;

value $ctry 'GERMANY'='yellow'

other='white';

run;

ods html file='c:\temp\row_bands.xls';

proc tabulate data=sashelp.prdsale f=comma8.;

title '2) Changing Style for Country and Division in Bands';

title2 'The parent for DIVISION classlev is COUNTRY classlev';

title3 'The COUNTRY classlev color is determined by the STYLE override';

* this will change COUNTRY and DIVISION levels to their respective colors;

class country division/ style={background=green foreground=black};

classlev country / style={background=$ctry. foreground=black};

classlev division / style=<parent>;

** this will change all PRODTYPE levels to CYAN;

class prodtype / style={background=cyan foreground=black};

classlev prodtype /style=<parent>{foreground=black};

var actual /style=<parent>{foreground=black};

** note use of style=<parent> in row dimension;

table country=' '*division=' '*{style=<parent>},

'Actual'*(sum='Total' *[style=[foreground=black]] max='Max'*[style=[foreground=green]])

box={label="Banding Row Colors for Country and Division"};* style_precedence=row;

run;

ods _all_ close;

Re: ODS HTML background colours in columns and rows

Cynthia_sas — Thu, 16 Oct 2014 23:20:50 GMT

HI:

Hmmmm, I don't see how your code is working at all. you should have a slash (/) BEFORE the BOX= option, so I don't understand how you got your screen shot. Also, why did you get rid of the style_precedence=row???? In addition, your screen shot shows PRODTYPE in the column dimension, but this code does NOT have PRODTYPE in the TABLE statement at all. Are you sure that the code you posted is the code that produced your screen shot? At any rate, if you do NOT have style_precedence=row, then you will NOT get banding across the entire row, including the data cells. PROC TABULATE only has 1 dimension that can "win" when conflicting style elements are specified for multiple dimensions.

PROC REPORT, on the other hand, could make the MAX column green and still do the yellow banding that you want. Have you ever used PROC REPORT before?

Also, I notice that you used ODS HTML instead of ODS MSOFFICE2K, as I showed in my code snippet. The reason I used ODS MSOFFICE2K is that MSOFFICE2K is "Microsoft friendly" HTML -- it is the HTML standard invented by Microsoft when they disagreed with the W3C over the HTML 4.0 specification. If you use ODS MSOFFICE2K as the destination, the resulting HTML tags will be "Microsoft friendly" tags. In the same vein, if you use ODS TAGSETS.EXCELXP, you will create Microsoft-friendly XML that Excel knows how to open and render to create multi-sheet workbooks.

cynthia

Re: ODS HTML background colours in columns and rows

Bill — Mon, 20 Oct 2014 15:45:02 GMT

Thanks Cynthia;

It appears that I was somewhat too generous in cutting code for posting ...

I removed the style_precedence=row in attempt to eliminate the "win" that was happening. Now knowing the constraint that Proc Tabulate can only "win" in one direction now becomes the issue. I'll have to learn how to use proc report.

Thanks for the tip about MSOffice2k anf the tagsets. I'll have to investigate that too.

Thank you for your help,

Bill

Re: ODS HTML background colours in columns and rows

Cynthia_sas — Mon, 20 Oct 2014 19:59:11 GMT

HI, Bill:

Here's something to get you started and a little "light" reading on the topic of PROC REPORT.

http://support.sas.com/resources/papers/proceedings13/366-2013.pdf

http://support.sas.com/resources/papers/proceedings14/SAS388-2014.pdf

Cynthia

proc format;
value $ctry 'GERMANY'='yellow'
other='white';
run;

ods html file='c:\temp\report_bands.html' style=sasweb;
ods tagsets.excelxp file='c:\temp\report_bands.xml' style=sasweb;
ods msoffice2k file='c:\temp\report_bands.xls' style=sasweb;
proc report data=sashelp.prdsale nowd;
where prodtype in ('FURNITURE', 'OFFICE');
title '1a) Using PROC REPORT';

column country rowcolor division prodtype,(actual actual=summax);
define country / group;
define rowcolor / computed /* noprint */;
define division / group;
define prodtype / across;
define actual / sum 'Sum';
define summax / max 'Max' style(column)={foreground=green font_weight=bold};
break before country / ;
compute country;
    length holdctry $10;
    if upcase(_break_) = 'COUNTRY' then
       holdctry = country;
endcomp;
compute rowcolor / character length=100;
    stylestr = catt('style={background=',put(holdctry,$ctry.),'}');
    rowcolor = stylestr;
    call define (_row_,'style',stylestr);
endcomp;
run;

proc report data=sashelp.prdsale nowd;
where prodtype in ('FURNITURE', 'OFFICE');
title '1b) Using PROC REPORT';

column country rowcolor division prodtype,(actual actual=summax);
define country / group;
define rowcolor / computed   noprint ;
define division / group;
define prodtype / across;
define actual / sum 'Sum';
define summax / max 'Max' style(column)={foreground=green font_weight=bold};
break before country / ;
compute country;
    length holdctry $10;
    if upcase(_break_) = 'COUNTRY' then
       holdctry = country;
endcomp;
compute rowcolor / character length=100;
    stylestr = catt('style={background=',put(holdctry,$ctry.),'}');
    rowcolor = stylestr;
    call define (_row_,'style',stylestr);
endcomp;
run;
ods _all_ close;