Outputting Percent. format in ODS EXCEL

[Haris]

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;

[Ksharp]

Transform it into CHARACTER type

[Haris]

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...

[Ksharp]

An alternative way is defining a special style :  style={ tagattr='format:@'  }

[SROMAA]

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;

[Haris]

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.   

[SROMAA]

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.

[SROMAA]

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;

[Haris]

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.

[SROMAA]

I'm using SAS 9.4M1.  This is nuts!  The only other thing I know to try is using the tagattr suggested by SASKiwi.

[SASKiwi]

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: ########"%"_);(########"%")

[Haris]

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.

[SASKiwi]

Try this as a DEFINE column in PROC REPORT:

define MyPercent pctsum "My Percent" format = percent10.1 style=[tagattr="format:#,##0.0%"];

[Vamongeau]

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.

[Haris]

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...