topic Re: Formatting the numbers in ODS and Base Reporting

Formatting the numbers

sg_sk — Tue, 07 Feb 2017 23:46:08 GMT

Hi,

Can anyone help me with formatting the numbers in millions. I want the numbers outputed to excel and formatted in millions. I used the style = {font_face=Calibri font_size=3 tagattr='format:#,##0.0,,'};The actual number is 32784538360 and after running proc report the number displayed in excel as 32,785.0 and when i click the cell the number present in the cell is 32785000000 (all zeros for the last 6 digits). (the same style works fine for smaller number 65809794, displayed in excel as 65.8 and on clicking the cell it holds the enitre number 65809794)

For the first example(32784538360), I want the number in the excel to be displayed as 32,784.5. and the cell to have 32784538360. Also the totals and subtotals had the same issue.

I can't paste the entire code becaseu of the security issues. Thanks for the help!

Re: Formatting the numbers

Reeza — Tue, 07 Feb 2017 23:58:47 GMT

See the thread and answer here:

https://communities.sas.com/t5/ODS-and-Base-Reporting/ODS-tagsets-Excel-Xp-format-question/td-p/327639

Re: Formatting the numbers

sg_sk — Wed, 08 Feb 2017 00:16:59 GMT

Hi, this format has the same problem i mentioned in my initial post. i.e. it works fine when the number is in millions but for numbers in billions all after decimal becomes zeros.

Has anyone faced this problem before.

Re: Formatting the numbers

ballardw — Wed, 08 Feb 2017 00:36:26 GMT

Provide an example dataset in the form of datastep code and the code used to create the output. https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712 has instructions on turning a SAS dataset into datastep code that you can paste here in a code box using the {i} or attach as a text file.

Likely we only need some examples of that variable and the Proc Print to display that variable but the ODS destination any print options are needed.

Re: Formatting the numbers

Tom — Wed, 08 Feb 2017 01:03:28 GMT

The wrong answer is marked as the solution in the topic linked by Reeza.

The problem with values stored in the Excel is caused by nor telling SAS what format to use to write the numbers.

Run this data step to see the difference in how SAS would write a value like, 1232784538360, using different formats.

data _null_;
  x=1232784538360;
  put (x x x) (best12. / 15. / comma20.);
run;

So in addition to use the STYLE option to specify how Excel should format the value you need to use a SAS FORMAT.

Re: Formatting the numbers

sg_sk — Wed, 08 Feb 2017 20:15:19 GMT

Hi,

Below is my code and attached the output excel (also I updated the excel with how actual numbers appear in SAS).

There are two varaibles one is sales and the other is return- i tried formatting sales using comma20. best12.and 15. there were no difference in the results.

data SALESDATA;

set SALESDATA;

format SALES comma20.;

run;

ODS TAGSETS.EXCELXP OPTIONS ( Sheet_Name = "&sheetname."

Absolute_Column_Width = '20,20,12,12,12,8,8,8,8'

sheet_interval='none'

Orientation = 'portrait'

HIDDEN_COLUMNS='none'

Row_Heights = '20,20,20,20,20,20,20'

zoom='85'

Center_Horizontal = 'yes'

);

proc report data=cmd.SALESDATA spanrows nowd style(header)={font_weight=bold };

column source product sales return ;

define source / group ' ' style(column)=Header{vjust=middle just=left font_face=Calibri font_size=3 }

define product / group 'Product' style={font_face=Calibri font_size=3 } ;

define sales /display analysis sum style(column)={font_face=Calibri font_size=3 tagattr='format:#,##0.0,,;-#,##0.0,,;-'};

define return /display 'Outstanding' analysis sum style(column)={font_face=Calibri font_size=3 tagattr='format:#,##0.0,,;-#,##0.0,,;-'};

RUN;

Note in the excel, the number in millions (8 digits) are displayed correct but the numbers in billions (11 digits) gets zero after decimal places.

Re: Formatting the numbers

sg_sk — Wed, 08 Feb 2017 20:36:37 GMT

Hi,

solved it by adding the format in define statement.

define sales/display analysis sum format=comma18. style(column)={font_face=Calibri font_size=3 tagattr='format:#,##0.0,,;-#,##0.0,,;-'};