topic Re: where to find format attribute in ODS and Base Reporting

where to find format attribute

Inp — Thu, 01 Nov 2007 18:51:35 GMT

Hi
To format the data in ODS ExcelXP, we use tagattr for example
style = {tagattr='format : currency'}

My question is where can I go and find the other tagattr values to format the data and it's description. I found some of them like the one I wriiten as an example. I want to know the all available format attribute inorder to format data with leading zeros , comma etc...

Thanks

Inp

Re: where to find format attribute

Cynthia_sas — Thu, 01 Nov 2007 19:34:32 GMT

Hi:
These are Microsoft formats. So you'd find them in the Microsoft documentation. One way I find them is to reverse engineer them -- either make a small spreadsheet and then format the number the way I want and then save the file as either HTML or XML and then open the file with Notepad. Or, I use this paper as a reference (see the table of frequently used formats on Page 7).
http://www2.sas.com/proceedings/sugi28/052-28.pdf

cynthia

Re: where to find format attribute

deleted_user — Thu, 23 Apr 2009 14:09:03 GMT

Cynthia

I have reverse engineered as you said and got this as my format:

What part of this should I extract and put into my tagattr?

Thanks
Drew

Re: where to find format attribute

Cynthia_sas — Thu, 23 Apr 2009 14:45:52 GMT

Hi:
That is truly the most complicated Microsoft format I have ever seen! My guess (and it's only a guess) is that it would be everything within the double quotes would move into the tagattr specification.
[pre]
"_-[$$-409]* #,##0_ ;_-[$$-409]* \-#,##0\ ;_-[$$-409]* "-"_ ;_-@_ "

either:
tagattr="_-[$$-409]* #,##0_ ;_-[$$-409]* \-#,##0\ ;_-[$$-409]* "-"_ ;_-@_ "
(without any other specification, the string is assumed to be a format)
or explicitly using Format:

tagattr="Format:_-[$$-409]* #,##0_ ;_-[$$-409]* \-#,##0\ ;_-[$$-409]* "-"_ ;_-@_ "
[/pre]

My Microsoft format explorations have not led me to anything this complicated. You may wish to work with Tech Support on this questions.

cynthia

Re: where to find format attribute

deleted_user — Thu, 23 Apr 2009 15:21:23 GMT

Thanks Cynthia

I also thought that this was a little complex - I am simply trying to put the '$' and '€' formats onto my data - my 'currnecy' format is the '£' and I need to use a macro to repeat the report in all 3 currencies. Putting the string into a macro variable causes all kinds of problems because of things like the '#' character.

Any help would be appreciated.

Thanks.
Drew

Re: where to find format attribute

Cynthia_sas — Thu, 23 Apr 2009 18:26:49 GMT

Hi:
I guess my thought would be to avoid a macro VARIABLE for the string and instead use macro conditional logic to build the whole procedure statement. This would mean having a macro program. Or you could work with Tech Support to see how macro quoting functions might be used effectively in this situation. I also thought there were some good suggestions here:
http://support.sas.com/forums/message.jspa?messageID=20119#20119

You could also try to simplify the format and see whether there is a simpler version of the format that would work for you. I don't really use Excel formats enough to understand how the format is being constructed from this string.

An simple example of conditionally specifying a whole procedure statement is shown below using ODS HTML, which is roughly the equivalent of what you want to do.

cynthia
[pre]
%macro dostmt(sel=X);

proc print data=sashelp.class;
title "The selection value is: &sel";
%if %upcase(&sel) = P %then %do;
var name / style(header) = {background=purple};
%end;
%else %if %upcase(&sel) = D %then %do;
var name / style(header) = {background=pink};
%end;
%else %do;
var name / style(header) = {background=cxdddddd};
%end;
var age height;
run;
%mend dostmt;

ods html file='sel_p.html' style=sasweb;
%dostmt(sel=P);
ods html close;

ods html file='sel_d.html' style=sasweb;
%dostmt(sel=D);
ods html close;

ods html file='sel_o.html' style=sasweb;
%dostmt(sel=o);
ods html close;
[/pre]

Re: where to find format attribute

deleted_user — Fri, 24 Apr 2009 07:18:27 GMT

Thanks Cynthia, you've been awesome as always!