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
Looks like:
var total / style={tagattr="format:###,###,###"};
Thanks for the help!
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.
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.
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.
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;
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 😉
Looks like:
var total / style={tagattr="format:###,###,###"};
Thanks for the help!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.