BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
sg_sk
Calcite | Level 5

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

6 REPLIES 6
sg_sk
Calcite | Level 5

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.

ballardw
Super User

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.

Tom
Super User Tom
Super User

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.

sg_sk
Calcite | Level 5

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.

sg_sk
Calcite | Level 5

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

 

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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