LISTSERV at the University of Georgia
Menubar Imagemap
Home Browse Manage Request Manuals Register
Previous messageNext messagePrevious in topicNext in topicPrevious by same authorNext by same authorPrevious page (June 2005, week 4)Back to main SAS-L pageJoin or leave SAS-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
Date:   Wed, 22 Jun 2005 10:35:24 -0600
Reply-To:   Alan Churchill <SASL001@SAVIAN.NET>
Sender:   "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:   Alan Churchill <SASL001@SAVIAN.NET>
Subject:   Re: Tagset.ExcelXP Help - Excel cannot open workbook
Comments:   To: "Terjeson, Mark (IM&R)" <Mterjeson@RUSSELL.COM>
In-Reply-To:   <16FD64291482A34F995D2AF14A5C932C44F069@MAIL002.prod.ds.russell.com>
Content-Type:   text/plain; charset="us-ascii"

Mark,

One final note on this issue. A while back, after getting frustrated with doing put statements, I wrote a Windows utility that handles the puts and quoting for you. Infile works great as well but just in case...

It is free and can be found here:

http://www.savian.net/utilities.aspx

It is called SasEncase.

Thanks, Alan

Savian "Bridging SAS and Microsoft Technologies" http://www.savian.net

-----Original Message----- From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Terjeson, Mark (IM&R) Sent: Wednesday, June 22, 2005 8:22 AM To: SAS-L@LISTSERV.UGA.EDU Subject: Re: Tagset.ExcelXP Help - Excel cannot open workbook

Hi John,

If you do find solutions for a template route for generating multi-sheet workbooks let us know.

One last parting thought. We went through the same exercise of spending a lot of time researching all the possible papers, articles, posts, etc., to find a couple hints of a template route that would provide multiple sheets, including asking these same questions on SAS-L, only to come up with no working asnwers for the platforms we are using. So after spending a lot of time, off and on for three weeks, and in the interest of time, trying the XML alternative had complete SAS generated multi-sheet Excel workbook generation in under three minutes. No joke. With my sample Excel multi-sheet workbook, hit Save-As, choose XML type, click OK. I have an editor that can search and replace the beginning of line with something as well as the end of line with something. Replacing the single quote characters with a macro variable (so I could have single quotes on the whole text line), I then replaced all lines of text with PUT ' on the front and '; on the end. Search and replaces took a total clock time of 15 seconds. Wrap these lines of text with a DATA statement and FILE statement and a RUN statement and the program runs from SAS generating a full Excel workbook multi-sheets and all.

Just wanted to suggest that you do not need to know XML to do this. All you need to know is how to PUT text character strings in SAS, which you probably already know how to do. Obviously, if you have a dataset that changes, you merely have to know how to do a little string replacement with the new values.

However, we'd like to know about a template solution if one surfaces as well.

Let us know if you need some more specifics or moral support. :o)

Hope this is helpful.

Mark Terjeson Senior Programmer Analyst, IM&R Russell Investment Group

Russell Global Leaders in Multi-Manager Investing

-----Original Message----- From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Gerstle, John Sent: Wednesday, June 22, 2005 7:43 AM To: SAS-L@LISTSERV.UGA.EDU Subject: Re: Tagset.ExcelXP Help - Excel cannot open workbook

Thanks Alan and Mark for your responses.

I agree that creating my own XML code would be ideal - though the learning curve is a little steep in terms of time and documentation. A lot to read and ingest and no time.

But still...for the problem I have run into - anyone who has used one of these tagsets and possibly run into a similar problem - any idea of a solution??

The place to start would be looking at the error messages and determining what's being said. "XML Error in Style"; "XML Error in Worksheet Setting"; - this suggests that the tagset (ExcelXP) is creating (possibly) faulty XML code. Or is it one of the options that I've selected? Or is it something within the Proc Report/Tabulate, in terms of style elements, that is causing the "Bad Values"?? (Of course, it could be Excel itself - though it is Excel XP/2003) [Original post way below the Sig line]

I've looked through the source code for ExcelXP - it's going to take some time to ingest. "Worksheet settings" I found but not "DataEmphasis style" (Bold?)

(BTW - anyone know what coding language is used for tagset source code? It's not the standard SAS syntax. Possibly C++?)

Well anyhoo...I wanted to explicitly post this issue for others that may run into this problem. The promise of combining worksheets into one workbook, while maintaining formats, is exciting. Of course, if one runs into too many difficulties, they will stop trying to use the tool. We need more discussion on how and when to use a tagset like this and what to do when/if there are problems. At the moment, the FAQ at the SAS website is not very useful for using tagsets. I guess I should pose this question there though.

John Gerstle, MS Biostatistician CDC Information Technological Support Contract (CITS) NCHSTP \DHAP \HICSB \Research, Analysis, and Evaluation Section Phone: 404-639-3980 Fax: 404-639-2980

>> -----Original Message----- >> From: Alan Churchill [mailto:SASL001@savian.net] >> Sent: Tuesday, June 21, 2005 5:53 PM >> To: Gerstle, John; SAS-L@LISTSERV.UGA.EDU >> Subject: RE: Tagset.ExcelXP Help - Excel cannot open workbook >> >> John, >> >> Consider rolling your own XML rather than having SAS gen the code. Others >> on >> here may be able to point you in a correct direction on ODS but my >> feeling is typically to have control myself over code generation when

>> it comes to >> the presentation layer. There are exceptions of course but when you hit >> snags, it is worth considering imo. >> >> Look at my postings on this subject (XML and Excel) over the last 2 weeks >> if >> you are interested. I also posted some papers on my website showing a

>> possible approach (screenshots included). >> >> Thanks, >> Alan >> >> Savian >> "Bridging SAS and Microsoft Technologies" http://www.savian.net >> >> -----Original Message----- >> From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of >> Gerstle, John >> Sent: Tuesday, June 21, 2005 2:24 PM >> To: SAS-L@LISTSERV.UGA.EDU >> Subject: Tagset.ExcelXP Help - Excel cannot open workbook >> >> I've been studying and trying to understand Tagsets and the like and >> have tried to use the ExcelXP Tagset to combine several outputs into one >> workbook. As you've gathered, I've run into problems. :-) >> >> The ExcelXP tagset was downloaded from: >> http://support.sas.com/rnd/base/topics/odsmarkup/ ; it's the May 2005 >> version. >> >> The code used: >> >> %include "C:\Tagsets\excltags.tpl"; /**Newest version May 2005**/ >> >> ods tagsets.ExcelXP >> path = "C:\Documents and Settings\My Documents" >> file = "Here and Now.xls" >> style = sas2excel >> options(orientation='landscape' >> embedded_titles='Yes' >> sheet_interval='None' >> sheet_name='Table 1'); >> %table1; >> ods tagsets.ExcelXP options(sheet_name='Table 2'); >> %table2; >> ods tagsets.ExcelXP options(sheet_name='Table 3'); >> %table3; >> ods tagsets.ExcelXP close; >> >> (The macros above, %table1,2,3, contain Proc Reports and Tabulates, and >> multiple tables. There are some style elements being applied within that >> code.) >> >> The SAS code runs fine with no Warnings or Errors in the LOG. The >> workbook is created. When I attempt to open it in Excel, a window >> pops-up stating there are problems opening the file. A Log file is >> created and is filled with an enormous amount of these error messages

>> (this is edited down to show only those unique messages): >> >> >> XML ERROR in Style >> REASON: Bad Value >> FILE: C:\Documents and Settings\My Documents\Here and Now.xls >> GROUP: Styles >> TAG: Style >> ATTRIB: Parent >> VALUE: DataEmphasis >> >> XML ERROR in Style >> REASON: Bad Value >> FILE: C:\Documents and Settings\My Documents\Here and Now.xls >> GROUP: Style >> TAG: Interior >> ATTRIB: Color >> VALUE: 01 >> >> XML ERROR in Worksheet Setting >> REASON: Illegal Tag >> FILE: C:\Documents and Settings\My Documents\Here and Now.xls >> GROUP: WorksheetOptions >> TAG: Layout >> >> XML ERROR in Worksheet Setting >> REASON: Illegal Tag >> FILE: C:\Documents and Settings\My Documents\Here and Now.xls >> GROUP: WorksheetOptions >> TAG: PageMargins >> >> XML ERROR in Table >> REASON: Bad Value >> FILE: C:\Documents and Settings\My Documents\Here and Now.xls >> GROUP: Table >> TAG: Row >> ATTRIB: Index >> VALUE: 695 /***(this value changes)***/ >> >> It looks to me that the errors are in assigning values within the XML

>> code. My question is where would I go to address these errors?? Is it >> possible that the STYLE I'm using for the output, SAS2EXCEL, may be >> causing some problems or the STYLE ELEMENTS in each of the table >> macros?? My gut says no, that it's in the generation of XML by the >> tagset. But I'm not sure where the problems may be residing. Maybe I

>> need to use other options of the tagset. >> >> Separately, the tables open correctly as HTML files in Excel. >> >> Any ideas or suggestions?? Thanks! >> >> John Gerstle, MS >> Biostatistician >> CDC Information Technological Support Contract (CITS) >> NCHSTP \DHAP \HICSB \Research, Analysis, and Evaluation Section >> Phone: 404-639-3980 >> Fax: 404-639-2980 >>


Back to: Top of message | Previous page | Main SAS-L page