Your SAS programs, embedded in web apps and elsewhere

Preserve leading zeroes in STP called by Add-In

Accepted Solution Solved
Reply
PROC Star
Posts: 1,322
Accepted Solution

Preserve leading zeroes in STP called by Add-In

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.


Accepted Solutions
Solution
‎10-21-2012 10:14 PM
SAS Super FREQ
Posts: 8,866

Re: Preserve leading zeroes in STP called by Add-In

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

View solution in original post


All Replies
Frequent Contributor
Posts: 129

Re: Preserve leading zeroes in STP called by Add-In


Quentin,

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

Larry

PROC Star
Posts: 1,322

Re: Preserve leading zeroes in STP called by Add-In

Posted in reply to LarryWorley

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.

Solution
‎10-21-2012 10:14 PM
SAS Super FREQ
Posts: 8,866

Re: Preserve leading zeroes in STP called by Add-In

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

PROC Star
Posts: 1,322

Re: Preserve leading zeroes in STP called by Add-In

Posted in reply to Cynthia_sas

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.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 562 views
  • 0 likes
  • 3 in conversation