ODS SPAN color and borders

LB — Mon, 21 Nov 2016 22:20:08 GMT

Happy Thankgiving week all!

I have a couple of small questions regarding output into Excel with ODS EXCEL. I am running this on SAS 9.4 M3 on a linux system.

If you look at the attached -I am attempting to simply change the background color of the top headers- I can do this within the style command for the headers but then it does across the row-which I don't want- I would like to individually change those colors. I have attempted to insert style statements into:

("ESTIMATED PERCENT AMBULATION FOR MONTH OF &MO_REPORT" month_num b_denom mon_compliance) between the parentheses w/o success. I can do this for the ODS PDF destination and that works though.

Also when I use the code

style (header)={background=white just=center color=gray font =("arial",8.0pt) bordercolor=black borderwidth=1 frame=box};

It works into the EG windoes environment but does not render the black borders around cells in the ODS excel output. Any advice on this?

Some snippet of code below.

Any assistance much appreciated.

Lawrence

proc report data=dfrx.ms_stats_out (where=(fac_id= "xxx")) headline headskip nowd spanrows
style (column)={background=white just=center color=black font =("arial",7.5pt) bordercolor=black borderwidth=1 frame=box}
style (header)={background=white just=center color=gray font =("arial",8.0pt) bordercolor=black borderwidth=1 frame=box};

columns UNIT ("PERCENT/AMBULATION/FOR/&Flow_Beg" numerator denom pct_compliance)
("ESTIMATED PERCENT AMBULATION FOR MONTH OF &MO_REPORT" month_num b_denom mon_compliance);

...

define UNIT/'Unit' style (header)={background=white just=center color=black font =("arial",8.0pt)};
define pct_compliance/"% Compliance" style (header)={background=green just=center color=white font =("arial",8.0pt) bordercolor=black borderwidth=2};
define month_num/"Numerator " style (header)={background=green just=center color=white font =("arial",8.0pt)};
define b_denom/"Denominator" style (header)={background=green just=center color=white font =("arial",8.0pt)};
define mon_compliance/"% Compliance" style={tagattr='format:0%'} style (header)={background=green just=center color=white font =("arial",8.0pt)};
define Numerator/"Numerator" style (header)= {background=green just=center color=white font =("arial",8.0pt)};
define Denom/"Denominator" style(header)={background=green just=center color=white font =("arial",8.0pt)};

....

run;

Re: ODS SPAN color and borders

Cynthia_sas — Wed, 23 Nov 2016 13:54:23 GMT

Hi:
A few comments:
-- Note sure about what the "..." means. What you've posted only seems to show the green headers in your screen shot
-- You do not show HOW you are getting the output into Excel -- yes, you said you were using ODS EXCEL, but you didn't show the ODS code and any options/suboptions in effect
-- headline and headskip are meaningless for most ODS destinations -- they were designed for LISTING output only
-- you do not have any usages -- so I assume you are taking the default usage of ANALYSIS SUM for numeric variables and DISPLAY for character variables
--You did not post data so to run your code, people either need to make some fake data or imagine what a program change would do
-- hard to answer your question about borders because Excel is famous for not using borders, widths, decimal places, etc -- the SAS Style you specified on your ODS statement might have an impact -- for example style=htmlblue vs style=journal vs style=sasweb all produce different borderwidths and internal rules in a different way
--you can simplify your style overrides, for example, once you specify font and just in the PROC REPORT statement, you do not have to change it for the DEFINE statement.

As a comparison of what different styles do in ODS EXCEL, consider these outputs:

STYLE=HTMLBLUE

(at least there are border lines using STYLE=HTMLBLUE)

STYLE=JOURNAL

STYLE=SASWEB

Compared to output with ODS HTML and STYLE=HTMLBLUE using the same PROC REPORT code. Note how Borderwidth=5px changes for HTML, but did NOT change for ODS EXCEL:

produced with this slightly changed data and program:

%let flow_beg=Something;
%let mo_report=November;

data class;
set sashelp.class;
unit='xxx';
run;

ods html(id=ht) file='c:\temp\ods_html_lb.html' style=htmlblue;
ods excel(id=1) file='c:\temp\lb1.xlsx' style=htmlblue;
ods excel(id=2) file='c:\temp\lb2.xlsx' style=journal;
ods excel (id=3) file='c:\temp\lb3.xlsx' style=sasweb;
proc report data=class (where=(sex="F")) nowd spanrows
style (column)={background=white just=center color=black font=("arial",7.5pt) bordercolor=black borderwidth=5px frame=box}
style (header)={background=white font_weight=bold just=center color=gray font=("arial",8.0pt) bordercolor=black borderwidth=5px frame=box};

columns UNIT ("PERCENT/AMBULATION/FOR/&Flow_Beg" name age height)
("ESTIMATED PERCENT AMBULATION FOR MONTH OF &MO_REPORT" sex age=b_age weight);

define UNIT/'Unit' style(header)={background=white color=black };
define name/"numerator" style(header)= {background=green  color=white };
define age/"denominator" style(header)={background=green  color=white };
define height/"% Compliance" 
             style(column)={tagattr='format:0%'} 
             style(header)={background=green  color=white };
define sex/"numerator " style(header)={background=green  color=white };
define b_age/"denominator" style(header)={background=green  color=white };
define weight/"% Compliance" 
             style(column)={tagattr='format:0%'} 
             style(header)={background=green color=white };
run; 
ods html(id=ht) close;
ods excel(id=3) close;
ods excel(id=2) close;
ods excel(id=1) close;

cynthia

topic Re: ODS SPAN color and borders in ODS and Base Reporting

ODS SPAN color and borders

Re: ODS SPAN color and borders