08-01-2012 07:42 AM
hello, we are using sas excel add-in to show report made with proc report. Report contains numbers that mean amoounts of currency (but our currency is not included in SAS currency formats and we need to show it with space " " as thousands separator and . as decimal separator). is there any way of formating numbers in sas that excel would show them as currency with thousands separated by space " "?
08-01-2012 09:19 AM
Thanks for your answer but i already spent ~6 hours looking for the solution and i definatelly read that link
the thing is that i have numeric values like "10000.99" in my data set and after i generate report in excel using excel sas add-in those values should be shown as "10 000.99". if i create custom farmat using proc format picture "000 000 009.99" i see "10 000.99" in sas output window but excel still interprets these numeric values as numbers ant puts them into standart numeric format without spaces.
Possible solution would be put currency format in sas (as dollar32.2 etc.) ant then excel does what i need but new problem occurs - there shouldnt be $ sign...
The only solutionin my mind after a day of searching is to change type of values into char in sas data set and insert spaces where needed. but there would be any possibility to do filtering data with numeric cases in excel after that..
08-01-2012 09:32 AM
I've only recently started using the Add-in, so this could be off base, but....
As is often the case, sounds like the problem is Excel (sigh).
I think what you want is a way to specify the tagattr= style. Eric Gebhart has a paper on how to do this using tagsets.excelxp:
So the next question would be whether you can also do this using the (default) tagsets.sasreport12 (I'm not sure).
Or I guess you could try changing your report to be generated using tagsets.excelxp instead of tagsets.sasreport12, and see if the add-in is happy consuming that format. It may be, because both tagsets.sasreport12 and tagsets.excelxp are xml. But I haven't tried it.
08-01-2012 10:13 AM
The Excel Add-in for Microsoft Office does not "consume" ODS TAGSETS.EXCELXP flavor of XML -- the only formats that are accepted by the Add-in are: CSV, HTML and SASReport XML. Instead of TAGATTR, which is specific to TAGSETS.EXCELXP, the OP needs to use HTMLSTYLE with HTML-based destinations for the Add-in. Examples of TAGATTR are only useful when you are generating Spreadsheet Markup Language 2003 XML using TAGSETS.EXCELXP. Since the Add-in can receive HTML results, you have to send your format to Excel using the HTMLSTYLE style attribute override.
I have some examples of HTMLSTYLE to change a format in this SAS Global Forum
http://support.sas.com/resources/papers/proceedings11/266-2011.pdf paper. And the code for the paper is available at http://support.sas.com/rnd/papers/ (search for the paper title to find the download link).
08-01-2012 11:35 AM
You're welcome. If you go to the SAS Options in any Add-in client and click the tab for stored process results, you will see what type of SP output the client can consume. So, for example, PPT can only receive SASReport XML; while Word can only receive RTF, HTML and SASReport XML, etc, etc. So each client has a way for the user to change the result type as appropriate for the AMO client.
If you really, really needed TAGSETS.EXCELXP for your reports, you could write a stored process and run it via a web client (like the Portal or the Stored Process Web App) in order to create TAGSETS.EXCELXP flavor of XML. There are quite a few examples of this in the Stored Process forum because when you execute a SP to get this (EXCELXP) type of results, you have to change the content-type header for the SP stream in order to launch the results in an app other than the browser. So, you can use TAGSETS.EXCELXP for a stored process, you are just limited to the type of client app that can receive the results.
Portal/SPWA/custom web service=yes; Add-in clients=no; Web Report Studio = no.
08-01-2012 01:00 PM
Thanks again Cynthia,
That's just the approach I was hoping would work for a SPWA solution. So far I have had success streaming html/rtf/pdf back to SPWA (with the help of your posts and papers in figuring out how to set the appropriate content-type so that the file is opened by the appropriate app), so will take a shot at streaming TAGSETS.EXCELXP this afternoon.