Percentage format exporting to excel

[DRivas10]

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:

1)

proc report data=X

    column value;

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

run;

2)

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!

[TomKari]

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

[DRivas10]

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?

[ballardw]

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.

[Tom]

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

[DRivas10]

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.

[Tom]

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.

[TomKari]

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

[Reeza]

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.