BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.

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.

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.
1 ACCEPTED SOLUTION

Accepted Solutions
Cynthia_sas
SAS Super FREQ

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

4 REPLIES 4
LarryWorley
Fluorite | Level 6


Quentin,

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

Larry

Quentin
Super User

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.

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:

  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
Super User

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.

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