ODS Excel proc print borders and SasDocPrinter

PhilC — Tue, 11 Dec 2018 18:59:54 GMT

How do I get borders on the following example table using the SASdocPrinter style and ODS EXCEL?

%MACRO Brdr_clr_wdth(c,w);
  bordertopcolor=&c    bordertopwidth=&w
  borderbottomcolor=&c borderbottomwidth=&w
  borderleftcolor=&c   borderleftwidth=&w
  borderrightcolor=&c  borderrightwidth=&w
%MEND;

ODS Excel(CARS) 
  File="%sysfunc(pathname(work,L))\Temp_cars.xlsx"
  style=SasDocPrinter;

  proc print data=sashelp.CARS(obs=2) NoObs label 
    style(HEADER)=[font_weight=bold %Brdr_clr_wdth(black,1) ] 
    style(TABLE)=[                  %Brdr_clr_wdth(black,1) ] 
    ;
    VAR Model	MSRP;
  run;

ODS Excel(CARS) close;

Re: ODS Excel proc print borders and SasDocPrinter

Cynthia_sas — Wed, 12 Dec 2018 15:18:07 GMT

Hi:
Take a look at this paper https://support.sas.com/resources/papers/proceedings16/SAS5642-2016.pdf which describes that TAGSETS.EXCELXP and ODS EXCEL use different values for setting border width (page 18-19) -- plus it also looks like the destinations use different background values so the lines will show.

Also note that SASDOCPRINTER was initially a style written for the PDF and other Printer-family destinations, so it may not work as well as starting from HTMLBLUE.

Cynthia

Re: ODS Excel proc print borders and SasDocPrinter

PhilC — Wed, 23 Jan 2019 14:09:57 GMT

Thanks @Cynthia_sas,

I also knew that I could redefine a style. An earlier attempt left me fruitless and frustrated -- on many levels. This time I pushed through and made myself a "HTMLBW" style. I'm still frustrated because (disclaimer) I know its just a hack and I have no clue if and when I use it that it will randomly fail on me and force me to tweak something. I'm going to share it here, just incase someone might find useful.

Styles.HTMLBW:

proc template;                                                                
   define style Styles.HTMLBW;                                              
      parent = styles.statistical;  
      class fonts                                                             
         "Fonts used in the default style" /                                  
         'TitleFont2' = ("<sans-serif>, Helvetica, sans-serif",4) 
         'TitleFont' = ("<sans-serif>, Helvetica, sans-serif",5);
      class GraphColors /                                                     
         'gndata12' = cxECE8C4                                                
         'gndata11' = cxDBD8F8                                                
         'gndata10' = cxC6E4BF                                                
         'gndata9' = cxE6CEAD                                                 
         'gndata8' = cxE5C1D4                                                 
         'gndata7' = cxCCDFF0                                                 
         'gndata6' = cxDDDEB5                                                 
         'gndata5' = cxDBC7E7                                                 
         'gndata4' = cxD5C6B4                                                 
         'gndata3' = cxB7D4D3                                                 
         'gndata2' = cxE7B3B4                                                 
         'gndata1' = cxBBC2DC                                                 
         'gndata' = cxBBC2DC                                                  
         'gofill' = cxFFFFFF                                                  
         'gout2cend' = cx919191                                               
         'gout2cstart' = cxe9e9e9                                             
         'gblockheader' = cxcfd5de                                            
         'gcphasebox' = cx989EA1                                              
         'gphasebox' = cxDBE6F2                                               
         'gczonec' = cxBECEE0                                                 
         'gzonec' = cxCCDCEE                                                  
         'gczoneb' = cxCCDCEE                                                 
         'gzoneb' = cxD7E5F3                                                  
         'gzonea' = cxE3EDF7                                                  
         'gconramp3cend' = cx9C1C00                                           
         'gconramp3cneutral' = cx222222                                       
         'gconramp3cstart' = cx0E36AC                                         
         'gramp3cend' = cxD05B5B                                              
         'gramp3cneutral' = cxFFFFFF                                          
         'gramp3cstart' = cx667FA2                                            
         'gcontrollim' = cxE6F2FF                                             
         'gccontrollim' = cxBFC7D9                                            
         'gruntest' = cxCAE3FF                                                
         'gcruntest' = cxBF4D4D                                               
         'gclipping' = cxFFFFC6                                               
         'gcclipping' = cxC1C100                                              
         'gaxis' = cx989EA1                                                   
         'greferencelines' = cx989EA1;                                        
      class colors /                                                          
         'link2' = cx0000FF                                                   
         'link1' = cx800080                                                   
         'docbg' = cxFFFFFF                                                   
         'contentbg' = cxFFFFFF                                               
         'systitlebg' = cxFFFFFF                                              
         'titlebg' = cxFFFFFF                                                 
         'proctitlebg' = cxFFFFFF                                             
         'headerbg' = cxEDF2F9                                                
         'captionbg' = cxFFFFFF                                               
         'captionfg' = cx000000                                               
         'bylinebg' = cxFFFFFF                                                
         'notebg' = cxFFFFFF                                                  
         'tablebg' = cxFFFFFF                                                 
         'batchbg' = cxFFFFFF                                                 
         'systitlefg' = cx000000                                              
         'titlefg' = cx000000                                                 
         'proctitlefg' = cx000000                                             
         'bylinefg' = cx000000                                                
         'notefg' = cx000000;                                                 
      class Header /                                                          
         bordercolor = cx000000                                    
         backgroundcolor = cxFFFFFF                                  
         color = cx000000;                                                    
      class Footer /                                                          
         bordercolor = cx000000                                    
         backgroundcolor = cxFFFFFF                                  
         color = cx000000;                                                    
      class RowHeader /                                                       
         bordercolor = cx000000                                    
         backgroundcolor = cxFFFFFF                                  
         color = cx000000;                                                    
      class RowFooter /                                                       
         bordercolor = cx000000                                    
         backgroundcolor = cxFFFFFF                                  
         color = cx000000;                                                    
      class Table /                                                           
         bordertopwidth = 1px                                                 
         borderleftwidth = 1px                                                
         borderbottomwidth = 1px                                              
         borderrightwidth = 1px                                               
         bordercolor = cx000000                                               
         cellpadding = 5;                                                     
      style Data /                                                            
         font = fonts('DocFont')                                              
         backgroundcolor = cxFFFFFF                                           
         bordercolor = cx000000                                               
         bordertopwidth = 1px                                                 
         borderleftwidth = 1px                                                
         borderbottomwidth = 1px                                              
         borderrightwidth = 1px                                               
         borderstyle = solid;                                                 
      class Graph /                                                           
         attrpriority = "Color";                                              
      class GraphFit2 /                                                       
         linestyle = 1;                                                       
      class GraphClipping /                                                   
         markersymbol = "circlefilled";                                       
      class Link /                                                            
         color = colors('link2');                                             
      class VisitedLink /                                                     
         color = colors('link1');                                             
      class ActiveLink /                                                      
         color = colors('link1');                                             
   end;                                                                       
run;

Re: ODS Excel proc print borders and SasDocPrinter

PhilC — Thu, 13 Dec 2018 19:29:26 GMT

After working on these template definitions, for hours, I see ghostly six digit hexadecimal when I blink my eyes. yuck

topic Re: ODS Excel proc print borders and SasDocPrinter in ODS and Base Reporting

ODS Excel proc print borders and SasDocPrinter

Re: ODS Excel proc print borders and SasDocPrinter

Re: ODS Excel proc print borders and SasDocPrinter

Re: ODS Excel proc print borders and SasDocPrinter