topic Re: Why does not my Proc template style on ODS Excel ? in ODS and Base Reporting

Why does not my Proc template style on ODS Excel ?

bala_pa — Wed, 22 Mar 2017 19:17:59 GMT

Hello SAS Form,

I am currently using a template style to create same report in xlsx file using ods excel and xml using tagsets. The template works on xml and not on xlsx. For example: The font, borders are as per style in xml format whereas xlsx uses some default of its own. Can someone help me resolve the issue? Below is the sample code that I am working on. I am using SAS 9.4 M1

proc template;
define style customnew;
parent=Styles.Normal;

style header /
font_face='Microsoft Sans Serif'
fontsize=11pt
color=black
fontweight=bold
textalign=center
verticalalign=middle
backgroundcolor=lightblue
;
style data /
font_face='Microsoft Sans Serif'
fontsize=10pt
borderbottomcolor=black

textalign=center
verticalalign=middle
;
style Table /
borderwidth = 3px
bordercolor=black
frame = box
rules = all
borderwidth = 1pt
bordertopcolor = black
borderbottomcolor = black
borderleftcolor = black
borderrightcolor = black

;
style Body /
topmargin = _undef_
bottommargin = _undef_
leftmargin = _undef_
rightmargin = _undef_
;
end;
run;

%let outputpath=C:\Users;

ods excel file="&outputpath.\class.xlsx" style=customnew;
proc print data=sashelp.class;
run;
ods excel close;

ods tagsets.excelxp file="&outputpath.\class.xml" style=customnew;
proc print data=sashelp.class;
run;
ods tagsets.excelxp close;

Re: Why does not my Proc template style on ODS Excel ?

Cynthia_sas — Thu, 23 Mar 2017 00:19:45 GMT

Hi:

I used a slightly modified version of your template and added a very obvious change to the style header (pink and purple) so I could be sure that the style template was being used. I can verify that the style template is being used as you can see in the screen shots below. My template was almost the same as yours except I added an ODS PATH statement so I could control the location of the template and I added the style header section and I cleaned up the indention.

If your issue is with the border lines, that is something of a sticking point with templates and Excel. Microsoft has one way to do borders and border lines and won't really respect what you specify here, easily.

One way to verify that Excel does not respect border lines -- either presence or absence, take a look at the differing outputs created by this code:

ods html(id=1) file='c:\temp\output\class_jour.html' style=journal;
ods tagsets.excelxp(id=2) file='c:\temp\output\class_jour.xml' style=journal;
ods excel(id=3) file="&outputpath.\class_jour.xlsx" style=journal;
proc print data=sashelp.class;
run;
ods excel(id=3) close;
ods tagsets.excelxp(id=2) close;
ods html(id=1) close;

as you can see in the screen shot, the HTML file is lovely, with no interior table lines, as defined by the journal style. Compare that with the ODS EXCEL and ODS TAGSETS.EXCELXP outputs:

When I run my version of your code, I do see the style is used for those elements and attributes that Excel respects. For example, it did use the MIcrosoft Sans Serif font and the sizes specified.

cynthia

Re: Why does not my Proc template style on ODS Excel ?

bala_pa — Mon, 27 Mar 2017 21:28:49 GMT

Hello Cynthia,

I included the ODS path statement and also modified the Proc Template (FONT, its SIZE and HEADER Colors)to check if it worked. Yes all the changed worked for .xml file whereas only the color and size options we applied to the xlsx format. You can see that the xlsx file does not use the MocroSoft Sans Serif font and borders. If this is an issue like you told earlier with MS Excel and templates, can you suggest me a suitable template style which I can make modifications and will still work with MS Excel.

Thank you,

Bala

Re: Why does not my Proc template style on ODS Excel ?

Cynthia_sas — Tue, 28 Mar 2017 01:23:01 GMT

Hi:
As I indicated in my posting, "If your issue is with the border lines, that is something of a sticking point with templates and Excel. Microsoft has one way to do borders and border lines and won't really respect what you specify here, easily."

The code I and examples I posted illustrated that point .. the border lines work for RTF, PDF and HTML (or absence of border lines if you use the JOURNAL style) -- but they do NOT work with ODS EXCEL or ODS TAGSETS.EXCELXP. And, I am not sure that you can specify to turn on the border lines with a style template. This would be a question for Tech Support.

I did see the Microsoft Sans Serif font used in my Excel output. If you replace all the occurences of Courier New with Microsoft Sans Serif in your template, you should see that font being used, assuming it is on your system. If your template font is not showing in Excel, then that, along with border lines is a question for Tech Support.

cynthia

Re: Why does not my Proc template style on ODS Excel ?

bala_pa — Tue, 28 Mar 2017 15:55:29 GMT

Thank you

Bala