topic Formatting percentage field in Proc Print in ODS and Base Reporting

Formatting percentage field in Proc Print

sasmom — Thu, 09 Jun 2016 23:12:55 GMT

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;

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

Re: Formatting percentage field in Proc Print

AndrewHowell — Fri, 10 Jun 2016 01:25:39 GMT

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:

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.

Re: Formatting percentage field in Proc Print

Reeza — Fri, 10 Jun 2016 01:57:59 GMT

Best resource for tagset questions is here:

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

Re: Formatting percentage field in Proc Print

sasmom — Fri, 10 Jun 2016 13:32:02 GMT

THANKS! That worked!

Re: Formatting percentage field in Proc Print

sasmom — Fri, 10 Jun 2016 13:32:42 GMT

Thanks Reeza for the link.

Re: Formatting percentage field in Proc Print

ChrisHemedinger — Sat, 11 Jun 2016 03:33:15 GMT

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