The SAS Output Delivery System and reporting techniques

ODS SPAN color and borders

Reply
Frequent Contributor
Frequent Contributor
Posts: 139

ODS SPAN color and borders

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; 


ODS EXAMPLE.jpg
SAS Super FREQ
Posts: 8,820

Re: ODS SPAN color and borders

[ Edited ]

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

ods_excel_style_htmlblue.png

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

 

STYLE=JOURNAL

ods_excel_style_journal.png

 

STYLE=SASWEB

ods_excel_style_sasweb.png

 

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:

ods_html_htmlblue.png

 

 

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

Ask a Question
Discussion stats
  • 1 reply
  • 243 views
  • 0 likes
  • 2 in conversation