ods tagsets.excelxp Help with comma formats on numbers

[buechler66]

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

[RW9]

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.

[Reeza]

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.

[Reeza]

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.

[buechler66]

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;

[Reeza]

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 😉

[buechler66 · Accepted solution]

Looks like:

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

Thanks for the help!