topic Re: Excel changing decimal value, rounding perhaps? in ODS and Base Reporting

Excel changing decimal value, rounding perhaps?

LisaSAS — Mon, 19 Oct 2015 15:35:40 GMT

I have a query created where my output data table is showing 1089668.06 in the field - which is what I want. However, when I do a PROC REPORT to Excel, the value changes to 1089668.1. I'm not doing anything fancy in my PROC REPORT, mainly just want it to go to multiple excel sheets within one workbook. A portion of my code is below..

ods _all_ close;

ods listing close;

ods tagsets.ExcelXP path = 'network path'

file = 'FileName.xls' style=MINIMAL;

ods tagsets.ExcelXP

options(sheet_name='WorksheetName'

frozen_headers="yes"

absolute_column_width='8.75, 6.25, 10.57, 7.43, 15, 8.14, 30.86, 10.57'

autofit_height='yes');

PROC REPORT DATA=WORK.TblFinal (WHERE = (where clause))

STYLE(REPORT) = [BACKGROUND=WHITE FOREGROUND=BLACK]

STYLE(COLUMN) = [BACKGROUND=WHITE FOREGROUND=BLACK FONT_SIZE=2]

STYLE(HEADER) = [BACKGROUND=WHITE FOREGROUND=BLUE BOLD FONT_SIZE=2];

COLUMNS SKU Channel ShipToCountry Territory ForeignSalesValue Units Description RetailerCode

;

DEFINE SKU / 'SKU' DISPLAY ;

DEFINE Channel / 'Channel' DISPLAY;

DEFINE ShipToCountry / 'Ship To Country' DISPLAY;

DEFINE Territory / 'Territory' DISPLAY;

DEFINE ForeignSalesValue / 'Foreign Sales Value' DISPLAY;

/*style={tagattr='format:#,###,##0.00'} previously I had this, so I took it out, but it did not seem to matter*/

DEFINE Units / 'Units' DISPLAY style={tagattr='format:#,##0'};

DEFINE Description / 'Descrption' DISPLAY;

DEFINE RetailerCode / 'Retailer Code' DISPLAY;

;RUN;quit;

Re: Excel changing decimal value, rounding perhaps?

Cynthia_sas — Mon, 19 Oct 2015 17:53:12 GMT

Hi:

I am not sure how you say that TAGATTR does not make a difference. Perhaps you need to work with Tech Support. If you run the attached code, you should see that the SAS format is used in the ODS HTML output, but that in the ExcelXP output, only when you use TAGATTR do you have control over the number of decimal places. Here's an example of the output produced by the simplified version of your program listed below.

cynthia

Shows that TAGATTR is needed:

SAS code:

ods _all_ close;

ods html file='c:\temp\test_decimal.html';

ods tagsets.ExcelXP path = 'c:\temp'

file = 'test_decimal.xml' style=MINIMAL;

options(sheet_name='test_decimal');

PROC REPORT DATA=sashelp.class nowd

STYLE(REPORT) = [BACKGROUND=WHITE FOREGROUND=BLACK]

STYLE(COLUMN) = [BACKGROUND=WHITE FOREGROUND=BLACK FONT_SIZE=2]

STYLE(HEADER) = [BACKGROUND=WHITE FOREGROUND=BLUE BOLD FONT_SIZE=2];

COLUMNS name age sex height weight;

define name / order;

define age / f=z6.2 'age with z6.2 SAS format';

DEFINE height / 'height with tagattr' DISPLAY f=6.2

style={tagattr='format:##0.00'} ;

DEFINE weight / 'weight without tagattr' DISPLAY f=6.2;

RUN;

ods _all_ close;

Re: Excel changing decimal value, rounding perhaps?

LisaSAS — Mon, 19 Oct 2015 18:55:41 GMT

Cynthia,

Thank you for your quick response. When I stated the TAGATTR did not make a difference, based on how I was using it, it was not. However, once I looked at your code, I realized I did not have the f=10.2 along with it, which was needed.

Here's what I had, which did not work

DEFINE ForeignSalesValue / 'Foreign Sales Value' DISPLAY

style={tagattr='format:#,##0.00'};

Here's what I changed it to, which did work!

DEFINE ForeignSalesValue / 'Foreign Sales Value' DISPLAY f=10.2 style={tagattr='format:##0.00'};

Again, thank you!

...I've been trying to find exactly what the f= is, would you have anything to point me in the right direction?

Re: Excel changing decimal value, rounding perhaps?

Cynthia_sas — Mon, 19 Oct 2015 20:04:26 GMT

Hi:
F= is an abbreviation for FORMAT=. So if you know what a SAS format is from taking Programming 1, then you can refresh your info about formats in the Programming 1 class. Or look at the DEFINE statement documentation for how F=/FORMAT= is used.

Or, look in the documentation for the section on FORMATS used in SAS.

cynthia