The SAS Output Delivery System and reporting techniques

Thousands separator in tagset Excelxp

Reply
Contributor
Posts: 33

Thousands separator in tagset Excelxp

Hi

I want to have my figures in Excel to be thousand separated, i can not figure out how to do that. I use the tagset.excelxp. I have tried

ods tagsets.excelxp file="test.xls"
options (frozen_headers='yes' frozen_rowheaders='yes' default_column_width='10' Thousands_separator='yes' doc='help');

and Thousands_separator=' '

Anyone? I wan´t the result in excel to be for example 1 200.
Br
Jenny
SAS Employee
Posts: 95

Re: Thousands separator in tagset Excelxp

Hello Jenny,

The thousands separator option is to indicate what the separator is, so it can be detected by
the tagset. Numbers cannot have anything in them except numbers and a decimal separator as
recognized by your version of Excel. So all formatting that occurs in SAS must be removed before
numbers will be numbers in Excel.

That works fine, except all SAS formatting is lost. The Tagset detects numbers and currency, and percentages, and sets the formats and types for those things. If you don't want the standard Excel number format, then an excel format needs to be applied to that column in your report.

In proc report;

define mynumber / style = {tagattr="format:###,###,###"};

Here is a fancier format for 2 columns in proc print.

var predict actual / style={tagattr='format:$#,##0_);[Red]\($#,##0\)'};

It is not generally necessary to force the type of a variable, but if that comes up, this is how
you would do that.

var weight / style(data)={tagattr="type:Number format:#0.00"};

Tagattr also allows for rotating the text in the cell and hiding the row.

style = {tagattr="rotate:45"};

style = {tagattr="hidden:yes"};
N/A
Posts: 0

Re: Thousands separator in tagset Excelxp

Gee that's good. Dare I imagine that other cell attributes such as borders, font size and colour, and perhaps cell protection can be applied through similar commands?

I missed a great deal by not getting to Geekfest last year, and I regret I shall miss it again this year.

Kind regards

David
SAS Employee
Posts: 95

Re: Thousands separator in tagset Excelxp

Yep. There are all sorts of things you can do. borders, fonts, colors, just like in HTML, RTF and PDF... Within the limitations of Excel of course. Url style links work too so it is possible
to create drill down reports...
Contributor
Posts: 33

Re: Thousands separator in tagset Excelxp

Hi Eric


Thank you for your answer. I still have problems though. The tagattr doesn't seem to work with proc tabulate.

I have tried the styleattr and to make a picture format and apply that together with the thousands_separator. Nothing seem to "bite".

data test;
format var1 8.;
var1 = 123456;
var2 = put(today(),yymmdd10.);
run;

ods path SASUSER.TEMPLAT(UPDATE) SASHELP.TMPLMST(READ) SASHELP.TEMPLAT(READ);

ods tagsets.excelxp file="/sas/sasdata/MAExports/test.xls" style=bwinstyle
options (frozen_headers='yes' frozen_rowheaders='yes' default_column_width='10' Thousands_separator=',' );

options missing = 0;
ods tagsets.excelxp options (sheet_name = "With tagattr");

proc tabulate data = test;
var var1/style={tagattr="format:###,###,###"};
class var2;
table var2,var1;
run;

ods tagsets.excelxp options (sheet_name = "With picture");

proc format;
picture curr low-high='000,000' ;
run;

proc tabulate data = test;
var var1;
class var2;
table var2,var1*f=curr.*sum;
run;

ods tagsets.excelxp close;

br
Jenny
SAS Employee
Posts: 95

Re: Thousands separator in tagset Excelxp

The way you had it, the style over ride would only apply to the header.
This will apply it to the data.

proc tabulate data = test style=={tagattr="format:###,###,###"};
var var1;
class var2;
table var2,var1;
run;

this works for proc print....

proc print data = test;
var var1/style={tagattr="format:###,###,###"};
var var2;
run;
N/A
Posts: 0

Re: Thousands separator in tagset Excelxp

Is there any way to apply the format to a specific variable when specifying multiple variables in a tabulate var statement?

TIA
Super Contributor
Super Contributor
Posts: 3,174

Re: Thousands separator in tagset Excelxp

Gentle suggestion to the OP with these forum submissions - create a new post which you author and paste a link to a prior post, if useful -- suggest not piggy-backing on a 2 year old post from someone else.

SAS Forums Admin: This concern along with other forum posting guidelines really needs to be added as a sticky on the main page, if that is possible?


Scott Barry
SBBWorks, Inc.
SAS Super FREQ
Posts: 8,739

Re: Thousands separator in tagset Excelxp

Hi:
In the future, it really would be better to start an new post and then refer back to this post.

For TABULATE purposes, the VAR statement STYLE= override only impacts the variable HEADER cells. To impact the calculated statistics for one variable differently from another variable, you would need to use the STYLE= override in the table statement.

An example of this is shown here:
http://support.sas.com/kb/25/410.html

Note the way that the TABLE statement has
[pre]
var * {style={attribute=value}}
OR you could have
var * stat*{style={attribute=value}}
[/pre]

and your TAGATTR attribute would go inside the curly braces with the Excel format in quotes as the value.

cynthia
Ask a Question
Discussion stats
  • 8 replies
  • 1974 views
  • 0 likes
  • 5 in conversation