topic Re: export to excel(.xls) file with specified font in SAS Procedures

export to excel(.xls) file with specified font

Mike_Davis — Mon, 09 Jul 2012 20:17:43 GMT

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;

Re: export to excel(.xls) file with specified font

Cynthia_sas — Mon, 09 Jul 2012 20:46:15 GMT

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;

Re: export to excel(.xls) file with specified font

Mike_Davis — Tue, 10 Jul 2012 13:14:17 GMT

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

Re: export to excel(.xls) file with specified font

Ksharp — Wed, 11 Jul 2012 01:53:10 GMT

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

Re: export to excel(.xls) file with specified font

Cynthia_sas — Wed, 11 Jul 2012 02:57:30 GMT

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

Re: export to excel(.xls) file with specified font

Mike_Davis — Wed, 11 Jul 2012 13:44:35 GMT

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

Re: export to excel(.xls) file with specified font

Cynthia_sas — Wed, 11 Jul 2012 13:56:51 GMT

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).

Re: export to excel(.xls) file with specified font

Mike_Davis — Wed, 11 Jul 2012 19:08:12 GMT

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

Re: export to excel(.xls) file with specified font

Cynthia_sas — Wed, 11 Jul 2012 20:12:44 GMT

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

Re: export to excel(.xls) file with specified font

Mike_Davis — Thu, 12 Jul 2012 13:28:36 GMT

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

Re: export to excel(.xls) file with specified font

Cynthia_sas — Thu, 12 Jul 2012 15:09:04 GMT

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