Tabulate, ExcelXP, and negative #'s

[steve_citi]

In tabulate, should tagattr be put on the var statement, or within the TABLE statement (as in f=dollar12.2*s={tagattr= )

Isn't the syntax for the 2 slightly different?
Doesn't one use style={tagattr=format:
While the other uses style={tagattr=\

I really need some clarity here.

Also, can someone help me find the correct tagattr for a number which is black when positive, red and in parethesis when negative. If not red, I'll settle for just a negative sign.

Right now the values which are negative are coming out in Excel as text.

[Cynthia_sas]

Hi:
Look on pages 21, 22, 23 of this paper for information about constructing the type of Microsoft format for positive and negative numbers, etc:
http://support.sas.com/resources/papers/proceedings09/016-2009.pdf

I'll address the TABULATE placement question and leave you to play with the correct format.

To answer your placement question, let's look at a sample PROC TABULATE program that's changing a lot of background colors. I know that you do not want to change background colors, but go with me down this road:
[pre]
ods tagsets.excelxp file='c:\temp\class.xls' style=sasweb;

proc tabulate data=sashelp.class;
class age sex / style={background=pink};
classlev age / style={background=green};
classlev sex / style={background=red};
var height /style={background=cyan};
table age,
sex*height*mean*{style={background=yellow}}
/ box={label='BOX' style={background=cxdddddd}};
keyword mean / style={background=black};
run;

ods tagsets.excelxp close;
[/pre]

What you will see if you run this program is that the output is quite colorful. However, you can determine, from every different color, exactly which STYLE= override impacts which piece of the TABULATE output. So, for example, the style override on the VAR statement only impacts the column HEADER for the HEIGHT variable, just as the style override on the CLASS statement only impacts the HEADERS for the AGE and SEX variables.

If the statistic resulting from the dimension sex*height*mean crossing with the class variable AGE is what you want to impact with a TAGATTR style override (what will be shown in YELLOW in the final output) -- then that (the TABLE statment usage) is the place where you put the TAGATTR reference (once you figure out the right way to construct the TAGATTR string).

You are correct that there is a slight difference in how the STYLE override is specified in the CLASS or VAR statements versus in the TABLE statement. If you think about it, it makes sense... what if, in a SAS dataset, you had a variable called STYLE so how would you distinguish the variable STYLE from the STYLE= in the override part of the specification??? It's no problem in the CLASS or VAR statement, because the ODS STYLE reference comes after a slash. But, in the TABLE statement, you need an extra set of curly braces (or square brackets) to let ODS know that everything within the outer curly braces is a STYLE override.

cynthia

[steve_citi]

Cynthia,

Thank you so much for your reply, especially at this hour.

I have tried the number formats specified in the document,

tagattr='\$###,##0_);[Red]\($#,##0\)' AND
$###,###,##0.00;$-###,###,##0.00


and, now that I understand where to put the tagattr to acheive the desired results, could you please address the syntax of a general tagattr for tabulate (var vs. table)

(I think it may be a syntax problem though I can't seem to find it)

In the var statement, do you use /style={tagattr='\ ???

In the table statement do you use s={tagattr='format:

In fact, I have been trying both, though without any success.

Once again thanks!

[Cynthia_sas]

Hi:
Actually, I don't see TAGATTR as being an appropriate style override for a the column HEADER -- in my example, why would you put a TAGATTR on the column HEADER for HEIGHT???? How could the word 'HEIGHT' use a TAGATTR??

Remember that the VAR statement in PROC PRINT, as shown in that paper is not the same as the VAR statement in PROC TABULATE. The VAR statement in PROC PRINT usually has style(header) or style(data) -- if you look on page 23 of the reference I posted, the paper author uses style(data) for his TAGATTR example:
[pre]
var sales /
style(data)={tagattr="format:[Blue][>=1000]$#,##0;[Red][<0]$#,##0;$#0;[white]@"};
[/pre]

There is NO style(data) equivalent syntax used in PROC TABULATE. If you were going to use that particular TAGATTR override, from page 23, for TABULATE in a TABLE statement, the syntax would be:
[pre]
table age,
style*height*mean*{style={tagattr="format:[Blue][>=1000]$#,##0;[Red][<0]$#,##0;$#0;[white]@"}}
/ box={label='BOX' style={background=cxdddddd}};
[/pre]

Note the extra set of curly braces and the fact that the syntax after the = sign is exactly the same for PROC PRINT and PROC TABULATE. What is different is the placement of the STYLE override in the TABLE statement and the fact that STYLE(DATA) is not required for TABULATE.

(BTW, I believe what was messing up your posting of the TAGATTR string was the < and > in the format specification. For more information on how to "recode" those special symbols for posting and to maintain the indent levels of the code that you post, refer to this forum posting:
http://support.sas.com/forums/thread.jspa?messageID=27609毙 )

cynthia