BookmarkSubscribeRSS Feed
Jennys
Calcite | Level 5
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
8 REPLIES 8
Eric_SAS
SAS Employee
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"};
deleted_user
Not applicable
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
Eric_SAS
SAS Employee
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...
Jennys
Calcite | Level 5
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
Eric_SAS
SAS Employee
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;
deleted_user
Not applicable
Is there any way to apply the format to a specific variable when specifying multiple variables in a tabulate var statement?

TIA
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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.
Cynthia_sas
SAS Super FREQ
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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 3573 views
  • 0 likes
  • 5 in conversation