topic Re: Percentage format exporting to excel in SAS Enterprise Guide

Percentage format exporting to excel

DRivas10 — Thu, 17 Oct 2019 15:16:02 GMT

Dear all,

I have an issue when exporting my SAS tables to excel.

When exporting a decimal value (numerica format in SAS, let's say 0,2328) to excel (ods excel), I have 2 possible options that I'm aware of:

proc report data=X

column value;

define value / display "Value" format=percent10.2;

run;

proc report data=X

column value;

define value / display "Value" format=percentn10.2;

run;

With the first option, visually I get 23% as numeric value that I can later expand to 23,28%.

With the second option, I get 23.28% as a text value that I'd have to change to number by changing the . for the ,

Does anybody know how to get the output in excel visually as 23,28%, as a numeric value?

Thanks in advance!

Re: Percentage format exporting to excel

TomKari — Thu, 17 Oct 2019 15:53:14 GMT

Using one of the localized formats might work. Here's a link to the documentation:

https://support.sas.com/documentation/cdl/en/nlsref/61893/HTML/default/viewer.htm#a002604001.htm

Tom

Re: Percentage format exporting to excel

DRivas10 — Thu, 17 Oct 2019 16:06:05 GMT

Hello Tom,

Thank you for your quick answer.

I get the number separated by comma, but when opening excel I get a flag saying value stuck as text. How is that?

Re: Percentage format exporting to excel

ballardw — Thu, 17 Oct 2019 16:26:09 GMT

Maybe the TAGATTR style attribute will help.

You would have a Style for the variable that might look like:

style={tagattr='format:$#,##0_);[Red]\($#,##0\)'}

Which uses the more Excel like display values. I don't mess with such much so you would have to look in the cell properties for Excel to make the correct string following the format: to display the percenta as desired. The above has two value rules separated by ; to do negative values in red surrounded by parentheses. The bit between the singles quotes is the entire TAGATTR string.

Re: Percentage format exporting to excel

Tom — Thu, 17 Oct 2019 16:29:10 GMT

How are you directing the output of PROC REPORT to Excel?

Re: Percentage format exporting to excel

DRivas10 — Thu, 17 Oct 2019 16:39:55 GMT

If I understand well the question, I believe the answer is ods excel. I set up the code like this:

goptions device=actximg;

Ods excel file = "&route." style=htmlblue

options (sheet interval="none" sheet name="INDEX" embedded_titles='yes');

ods escapechar='~';

****TITLES FOR EACH SHEET*****

proc report mentioned above

ods excel options (sheet interval="none" sheet name="INDEX2" embedded_titles='yes');

****TITLES FOR EACH SHEET*****

different proc report and graphs

ods excel close;

That's how I code it.

Re: Percentage format exporting to excel

Tom — Thu, 17 Oct 2019 17:11:21 GMT

In that case remember that the FORMAT used will only impact how SAS converts your values into text. Not how Excel decides to display the text. You will most likely need to use the TAGATTR style option to tell SAS how to tell Excel how to display the values.

Re: Percentage format exporting to excel

TomKari — Thu, 17 Oct 2019 17:17:04 GMT

I've never tried to use Excel with a different format for thousands and decimal, but I'm wondering is there a way to set Excel to display in that format, and if there is will it fix your problem?

Tom

Re: Percentage format exporting to excel

Reeza — Thu, 17 Oct 2019 20:50:32 GMT

Have you tried a custom format? If it's shown as the value with the 23.28% is it okay if it's a character?

I would create a custom format and use the TAGATTR option and would expect that to work.