BookmarkSubscribeRSS Feed
EdvinasS
Calcite | Level 5

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 " "?

7 REPLIES 7
EdvinasS
Calcite | Level 5

Thanks for your answer but i already spent ~6 hours looking for the solution and i definatelly read that link Smiley Happy

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..

Quentin
Super User

Hi,

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:

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

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.

Good luck,

--Q.

BASUG is hosting free webinars Next up: Jane Eslinger presenting PROC REPORT and the ODS EXCEL destination on Mar 27 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.
Cynthia_sas
SAS Super FREQ

Hi:

  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).

cynthia

Quentin
Super User

Thanks much Cynthia,

That makes sense, and you saved me from trying (and failing) to get the add-in to consume tagesets.excelxp.

--Q.

BASUG is hosting free webinars Next up: Jane Eslinger presenting PROC REPORT and the ODS EXCEL destination on Mar 27 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.
Cynthia_sas
SAS Super FREQ

Q--

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.

cynthia

Quentin
Super User

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.

--Q.

BASUG is hosting free webinars Next up: Jane Eslinger presenting PROC REPORT and the ODS EXCEL destination on Mar 27 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.

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
  • 7 replies
  • 1296 views
  • 0 likes
  • 4 in conversation