ods with excel color coding 1

[nirmalraj]

Hi

Is there a method of giving different colors to the cells within the excel, when the output is generated using ODS .Can it be done using some options that comes for generating html output.Is this possible.

I would like to have a few cells within excel spreadsheet in different colors.Can SAS genrate this.

Thanks,
Raj

 

[ChrisW75]

Yep, you apply styles to cells dependant on their contents. It can be done using Proc Report. There's a couple of methods

Method 1 using format.

proc format ;
  value traffic low-100='cx006600'
                100<-500='cxFF9900'
                500<-high='cxCC0000' ;
run ;


ods htmlcss file="<filepath>"  ;
ods listing close ;

proc report data=dset1 missing split='*' nowindows
       style(REPORT)={background=white}
       style(HEADER)={foreground=black background=gold font_weight=bold }
       style(COLUMN)={foreground=black font_weight=bold}
       style(SUMMARY)={foreground=black font_weight=bold}
       ;

column col1
       col2
       ...
       ...   
       ;

define col1          / sum 'Column1'   style(column)={foreground=traffic. font_weight=bold};
define col2 ....


run;

ods htmlcss close ;

Using compute:-

ods rtf file="&recon_report";
ods htmlcss file="&recon_report2";

proc report data=results  nowd split='*'
   style(column)=[font_face=arial font_size=9pt background=white foreground=black]
   style(header)=[font_face=arial font_weight=bold just=center foreground=black background=ffffff00 font_size=11pt];

  columns missing_flg comm_dt commenced_dt;

  define missing_flg     /noprint;
  define commenced_dt    /display 'Sys1*Commenced*Date' format=ddmmyy10.;
  define comm_dt         /display 'Sys2t*Commenced*Date' format=ddmmyy10.;

  compute missing_flg;
  if missing_flg = 'Y' then
         call define(_row_,"style","style={background=orange font_weight=bold}") ;
  endcomp;

  compute acction_commenced_dt;
  if comm_dt ne commenced_dt and missing_flg ne 'Y'  then 
       call define(_col_,"style","style={background=red font_weight=bold}") ;
  endcomp;

run;
ods htmlcss close;
ods rtf close;

NB - It is really important to look at the order of the columns. You cannot do a compute on a column that is processed later. If in the compute you switched comm_dt and commenced_dt, commenced_dt would always be treated as missing.