The SAS Output Delivery System and reporting techniques

Formatting the numbers

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 6
Accepted Solution

Formatting the numbers

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!


Accepted Solutions
Solution
‎02-08-2017 03:49 PM
Super User
Super User
Posts: 6,317

Re: Formatting the numbers

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.

View solution in original post


All Replies
Grand Advisor
Posts: 17,320

Re: Formatting the numbers

Occasional Contributor
Posts: 6

Re: Formatting the numbers

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.

Grand Advisor
Posts: 10,204

Re: Formatting the numbers

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

Solution
‎02-08-2017 03:49 PM
Super User
Super User
Posts: 6,317

Re: Formatting the numbers

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.

Occasional Contributor
Posts: 6

Re: Formatting the numbers

[ Edited ]

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.

Occasional Contributor
Posts: 6

Re: Formatting the numbers

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,,;-'};

 

 

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 201 views
  • 0 likes
  • 4 in conversation