08-14-2015 01:08 PM
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;
ods excel file="d:\Temp\Test.xlsx" style=Printer;
ods excel options(sheet_name='Print');
proc print data=Class;
ods excel options(sheet_name='SQL');
ods excel options(sheet_name='Report');
proc report data=Class;
ods excel close;
08-15-2015 10:41 AM
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...
08-19-2015 07:24 PM
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;
08-20-2015 10:56 AM
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.
08-20-2015 11:57 AM
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.
08-20-2015 01:58 PM
Specifically, the following code works fine:
ods excel file="%sysfunc(pathname(WORK))\test.xlsx"
proc report data=SASHEP.CLASS;
columns height weight pctwt;
define height / display order=internal;
define weight / display;
define pctwt / computed f=percent8.1;
ods excel close;
08-20-2015 03:49 PM
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: ########"%"_);(########"%")
08-21-2015 11:53 AM
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.
08-21-2015 02:23 PM
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.