- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
I want to output a percent-formatted variable to Excel. Very excited about the capabilities of the new ODS EXCEL but it misbehaves on me.
I used three methods to print a percetn8.2 formatted variable: PRINT, SQL, REPORT. All three produce different results.
PRINT is the worst--it truncates the number to two decimals, outputs some of the numbers as text with '%' and others as numbers without '%' without truncation but rounded to two decimals.
SQL outputs the variable properly, truncates all numbers to two digits but output is in text column rather than number.
REPORT is the best -- outputs number with '%', does not truncate the number, but I can't get it to show how any decimals it rounds to a full number.
Here's the code and I am running SAS 9,4 M2:
data Class; set SASHelp.Class;
PctWt = Height/Weight;
format PctWt percent8.2;
run;
ods excel file="d:\Temp\Test.xlsx" style=Printer;
ods excel options(sheet_name='Print');
proc print data=Class;
run;
ods excel options(sheet_name='SQL');
proc sql;
select *
from Class;
quit;
ods excel options(sheet_name='Report');
proc report data=Class;
run;
ods excel close;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Transform it into CHARACTER type
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Really? To move percent-formatted columns from SAS to Excel you recommend that I turn them to text? I hope that's not the answer! PROC SQL already does that in the example above...
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
An alternative way is defining a special style : style={ tagattr='format:@' }
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
I use PROC REPORT and specify the format on the define line. ExcelXP was touchy and didn't like data to be pre-formatted so you may need to remove the format statement in the data step to make ODS Excel apply the intended format. In your case it would look like this:
proc report data=class;
define pctwt / display f=percent8.2;
run;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Like the idea of not using the format in the dataset and formatting in at the output stage. Unfortunately, makes no difference in output--Excel still has shows percent8.0 rather than 8.2 that was requested by PROC REPORT.
I hope this is a bug that SAS will work out by the time ODS EXCEL graduates from testing stage.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Did you remove the format from the data step? I tested with the format in PROC REPORT only using both PERCENT8.2 and PERCENT8.3 and the XLSX file created by ODS EXCEL contained the correct format both times. Another issue may be the version of Excel used to open the XLSX file. I am using Excel 2013.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Specifically, the following code works fine:
ods excel file="%sysfunc(pathname(WORK))\test.xlsx"
options(sheet_name='Report');
proc report data=SASHEP.CLASS;
columns height weight pctwt;
define height / display order=internal;
define weight / display;
define pctwt / computed f=percent8.1;
compute pctwt;
pctwt=height/weight;
endcomp;
run;
ods excel close;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
SROMAA, I have Excel 2010. What version of SAS are you using? I still get percentages rounded to a full number or percent8.0 format, not percent8.1.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
I'm using SAS 9.4M1. This is nuts! The only other thing I know to try is using the tagattr suggested by SASKiwi.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
BTW, my version works OK in the Excelxp tagset, so if you are unable to get it working using ODS EXCEL you could try EXCELXP.
SROMA's program works fine for me using SAS 9.4 M2 and Excel 2010. When I highlight the percent column in Excel, it tells me a custom format has been applied, which looks like this: ########"%"_);(########"%")
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
SASKiwi, I get the same but this is not what the program asks. We should be getting #######.#"%"_);(#######.#"%") not ########"%"_);(########"%"). Now I am stuck with correcting the format manually and I have hundreds of columns.
The current program is using ExcelXP tagset but we need to include graphs in the output which ExcelXP does not do. Hence the need to migrate from ODS Tagsets.ExcelXP to ODS Excel.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Try this as a DEFINE column in PROC REPORT:
define MyPercent pctsum "My Percent" format = percent10.1 style=[tagattr="format:#,##0.0%"];
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
I don't use proc report, but with proc tabulate, I use the following formats.
picture pctfmt(round) low-high= "009.9%";
picture multi(round) low-high= "009.9%" (multiplier=1000) ;
I then apply them to the variable when output via tabulate.
Don't know if this will help you with proc excel.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Is anyone from SAS tech support monitoring these threads? I should probably open a ticket with this issue or at least let them know is some way...