topic Re: Showing numbers in millions (tagattr) in SAS Programming

Showing numbers in millions (tagattr)

Sanjay_M — Mon, 18 Feb 2019 14:56:37 GMT

I am using ODS tagsets.excelxp with the below code to show the output in excel. I'm using this format to show the numbers in millions.

Example:

9416016 as 9

-1158933 as -1

How can I show the below positive and negative numbers that are less than million as ZERO.

-160231 should be shown as 0

160231 should be shown as 0

call define("&&allperiod&i", 'style', 'style={background=&colour4 width=1 tagattr="format:#,###,,;-#,###,,;-"}');

Re: Showing numbers in millions (tagattr)

RW9 — Mon, 18 Feb 2019 15:27:39 GMT

Thats not a tagset issue. If you want to display data differently, then either correct the data in a datastep before reporting, i.e.

if value < 1000000 then value=0;

Or apply a format to the variable which has something like;

proc format;
  value r
    low-1000000="0";
run;
data want;
  set have;
  format value r.;
run;

Re: Showing numbers in millions (tagattr)

Sanjay_M — Tue, 19 Feb 2019 09:53:37 GMT

In excel if you type -160230 in a cell and apply custom format #,##0,, the value shows as zero.

the display value is shown as zero and for information when you enter the cell it shows the actual value in the formula bar.

I am trying to replicate this.

Re: Showing numbers in millions (tagattr)

RW9 — Tue, 19 Feb 2019 10:08:03 GMT

Yes, but that is something which Excel does. Excel is not SAS. When you output from ods tagsets.excelxp what you are actually creating is a text file, which contains your data in XML form, with tags and such like which Excel will read and interpret - this is the key word here = interpret. Excel system is of its own, nothing whatsoever to do with SAS, so how it reads that text file and understands it is its own process. If you want 0 to be displayed for numbers less than a certain value, use one of the two methods given. If you want to use Excel and some Excel specific functions, then invest in learning Excel and VBA. The tagattr, is just more text added to the file which tries to indicate to Excel what to do with the data, if Excel chooses not to do this or ignores it, or does something else, that is really up to it (and another good reason not to use for anything in the first place).