export to excel(.xls) file with specified font

[Mike_Davis]

Helo everyone,

Is that possible we can use proc export to generate an .xls(excel 2003) file with specified font, such as "Calibri"?

I want the output excel file use font: "Calibri".

the default font of my output file  is "MS Sans Serif",looks not good.

Thanks

Mike

PROC EXPORT DATA=sashelp.class

OUTFILE = "c:\temp\sashelpclass.xls"

DBMS = EXCEL2002 REPLACE;

SHEET = "sashelpclass";

RUN;

[Cynthia_sas]

Hi:

  When you use PROC EXPORT (or the SAS Excel Libname Engine), you are doing a "data to data" export. Using PROC EXPORT, a SAS dataset is exported to proprietary, Excel data (worksheet/workbook) format. There are no fonts or colors that you can specify in this type of export process. All fonts and colors are provided by the application that opens the exported data -- in this case, Microsoft Excel. So, you can either change your Excel defaults or switch to ODS-based methods, so that you can specify style characteristics for the report file that you create with ODS.

  With ODS, if you create either an HTML file or an XML file from your procedure of choice (such as PROC PRINT), for example, then you CAN specify fonts (and colors) to be used when the file is opened and rendered. Because you are creating a report result file using ODS, you CAN specify fonts by using ODS STYLE= override methods.

  In the code below, ODS methods of changing the font are shown for a Microsoft HTML file (created by ODS MSOFFICE2K) and for a Spreadsheet Markup Language XML file (created by TAGSETS.EXCELXP). You can open each file by doing a File--> Open from inside Microsoft Excel. Note that with ODS TAGSETS.EXCELXP, there is also a sub-option method for specifying the sheet-name.

cynthia

ods listing close;

title; footnote;

** do file--> open from inside Excel to open these files;

ods msoffice2k file='c:\temp\class_ht.html' style=sasweb;

ods tagsets.excelxp file='c:\temp\class_xp.xml' style=sasweb

    options(sheet_name="sashelp_class");

proc print data=sashelp.class noobs

     style(header)={font_face="Calibri" font_size=12pt}

     style(data)={font_face="Calibri" font_size=10pt};

var name age sex height weight;

run;

ods _all_ close;

[Mike_Davis]

Thank you very much..but it will cause cause error when I run the code in SAS 9.1.3:

Thanks!

Mike

ERROR:  Read Access Violation In Task [ PRINT )

Exception occurred at (673722F0)

Task Traceback

Address   Frame     (DBGHELP API Version 4.0 rev 5)

673722F0  06ABE6A8  saswuchr:mcn_main+0x12F0

60AD73B0  06ABEE8C  sasxml:mcn_main+0x363B0

60AE8790  06ABF324  sasxml:mcn_main+0x47790

60D62C20  06ABF41C  sasmrk:mcn_main+0x11C20

60D6127F  06ABF55C  sasmrk:mcn_main+0x1027F

60AE6235  06ABF5E8  sasxml:mcn_main+0x45235

60D556F8  06ABF684  sasmrk:mcn_main+0x46F8

60D551A4  06ABF6AC  sasmrk:mcn_main+0x41A4

66F1AE26  06ABFA4C  sasprt7:mcn_main+0x9E26

66F15F94  06ABFCE4  sasprt7:mcn_main+0x4F94

66F1C8A2  06ABFD08  sasprt7:mcn_main+0xB8A2

66F1C43F  06ABFD88  sasprt7:mcn_main+0xB43F

66F1164E  06ABFDFC  sasprt7:mcn_main+0x64E

66F1C339  06ABFE40  sasprt7:mcn_main+0xB339

66F32054  06ABFF88  sasprint:mcn_main+0x1054

01272B02  06ABFFA0  sashost:Main+0xBE72

01276C20  06ABFFB4  sashost:Main+0xFF90

7C80B729  06ABFFEC  kernel32:GetModuleFileNameA+0x1BA

[Ksharp]

OR try to use proc report.

ods listing close;
ods tagsets.excelxp file='c:\temp\class_xp.xls' style=sasweb
    options(sheet_name="sashelp_class");
proc report data=sashelp.class nowd
     style(header)={font_face="Calibri" font_size=12pt}
     style(report)={font_face="Calibri" font_size=10pt};
column name age sex height weight;
run;
ods tagsets.excelxp close;
ods listing;

Ksharp

[Cynthia_sas]

Hi:

If it works with REPORT and not with PRINT in SAS 9.1.3, then that is also an issue for Tech Support. Style= overrides have been possible with both PROC REPORT and PROC PRINT since ODS was first introduced.

Also, when I run your code, the Headers are in Calibri font, but the data columns are still in Arial. To change the data columns to Calibri, the style(report) override should change to style(column):

proc report data=sashelp.class nowd

     style(header)={font_face="Calibri" font_size=12pt}

     style(column)={font_face="Calibri" font_size=10pt};

There are very few fonts that are controlled by the "report" area in a style override -- that area is usually where you change interior table lines, cellspacing, cellpadding, etc.

cynthia

[Mike_Davis]

Thank you!

This ods tagsets.excelxp output(style=sasweb) is very great, only one thing need to be mentioned: how to get the excel file change to no background color(use the most simple style but  only with specialf font) in SAS ?

Thanks!

Mike

[Cynthia_sas]

Hi:

  One of the reasons for using ODS is to be able to have styles, colors and font capability in the result file -- this is something that you do NOT have with data to data export methods like PROC EXPORT and the LIBNAME engine. When I use STYLE=SASWEB, the background of the cells are white and the header cells are blue? I'm not sure I understand what you want when you say "no background color" -- when I open Excel, the usual background to all the cells is white? If you do not want the header colors to be blue, then you might try another style, such as STYLE=MINIMAL

or, change the code so the header is also white:

proc report data=sashelp.class nowd

     style(header)={font_face="Calibri" font_size=12pt}

     style(column)={font_face="Calibri" font_size=10pt background=white};

cynthia

Message was edited by: Cynthia Zender The screenshot shows how my output looks when created with SAS 9.3 (making the header background white) and opened with Excel 2010. The version of TAGSETS.EXCELXP I am using is: NOTE: This is the Excel XP tagset (Compatible with SAS 9.1.3 and above, v1.122, 01/04/2011).

[Mike_Davis]

Thank you !

I attached another picture here,

After use the code to generate the excel file,I have to open it in excel and I need to change the background color to no fill(please see the picture I attached below).

Is there any way to create the same excel file in SAS instead of changing  in excel manually?

Thanks

Mike

[Cynthia_sas]

Hi:

  I'm baffled, as you can see from my screen shot, I do have interior table lines when I open the output using Excel 2010. I did NOT use the No Fill option, at all. The only recommendation I can make is that you work with Tech Support to figure out what the difference is between how you're creating your output (ExcelXP version, SAS version, Microsoft Office version) and what seems to be happening with the most current versions of XP, SAS and Office.

cynthia

[Mike_Davis]

Hi, I attached a more clear picture,This is a screen shoot of excel file create by the SAS code.

'Before' part  are the orginal output file.

'After ' part are the part used with no fill under excel(which are what I need to be generated directly by SAS).

Thanks

Mike

[Cynthia_sas]

Hi:

  I still think you should work with Tech Support. I think it might be possible to do some of what you want with a style template change, however, you might also need to update your version of TAGSETS.EXCELXP, first. http://support.sas.com/kb/38/282.html (talks about grid lines) However, I did NOT have to use a custom style template to get gridlines when I used EXCELXP. So I really think that Tech Support is your best bet for additional help.

  I do remember that in the early days of TAGSETS.EXCELXP, it was possible to get a gray background to the sheet and no interior table lines, but as you see in my screenshot, when I use version  v1.122 of the EXCELXP tagset and SAS 9.3 and Excel 2010, I don't have a gray background and I do have interior lines.

  Tech Support can work with you to figure out the solution.

cynthia