The SAS Output Delivery System and reporting techniques

Reporting with Excel SAS ADD-in

Reply
Contributor
Posts: 29

Reporting with Excel SAS ADD-in

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

PROC Star
Posts: 7,364

Re: Reporting with Excel SAS ADD-in

Contributor
Posts: 29

Re: Reporting with Excel SAS ADD-in

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

PROC Star
Posts: 1,236

Re: Reporting with Excel SAS ADD-in

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.

SAS Super FREQ
Posts: 8,744

Re: Reporting with Excel SAS ADD-in

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

PROC Star
Posts: 1,236

Re: Reporting with Excel SAS ADD-in

Thanks much Cynthia,

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

--Q.

SAS Super FREQ
Posts: 8,744

Re: Reporting with Excel SAS ADD-in

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

PROC Star
Posts: 1,236

Re: Reporting with Excel SAS ADD-in

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.

Ask a Question
Discussion stats
  • 7 replies
  • 422 views
  • 0 likes
  • 4 in conversation