The SAS Output Delivery System and reporting techniques

Formatting percentage field in Proc Print

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 19
Accepted Solution

Formatting percentage field in Proc Print

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;

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


Accepted Solutions
Solution
‎06-10-2016 09:54 AM
Moderator
Posts: 252

Re: Formatting percentage field in Proc Print

[ Edited ]

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.

View solution in original post


All Replies
Solution
‎06-10-2016 09:54 AM
Moderator
Posts: 252

Re: Formatting percentage field in Proc Print

[ Edited ]

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.

Super User
Posts: 19,874

Re: Formatting percentage field in Proc Print

Posted in reply to AndrewHowell

Best resource for tagset questions is here:

 

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

Occasional Contributor
Posts: 19

Re: Formatting percentage field in Proc Print

Thanks Reeza for the link.

Occasional Contributor
Posts: 19

Re: Formatting percentage field in Proc Print

Posted in reply to AndrewHowell

THANKS! That worked!

Community Manager
Posts: 2,956

Re: Formatting percentage field in Proc Print

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 1410 views
  • 2 likes
  • 4 in conversation