Formatting percentage field in Proc Print

[sasmom]

I am sending a table from EG to an excel file using ODS and proc print.

The percentage field in SAS table that is going to the Excel has numbers like below.

94.9%
24.4%
95.5%
7.1%

but when the SAS table gets to an excel file the format shows with two decimal point like below.

94.90%

24.40%

95.50%

I tried percent7.1, percent8.1 but neither works.

Below is my code. I need to know how to fix the issue so in excel the percentage field is one decimal.

Thanks

*********************************************************************************

ods listing close;

ods tagsets.ExcelXP style= mystyle file="excelfile.xls"

options(sheet_name='tab1' autofilter='all' orientation='landscape');

proc print data=table1 noobs split= '/'

style(header) = { font_style=italic font_weight = bold font_face = "calibri" font_size = 8pt just = c}

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

var startdt enddt client_id custid /style(header) ={foreground = white background = #ff6600};

var Target /style(header) = vertical_header {foreground = black background = #ccccff};

var Pct_Touched Pct_Enrolled pct_part /style(header) = vertical_header {foreground = black background = #ccff99};

format Target Pct_Touched Pct_Enrolled pct_part percent8.1;

run;

ods tagsets.ExcelXP close;

*************************************************************************************

[AndrewHowell · Accepted solution]

The format statement won't work here - you will  need to include the Excel formatting options in the ODS style options of your variables.

Try:

var Target /
  style={tagattr='format:0.0%'}
  style(header) = vertical_header {foreground = black background = #ccccff}
  ;
var Pct_Touched Pct_Enrolled pct_part /
  style={tagattr='format:0.0%'}
  style(header) = vertical_header {foreground = black background = #ccff99}
  ;

Note you must have the "style" option before the "style(header)" option..

There are some excellent ODS EXCELXP whitepapers out there:

• ODS ExcelXP: Tag Attr Is It! Using and Understanding the TAGATTR= Style Attribute with the ExcelXP T...
• Don’t Gamble with Your Output: How to Use Microsoft Formats with ODS Cynthia L. Zender, SAS Institut...
• Creating Stylish Multi-Sheet Microsoft Excel Workbooks the Easy Way with SAS® Vincent DelGobbo, SAS ...
• Multi-sheet Workbooks from SAS ® data using the ODS ExcelXP tagset or Another Way to EXCEL using SAS...
• SAS to Excel with ExcelXP Tagset Mahipal Vanam, Kiran Karidi and Sridhar Dodlapati

So it's worth doing a little research to get fully informed (whereas the Discussions can often be a one-off answer to a one-off question).

Best best is to go to Lex Jansen's website and search for ExcelXP.

There's a Focus Area on ExcelXP tagsets in the support.sas.com site.

.. and keep an eye on anything @ChrisHemedinger posts on the subject in his SasDummy blog.

Hope this helps.

[Reeza]

Best resource for tagset questions is here:

http://www.sas.com/events/cm/867226/ExcelXPPaperIndex.pdf

[sasmom]

Thanks Reeza for the link.

[sasmom]

THANKS! That worked!

[ChrisHemedinger]

See also this video that features @Chevell_sas demoing some cool features of ODS EXCEL, including formulas and formatting.