Preserve leading zeroes in STP called by Add-In

[Quentin]

Hi All,

I am working on a stored process that will be called from Excel Add-In, and will return a table to Excel.  The ODS destination is tagsets.sasreport12.

Having a problem where if Excel thinks the value in a cell is numeric, it treats it as such, and does not preserve leading/trailing zeroes.  So a character value of '01.0' in SAS becomes an Excel value of 1.

I have read the very helpful paper, Dont Gamble with Your Output: How to Use Microsoft Formats with ODS, and that describes how to use TAGATTR when destination is tagsets.ExcelXP and HTMLSTYLE for HTML destinations. Is there something similar I could use when the destination is tagsets.SASreport12 ?

I was able to preserve zeroes by prepending a single quote mark to character values, which I guess is a way in Excel to force a value to be text.  But then I get an ugly warning from Excel that I have a number formatted as text.

I suppose I may be better off switching to an HTML destination so that I can use HTMLSYLE? But just wanted to ask, before giving up on SASreport.

Appreciate any suggestions.

Thanks,

-Q.

[LarryWorley]

Quentin,

You may have tried this, but have you tried the TAGATTR construct with tagsets.SASreport12?  If so what were the results?

Larry

[Quentin]

LarryWorley wrote:


Quentin,

You may have tried this, but have you tried the TAGATTR construct with tagsets.SASreport12?  If so what were the results?

Larry

No joy, unfortunately.

[Cynthia_sas · Accepted solution]

Hi:

  Sadly, as you discovered, the TAGATTR and HTMLSTYLE methods only work with ExcelXP and MSOFFICE2K respectively. SASReport XML (what is created with SASreport12) is an internal format that can be rendered by many different client applications. So there is no way to make an impact in this destination.

  Possibly, if you changed your SP results to HTML-based (MSOFFICE2K) and used HTMLSTYLE style overrides, as discussed in my paper, then you might get somewhere with the Add-in for Excel. However, this might make your SP incompatible with client apps like Web Report Studio or PPT. But if you don't mind that, then I think using HTML based results is your best bet. At the current time, the SAS Add-in for Microsoft Office will not "accept" TAGSETS.EXCELXP flavor of XML, so for what you want to do, HTML is the only alternative.

cynthia

[Quentin]

Thanks Cynthia,

I was thinking that might be answer, as if there were a way to do it via SASReport figure it would have been in your excellent paper (or excellent ODS book).  Just wanted to double check before making the jump to an HTML-based destination.  For most of what I have been doing, I've been using SPWA as the client, so incompatibility with WRS is not a problem.

--Q.