topic Re: Outputting Percent. format in ODS EXCEL in ODS and Base Reporting

Outputting Percent. format in ODS EXCEL

Haris — Fri, 14 Aug 2015 17:08:46 GMT

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;

Re: Outputting Percent. format in ODS EXCEL

Ksharp — Sat, 15 Aug 2015 12:38:34 GMT

Transform it into CHARACTER type

Re: Outputting Percent. format in ODS EXCEL

Haris — Sat, 15 Aug 2015 14:41:57 GMT

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

Re: Outputting Percent. format in ODS EXCEL

Ksharp — Sun, 16 Aug 2015 03:34:21 GMT

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

Re: Outputting Percent. format in ODS EXCEL

SROMAA — Wed, 19 Aug 2015 23:24:36 GMT

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;

Re: Outputting Percent. format in ODS EXCEL

SASKiwi — Thu, 20 Aug 2015 01:57:46 GMT

Try this as a DEFINE column in PROC REPORT:

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

Re: Outputting Percent. format in ODS EXCEL

Haris — Thu, 20 Aug 2015 14:56:09 GMT

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.

Re: Outputting Percent. format in ODS EXCEL

SROMAA — Thu, 20 Aug 2015 15:57:38 GMT

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.

Re: Outputting Percent. format in ODS EXCEL

SROMAA — Thu, 20 Aug 2015 17:58:55 GMT

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;

Re: Outputting Percent. format in ODS EXCEL

Haris — Thu, 20 Aug 2015 19:12:44 GMT

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.

Re: Outputting Percent. format in ODS EXCEL

SASKiwi — Thu, 20 Aug 2015 19:49:05 GMT

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

Re: Outputting Percent. format in ODS EXCEL

Haris — Fri, 21 Aug 2015 15:53:50 GMT

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.

Re: Outputting Percent. format in ODS EXCEL

SROMAA — Fri, 21 Aug 2015 16:06:40 GMT

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

Re: Outputting Percent. format in ODS EXCEL

Vamongeau — Fri, 21 Aug 2015 18:23:33 GMT

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.

Re: Outputting Percent. format in ODS EXCEL

Haris — Thu, 27 Aug 2015 14:15:27 GMT

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

Re: Outputting Percent. format in ODS EXCEL

data_null__ — Thu, 27 Aug 2015 15:05:13 GMT

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.

Re: Outputting Percent. format in ODS EXCEL

Haris — Thu, 27 Aug 2015 15:23:06 GMT

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!

Re: Outputting Percent. format in ODS EXCEL

data_null__ — Thu, 27 Aug 2015 15:46:41 GMT

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

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

Re: Outputting Percent. format in ODS EXCEL

data_null__ — Thu, 27 Aug 2015 15:57:05 GMT

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

Re: Outputting Percent. format in ODS EXCEL

davidbruckner — Thu, 27 Aug 2015 18:56:14 GMT

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;