The SAS Output Delivery System and reporting techniques

Format a cell in excel as number by ods excelXP tagsets

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 17
Accepted Solution

Format a cell in excel as number by ods excelXP tagsets

Hi ,

I am displaying the Number variable in a dataset to excel having properties in cell as number.(Using excelXP tagsets for doing this)

But by using the tagattr='format:#,##' but i am getting the column properties in excel as Custom and zero value in the dataset is getting empty value in excel

Anybody help me to get the solution for the above said two problem.

Thanks

Sylas.J


Accepted Solutions
Solution
‎02-26-2013 10:59 PM
SAS Super FREQ
Posts: 8,641

Re: Format a cell in excel as number by ods excelXP tagsets

Hi, I am having a hard time understanding what your data looks like, what your code looks like and without any data or code to play with, it's only a guess as to what you are seeing.

  However, it has been my understanding that if you are not explicitly defining a format for 0 inside TAGATTR, as described in my SGF paper (http://support.sas.com/resources/papers/proceedings11/266-2011.pdf , page 8), then you needed to define your format like this:

#,##0  instead of #,### otherwise, I think the # serves to suppress a zero value.

This is my understanding:

The use of the '#‘ in the Microsoft format for the percent plays a different role than the '0‘ in the format specification. A "#" in an Excel format displays one or more numeric digits, excluding insignificant zeros, and the "0" instructs Excel to display a single numeric digit, including insignificant zeros.

cynthia

View solution in original post


All Replies
Solution
‎02-26-2013 10:59 PM
SAS Super FREQ
Posts: 8,641

Re: Format a cell in excel as number by ods excelXP tagsets

Hi, I am having a hard time understanding what your data looks like, what your code looks like and without any data or code to play with, it's only a guess as to what you are seeing.

  However, it has been my understanding that if you are not explicitly defining a format for 0 inside TAGATTR, as described in my SGF paper (http://support.sas.com/resources/papers/proceedings11/266-2011.pdf , page 8), then you needed to define your format like this:

#,##0  instead of #,### otherwise, I think the # serves to suppress a zero value.

This is my understanding:

The use of the '#‘ in the Microsoft format for the percent plays a different role than the '0‘ in the format specification. A "#" in an Excel format displays one or more numeric digits, excluding insignificant zeros, and the "0" instructs Excel to display a single numeric digit, including insignificant zeros.

cynthia

Occasional Contributor
Posts: 17

Re: Format a cell in excel as number by ods excelXP tagsets

Hi Cynthia,

The above said solution resolves the issue i faced, thank you very much for your solution.

Regards

Sylas.J

Post a Question
Discussion Stats
  • 2 replies
  • 198 views
  • 1 like
  • 2 in conversation