DATA Step, Macro, Functions and more

ods tagsets.excelxp Help with comma formats on numbers

Accepted Solution Solved
Reply
Regular Contributor
Posts: 212
Accepted Solution

ods tagsets.excelxp Help with comma formats on numbers

[ Edited ]

I'm stuck trying to get commas on every 3rd digit of my numbers. Neither the Thousands_Separator option or the Format statement seem to have any affect on the formatting.  Any suggestions would be appreciated.

 

ods tagsets.excelxp file="c:\users\ssbuechl\FinalData.xls" style=statistical options(embedded_titles='yes' orientation='landscape' gridlines='yes' THOUSANDS_SEPARATOR=',');

proc print noobs data=FinalData; format Rule_Order best6. total comma10. _: comma10. null comma10.; var rule_order score_impacting rule_nm total _: null; run; ods tagsets.excelxp close;

 

RULE_ORDER	SCORE_IMPACTING	   RULE_NM      TOTAL	_04_11_2016	_04_09_2016	_04_08_2016	_04_07_2016	_04_06_2016	_04_05_2016
999.3	Y	TOTAL BIDS VOLUME SAMPLED	54504	17	        124	        143	        222	         493	        1092
337	N	PREP TYPE CODE MISMATCH	15588	4	90	        110	        184	        396	         908

 


Accepted Solutions
Solution
‎04-13-2016 12:38 PM
Regular Contributor
Posts: 212

Re: ods tagsets.excelxp Help with comma formats on numbers

Looks like:

 

var total / style={tagattr="format:###,###,###"};

 

Thanks for the help!

View solution in original post


All Replies
Super User
Super User
Posts: 7,413

Re: ods tagsets.excelxp Help with comma formats on numbers

Applying a SAS format makes no difference.  Excel is interpreting the XML that you create when it opens it - it is at that point of Excel parsing the XML that it decides what your data is and how to display it - it is a "feature" of Excel and one good reason not to use it for any purpose!  However if you have to then you have to add in Excel style references to the XML that is generated.  You do this by putting in style{} commands in your report/print - as you would to alter any output appearance.  Here is a link to several examples:

http://support.sas.com/resources/papers/proceedings10/031-2010.pdf

 

And example:

proc print data=buy;
 format datetime e8601dt.;
 var datetime / style(data)={tagattr='type:DateTime format:YYYY-MM-DD'};
 var date;
 var amount;
run;

The tagattr statement tells the tagset to include that text in the XML.  Then when Excel reads the XML it sees a format statement it recoginses and formats the data appropriately.

Super User
Posts: 17,899

Re: ods tagsets.excelxp Help with comma formats on numbers

Here's an index to options for the Tagsets destination.

http://www.sas.com/events/cm/867226/ExcelXPPaperIndex.pdf

 

You'll see the entry for Excel format:comma and then the corresponding papers and pages that have relevant examples.

You could also search here, since it's an FAQ

 

Note that if you're on SAS 9.4 you can use ODS Excel instead of tagsets.

Super User
Posts: 17,899

Re: ods tagsets.excelxp Help with comma formats on numbers

Here's an index to options for the Tagsets destination.

http://www.sas.com/events/cm/867226/ExcelXPPaperIndex.pdf

 

You'll see the entry for Excel format:comma and then the corresponding papers and pages that have relevant examples.

You could also search here, since it's an FAQ

 

Note that if you're on SAS 9.4 you can use ODS Excel instead of tagsets.

Regular Contributor
Posts: 212

Re: ods tagsets.excelxp Help with comma formats on numbers

Weird, my output changed from a number to this:

 

RULE_ORDER	   SCORE_IMPACTING	RULE_NM                               TOTAL
999.3                  Y                TOTAL BIDS VOLUME SAMPLED             co03a

 

Do I have the correct syntax for the Total variable?

 

226  ods tagsets.excelxp file="c:\users\ssbuechl\FinalData.xml" style=statistical options(embedded_titles='yes'
226! orientation='landscape' gridlines='yes' THOUSANDS_SEPARATOR=',');
NOTE: Writing TAGSETS.EXCELXP Body file: c:\users\ssbuechl\FinalData.xml
NOTE: This is the Excel XP tagset (Compatible with SAS 9.1.3 and above, v1.122, 01/04/2011). Add options(doc='help') to the
ods statement for more information.
227  proc print noobs data=FinalData;
228  * format Rule_Order best6. total comma10. _: comma10. null comma10.;
229  var rule_order score_impacting rule_nm;
230  var total / style={tagattr='format:comma'};
231  var _: null;
232  run;
Super User
Posts: 17,899

Re: ods tagsets.excelxp Help with comma formats on numbers

No you don't have the comma format properly specified. See how it was specified in an example. I'd have to look it up to tell you, so you might as well do that Smiley Wink

Solution
‎04-13-2016 12:38 PM
Regular Contributor
Posts: 212

Re: ods tagsets.excelxp Help with comma formats on numbers

Looks like:

 

var total / style={tagattr="format:###,###,###"};

 

Thanks for the help!

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 299 views
  • 2 likes
  • 3 in conversation