Outputting Percent. format in ODS EXCEL

[data_null__]

If you round(pctwt,.0001) everything will be fine.  Excel is easily confused by data.  Of course the SQL will still be character in numeric field.

[Haris]

data_null_, the only thing rounding fixes, well sort of, is the PRINT tab where all the numbers are now rounded to full percent8.0 format.  Made no difference in the REPORT tab.

Why you say 'of course' about SQL and character output?  It outputs seemingly numeric #######0 and #######0.0 formats for Age Height and Weight so why not a numeric %.  Or are the former character formats?  In any way, the Age Height and Weight columns sort as numbers, which is the bottom line (for me at least).  The percent column does not--it sorts the percent as character strings and does not recognize that these are numbers not text strings.  VERY BAD!

[data_null__]

Show your code.  I don't think you did it right.

You may be assuming that SAS is doing something it is not.

[data_null__]

You also need to use a larger W on percent format.  I used 12.2 and rounded to .0001

[dknochen]

I use SAS 9.4 M1, and Excel Office Pro + 2013.

I have a computed variable in my dataset with a value of 0.72578614089372, and it has a format=percent8.2.

Running proc report with an analysis statement for that variable using format=8.2 style=[tagattr="format:#,##0.00%"] that is giving me consistent results (eg. "72.58%" when the value is 72.578614089372).

Thank you for sharing the style above, I'm fine with this method.

[davidbruckner]

NOTE: I am using SAS 9.4 M2 with Office 2013

Taking your original code, I modified your Proc Report syntax to the standard way I use the Procedure and I get the results I believe you are looking for on the variable MyPctWt?

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;

   columns Name Sex Age Height Weight PctWt MyPctWt;

   define PctWt / display ;

   define height / analysis sum;

   define weight / analysis sum;

   define MyPctWt / computed  style=[tagattr="format:#,###.00%"];

   compute MyPctWt;

      MyPctWt=height.sum /weight.sum;

   endcomp;

run;

ods excel close;

[Haris]

Yup, that works, DavidBruckner.  And you don't need to compute a separate variable but can apply a number format to the original variable like this:

define PctWt

/ display format=8.4 style=[tagattr="format:#,###.00%"];

The only trouble is that the display in SAS is now not right--it shows percentile not percent.

[Ron_n]

you may use

define PctWt/ display style(column)={tagattr='format:##0.00%'};