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!
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
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?
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.
How are you directing the output of PROC REPORT to Excel?
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.
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.
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
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: